Question 421158 in PostgreSQL Question posted in
The official documentation can be found here.

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
Tags:

2

Answers


  1. 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

    Login or Signup to reply.
  2. 0

    Granting Full Access to the Schema:

    GRANT USAGE ON SCHEMA s1 TO u2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search