Cannot drop admin role which is role grantor because the role grantor cannot be changed.

Question

When I create new role/group in Azure Database for PostgreSQL flexible server 16 under a workload identity admin (admin_wi) and grant the role to another user

CREATE ROLE testrole;

GRANT testrole TO azure_pg_admin WITH ADMIN OPTION;
the role details are following

SELECT roleid::regrole, member::regrole, grantor::regrole, admin_option FROM pg_auth_members WHERE roleid IN (SELECT oid FROM pg_catalog.pg_roles WHERE rolname = 'testrole');

"roleid"	"member"			"grantor"	"admin_option"
----------------------------------------------------------
"testrole"	"admin_wi"			"azuresu"	true
"testrole"	"azure_pg_admin"	"admin_wi"	true

The problem is that the grantor cannot be changed. And because of that I cannot drop the admin_wi user. How can I work around this?

Why I need it?

I need for disaster recovery scenario. I would like to grant all ownership and rights to group and grant more users to the same group. The workload identity I have is based on in AKS/ServiceAccount. I would like to be able to recreate it, but that is not possible because it is a role grantor. I want to delete admin but keep the group.

All other resources - tables, diagrams, functions can be solved using ownership. Now it seems that you can never drop the admin user under which you create the role. I haven't figured out how to solve it.

Answer 1

@Tomáš Krečmer

Thank you for being patient while checking on this.

The issue is due to PostgreSQL 16 changes to role permissions. In PostgreSQL 16, the grantor of a role cannot be changed, and you can't drop admin_wi as it's still the grantor of test role. Here's a workaround you can follow:

Workaround:

1.Revoke & Regrant Role:

REVOKE testrole FROM azure_pg_admin;

GRANT testrole TO azure_pg_admin WITH ADMIN OPTION;

2. Verify the Grantor:

SELECT roleid::regrole, member::regrole, grantor::regrole, admin_option

FROM pg_auth_members

WHERE roleid = 'testrole'::regrole;

3. Drop admin_wi:

DROP ROLE admin_wi;

After reassigning, you should be able to drop admin_wi.

 After reassigning the grantor, you should be able to drop admin_wi. If you need more details on these changes, you can refer to the PostgreSQL 16 Release Notes.

Please let us know the result.

Regards,

Oury

Answer 2

It does not work. This command
GRANT testrole TO azure_pg_admin WITH ADMIN OPTION;is exactly the one I have in the description of the question. After the run admin_wi will be grantor for testrole.

"roleid" "member" "grantor" "admin_option" 
---------------------------------------------------------- 
"testrole" "azure_pg_admin" "admin_wi" true

and drop fails

Failed to drop Azure AD Principal. Reason - 2BP01: role "admin_wi" cannot be dropped because some objects depend on it