CODEWITHSUNDEEP
postgresql
red888
red888

Reputation: 31704

Granting user membership to another role does not give it the same permissions to objects the other role owns

FYI I'm using google cloud sql postgres (postgres 13), but I don't think it is modifying default behavior.

My understanding was if I assign a user membership to a role that owns an object the user inherits the same permissions, but I am not seeing this.

-- login an user1, create a new table and confirm I can query it
CREATE TABLE test01 (did integer);
SELECT * FROM test01;
-- assign user2 member to the user1 role
GRANT "user2" TO "user1";

-- login as user2 and try to query
SELECT * FROM test01;
-- get a permissions error
ERROR:  permission denied for table test01

Is this expected behavior? Shouldn't user1 get all the permissions user2 has on the objects it owns?

Upvotes: 0

Views: 1728

Answers (2)

red888
red888

Reputation: 31704

This is very dumb, perhaps I should delete this post, but someone already attempted to answer it and perhaps this is helpful to another user.

I had the grant statement backwards.

GRANT "user1" TO "user2";

NOT

GRANT "user2" TO "user1";

I guess the language got me. I was thinking of it in terms of giving role X permission to role Y but your giving role Y membership to role X

Upvotes: 1

richyen
richyen

Reputation: 10078

You may want to opt to use ALTER ROLE to specify group membership (or include IN ROLE or IN GROUP when doing CREATE ROLE -- note that in Postgres, roles/groups are interchangeable).

Here's an example:

-- Create user1
postgres=# create role user1 LOGIN;
CREATE ROLE

-- Use user1 to create table
postgres=# \c postgres user1;
You are now connected to database "postgres" as user "user1".
postgres=> CREATE TABLE test01 (did integer);
CREATE TABLE
postgres=> SELECT * FROM test01;
 did 
-----
(0 rows)

-- Create user2, belonging to the group/role user1
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# create role user2 IN GROUP user1 LOGIN;
CREATE ROLE


-- Use user2 to select from the table
postgres=# \c postgres user2;
You are now connected to database "postgres" as user "user2".
postgres=> SELECT * FROM test01;
 did 
-----
(0 rows)

Upvotes: 1

Related Questions