Re: Unable to revoke insert privileges on a table

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/