Unable to revoke insert privileges on a table

From: "Burgess, Freddie" <FBurgess(at)Radiantblue(dot)com>
To: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Unable to revoke insert privileges on a table
Date: 2014-06-11 15:41:00
Message-ID: 3BBE635F64E28D4C899377A61DAA9FE02E2E9531@NBSVR-MAIL01.radiantblue.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

thanks

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Magnus Hagander 2014-06-11 15:43:08 Re: Unable to revoke insert privileges on a table
Previous Message jacek.zalewski 2014-06-11 14:05:26 BUG #10611: pg_restore incorrectly restores DB dump (plain and tar format) containing LARGE OBJECTS witdh commen