Postgresql - user does not inherit table privileges

Question

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

Rodolphe · Accepted Answer · 2024-12-23 11:15:08Z

0

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

answered Dec 23, 2024 at 11:15

Rodolphe

8584 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

meirben Dec 23, 2024 at 17:00

Hello Rodolphe ! trying what you said ...

meirben Dec 23, 2024 at 17:03

psql -h ora05lw -U u1 -d kuku # ALTER DEFAULT PRIVILEGES IN SCHEMA s1 GRANT SELECT ON TABLES TO r1; # psql -h ora05lw -U u2 -d kuku # select * from s1.kuku; I get permission denied ...

Dhinesh kumar P · Accepted Answer · 2025-01-09 05:04:19Z

0

Granting Full Access to the Schema:

GRANT USAGE ON SCHEMA s1 TO u2;

answered Jan 9 at 5:04

Dhinesh kumar P

212 bronze badges

Collectives™ on Stack Overflow

Postgresql - user does not inherit table privileges

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related