Azure Database for PostgreSQL — Flexible Server Community

Status: tested against querycop HEAD on 2026-05-20. Endpoint and az CLI conventions cited as of 2026-05.

This page covers Azure Database for PostgreSQL — Flexible Server, Microsoft’s current managed PostgreSQL offering. The older Single Server product is on a retirement path (per Microsoft’s published schedule) and is not covered here — operators on Single Server should plan a migration before this page becomes load-bearing.

Two auth modes are supported by Flexible Server: PostgreSQL authentication (built-in users with passwords) and Microsoft Entra ID authentication (formerly Azure AD), which substitutes a short-lived OAuth2 access token for the database password. Both work through Querycop.

TL;DR

For a Flexible Server with Microsoft Entra ID auth, the production-ready config is:

Env var	Value
GATEKEEPER_BACKEND_HOST	myserver.postgres.database.azure.com
GATEKEEPER_BACKEND_PORT	5432
GATEKEEPER_BACKEND_TLS_MODE	verify-full
GATEKEEPER_BACKEND_TLS_CA_FILE	/etc/ssl/certs/ca-certificates.crt (OS root bundle — the cert chain uses publicly-trusted roots)
GATEKEEPER_BACKEND_TLS_SERVER_NAME	(unset — derived from BACKEND_HOST)
GATEKEEPER_BACKEND_TOKEN_CMD	az account get-access-token --resource-type oss-rdbms --query accessToken -o tsv

Flexible Server’s server certificate is issued from publicly-trusted roots (DigiCert Global Root G2 / Microsoft RSA Root Certificate Authority 2017 chain), so the OS root bundle that ships with the Querycop container verifies the chain — no Azure-specific CA file to maintain. verify-full matches the server’s DNS-name SAN without any BACKEND_TLS_SERVER_NAME override.

For Flexible Server with password auth, drop the BACKEND_TOKEN_CMD row and supply the password through your client.

Prerequisites

• A running Flexible Server instance. The server name, resource group, and connectivity mode (public access vs private VNet) are visible in the Azure portal under Azure Database for PostgreSQL → <your server> → Overview.

• Network reachability from the Querycop host to port 5432 on the server endpoint. With public access, Azure firewall rules must allow the Querycop host’s outbound IP. With VNet integration / Private Endpoint, Querycop must run inside the same VNet (or peered, via VPN, etc.).

• The OS root CA bundle on disk at a known path. The cert chain for Flexible Server uses publicly-trusted roots so the OS bundle is sufficient:

  • Debian / Ubuntu / the debian:trixie-slim-based Querycop runtime image: /etc/ssl/certs/ca-certificates.crt
  • Alpine (after apk add ca-certificates): /etc/ssl/certs/ca-certificates.crt
  • RHEL / Fedora / Amazon Linux: /etc/pki/tls/certs/ca-bundle.crt

  Querycop’s verify-ca / verify-full modes require an explicit GATEKEEPER_BACKEND_TLS_CA_FILE path — the implicit-system-pool fallback is deliberately not supported, so an explicit path needs to land in the env even when the file you’re pointing at IS the OS root bundle.

• For Microsoft Entra ID auth: Entra ID authentication enabled on the server, with an Entra ID admin configured at the server level:

  # Enable Entra ID auth on the server (recent surface — older az
  # versions use --active-directory-auth Enabled).
  az postgres flexible-server update \
    --resource-group myRG --name myserver \
    --active-directory-auth Enabled
  
  # Designate an Entra ID principal as the server's Entra admin.
  # This admin is the one allowed to create other Entra-mapped PG
  # roles via the pgaadauth_create_principal() function.
  az postgres flexible-server ad-admin create \
    --resource-group myRG --server-name myserver \
    --object-id <admin-object-id> \
    --display-name '<admin display name>' \
    --type User

