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
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
2