Connecting to Azure PostgreSQL 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.

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

Assigned User-Assigned Identity to the VM

List User-Assigned Identity to get its clientId

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

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

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

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

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

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

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

Principal Engineer / Architect, FastTrack for Azure at Microsoft