Use psql with Azure Active Directory authentication

I needed to connect to an Azure Database for PostgreSQL which uses Azure Active Directory for authentication. Despite the many GUI clients available, I prefer using psql. Fortunately, it’s fairly easy to connect using psql.

The following steps are taken from here.

Step 1 - Install Azure CLI

On macOS, the simplest method is to use Homebrew:

brew update && brew install azure-cli

Detailed instructions can be found here.

Step 2 - Login to Azure

az login

This command will open a browser window where you can login to Azure.

Step 3 - Retrieve and set Azure AD access token as the password

Next, we need to get an access token from Azure AD and set that in the PGPASSWORD environment variable so psql can access it. This is easy to do if you have jq installed.

export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms | jq -r '.accessToken')

Step 4 - Connect using psql

That was it! You are ready to connect to the server using psql:

psql "host=... user=... dbname=... sslmode=require"

You can also combine the previous two commands, which can be useful if you want to create an alias:

PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms | jq -r '.accessToken') psql "host=... user=... dbname=... sslmode=require"