Thread: Unable to revoke insert privileges on a table

Unable to revoke insert privileges on a table

From

"Burgess, Freddie"

Date:

11 June 2014, 15:41:13

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.

thanks

Re: Unable to revoke insert privileges on a table

From

Magnus Hagander

Date:

11 June 2014, 15:43:17

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

Re: Unable to revoke insert privileges on a table

From

"Burgess, Freddie"

Date:

11 June 2014, 16:03:12

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/

Re: Unable to revoke insert privileges on a table

From

Magnus Hagander

Date:

11 June 2014, 16:11:13

On Wed, Jun 11, 2014 at 6:03 PM, Burgess, Freddie <FBurgess@radiantblue.com>
wrote:

>  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?
>

No, as long as the user has superuser rights, it will bypass all
permissions checks in the whole system. If you want to be able to reduce
permissions, you need to use a user that's not a superuser.

There should be no implications on replication at all.

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