How to rotate non-admin user password on Azure PostgreSQL Flexible Server when asherpa_admin lacks CREATEROLE

Hi Kogu,

In Azure Database for PostgreSQL Flexible Server, you don't have true superuser access. If your server administrator (asherpa_admin) loses the CREATEROLE attribute, it usually means those permissions were accidentally revoked, or the role was changed to NOINHERIT.

Here are three ways to resolve this, in order from easiest to most robust.

1. The "SET ROLE" Solution

It's possible that asherpa_admin is still a member of the azure_pg_admin group, but the membership is set to NOINHERIT. If so, you have the permissions, but they aren't enabled by default when you log in.

Log in as asherpa_admin and try explicitly assuming the top administrator role before changing the password:

-- Assume the Azure admin role
SET ROLE azure_pg_admin;
-- Attempt to rotate the password
ALTER USER "asherpa-func" PASSWORD 'your_new_password_here';
-- Reset back to your normal session role
RESET ROLE;

If the SET ROLE command returns a "permission denied" error, proceed to step 2.

2. The "Reset Password" Trick on Azure Portal (Repair Mechanism)

If the asherpa_admin user has completely lost the CREATEROLE attribute or membership in azure_pg_admin, you cannot fix this from within PostgreSQL because you lack the privileges to elevate their authority.

However, you can use Azure Control Plane to repair the user. Changing the administrator password through Azure Portal (or Azure CLI) not only changes the password but also runs a backend script to restart the administrator user, reapplying the azure_pg_admin membership and CREATEROLE attribute.

• Access Azure Portal.
• Navigate to your Azure Database for PostgreSQL Flexible Server.
• In the Settings menu on the left, click Authentication.
• Find the Administrator credentials section.
• Enter a new password for asherpa_admin and save it.
• Log back into the database using the asherpa_admin account and the new password.

Your asherpa_admin user will now display the CREATEROLE property when you inspect pg_roles, and you will be able to run the ALTER USER command for asherpa-func.

3. Use the Microsoft Entra ID Admin

If you have Microsoft Entra ID authentication enabled on this Flexible Server, you have a backdoor.

The designated Entra ID Administrator automatically acts as an azure_pg_admin with full CREATEROLE privileges.

1. Log into the database using the Entra ID Administrator account (using an access token).
2. Once logged in, you can directly rotate the application user's password:

ALTER USER "asherpa-func" PASSWORD 'your_new_password_here';

3. Alternatively, you can use the Entra ID administrator to permanently fix your local administration issues:

GRANT azure_pg_admin TO asherpa_admin;   
ALTER ROLE asherpa_admin CREATEROLE;