Use Microsoft Entra ID and native PostgreSQL roles for authentication with Azure Cosmos DB for PostgreSQL

APPLIES TO: Azure Cosmos DB for PostgreSQL (powered by the Citus database extension to PostgreSQL)

In this article, you configure authentication methods for Azure Cosmos DB for PostgreSQL. You manage Microsoft Entra ID admin users and native PostgreSQL roles for authentication with Azure Cosmos DB for PostgreSQL. You also learn how to use a Microsoft Entra ID token with Azure Cosmos DB for PostgreSQL.

An Azure Cosmos DB for PostgreSQL cluster is created with one built-in native PostgreSQL role named 'citus'. You can add more native PostgreSQL roles after cluster provisioning is completed.

You can also configure Microsoft Entra ID (formerly Azure Active Directory) authentication for Azure Cosmos DB for PostgreSQL. You can enable Microsoft Entra ID authentication in addition or instead of the native PostgreSQL authentication on your cluster. You can change authentication methods enabled on cluster at any point after the cluster is provisioned. When Microsoft Entra ID authentication is enabled, you can add multiple Microsoft Entra ID users to an Azure Cosmos DB for PostgreSQL cluster and make any of them administrators. Microsoft Entra ID user can be a user or a service principal.

Choose authentication method

You need to use Azure portal to configure authentication methods on an Azure Cosmos DB for PostgreSQL cluster.

Complete the following items on your Azure Cosmos DB for PostgreSQL cluster to enable or disable Microsoft Entra ID authentication and native PostgreSQL authentication.

1. On the cluster page, under the Cluster management heading, choose Authentication to open authentication management options.
2. In Authentication methods section, choose PostgreSQL authentication only, Microsoft Entra ID authentication, or PostgreSQL and Microsoft Entra ID authentication as the authentication method based on your requirements.

Once done proceed with configuring Microsoft Entra ID authentication or adding native PostgreSQL roles on the same Authentication page.

Configure Microsoft Entra ID authentication

Prerequisites

Users need to be allowed to sign in to Azure Cosmos DB for PostgreSQL in the Microsoft Entra ID tenant. These steps should be performed once for the Microsoft Entra ID tenant that is going to be used for authentication on Azure Cosmos DB for PostgreSQL clusters.

az ad sp update --id b4fa09d8-5da5-4352-83d9-05c2a44cf431 --set accountEnabled=true

Add Microsoft Entra ID admins to Azure Cosmos DB for PostgreSQL cluster

To add or remove Microsoft Entra ID roles on cluster, follow these steps on Authentication page:

1. In Microsoft Entra ID authentication section, select Add Microsoft Entra ID admins.
2. In Select Microsoft Entra ID Admins panel, select one or more valid Microsoft Entra ID user or enterprise application in the current AD tenant to be a Microsoft Entra ID administrator on your Azure Cosmos DB for PostgreSQL cluster.
3. Use Select to confirm your choice.
4. In the Authentication page, select Save in the toolbar to save changes or proceed with adding native PostgreSQL roles.

Configure native PostgreSQL authentication

To add Postgres roles on cluster, follow these steps on Authentication page:

1. In PostgreSQL authentication section, select Add PostgreSQL role.
2. Enter the role name and password. Select Save.
3. In the Authentication page, select Save in the toolbar to save changes or proceed with adding Microsoft Entra ID admin users.

The native PostgreSQL user is created on the coordinator node of the cluster, and propagated to all the worker nodes. Roles created through the Azure portal have the LOGIN attribute, which means they’re true users who can sign in to the database.

Connect to Azure Cosmos for PostgreSQL by using Microsoft Entra ID authentication

Microsoft Entra ID integration works with standard PostgreSQL client tools like psql, which aren't Microsoft Entra ID aware and support only specifying the username and password when you're connecting to PostgreSQL. In such cases, the Microsoft Entra ID token is passed as the password.

We tested the following clients:

• psql command line: Use the PGPASSWORD variable to pass the token.
• Other libpq-based clients: Examples include common application frameworks and object-relational mappers (ORMs).
• pgAdmin: Clear Connect now at server creation.

Use the following procedures to authenticate with Microsoft Entra ID as an Azure Cosmos DB for PostgreSQL user. You can follow along on your local machine.

Sign in to the user's Azure subscription

Start by authenticating with Microsoft Entra ID by using the Azure CLI.

az cloud set -n AzureChinaCloud
az login

The command opens a browser window to the Microsoft Entra ID authentication page. It requires you to give your Microsoft Entra ID user name and password.

The user account name you use to authenticate (for example, user@tenant.partner.onmschina.cn) is the one the access token will be generated for in the next step.

Retrieve the Microsoft Entra ID access token

