After Postgresql flexible migration from 14 to 16 admin user can't ALTER other role anymore

Question

Since migrating my flexible server to postgresql version 16 my admin user can't modify roles anymore.
With version 16 it's mandatory to have WITH ADMIN OPTION on roles to be able to alter them. My problem is that it wasn't the case on version 14 and my admin user doesn't have this option before the migration.
The problem is that we can't add this option after the migration andit is not possible to rollback to version 14 of postgresql. Although in Azure it's not possible to log as a superuser so I'm really blocked right now.

Does anyone has encouter the same issue with migration to postgresql 16 and do you have a solution or workaround for this problem.

Thanks for your help.

Answer 1

@Davi, Thomas Thanks for sharing additional information, yes this is part of Postgres 16 limitation which you can go through here https://www.postgresql.org/docs/16/release-16.html

To resolve your issue, you can modify the role as shown sample below, let me know if you find any issue.

Regards

Geetha

Answer 2

Hey @GeethaThatipatri-MSFT , we are having the exact same problem.

We had a bunch of accounts that were created in postgres 14 and we then upgraded to 16.

Our admins only have INHERIT and SET over those roles and thus we can't, for example, drop them.

I've tried what you suggested above but I couldn't do much with it.

Can you explain it more thoroughly or provide any other solution?

Perimissions example:

 psql_my_admin                        | service_account_staging                    | INHERIT, SET        | azuresu

and I can't do anything on the role:

GRANT service_account_staging TO azureuser; ERROR:  permission denied to grant role "service_account_staging" DETAIL:  Only roles with the ADMIN option on role "service_account_staging" may grant this role.

Thanks