Re: Unable to revoke insert privileges on a table

On Wed, Jun 11, 2014 at 5:41 PM, Burgess, Freddie <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/