Alter default privileges is not working for roles

[Teju Jakkidi vlogs <teja.jakkidi05@xxxxxxxxx>]

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.