Alter default privileges is not working for roles

From: Teju Jakkidi vlogs <teja(dot)jakkidi05(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Alter default privileges is not working for roles
Date: 2023-02-22 17:52:53
Message-ID: CAKA2XvY8sH6u8454AYjqd-zXA1B=aW=PcFfcbEoPiD6OzPEtSA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello Admins,

I have a below scenario set up where alter default privileges are not
working as expected:

db1 > schema1 > multiple users have full access to this schema and can
create objects. So, now the object's owners are different users. We
implemented triggers and functions to change the ownership of objects to a
role (ownerrole which has full access on the schema). When a user creates
objects, the trigger alters the object owner to the ownerrole.
So, when user1 or any user creates the objects, the objects are owned by
ownerrole as per the triggers.

Now we have roles defined as below:
write_role - grant select,insert,update,delete on all tables in schema
schema1 to write_role;
alter default privileges for role ownerrole in schema schema1 grant
select,insert,update,delete on tables to write_role;

We have assigned this role to user2. Now our expectation is that if there
is any table created by any user in schema1, user2 should be able to do DML
on the table as we have the alter default statement executed. But looks
like it is not granting privileges to the new objects created in the schema
and user2 is not able to perform any actions on the table.

Please let me know if I am missing anything here.

Thanks,
Teja. J.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2023-02-22 18:32:58 Re: Alter default privileges is not working for roles
Previous Message Alvaro Herrera 2023-02-22 17:15:51 Re: pg_dump: error