Re: Unable to revoke insert privileges on a table

Can I define this "separate user" with superuser rights, and modify our clients connection to this user instead of the postgres user? Are there implications with pgpool or our standby server running streaming replication?

thanks Magnus
________________________________
From: Magnus Hagander [magnus(at)hagander(dot)net]
Sent: Wednesday, June 11, 2014 11:43 AM
To: Burgess, Freddie
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] Unable to revoke insert privileges on a table

On Wed, Jun 11, 2014 at 5:41 PM, Burgess, Freddie <FBurgess(at)radiantblue(dot)com<mailto:FBurgess(at)radiantblue(dot)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 revoking this privilege, I am still able to insert a row into a table

tapsdb=# REVOKE INSERT ON public.annotation from group public CASCADE;
REVOKE

tapsdb=# select * from information_schema.role_table_grants where grantee='postgres' and table_name = 'annotation';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
----------+----------+---------------+--------------+------------+----------------+--------------+----------------
postgres | postgres | tapsdb | public | annotation | SELECT | YES | YES
postgres | postgres | tapsdb | public | annotation | REFERENCES | YES | NO
postgres | postgres | tapsdb | public | annotation | TRIGGER | YES | NO
(3 rows)

tapsdb=# insert into annotation (uuid,uid,annotated_object_uuid,annotation_time) values (uuid_generate_v1(),1,uuid_generate_v1(),now()::timestamp without time zone);
INSERT 0 1
tapsdb=# select * from annotation;
uuid | uid | annotated_object_uuid | annotation_time | author | annotations | annotated_type_name
--------------------------------------+-----+--------------------------------------+----------------------------+--------+-------------+---------------------
522b9bde-eec5-11e3-81b9-f3afc2169573 | 1 | 522baf34-eec5-11e3-8335-1b4d52771c5f | 2014-06-07 21:28:20.563677 | | |

has_table_privilege function still returning Insert privileges

tapsdb=# 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 one you are looking up grantee information for), that's the superuser and it overrides all other permissions. You cannot restrict permissions from the user postgres. You need to use a separate user, and then you can use the postgres user to restrict permissions from the other user(s).

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/