DEV Community: Guy Bowerman

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

Guy Bowerman — Tue, 18 Feb 2025 05:00:08 +0000

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 myuser@mycompany.com --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 myuser@mycompany.com

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=myuser@mycompany.com 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.

Setting up Postgres on Azure private endpoints using CLI

Guy Bowerman — Mon, 02 Sep 2024 07:12:34 +0000

Previously I wrote about Securing Postgres on Azure with a private endpoint to ensure a Postgres server can only be connected from a specific subnet, via the private Azure backbone network.

Suppose you want to set up a Postgres flexible server instance with private endpoints using Azure CLI instead of the Portal? This article walks through a CLI script to do that.

Firstly, set up a few variables (assuming it's running as a bash script):

#!/usr/bin/bash resourceGroup="my-private-rg" location="westus3" vnetName="my-private-vnet" subnetName="my-private-subnet" dnsZoneName="privatelink.postgres.database.azure.com" connectionName="privatelink" privateEndpointName="myPrivateEndpoint" privateLinkName="privatelink" serverName="my-private-pg-server" adminUser="myuser"

Next create an Azure resource group, and a Virtual Network with a subnet:

az group create --name $resourceGroup --location $location az network vnet create --resource-group $resourceGroup --name $vnetName --subnet-name $subnetName --subnet-prefixes 10.0.0.0/24

Now create a new PostgreSQL flexible server instance.
Note: Setting public access to None, and assuming you've set an environment variable $password for the admin password:

az postgres flexible-server create --resource-group $resourceGroup --name $serverName --tier Burstable --sku-name Standard_B1ms --public-access None --admin-user $adminUser --admin-password $password

Once the flexible server instance is running, create a private endpoint, and attach it to the flexible server resource id by dynamically calling "az resource show" to get the resource id:

az network private-endpoint create --name $privateEndpointName --connection-name $connectionName --resource-group $resourceGroup --vnet $vnetName --subnet $subnetName --private-connection-resource-id $(az resource show -g $resourceGroup -n $serverName --resource-type "Microsoft.DBforPostgreSQL/flexibleServers" --query "id" -o tsv) --group-id postgresqlServer

Next create a private DNS zone and link it to the virtual network.

az network private-dns zone create --resource-group $resourceGroup --name $dnsZoneName az network private-dns link vnet create --resource-group $resourceGroup --zone-name $dnsZoneName --name $privateLinkName --virtual-network $vnetName --registration-enabled false

Lastly, get the private IP address of the private endpoint, and create a private DNS record to point the Postgres server name to this address:

networkInterfaceId=$(az network private-endpoint show --name $privateEndpointName --resource-group $resourceGroup --query 'networkInterfaces[0].id' -o tsv)

privateIP=$(az resource show --ids $networkInterfaceId --api-version 2019-04-01 -o json | grep privateIPAddress\" | grep -oP '(?<="privateIPAddress": ")[^"]+')

az network private-dns record-set a create --name $serverName --zone-name $dnsZoneName --resource-group $resourceGroup az network private-dns record-set a add-record --record-set-name $serverName --zone-name $dnsZoneName --resource-group $resourceGroup -a $privateIP

Once it's set up, you can create resource in the subnet that can now connect privately to the database server.

One thing worth noting. If you look at the Postgres server network properties after running this script, "Public access" is checked:

It doesn't mean the server can be accessed other than via the private endpoint, as there are no firewall rules in place. You can safely uncheck this box. (And I need to tinker with the CLI some more so this box isn't checked.)

Securing Postgres on Azure with a Private Endpoint

Guy Bowerman — Sun, 01 Sep 2024 03:05:27 +0000

Here's a quick guide to setting up secure access to Azure Database for PostgreSQL flexible server, AKA Postgres on Azure, using a private endpoint, so the PostgreSQL server is only accessible from a specified subnet, which connects privately over the Microsoft backbone network. In this example a Linux VM will be created in the subnet to act as a database client.

Basic steps

Create an Azure Virtual Network (VNET) with a default subnet.
Create an Azure Database for PostgreSQL flexible server instance (with a private endpoint and private DNS integration).
Create a VM with a PostgreSQL client in the subnet.
Install psql on the VM.
Connect to the Postgres instance from the VM.

BTW if you want to do steps 1-3 purely in Azure CLI, see Setting up Postgres on Azure private endpoints using CLI.

1. Create a VNET
Create an Azure Resource Group and a VNET in the Azure portal or using Azure CLI.

E.g., with CLI:
az group create --name myPrivateRG --location westus3 az network vnet create --resource-group myPrivateRG --name myPrivateVNet --subnet-name myPrivateSubnet --subnet-prefixes 10.0.0.0/24

2. Create an Azure Database for PostgreSQL flexible server instance
The Microsoft docs also has a tutorial for this part. See: Create and manage virtual networks with Private Link for Azure Database for PostgreSQL - Flexible Server by using the Azure portal. In this article I'll go into a little more detail on the networking options.

In the Azure portal, create a new Azure Database for PostgreSQL flexible server instance, picking the default settings for Development workload type (Burstable compute SKU):

In the Networking panel select Public access as the connectivity method, but uncheck the "Public access" checkbox. This means only private endpoint connections will be allowed.

Then on the same panel click "Add private endpoint". In the "Create private endpoint" panel create a new private endpoint which points to the virtual network and subnet created earlier, and select "Enable Private DNS integration" to enable private host resolution from the client.

When you create the PostgreSQL server you'll get a warning saying that no firewall rule has been added so you won't be able to connect to it over the internet, which is good, because you only want to connect via private endpoint over the Azure backbone.

3. Create a VM with a PostgreSQL client in the subnet
Create a VM in the subnet referenced by the private endpoint, which will be the only client resource allowed to connect to the PostgreSQL server.

Here's an example of creating an Ubuntu VM in the Azure Portal, choosing the default settings, in the VNET created in step 1.

Use an SSH key to connect, and if you're generating a new key pair you'll have the option to save the PEM file (which contains your private key) locally when creating the VM.

In the Networking panel confirm the VNET and Subnet match the one created earlier for the private endpoint:

4. Install psql on the VM
Once the VM is running, SSH to the VM using the locally saved key. In this case I ran ssh from a Debian instance running in WSL (Windows Subsystem for Linux) to the public IP address of the VM. I saved the PEM file in a .ssh directory and set the file permissions to 600 (chmod 600 .ssl/myPrivateVM_key.pem):

ssh -i .ssh/myPrivateVM_key.pem azureuser@nn.nn.nn.nnn

Use the Ubuntu package manager to install the Postgres client tool psql on the vM:

sudo apt-get update sudo apt-get upgrade sudo apt install postgresql-client

5. Connect to the Postgres instance from the VM

With the postgresql-client package installed you can use the psql tool to connect to the database server via the private endpoint. Note "myuser" is the name of the admin user you specified when you created the PostgreSQL server (this example assumes you specified a regular user/password, not an Entra ID).

psql -h my-private-pg-server.postgres.database.azure.com -U myuser -d postgres

Any resource you create in this subnet, for example a web app, will be able to connect to the PostgreSQL server via the private endpoint using Azure's private backbone network. No resources outside this subnet will be able to connect.

Postgres on Debian in WSL

Guy Bowerman — Mon, 15 Apr 2024 04:16:48 +0000

This article covers the basics of installing and running PostgreSQL on Debian in Windows subsystem for Linux (WSL).

Updated June 15 2025 (to include running Postgres 18 on WSL, and connecting with the VS Code Postgres extension).

WSL makes for a great development environment if you're running Windows and want to use Linux locally. It's also a very convenient platform run to a local PostgreSQL server, and integrates nicely with dev tools like VS Code.

Which Linux distro?

You can choose from many Linux distributions to run on WSL (run wsl -l -o to see a list or import any distro using a TAR file). I like to use Debian as it's a popular, stable, general purpose distro with good support. You can install Debian to WSL directly from the Microsoft Store, and use Windows Terminal to log on to it.

Which Postgres version?

Ignore this step if you want want the default Postgres version that comes with your distro packages.

See the postgresql.org Apt/FAQ for guidance on how to install the latest released or development PostgreSQL version. For example, edit /etc/apt/sources.list.d/pgdg.list to point to the latest version 18 pre-release by adding this line:

deb http://apt.postgresql.org/pub/repos/apt/ bookworm-pgdg main 18

Install Postgres

The first thing to do is make sure you're up to date with the latest patches. On the Debian command line:

sudo apt update
sudo apt upgrade
sudo apt autoremove # to free up space if needed

If you're installing the latest released version (e.g., 17), install with:

sudo apt install postgresql postgresql-contrib

See Install PostgreSQL in the WSL docs for more details.

Installing a development release. E.g., postgresql-18

Alternatively, install a specific version like 18 with:

sudo apt install postgresql-18

Set a password for the postgres user with sudo passwd postgres and then start the service:

sudo passwd postgres
sudo service postgresql start
sudo systemctl enable postgresql # to start service automatically

Note: If you previously had an earlier version of PostgreSQL installed and the version you want isn't starting, you can explicitly initialize it. You can then run both versions (listening on different ports), or you can drop the older cluster if you don't need it. Example:

sudo pg_createcluster 18 main --start
pg_lsclusters
sudo pg_dropcluster 17 main --stop
sudo service postgresql start

Connecting to PostgreSQL

You can then enter the PostgreSQL shell with:

sudo -u postgres psql

..and from there run SQL commands, e.g. create PostgreSQL users with CREATE USER

Or run SQL commands directly, e.g.

sudo -i -u postgres psql < create_db.sql
sudo -i -u postgre psql -d mydb < create_schema.sql
# list databases
sudo -i postgres psql --list

Connecting with VS Code

VS Code has become a great way to connect to a Postgres instance since the PostgreSQL for Visual Studio Code extension was released in May 2025.

Create a new connection, use 127.0.0.1 as the "SERVER NAME" and check in "Advanced Connection Settings" to make sure 5432 is the listening port (unless you're using a different listening port - you can check in Debian with pg_lsclusters).

Connecting with pgAdmin

You can install pgAdmin on Windows and connect the GUI Admin/developer tool directly to your Postgres server running in WSL.

In pgAdmin, create a new server with the name of your choice, Host name/address: 127.0.0.1 and a valid PostgreSQL user/password.

Once I've done my local development it's very straightforward to export a database from WSL to a Postgres cloud service or other production destination.