CODEWITHSUNDEEP
postgresqlpermissionssql-grant
Richard PB
Richard PB

Reputation: 55

Postgres GRANT ROLE of db owner to other another user not doing what expected. What am I missing?

I am a bit baffled here. Using pg 9.6

CREATE USER user1;
CREATE DATABASE mydb with OWNER user1;
CREATE TABLE x ...;
CREATE USER user2; 
GRANT user1 to user2;

Login as user2.

INSERT INTO TABLE x ...;
CREATE TABLE z ...;

All is good up to here.

Login as user1 (DB owner).

SELECT FROM z; -- fails; table is owned by user2

Shouldn't the db owner have access to ALL objects?

Also, if I:

CREATE USER user3;
GRANT user1 /* db owner */ TO user3;

I cannot access objects created by user2 even though user3 has role granted from DB owner.

Upvotes: 1

Views: 4495

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246483

Permissions on a database have nothing to do with permissions on other objects in the database, so you can be denied access to database objects even if you are database owner.

Ownership of a database means that you have all privileges on the database object itself and can grant them to others: CONNECT to the database, CREATE schemas and TEMPORARY tables. Furthermore, you can ALTER and DROP the database.

If you grant the database owner to another user, that user gets all the permissions of the database owner, but not the right to ALTER or DROP the database (unless SET ROLE is used to “become” the database owner).

Upvotes: 2

Victor Di Leo
Victor Di Leo

Reputation: 82

GRANT comes in two forms: grant on database objects and grant on roles, and they are quite different:

9.1 docs for GRANT

The last line in the syntax section is the GRANT you are attempting. Notice the grant is on a role, not a user, but in PostgreSQL a role is a user:

"A role can be thought of as either a database user, or a group of database users, depending on how the role is set up".

roles - 9.1 docs

This serverfault link might help: postgresql-what-does-grant-all-privileges-on-database-do

Upvotes: 2

Related Questions