• A PostgreSQL role mapped to your Entra ID principal. The Flexible Server pgaadauth extension exposes pgaadauth_create_principal() for this — run it as the Entra admin user against the target database:

  -- For an Entra ID user account
  SELECT * FROM pgaadauth_create_principal('alice@contoso.com', false, false);
  
  -- For a service principal (managed identity / app registration);
  -- pass the principal's Object ID, not the App ID
  SELECT * FROM pgaadauth_create_principal_with_oid(
      'sp-display-name',
      '<service-principal-object-id>',
      'service',
      false, false);

  The Microsoft docs cover the function arguments in detail (Manage Entra users).

• The Entra ID principal Querycop runs as has been granted the appropriate PostgreSQL-side GRANTs on whatever schema / tables it needs — Entra-mapped roles get no default privileges.

• az CLI installed on the Querycop host with credentials available (an active az login, a managed identity attached to the host, or a service-principal az login --service-principal …). The CLI must be able to call az account get-access-token.

• Querycop with backend IAM token injection support (GATEKEEPER_BACKEND_TOKEN_CMD).

Pattern A — Password auth (evaluation / early production)

Flexible Server supports per-user passwords created with the standard CREATE USER … WITH LOGIN PASSWORD '…' flow. Querycop forwards the client’s password unchanged.

Step 1: Confirm the OS root bundle is in place

No Azure-specific CA file is required. The Querycop runtime container ships with an up-to-date system root CA bundle.

# Sanity check — should print a non-zero count of CERTIFICATE blocks
grep -c BEGIN /etc/ssl/certs/ca-certificates.crt

If you’d rather pin tighter than the OS bundle (defense-in-depth against future root-CA changes), Microsoft publishes a curated bundle at learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-networking-ssl-tls; download it and point BACKEND_TLS_CA_FILE at the saved PEM instead. Most operators don’t need this.

Step 2: Configure Querycop

# Required: where to find the backend
export GATEKEEPER_BACKEND_HOST=myserver.postgres.database.azure.com
export GATEKEEPER_BACKEND_PORT=5432

# Required: full TLS verification against the OS root bundle.
# CA_FILE must be set explicitly — Querycop rejects verify-ca /
# verify-full at startup if it's empty.
export GATEKEEPER_BACKEND_TLS_MODE=verify-full
export GATEKEEPER_BACKEND_TLS_CA_FILE=/etc/ssl/certs/ca-certificates.crt
# SERVER_NAME not set — Querycop derives it from BACKEND_HOST,
# which matches the SAN on the server's publicly-issued cert.

# Standard Querycop runtime
export GATEKEEPER_LISTEN_PORT=15432
export GATEKEEPER_API_PORT=8080
export ADMIN_API_KEY=$(openssl rand -hex 16)

querycop

Step 3: Connect the client

psql -h 127.0.0.1 -p 15432 -U appuser -d appdb
# Password prompt → enter the password set on the Flexible Server user

verify-full is the right default for production. require (no certificate verification) is evaluation-only. disable is not an appropriate choice for a managed cloud DB.

Pattern B — Microsoft Entra ID auth (recommended for production)

Entra ID auth substitutes a short-lived OAuth2 access token for the database password. Querycop mints the token via az account get-access-token and rewrites the client’s PasswordMessage to inject it (cleartext / MD5 PG auth paths both handled).

Step 1: Confirm the Azure-side Entra ID wiring

# 1. Entra ID authentication enabled on the server
az postgres flexible-server show \
  --resource-group myRG --name myserver \
  --query 'authConfig'
# Expected: a block with activeDirectoryAuth = Enabled

# 2. Server has at least one Entra ID admin configured
az postgres flexible-server ad-admin list \
  --resource-group myRG --server-name myserver

# 3. The Entra ID principal Querycop will run as can mint a token
#    for the OSS RDBMS resource scope.
az account get-access-token \
  --resource-type oss-rdbms \
  --query accessToken -o tsv \
  | head -c 80
# Expected: a JWT-shaped opaque token (eyJ…)

If any step fails on the Azure side, fix it before configuring Querycop — the proxy will faithfully propagate mint failures as connection aborts.

Step 2: Configure Querycop

Same BACKEND_HOST / TLS config as Pattern A, plus the token command:

export GATEKEEPER_BACKEND_HOST=myserver.postgres.database.azure.com
export GATEKEEPER_BACKEND_PORT=5432