Use the Azure CLI to acquire an access token for the Microsoft Entra ID authenticated user to access Azure Cosmos for PostgreSQL. Here's an example:

az account get-access-token --resource https://token.postgres.cosmos.azure.cn

After authentication is successful, Microsoft Entra ID returns an access token for current Azure subscription:

{
  "accessToken": "[TOKEN]",
  "expiresOn": "[expiration_date_and_time]",
  "subscription": "[subscription_id]",
  "tenant": "[tenant_id]",
  "tokenType": "Bearer"
}

The TOKEN is a Base64 string. It encodes all the information about the authenticated user and is associated with the Azure Cosmos DB for PostgreSQL service. The token is valid for at least 5 minutes with the maximum of 90 minutes. The expiresOn defines actual token expiration time.

Use a token as a password for signing in with client psql

When connecting, it's best to use the access token as the PostgreSQL user password.

While using the psql command-line client, the access token needs to be passed through the PGPASSWORD environment variable. The reason is that the access token exceeds the password length that psql can accept directly.

Here's a Windows example:

set PGPASSWORD=<TOKEN value from the previous step>

$env:PGPASSWORD='<TOKEN value from the previous step>'

Here's a Linux/macOS example:

export PGPASSWORD=<TOKEN value from the previous step>

You can also combine the previous two steps together using command substitution. The token retrieval can be encapsulated into a variable and passed directly as a value for PGPASSWORD environment variable:

export PGPASSWORD=$(az account get-access-token --resource https://token.postgres.cosmos.azure.cn --query "[accessToken]" -o tsv)

Now you can initiate a connection with Azure Cosmos DB for PostgreSQL using the Microsoft Entra ID user account that the access token was generated for. You would do it as you usually would with the user account as the user and without 'password' parameter in the command line:

psql "host=mycluster.[uniqueID].postgres.cosmos.azure.cn user=user@tenant.partner.onmschina.cn dbname=[db_name] sslmode=require"

Use a token as a password for signing in with PgAdmin

To connect by using a Microsoft Entra ID token with PgAdmin, follow these steps:

1. Clear the Connect now option at server creation.
2. Enter your server details on the Connection tab and save.
   1. Make sure a valid Microsoft Entra ID user is specified in Username.
3. From the pgAdmin Object menu, select Connect Server.
4. Enter the Microsoft Entra ID token password when you're prompted.

Here are some essential considerations when you're connecting:

• user@tenant.partner.onmschina.cn is the name of the Microsoft Entra ID user.
• Be sure to use the exact way the Azure user is spelled. Microsoft Entra ID user and group names are case-sensitive.
• If the name contains spaces, use a backslash (\) before each space to escape it.
• The access token's validity is 5 minutes to 90 minutes. You should get the access token before initiating the sign-in to Azure Cosmos for PostgreSQL.

You're now authenticated to your Azure Cosmos for PostgreSQL server through Microsoft Entra ID authentication.

Manage native PostgreSQL roles

When native PostgreSQL authentication is enabled on cluster, you can add and delete Postgres roles in addition to built-in 'citus' role. You can also reset password and modify Postgres privileges for native roles.

How to delete a native PostgreSQL user role or change their password

To update a user, visit the Authentication page for your cluster, and select the ellipses ... next to the user. The ellipses open a menu to delete the user or reset their password.

The citus role is privileged and can't be deleted. However, citus role would be disabled, if 'Microsoft Entra ID authentication only' authentication method is selected for the cluster.

How to modify privileges for user roles

New user roles are commonly used to provide database access with restricted privileges. To modify user privileges, use standard PostgreSQL commands, using a tool such as PgAdmin or psql. For more information, see Connect to a cluster.

For example, to allow PostgreSQL db_user to read mytable, grant the permission:

GRANT SELECT ON mytable TO db_user;

To grant the same permissions to Microsoft Entra ID role user@tenant.partner.onmschina.cn use the following command:

GRANT SELECT ON mytable TO "user@tenant.partner.onmschina.cn";

Azure Cosmos DB for PostgreSQL propagates single-table GRANT statements through the entire cluster, applying them on all worker nodes. It also propagates GRANTs that are system-wide (for example, for all tables in a schema):

-- applies to the coordinator node and propagates to worker nodes for Postgres role db_user
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_user;

Or for Microsoft Entra ID role

-- applies to the coordinator node and propagates to worker nodes for Azure AD role user@tenant.partner.onmschina.cn
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "user@tenant.partner.onmschina.cn";

Next steps

• Learn about authentication in Azure Cosmos DB for PostgreSQL
• Check out Microsoft Entra ID limits and limitations in Azure Cosmos DB for PostgreSQL
• Review Microsoft Entra ID fundamentals
• Learn more about SQL GRANT in PostgreSQL