Обсуждение: Alter default privileges is not working for roles
Hello Admins,
Please let me know if I am missing anything here.
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.
On Wed, Feb 22, 2023 at 10:53 AM Teju Jakkidi vlogs <teja.jakkidi05@gmail.com> wrote:
alter default privileges for role ownerroleNow our expectation is that if there is any table created by any user
The specification of "ownerrole" in that command means that said default privileges only are created if "ownerrole" is the one creating the objects - not "created by any user".
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Feb 22, 2023 at 10:53 AM Teju Jakkidi vlogs <
> teja.jakkidi05@gmail.com> wrote:
>> alter default privileges for role ownerrole
>> Now our expectation is that if there is any table created by any user
> The specification of "ownerrole" in that command means that said default
> privileges only are created if "ownerrole" is the one creating the objects
> - not "created by any user".
It's hard to say for sure when we've been shown no details, but
I suspect what the OP is describing is event triggers that issue
ALTER OWNER commands after the fact. The default privileges
mechanism is only applied at the instant of object creation;
we do not want ALTER OWNER or SET SCHEMA or similar commands
having random side-effects on existing privileges.
regards, tom lane