PostgreSQL 11 -> 16 major version upgrade blocked by legacy replication role - permission denied to alter restricted role

Question

PostgreSQL 11 -> 16 major version upgrade blocked by legacy replication role - permission denied to alter restricted role

Julia 0

I'm trying to upgrade an Azure PostgreSQL Flexible Server from version 11.22 to 16 using the built-in major version upgrade feature. The server was originally a Single Server that was automigrated to Flexible Server.

The upgrade precheck fails with:

"Upgrading with password authentication mode enabled is not allowed from source version MajorVersion11. Please enable SCRAM and reset the passwords prior to retrying the upgrade."

I've confirmed SCRAM is enabled (password_encryption = scram-sha-256 server parameter is set) and I've reset the passwords for all application roles. However, there is a legacy replication role left over from the automigration with these attributes: rolsuper=false, rolcanlogin=true, rolreplication=true, rolbypassrls=false

This role still has an MD5-stored password and I cannot reset it. Every attempt fails:

ALTER USER replication WITH PASSWORD 'newpassword';
-- ERROR: permission denied to alter restricted role 'replication'

ALTER ROLE replication NOREPLICATION;
-- ERROR: permission denied to alter restricted role 'replication'

DROP ROLE replication;
-- ERROR: permission denied to alter restricted role 'replication'

My admin user has the azure_pg_admin role. None of the above operations are permitted regardless.

Question: Is there a way - via Azure CLI, REST API, or any other mechanism - to reset the password or disable this legacy replication role so the upgrade precheck passes? Or is this a known automigration artifact that Microsoft needs to resolve on the backend?

0 comments

1 answer

Your answer

Answer 1

The behavior is consistent with the new role-based security model and upgrade rules for Azure Database for PostgreSQL Flexible Server, especially for servers upgrading from PostgreSQL 11.

For upgrades from PostgreSQL 11 to a higher version, the documented requirement is:

"If you're performing the upgrade from PG11 to a higher version, you must first configure your flexible server to use SCRAM authentication by enabling SCRAM and resetting all login-role passwords."[1]

However, restricted/system-managed roles such as azure_pg_admin and other internal roles cannot be altered by customers:

"the azure_pg_admin role is a system-managed, restricted role and cannot be modified by users. Attempts to alter it ... will result in an error like: ERROR: permission denied to alter restricted role"[0]

This same pattern applies to other restricted roles: they are controlled by the platform and not changeable via SQL, CLI, or REST by customers. The error messages seen when trying to alter or drop replication match this behavior.

There is no documented mechanism in Azure CLI, REST API, or SQL for customers to:

reset passwords,
change attributes, or
drop restricted roles.

In addition, PostgreSQL 16 introduces stricter role-based security and hierarchy rules, and Azure extends azure_pg_admin to manage nonrestricted roles and objects, not restricted/system roles:[0][3]

"members of the azure_pg_admin role can manage roles and access objects owned by any nonrestricted role"[0]

Because the upgrade requirement explicitly demands that all login-role passwords be reset for PG11→higher upgrades, and restricted roles cannot be altered by customers, this scenario falls outside what can be remediated from the customer side. The replication role with an MD5 password that cannot be changed is effectively a platform artifact.

The only viable path is to engage Microsoft support so that the backend team can remediate or adjust this restricted role (or the upgrade validation) on the service side. There is no supported self-service method in the available documentation to bypass or modify a restricted role to satisfy the SCRAM precheck.

References:

Share via

PostgreSQL 11 -> 16 major version upgrade blocked by legacy replication role - permission denied to alter restricted role

1 answer

Your answer