Upgrade failed on Azure Database for PostgreSQL flexible server

Question

Dear,

While performing an upgrade of PostgreSQL Database from version 16.10 to 17 we have encountered an issue unexpected error without any additional information.

Before attempting the upgrade we have performed following actions as per Microsoft documentation:

• Manually disable all of the extensions that were active (From server parameters and from database level)
• Dropped all of the event triggers on all databases that are on instance

Could you please assist us further since from the summary we are not able to determine what is the issue and how to proceed so upgrade would be successful.

Please let me know in case of any additional questions.

Kind regards,
Goran

Answer 1

Hello Goran Kuga

I have discussed with internal product team, and they confirmed that the error you're getting is related to a known permissions issue introduced with PostgreSQL 16, where role grants with ADMIN OPTION can fail during major version upgrades due to stricter role validation. 

We have tried to mitigate this issue and requesting you to please perform the upgrade operation on the server and do let me know the update.

Please execute this on the server, If it returns something - Major version upgrade will fail.

SELECT m.rolname AS role_name, r.rolname AS member_of, g.rolname AS grantor FROM pg_catalog.pg_roles m JOIN pg_catalog.pg_auth_members pam ON (pam.member = m.oid) LEFT JOIN pg_catalog.pg_roles r ON (pam.roleid = r.oid) LEFT JOIN pg_catalog.pg_roles g ON (pam.grantor = g.oid) WHERE m.rolname !~ '^pg_' and g.oid <> 10 AND g.rolname != 'azure_pg_admin' AND pg_has_role(g.oid, 'azure_pg_admin', 'SET');