Entra ID Authentication for Azure PostgreSQL

How Microsoft Entra ID works in Azure Database for PostgreSQL flexible server

The following high-level diagram summarizes how authentication works when you use Microsoft Entra authentication with Azure Database for PostgreSQL flexible server. The arrows indicate communication pathways.

 

 

1. Your application can request a token from the Azure Instance Metadata Service identity endpoint.
2. When you use the client ID and certificate, a call is made to Microsoft Entra ID to request an access token.
3. A JSON Web Token (JWT) access token is returned by Microsoft Entra ID. Your application sends the access token on a call to your Flexible Server.
4. The Flexible Server validates the token with Microsoft Entra ID.

 

Use case : Set up managed identity/Entra access instead of having roles with passwords for every person/app under PostgresSQL PaaS.

Create the Postgres SQL Flexible server & add the Entra ID users/groups who needs admin access on Database.

You can set up a group via the Azure portal and add Entra users to it. For guidance refer to https://learn.microsoft.com/en-us/entra/fundamentals/how-to-manage-groups

These permissions can set during the Postgres SQL server deployment or later after the provisioning.

Once you have the group created, you can add it to Postgres Flexible server under group under Security -> Authentication -> Microsoft Entra Admins.

 

 

You can also add the individual user account who needs admin access to the server.

 

How to access the Postgres SQL DB once permissions has been granted to the group-

 

Please follow below steps to connect to Postgres PaaS DB using pgadmin tool: 

1. Open pgadmin tool on server that has network access to Postgres SQL PaaS:

 

 

 

1. Select Add new Server and add server details- Servername: servername.postgres.database.azure.com 

Disable connect now and add connection details.

Port:5432

Maintenance: postgres

Username should be the group name that has been provided access to Postgres

Username: db-admins

Save the settings as shown below-

 

 

 

1. Login to the Azure portal using your secondary ID to get the access token
2. Open the Cloudshell 

Select "No Storage account" and enter Yes.

 

5. Run the below command to generate access token for your account :

 

az account get-access-token --resource-type oss-rdbms 

 

6. Copy the access token and enter it as password to connect to postgres using pgadmin tool

 

 

 

 

Enter the password and select ok, you will be connected to Postgres DB's.

 

How to use managed identity of the VM to connect the Application to Database:

 

The application can use managed identity as useraccount to establish connectivity to the Database. Instead of creating local user account or service account for connecting the app To postgres PaaS in Azure, we can use managed identity of the App that will be Entra ID authenticated. There will be no requirement of managing the credentials.

The below steps highlights how you can use system assigned managed identity of the VM to connect to Postgres SQL Paas-

 

Create an Azure Database for PostgreSQL flexible server user for your Managed Identity:

 

Connect as the Microsoft Entra administrator user to your Azure Database for PostgreSQL flexible server database, and run the following SQL statements, replacing <identity_name> with the name of the resources for which you created a system-assigned managed identity:

Note pgaadauth_create_principal must be run on the Postgres database.

select * from pgaadauth_create_principal('<identity_name>', false, false);

Success looks like:

    pgaadauth_create_principal

-----------------------------------

 Created role for "<identity_name>"

For more information on managing Microsoft Entra ID enabled database roles, see https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-connect-with-managed-identity

how to manage Microsoft Entra ID enabled Azure Database for PostgreSQL flexible server roles

The managed identity now has access when authenticating with the identity name as a role name and the Microsoft Entra token as a password.

 

Connect using Managed Identity

This section shows how to get an access token using the VM's user-assigned managed identity and use it to call Azure Database for PostgreSQL flexible server. Azure Database for PostgreSQL flexible server natively supports Microsoft Entra authentication, so it can directly accept access tokens obtained using managed identities for Azure resources. When creating a connection to Azure Database for PostgreSQL flexible server, you pass the access token in the password field.

Please refer to https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-connect-with-managed-identity#connect-using-managed-identity that shows how to connect using Managed identity in different languages.

 

How to confirm Managed identity has been given access to Postgres PaaS:

You can login to Postgres PaaS using managed identity from pgadmin tool. Here are the steps-

1. Run below commands on server with assigned managed identity to get the token –

az login --identity -i --allow-no-subscriptions

 

az account get-access-token --resource-type oss-rdbms

 

2. Open the Pgadamin tool & add username as managedidentity of the VM

Password will be token generated in Step 1.

3. If you are able to login , it confirms managed identity as required permissions & Application should be connecting to DB.