3

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).

  • There is a mismatch in your code: randomschema vs. id.testtable. You need to provide the verbatim code you were using and your version of Postgres or we might not be able to help you. (users are in the group - how exactly?) Also comment on the arbitrary capitalization of your role names. – Erwin Brandstetter Apr 16 '16 at 13:39
  • I am on 9.4 and the schema already exists and the table is going to be a new table. I also have an existing schema and an existing table that this has to work for also. – Snyder Apr 16 '16 at 23:25
0

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.

|improve this answer|||||
  • that worked, apparently I was forgetting to put 'inherit in role foomaster' when creating the user, so it wasn't inheriting things down on that group role, in your example. I dropped the new user and created it with the following: CREATE ROLE user1 LOGIN PASSWORD '123' INHERIT IN ROLE newgroup; – Snyder Apr 18 '16 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.