export GATEKEEPER_BACKEND_TLS_MODE=verify-full
export GATEKEEPER_BACKEND_TLS_CA_FILE=/etc/ssl/certs/ca-certificates.crt

# Entra ID token mint per connection. The OSS RDBMS resource scope
# is what Flexible Server checks; --resource-type oss-rdbms is the
# documented shortcut.
export GATEKEEPER_BACKEND_TOKEN_CMD='az account get-access-token \
    --resource-type oss-rdbms \
    --query accessToken -o tsv'

export GATEKEEPER_LISTEN_PORT=15432
export GATEKEEPER_API_PORT=8080
export ADMIN_API_KEY=$(openssl rand -hex 16)

querycop

Querycop enforces at startup that BACKEND_TOKEN_CMD ships with a TLS mode of require / verify-ca / verify-full — prefer is rejected because its plaintext-fallback path would leak the token. See docs/configuration.md §1.6.

Step 3: Connect the client

The username is your Entra ID principal — typically the email / UPN for human users, or the display name you passed to pgaadauth_create_principal_with_oid for service principals.

# For a human user
psql -h 127.0.0.1 -p 15432 -U alice@contoso.com -d appdb
# Password prompt: just press Enter (Querycop replaces with the Entra token)

# For a service principal mapped via pgaadauth_create_principal_with_oid
psql -h 127.0.0.1 -p 15432 -U sp-display-name -d appdb

PGPASSWORD=ignored works for clients that won’t accept an empty password.

Smoke test

# Terminal 1: bring up Querycop with the env above.
docker compose up -d  # or `querycop` directly

# Terminal 2: connect via psql.
psql -h 127.0.0.1 -p 15432 -U alice@contoso.com -d appdb -c 'select 1'
#  ?column?
# ----------
#         1
# (1 row)

A green select 1 means proxy-side TLS, the Entra ID token mint, the PasswordMessage rewrite, and the backend TLS all worked. If you see something else:

1. backend TLS negotiation failed: x509: certificate signed by unknown authority → the OS root bundle path is wrong or the bundle is stale. Re-verify BACKEND_TLS_CA_FILE points at a readable PEM with at least one CERTIFICATE block.
2. backend token command failed: AADSTS… → az couldn’t mint a token. Common causes: no active az login / managed-identity binding, principal lacks the oss-rdbms scope, or the Querycop process can’t read the ~/.azure/ profile (set AZURE_CONFIG_DIR if you’ve relocated it).
3. password authentication failed for user "alice@contoso.com" → either the role doesn’t exist (run pgaadauth_create_principal as the Entra admin), or Entra auth isn’t enabled on the server, or your client’s username doesn’t match the role created.

Gotchas

Entra ID access tokens are 60-minute credentials

az account get-access-token --resource-type oss-rdbms returns a JWT with a default 60-minute lifetime. Like the RDS / Cloud SQL stories, the Flexible Server validates the token during the initial auth exchange and does NOT re-check it during steady-state traffic — a successfully-authed connection stays alive for the lifetime of the TCP socket, regardless of the 60-minute mark.

What changes per connection:

• New connections after token expiry mint a fresh token; automatic because mint is per-connection.
• If the underlying Entra ID credentials expire or get revoked (az login session timeout, service-principal secret rotation, managed identity binding removed), all future mints fail with backend token command failed in the WARN log.

In-connection token rotation is not implemented; operators with multi-hour connections should expect occasional reconnect cycles.

Username format depends on the principal type

For Entra-mapped PG roles, the username your client sends MUST match what pgaadauth_create_principal*() registered:

• User principals (pgaadauth_create_principal('alice@contoso.com', …)): username is the UPN / email, exactly as registered. Case-sensitive in some Postgres configurations — copy it verbatim from the function call.
• Service principals / managed identities (pgaadauth_create_principal_with_oid('sp-display-name', '<oid>', 'service', …)): username is the display name you passed, NOT the App ID, NOT the Object ID. This is the most common first-setup stumble for workloads using a managed identity.

