Can not manage roles in v16 that where created in v15

Question

(There are already some similar questions, but none of them provide an answer.)

We have a Postgresql Flexible Server v16, upgraded from v15. We have the admin role "admin" with the azure_pg_admin role, and a large amount of roles created by "admin", each of them is the owner of a database.

Since v16, a role with CREATEROLE can no longer alter other roles, only if that role is granted to the current rule with the admin option. This is the default in v16, but wasn't in v15 so "admin" does not have admin for any of the other roles that where created before upgrading.

The suggested fix is to just grand it:

GRANT "other_user" TO "admin" WITH ADMIN TRUE, SET FALSE, INHERIT FALSE

But this does not work, since in order to grand admin for a role, you need admin for that role:

[42501] ERROR: permission denied to grant role "other_user" Detail: 
Only roles with the ADMIN option on role "other_user" may grant this role.

The easy solution would be to do this as a superuser. But azure does not allow superuser access, only the build on azure_pg_admin role, which only has CREATEROLE and CREATEDB.

Another fix that was suggested, but does not work:

"Create a new role, assign the owner of the database to that role, delete the old role."

This does not work, since the "admin" role does not have inherit or set for "other_role", so it can not change the owner. It can not give itself inherit or set, since that requires admin for that role. It can not give itself admin, since that also requires admin.

It also does not work because you would not be able to drop the old role, that also requires admin for that role.

This would be a super easy fix with superuser, but that is not possible on azure. So right now we are stuck, can't go back to v15, can't manage our roles and databases. Creating and managing new roles works fine, but managing old roles does not.

Is there any way to fix this without having to re-create the entire server and migrating all data, with downtime?

Answer 1

@Dino

Thank you for reaching out about the upgrade issue. We're aware of this issue and a fix has been already rolled out. Since your upgrade was completed before our general availability (Pre-GA), here are your options:

1. Wait for the Deployment: This fix is in progress and will resolve the issue once deployed.
2. Immediate Assistance: Alternatively, If the issue is urgent, we can connect you directly with our Support engineering team to assist in deploying the fix proactively.

Thanks a lot!

Regards,

Oury