CODEWITHSUNDEEP
postgresqlpermissions
Stepan
Stepan

Reputation: 1054

How can db owner grant select permission on other users table in postgres?

all. I have the following problem with postgresql.

I have a database "dwh" with schema "sa".

I have a user "dwa" who is the owner of database "dwh".

select pg_get_userbyid(datdba) dbowner
from pg_database where datname='dwh';
-- dwa

Another user "user1" created table "table1" in schema "sa".

Now when I'm trying (as "dwa") to grant permission to select from table1 I get an error:

grant select on sa.table1 to dwa;

[42501] ERROR: permission denied for table table1

Can user "dwa" (db owner) grant permission to select from table1 and how to do it?

Upvotes: 0

Views: 1929

Answers (2)

pifor
pifor

Reputation: 7892

You can try to run as user1 owner of schema sa and table table1:

grant usage on schema sa to dwa;
grant all privileges on all tables in schema sa to dwa with grant option;

But it is unusual to grant privilege to oneself.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 248215

To GRANT privileges on an object you need to fulfill at least one of three conditions:

  • you are a superuser

  • you own the object (or are a member of the role that owns it)

  • you have been granted the privilege WITH GRANT OPTION

Ownership of the database doesn't give you any privileges on objects within the database!

Upvotes: 3

Related Questions