Permission denied to ALTER DEFAULT PRIVILEGES

Question

I'm trying to alter the default privileges of a role (roleB) that I am a member of, to allow it to modify objects created by another role (roleA) that I am also a member of.

I have the inherit attribute set on my role.

This is on a PostgreSQL 16 instance in Azure (Azure Database for PostgreSQL flexible server).

Example errors:

Cannot execute SQL

ALTER DEFAULT PRIVILEGES FOR ROLE "roleA" REVOKE ALL ON SEQUENCES FROM "roleB";
ALTER DEFAULT PRIVILEGES FOR ROLE "roleA" GRANT USAGE,UPDATE,SELECT ON SEQUENCES TO "roleB";

permission denied to change default privileges

Answer 1

Hi gpatounas •,

Thanks for your patience.

ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.) Privileges can be set globally (i.e., for all objects created in the current database), or just for objects created in specified schemas.

While you can change your own default privileges and the defaults of roles that you are a member of, at object creation time, new object permissions are only affected by the default privileges of the current role and are not inherited from any roles in which the current role is a member.

The default privileges for any object type normally grant all grantable permissions to the object owner and may grant some privileges to PUBLIC as well. However, this behavior can be changed by altering the global default privileges with ALTER DEFAULT PRIVILEGES.

Refer to similar posts here which could be helpful in your case:

https://dba.stackexchange.com/questions/186797/alter-default-privileges-permission-denied

Refer to comments in this Q&A post:

https://learn.microsoft.com/en-us/answers/questions/852935/azure-postgresql-flexible-server-admin-user-permis

Let us know if this helps or your ask is different to what I understood.

Thanks.