Mismatch lands client-side as password authentication failed — not a Querycop signal; check the role exists with the username your client is sending:

SELECT rolname FROM pg_roles WHERE rolname LIKE '%alice%';

Public access vs Private Endpoint connectivity

Flexible Server defaults differ from older Single Server: new deployments can choose Public access (allowed IPs) with firewall rules, or Private access (VNet integration) which puts the server inside a subnet of a VNet and disables public IP.

For Querycop:

• Public access: the Querycop host’s outbound IP (or NAT egress IP) must be in the server’s firewall allow-list. If you’re testing from a developer laptop, add a temporary firewall rule: az postgres flexible-server firewall-rule create --resource-group myRG --name myserver --rule-name dev-laptop --start-ip-address 1.2.3.4 --end-ip-address 1.2.3.4.
• Private access / VNet: Querycop must run inside the same VNet (or a peered VNet with route + DNS resolution to the Private Endpoint). The hostname myserver.postgres.database.azure.com will resolve to a private IP via the Private DNS Zone.

A mis-configured VNet usually surfaces as backend dial failed: i/o timeout from Querycop — check the DNS resolution and the firewall first.

Client→proxy TLS vs proxy→Flexible Server TLS are SEPARATE legs

Leg	Configured by	Default
Client → Querycop	GATEKEEPER_PROXY_TLS_CERT / _KEY	OFF — plaintext unless you put TLS material in front
Querycop → Azure	GATEKEEPER_BACKEND_TLS_* (this page)	prefer (default) — upgrade to verify-full per recipe

In this cookbook the proxy→DB leg is verify-full. The client→proxy leg is your call — psql over plaintext to a localhost proxy is fine for development; for production put a TLS cert on Querycop (GATEKEEPER_PROXY_TLS_CERT / _KEY).

The Entra ID token never leaves Querycop, so even if the client→proxy leg is plaintext, the token is only ever in flight on the verify-full-protected backend leg.

az CLI must be on PATH where Querycop runs

The BACKEND_TOKEN_CMD shells out via sh -c; the spawned shell inherits the parent’s PATH. On a minimal container without the Azure CLI, the command fails fast with executable file not found in $PATH.

Two options:

• Install azure-cli in the Querycop runtime container.
• Wrap an explicit absolute path: BACKEND_TOKEN_CMD=/usr/bin/az account get-access-token ….

Managed Identity / service-principal env passthrough

The os.Environ() inheritance means AZURE_CONFIG_DIR / AZURE_CLIENT_ID / AZURE_TENANT_ID / AZURE_FEDERATED_TOKEN_FILE (and the standard workload-identity env an AKS-bound pod gets) all flow into the child shell — provided you set them on the parent (container spec, systemd unit, etc.).

For AKS + Workload Identity, bind a Kubernetes service account to an Entra ID workload identity that has the oss-rdbms scope on the target server, deploy Querycop with that KSA, and az picks up the federated credentials automatically.

For an Azure VM with a system-assigned managed identity, az account get-access-token resolves the identity via the IMDS endpoint with no extra env wiring — the identity must have been granted the oss-rdbms scope ahead of time.

Single Server is on a retirement path — confirm you’re on Flexible Server

This page is Flexible Server-only. If your endpoint hostname looks like <server>.postgres.database.azure.com it could still be either product family (the DNS suffix is shared). Confirm via:

az postgres server show --resource-group myRG --name myserver \
  --query sku
# vs
az postgres flexible-server show --resource-group myRG --name myserver \
  --query sku

The Single Server commands respond on a Single Server instance and 404 on a Flexible Server, and vice versa. The auth flow, AAD admin syntax, and pgaadauth extension surface all differ between the two — and Single Server is on a published retirement timeline that the cookbook is not tracking.

Cross-links

• docs/configuration.md §1.5 — backend TLS reference
• docs/configuration.md §1.6 — BACKEND_TOKEN_CMD reference
• Azure docs: Flexible Server — Microsoft Entra authentication
• Azure docs: Manage Entra users in Flexible Server
• Azure docs: Flexible Server networking — SSL/TLS