Re: Unable to revoke insert privileges on a table
От | Burgess, Freddie |
---|---|
Тема | Re: Unable to revoke insert privileges on a table |
Дата | |
Msg-id | 3BBE635F64E28D4C899377A61DAA9FE02E2EB54B@NBSVR-MAIL01.radiantblue.local обсуждение исходный текст |
Ответ на | Re: Unable to revoke insert privileges on a table (Magnus Hagander <magnus@hagander.net>) |
Ответы |
Re: Unable to revoke insert privileges on a table
|
Список | pgsql-bugs |
Can I define this "separate user" with superuser rights, and modify our cli= ents connection to this user instead of the postgres user? Are there implic= ations with pgpool or our standby server running streaming replication? thanks Magnus ________________________________ From: Magnus Hagander [magnus@hagander.net] Sent: Wednesday, June 11, 2014 11:43 AM To: Burgess, Freddie Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] Unable to revoke insert privileges on a table On Wed, Jun 11, 2014 at 5:41 PM, Burgess, Freddie <FBurgess@radiantblue.com= <mailto:FBurgess@radiantblue.com>> wrote: PostgreSQL version: 9.3.4 Operating system: RHEL 6.4 Description: I need to be able to set individual tables into read-only mode. Despite rev= oking this privilege, I am still able to insert a row into a table tapsdb=3D# REVOKE INSERT ON public.annotation from group public CASCADE; REVOKE tapsdb=3D# select * from information_schema.role_table_grants where grantee= =3D'postgres' and table_name =3D 'annotation'; grantor | grantee | table_catalog | table_schema | table_name | privileg= e_type | is_grantable | with_hierarchy ----------+----------+---------------+--------------+------------+---------= -------+--------------+---------------- postgres | postgres | tapsdb | public | annotation | SELECT = | YES | YES postgres | postgres | tapsdb | public | annotation | REFERENC= ES | YES | NO postgres | postgres | tapsdb | public | annotation | TRIGGER = | YES | NO (3 rows) tapsdb=3D# insert into annotation (uuid,uid,annotated_object_uuid,annotatio= n_time) values (uuid_generate_v1(),1,uuid_generate_v1(),now()::timestamp wi= thout time zone); INSERT 0 1 tapsdb=3D# select * from annotation; uuid | uid | annotated_object_uuid = | annotation_time | author | annotations | annotated_typ= e_name --------------------------------------+-----+------------------------------= --------+----------------------------+--------+-------------+--------------= ------- 522b9bde-eec5-11e3-81b9-f3afc2169573 | 1 | 522baf34-eec5-11e3-8335-1b4d5= 2771c5f | 2014-06-07 21:28:20.563677 | | | has_table_privilege function still returning Insert privileges tapsdb=3D# SELECT has_table_privilege('public.annotation','INSERT'); has_table_privilege --------------------- t (1 row) Is this a bug? or I'm I doing something wrong. Well, you're not showing which user you are actually making the connection = as. But assuing you are running as the user "postgres" (since that's the on= e you are looking up grantee information for), that's the superuser and it = overrides all other permissions. You cannot restrict permissions from the u= ser postgres. You need to use a separate user, and then you can use the pos= tgres user to restrict permissions from the other user(s). -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
В списке pgsql-bugs по дате отправления: