PostgreSQL – Group Permissions Not Inheriting to Table Level

permissionspostgresqlroleSecurity

I am moving to a group role from a lot of individual users and have one table that needs to have permissions SELECT, INSERT only.

Lets say I have:

user1
user2
newgroup

Both of the users are in the group and both users have INHERIT as well as the Group has INHERIT on it.

I have added 

GRANT USAGE ON SCHEMA testschema TO newgroup;
GRANT user1 to newgroup;
GRANT user2 to newgroup;

as well as 

GRANT SELECT, INSERT ON testschema.testtable TO newgroup;

When I log in as one of the new users, I do not have permission to that table(ex: testschema.testtable)

I look at the PSQL \z and see for that table newgroup has ar permissions.

I am getting permission denied for relation testschema.testtable. I do not want to create more of a mess by individually putting permissions on the table (it works if I do that currently).

Best Answer

The simple test:

postgres=# create schema foo;
CREATE SCHEMA
postgres=# create table foo.t(x int);
CREATE TABLE
postgres=# create role foomaster nosuperuser nologin inherit;
CREATE ROLE
postgres=# create role fooslave password '111' login inherit in role foomaster;
CREATE ROLE
postgres=# grant usage on schema foo to foomaster;
GRANT
postgres=# set role fooslave;
SET
postgres=> select * from foo.t;
ERROR:  permission denied for relation t
postgres=> set role postgres;
SET
postgres=# grant select on foo.t to foomaster;
GRANT
postgres=# set role fooslave;
SET
postgres=> select * from foo.t;
 x 
---
(0 rows)

It seems that you are doing something wrong.

Upd: About newely created table:

postgres=> set role postgres;
SET
postgres=# create table foo.tt(xx int);
CREATE TABLE
postgres=# set role fooslave;
SET
postgres=> select * from foo.tt;
ERROR:  permission denied for relation tt
postgres=> set role postgres;
SET
postgres=# grant select on foo.tt to foomaster;
GRANT
postgres=# set role fooslave;
SET
postgres=> select * from  foo.tt;
 xx 
----
(0 rows)

PPS: Hope that my investigations was helpful at least for somebody.

Related Question