Manage Microsoft Entra roles in Azure Database for PostgreSQL - Flexible Server
APPLIES TO: Azure Database for PostgreSQL - Flexible Server
This article describes how you can create a Microsoft Entra ID enabled database roles within an Azure Database for PostgreSQL flexible server instance.
Note
This guide assumes you already enabled Microsoft Entra authentication on your Azure Database for PostgreSQL flexible server instance. See How to Configure Microsoft Entra authentication
If you like to learn about how to create and manage Azure subscription users and their privileges, you can visit 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
- Open the Authentication page for your Azure Database for PostgreSQL flexible server instance in the Azure portal.
- 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.
- To remove an administrator - select Delete icon for the one to remove.
- Select Save and wait for provisioning operation to completed.
Note
Support for Microsoft Entra Administrators management via Azure SDK, az cli and Azure PowerShell is coming soon.
Manage Microsoft Entra roles using SQL
Once the first Microsoft Entra administrator is created from the Azure portal or API, you can use the administrator role to manage Microsoft Entra roles in your Azure Database for PostgreSQL flexible server instance.
We recommend getting familiar with Microsoft identity platform for best use of Microsoft Entra integration with Azure Database for PostgreSQL flexible server.
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:
- User - Including Tenant local and guest users.
- Service Principal. Including Applications and Managed identities
- 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 pgaadauth_list_principals(true);
Parameters:
- true -will return Admin users.
- false -will return all Microsoft Entra user both Microsoft Entra admins and Non Microsoft Entra admins.
Create a role using Microsoft Entra principal name
select * from pgaadauth_create_principal('<roleName>', <isAdmin>, <isMfa>);
For example: select * from pgaadauth_create_principal('mary@contoso.com', false, false);
Parameters:
- roleName - Name of the role to be created. This must match a name of Microsoft Entra principal:
- For users use User Principal Name from Profile. For guest users, include the full name in their home domain with #EXT# tag.
- For groups and service principals use display name. The name must be unique in the tenant.
- isAdmin - Set to true if when creating an admin user and false for a regular user. Admin user created this way has the same privileges as one created via portal or API.
- isMfa - Flag if Multi Factor Authentication must be enforced for this role.
Drop a role using Microsoft Entra principal name
Remember that any Microsoft Entra role that is created in PostgreSQL must be dropped using a Microsoft Entra Admin. If you use a regular PostgreSQL admin to drop an Entra role then it will result in an error.
Drop Role rolename;
Create a role using Microsoft Entra object identifier
select * from pgaadauth_create_principal_with_oid('<roleName>', '<objectId>', '<objectType>', <isAdmin>, <isMfa>);
For example: select * from pgaadauth_create_principal_with_oid('accounting_application', '00000000-0000-0000-0000-000000000000', 'service', false, false);
Parameters:
- roleName - Name of the role to be created.
- objectId - Unique object identifier of the Microsoft Entra object:
- For Users, Groups and Managed Identities the ObjectId can be found by searching for the object name in Microsoft Entra ID page in Azure portal. See this guide as example
- For Applications, Objectid of the corresponding Service Principal must be used. In Azure portal the required ObjectId can be found on Enterprise Applications page.
- objectType - Type of the Microsoft Entra object to link to this role: service, user, group.
- isAdmin - Set to true if when creating an admin user and false for a regular user. Admin user created this way has the same privileges as one created via portal or API.
- isMfa - Flag if Multi Factor Authentication must be enforced for this role.
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 Microsoft Entra ID mapping.
You can use the following SQL to assign security label:
SECURITY LABEL for "pgaadauth" on role "<roleName>" is 'aadauth,oid=<objectId>,type=<user|group|service>,admin';
Parameters:
- roleName - Name of an existing PostgreSQL role to which Microsoft Entra authentication needs to be enabled.
- objectId - Unique object identifier of the Microsoft Entra object.
- user - End user principals.
- service - Applications or Managed Identities connecting under their own service credentials.
- group - Name of Microsoft Entra group.
Next steps
- Review the overall concepts for Microsoft Entra authentication with Azure Database for PostgreSQL - Flexible Server
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for