Connecting to Azure PostgreSQL Single Server using Azure Active Directory and Managed Service Identity

In this video, we look at how to connect to Azure Database for PostgreSQL from an Azure Virtual Machine using that VM’s Managed Service Identity (MSI) via Azure PostgreSQL integration with Azure Active Directory (AAD).

Update 2020–05–20: Also, see the official doc describing how to use Managed Identity to connect to Azure PostgreSQL Single Server.

Update 2023–07–21: This video talks about Azure PostgreSQL Single Server. To access Azure PostgreSQL Flexible Server, please see this document.

Video Walkthrough

Tip: Play the video full screen

Preparation Steps

Create Azure PostgreSQL database and enable Azure Active Directory integration as described here.

Create Ubuntu 18.04 VM using Azure Portal (e.g. avpostgres2vm)

Create User-Assigned Identity

az identity create -g avpostgres2 -l eastus2 -n avpostgres2msi

Assigned User-Assigned Identity to the VM

az vm identity assign -g avpostgres2 -n avpostgres1vm --identities avpostgres2msi

List User-Assigned Identity to get its clientId

az vm identity show -g avpostgres2 -n avpostgres2vm -o json

Create Managed Service Identity Role in PostgreSQL

Login into PostgreSQL database using psql command line tool using the Azure Active Directory Admin user as described here

psql "host=avpostgres2.postgres.database.azure.com port=5432 dbname=postgres user=admin1@arsenvladoutlook.onmicrosoft.com@avpostgres2 sslmode=require"

Before creating the Managed Service Identity user, we need to turn off PostgreSQL validation of object ids with Azure Active Directory

SET aad_validate_oids_in_tenant = off;

Create Managed Service Identity user using the clientId as the value of PASSWORD

CREATE ROLE avpostgres2msi WITH LOGIN PASSWORD 'MSI_CLIENT_ID' IN ROLE azure_ad_user;

Login into PostgreSQL using VM’s Managed Service Identity OAuth Access Token

SSH to the Azure VM that has our Managed Service Identity assigned to it

From the SSH session, get VM’s OAuth access token for the Azure PostgreSQL resource from the Managed Identity Endpoint

curl 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fossrdbms-aad.database.windows.net' -H Metadata:true

Copy the long string that is returned in the “access_token” field and set it into psql’s PGPASSWORD environment variable

export PGPASSWORD=<access_token>

Connect to Azure PostgreSQL using the name of the role we assigned to the Managed Service Identity when creating it above (i.e. avpostgres2msi) and password that is in the PGPASSWORD environment variable

psql “host=avpostgres2.postgres.database.azure.com port=5432 dbname=postgres user=avpostgres2msi@avpostgres2 sslmode=require”

You should now be logged into the Azure PostgreSQL using VM’s Managed Service Identity without having to store user’s password (or service principal client_secret) in your application.

Thank you!

Please leave feedback and questions below or on Twitter https://twitter.com/ArsenVlad