Remove stale principal from Azure PostgreSQL flexible server database

Question

Our Azure PostgreSQL Flexible Server has a stale AAD principal mapping created with pgaadauth_create_principal(). The corresponding Managed Identity was deleted and recreated, so its Object ID changed. We cannot drop or update the principal because pgaadauth_drop_principal() and the pgaadauth schema aren’t exposed on Flexible Server. the documentation states three possibilities to drop the principal:

• The Azure portal
• The Azure Resource Manager (ARM) API
• By executing the following SQL statement:
  DROP ROLE rolename;

We tried the "Portal" and the "drop role" approach, but in the portal I can only see the super user account (which is not what we want to drop) and when issuing the SQL drop role statement we get a " ERROR: role "<principal-name>" does not exist " error.

Now we are lost. How can we remove this stale principal ?

Answer 1

You cannot remove stale AAD principals directly on azure PostgreSQL Flexible Server once the Managed Identity is deleted, since the internal mapping remains orphaned. The only reliable workaround is to recreate the server or restore from a backup prior to the mapping.

Azure PostgreSQL Flexible server does not expose pgaadauth_drop_principal() or any system catalog access to manually clean up orphaned entries. As of now, microsoft does not provide a direct method to remove stale AAD principals in Flexible Server once the associated identity is gone.

Limited but recommended actions:

1. Open a Microsoft support ticket -> request backend cleanup of the stale principal (they can run internal scripts).

As a workaround, recreate the Flexible Server or restore to a new one and reapply valid principals.

There is currently no user-accessible command or API to remove the orphaned AAD principal from Flexible Server.

Answer 2

Hi Arndt, Lothar (001) (EXT),

Thank you for reaching on Microsoft Q&A!

Can you please give the error screenshot and the query you have executed so that we can guide in right direction.

Can you try running the following command to get the details.

az postgres flexible-server microsoft-entra-admin list --resource-group <YourResourceGroup> --server-name <YourServerName> --output table

if you can see the old object ID you can try deleting that by executing the command let me know if you are getting any issues.

az postgres flexible-server microsoft-entra-admin delete --resource-group <YourResourceGroup> --server-name <YourServerName> --object-id <OldStaleObjectID> --yes