CODEWITHSUNDEEP
databasepostgresqlrolesprivileges
meirben
meirben

Reputation: 1

Postgresql - user does not inherit table privileges

Here is my problem : I have created a role R1 that have all privileges on schema S1. I also have created two users U1, U2, with inherit, that both are members of R1. U1 has created a table in S1. Why U2 can't query this table ?

PS C:> psql -h ora05lw -U dba -d kuku psql (16.0, server 13.3)

kuku=# create role r1 with login; CREATE ROLE kuku=# create user u1 with password 'aaaaaa'; CREATE ROLE kuku=# create user u2 with password 'aaaaaa'; CREATE ROLE kuku=# grant r1 to u1; GRANT ROLE kuku=# grant r1 to u2; GRANT ROLE kuku=# create schema s1; CREATE SCHEMA kuku=# grant all privileges on schema s1 to r1; GRANT kuku=# ALTER DEFAULT PRIVILEGES IN SCHEMA s1 GRANT SELECT ON TABLES TO r1; ALTER DEFAULT PRIVILEGES kuku=# \q PS C:> psql -h ora05lw -U u1 -d kuku psql (16.0, server 13.3)

kuku=> create table s1.kuku (one integer);
CREATE TABLE
kuku=> \q
PS C:\> psql -h ora05lw -U u2 -d kuku
psql (16.0, server 13.3)

kuku=> select * from "s1"."kuku";
ERROR:  permission denied for table kuku
kuku=> \q

Upvotes: -1

Views: 37

Answers (2)

Dhinesh kumar P
Dhinesh kumar P

Reputation: 21

Granting Full Access to the Schema:

GRANT USAGE ON SCHEMA s1 TO u2;

Upvotes: 0

Rodolphe
Rodolphe

Reputation: 858

When you create the DEFAULT PRIVILEGES you are connecte as "dba" so the privileges will be applie to objects created by dba not for objects created by "u1" this is visible when you do a \ddp command

Upvotes: 0

Related Questions