pgaadauth extension not allow-listed for azure_pg_admin users - how to create Entra ID-linked PostgreSQL roles?

Question

pgaadauth extension not allow-listed for azure_pg_admin users - how to create Entra ID-linked PostgreSQL roles?

Arjan Hoekstra 20

We are unable to create Microsoft Entra ID-linked PostgreSQL roles using pgaadauth_create_principal(...) on our Azure Database for PostgreSQL Flexible Server (Spain Central region). The server is configured for Microsoft Entra authentication only (passwordAuth = "Disabled", activeDirectoryAuth = "Enabled").

Calling pgaadauth_create_principal(...) fails with:

ERROR: function pgaadauth_create_principal(unknown, boolean, boolean) does not exist

The pgaadauth extension is listed in pg_available_extensions (the files are physically present on the server), but CREATE EXTENSION pgaadauth; fails with:

ERROR: extension "pgaadauth" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL

Adding pgaadauth to the server's azure.extensions allow-list parameter via CLI also fails (ServerParameterToCMSUnAllowedParameterValue). PGAADAUTH is not among the accepted values for this server.

Troubleshooting already performed: Confirmed the connecting user is the registered Microsoft Entra Administrator and a member of the azure_pg_admin role. Confirmed Microsoft Entra authentication is correctly enabled on the server. Restarted the server, with no effect (identical error). Removed and re-added the Microsoft Entra administrator via the Portal Authentication blade, two saved changes, with no effect (identical error). Attempted to add pgaadauth to azure.extensions directly via CLI, which was rejected as an unsupported value.

Is there a known workaround to create Entra ID-linked PostgreSQL roles on a Flexible Server when pgaadauth is blocked like this? Or is this a hard platform restriction for the Spain Central region that requires Microsoft to allow-list it first? I have also filed a feature request for this: https://feedback.azure.com/d365community/idea/0dd86cfe-7c9f-f111-85ce-000d3ae4ded4

0 comments

Answer accepted by question author

AI answer

pgaadauth is not something to manually allow-list and install as a normal extension on Flexible Server.

Supported ways to create Microsoft Entra-linked PostgreSQL roles are:

Create a role by Microsoft Entra principal name:

select * from pg_catalog.pgaadauth_create_principal(roleName text, isAdmin boolean, isMfa boolean)

roleName must match the Microsoft Entra principal.
isAdmin = true creates an admin user.
isAdmin = false creates a regular PostgreSQL user.

Create a role by Microsoft Entra object ID:

select * from pg_catalog.pgaadauth_create_principal_with_oid(roleName text, objectId text, objectType text, isAdmin boolean, isMfa boolean)

objectType must be user, group, or service.

Enable Microsoft Entra authentication for an existing PostgreSQL role by assigning the security label:

SECURITY LABEL for "pgaadauth" on role "<roleName>" is 'aadauth,oid=<objectId>,type=<objectType>,admin';

objectType must be user, group, or service.
Include admin only when the role should manage other Microsoft Entra roles.

Important supported behavior from the documentation:

Only a Microsoft Entra administrator can initially connect by using a Microsoft Entra account.
That administrator can then configure subsequent Microsoft Entra database users.
For managed identity scenarios, pgaadauth_create_principal must be run on the postgres database. If "No function matches..." appears, connect to postgres, not another database.

Based on the documented behavior, this is not described as a regional platform restriction or a requirement to manually allow-list pgaadauth in azure.extensions. The documented model is to use the built-in pgaadauth_* functions or the SECURITY LABEL mapping, not CREATE EXTENSION pgaadauth.

For the specific function pgaadauth_create_principal(...) does not exist error, the documented check is to ensure the session is connected to the postgres database before running the function.

References:

AI-generated content may be incorrect. Read our transparency notes for more information.

1 additional answer

Your answer

Answer 1

Hi @Arjan Hoekstra ,

Thank you for reaching out to the Microsoft Q&A forum.

The pgaadauth component is managed by Azure Database for PostgreSQL Flexible Server. It should not be manually installed using CREATE EXTENSION or added to the azure.extensions allow-list.

First, confirm that you are connected to the postgres database, because pgaadauth_create_principal() must be executed from that database.

Run:

SELECT current_database();

It should return:

postgres

Then retry:

SELECT *
FROM pg_catalog.pgaadauth_create_principal(
    '<Entra-principal-name>',
    false,
    false
);

You can also check whether the pgaadAuth functions are available:

SELECT version();

SELECT current_database();

SELECT proname
FROM pg_catalog.pg_proc
WHERE proname LIKE 'pgaadauth%';

If the functions are still unavailable while connected to the postgres database, then this is not something that can be fixed by adding pgaadauth to azure.extensions.

Since Microsoft Entra authentication is already enabled and you have already restarted the server and re-added the Entra Administrator, I recommend opening an Azure Support request. Ask the PostgreSQL service team to check the Entra/PGAadAuth provisioning on the affected Flexible Server and confirm why the functions are not exposed.

In short: first verify the connection is to postgres. If pgaadauth_create_principal() is still missing there, it requires Microsoft-side service investigation, rather than further customer-side extension configuration.