Using CLI to deploy Postgres on Azure with Entra (AAD) Admin

How do you use Azure CLI to create an Azure Database for PostgreSQL flexible server with an Entra (AAD) admin? Since many flexible server CLI deployment examples assume you're creating Postgres users rather than Entra, here's a walkthrough of deploying flexible server with an Entra admin role using Azure CLI.

This example assumes you're running Azure Cloud Shell in the Azure Portal. It would also work anywhere you've installed Azure CLI, but Cloud Shell makes for a useful sandbox as it always has the latest CLI version installed.

Why Entra?
As network perimeters have become more porous with Bring Your Own Device, cloud apps and remote work, identity has increasingly become the most important security perimeter for a company. Using a centrally controlled identity service like Microsoft Entra ID allows greater control and better security. See also a great article called Azure Identity Management and access control security best practices.

Let's get started with a CLI walkthrough...

1) Set subscription and create resource group

Set some variables and customize them for your purposes.

myResourceGroup=my-entra-flex-rg
myPGServer=my-entra-flex-server
myLocation=westus3

Set the subscription you're going to use in CLI.

az group set --name <your subscription name>

Create an Azure resource group.

az group create --name $myResourceGroup --location $myLocation

2) Create the flexible server

Deploy a flexible server. Set active-directory-auth to Enabled, and select whichever tier, tier, sku, location, version settings you need, e.g.,

az postgres flexible-server create -g $myResourceGroup \
--name $myPGServer --tier Burstable --sku-name Standard_B1ms \
--location $myLocation --create-default-database Enabled \
--active-directory-auth Enabled --password-auth Disabled \
--version 16

You may see a random password being created in the CLI output, but you can ignore that, we'll be setting an Entra user as the admin.

3) Get the object ID for your Entra user and assign it as admin

Setting an Entra admin user requires the Entra "object ID" of the user. You can query it using the "ad user show" CLI command:

objectId=`az ad user show --id [e-mail] --query "id" --output tsv`

Now you can set this Entra user as the flexible server admin user.

az postgres flexible-server ad-admin create -g $myResourceGroup \
-s $myPGServer --type User --object-id $objectId \
-u [e-mail]

4) Connect to your server as your Entra Admin user

Instead of a password you'll be using your Entra access token. Get the current token by running this command and assigning it to a variable $currentToken:

az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken

Now connect to your flexible server using this access token as the PostgreSQL "password" via any Postgres admin tool.

psql "host=$myPGserver.postgres.database.azure.com port=5432 dbname=postgres user=[e-mail] password=$currentToken"

One thing to keep in mind is that you're effectively using an Entra token as a password for PostgreSQL. Note that Entra tokens expire so you'll need to think about how you refresh them. I'm hoping future versions of open-source PostgreSQL will have a more implicit approach to centralized identity management with built-in OAUTH and Entra support.

Next I plan to look into using Entra IDs with the dblink extension.