Manage Microsoft Entra roles in Azure Database for PostgreSQL flexible server

APPLIES TO: Azure Database for PostgreSQL - Flexible Server

This article describes how to create Microsoft Entra ID-enabled database roles within an Azure Database for PostgreSQL flexible server instance.

To learn about how to create and manage Azure subscription users and their privileges, see the Azure role-based access control (Azure RBAC) article or review how to customize roles.

Create or delete Microsoft Entra administrators using Azure portal or Azure Resource Manager (ARM) API

1. Open the Authentication page for your Azure Database for PostgreSQL flexible server instance in the Azure portal.
2. To add an administrator, select Add Microsoft Entra Admin and select a user, group, application, or a managed identity from the current Microsoft Entra tenant.
3. To remove an administrator, select the Delete icon for the administrator you want to remove.
4. Select Save and wait for the provisioning operation to complete.

Manage Microsoft Entra roles using SQL

After you create the first Microsoft Entra administrator from the Azure portal or API, use the administrator role to manage Microsoft Entra roles in your Azure Database for PostgreSQL flexible server instance.

For the best experience with Microsoft Entra integration in Azure Database for PostgreSQL flexible server, we recommend getting familiar with Microsoft identity platform.

Principal types

Azure Database for PostgreSQL flexible server internally stores mapping between PostgreSQL database roles and unique identifiers of AzureAD objects. Each PostgreSQL database role can be mapped to one of the following Microsoft Entra object types:

1. User - Including Tenant local and guest users.
2. Service Principal. Including Applications and Managed identities
3. Group When a PostgreSQL role is linked to a Microsoft Entra group, any user or service principal member of this group can connect to the Azure Database for PostgreSQL flexible server instance with the group role.

List Microsoft Entra roles using SQL

select * from pg_catalog.pgaadauth_list_principals(isAdminValue boolean)

Arguments

isAdminValue

boolean when true returns Admin users. When falsereturns all Microsoft Entra users, including Microsoft Entra admins and nonadmins.

Return type

TABLE(rolname name, principalType text, objectId text, tenantId text, isMfa integer, isAdmin integer) a table with the following schema:

• rolname the name of the role in PostgreSQL.
• principalType the type of principal in Microsoft Entra ID. It can be user, group, or service.
• objectId the identifier of the object in Microsoft Entra ID for this principal.
• tenantId the identifier of the tenant hosting this principal in Microsoft Entra ID.
• isMfa returns a value of 1 if the user/role has MFA enforced.
• isAdmin returns a value of 1 if the user/role is an administrator in PostgreSQL.

Create a user or role with a Microsoft Entra principal name

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

Arguments

roleName

text name of the role to create. This name must match the name of the Microsoft Entra principal.

• For users, use the User Principal Name from the profile. For guest users, include the full name in their home domain with the #EXT# tag.
• For groups and service principals, use the display name. The name must be unique in the tenant.

isAdmin

boolean when true, creates a PostgreSQL admin user (member of the azure_pg_admin role and with CREATEROLE and CREATEDB permissions). When false, creates a regular PostgreSQL user.

isMfa

boolean when true, enforces multifactor authentication for this PostgreSQL user.

Return type

text single value that consists of a string "Created role for roleName", where roleName is the argument you pass for the roleName parameter.

Drop a role with a Microsoft Entra principal name

Remember that you must use a Microsoft Entra admin to drop any Microsoft Entra role that you created in PostgreSQL. If you use a regular PostgreSQL admin to drop a Microsoft Entra role, you get an error.

DROP ROLE rolename;

Create a role using Microsoft Entra object identifier

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

Arguments

roleName

text name of the role to create.

objectId

text unique object identifier of the Microsoft Entra object.

• For users, groups, and managed identities, find the objectId by searching for the object name in Microsoft Entra ID page in Azure portal. See this guide as example
• For groups and service principals, use the display name. The name must be unique in the tenant.
• For applications, use the objectId of the corresponding Service Principal. In Azure portal, find the required objectId on Enterprise Applications page.

objectType

text type of Microsoft Entra object to link to this role. It can be user, group, or service.

isAdmin

boolean when true, creates a PostgreSQL admin user (member of the azure_pg_admin role and with CREATEROLE and CREATEDB permissions). When false, creates a regular PostgreSQL user.

isMfa

boolean when true, enforces multifactor authentication for this PostgreSQL user.

Return type

text single value that consists of a string "Created role for roleName", where roleName is the argument you pass for the roleName parameter.

Enable Microsoft Entra authentication for an existing PostgreSQL role using SQL

Azure Database for PostgreSQL flexible server uses security labels associated with database roles to store their corresponding Microsoft Entra ID mapping.

Use the following SQL to assign the required security label and map it to a Microsoft Entra object:

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

Arguments

roleName

text name of an existing PostgreSQL role to enable Microsoft Entra authentication.

objectId

text unique object identifier of the Microsoft Entra object.

objectType

text set to user, group, or service (for applications or managed identities connecting under their own service credentials).

admin

text set to present or absent. If present in the security label, users or roles can manage other Microsoft Entra ID roles.

Related content

• Microsoft Entra authentication with Azure Database for PostgreSQL flexible server.