Enforce SSL connection should be enabled for PostgreSQL database servers

What does the recommendation "Enforce SSL connection should be enabled for PostgreSQL database servers" check?

Flags any Azure Database for PostgreSQL single server (resource type Microsoft.DBforPostgreSQL/servers) whose 'sslEnforcement' property is set to Disabled, which lets clients connect over plaintext as well as TLS. It checks only the server-level toggle: it does not inspect the minimum TLS version, the certificate clients present, or whether a given application actually negotiates an encrypted session, so a server can pass this control while still accepting an old TLS 1.0 handshake. It also does not cover Flexible Server, which has no sslEnforcement property and instead governs encryption through the require_secure_transport server parameter, so a separate recommendation applies there. The assessment is configuration-only and does not watch live traffic, so it tells you the door is closed, not who walked through it before you closed it.

Why does "Enforce SSL connection should be enabled for PostgreSQL database servers" matter?

With enforcement off, an application that omits sslmode can fall back to an unencrypted connection without any error or warning, so credentials, query payloads and result sets cross the network in clear text. Anyone positioned on the path, a compromised peered network, a misconfigured route or an on-path attacker, can read or alter that traffic, which is a textbook man-in-the-middle exposure: the attacker can harvest the connection login, replay it, and read every row the application reads. For a database holding customer or regulated data this is a reportable breach and a direct failure of common in-transit encryption obligations under PCI DSS, HIPAA and similar regimes, which can mean fines, mandatory notification and lost certification on top of the data loss itself. Enforcing SSL at the server closes the fallback once and for all, so the protection cannot be skipped by a careless client library, an out-of-date connection string or a developer testing locally.

How do I fix "Enforce SSL connection should be enabled for PostgreSQL database servers"?

1. Set sslEnforcement to Enabled on the server: az postgres server update --resource-group <rg> --name <server> --ssl-enforcement Enabled, or in the portal open the server, go to Connection security and turn on 'Enforce SSL connection', then Save.
2. Before enabling, confirm every client connects with sslmode=require (or stricter, such as verify-full with the Microsoft root CA), so the change does not break a connection that was silently running unencrypted.
3. Assign the built-in audit policy by display name so new servers are caught automatically: pid=$(az policy definition list --query "[?displayName=='Enforce SSL connection should be enabled for PostgreSQL database servers'].name" -o tsv); az policy assignment create --name pg-enforce-ssl --policy "$pid" --scope <scope>. Use the Deploy-PostgreSQL-sslEnforcement policy if you want remediation rather than audit only.

# 1. Lock the wire and the network on a production PostgreSQL flexible server.
RG=db-prod-rg
SRV=ordersprod-pg

# Enforce encrypted connections (dynamic, no restart needed).
az postgres flexible-server parameter set \
  --resource-group "$RG" --server-name "$SRV" \
  --source user-override \
  --name require_secure_transport --value ON

# Pin the minimum TLS version.
az postgres flexible-server parameter set \
  --resource-group "$RG" --server-name "$SRV" \
  --source user-override \
  --name ssl_min_protocol_version --value TLSv1.2

# Disable public network access.
az postgres flexible-server update \
  --resource-group "$RG" --name "$SRV" \
  --public-access Disabled

# 2. Turn on the pgaudit trail and connection throttling.
for p in "pgaudit.log=role,ddl,misc" \
         "pgaudit.log_client=ON" \
         "pgaudit.log_level=log" \
         "pgaudit.log_statement=on" \
         "pgaudit.log_statement_once=on" \
         "connection_throttle.enable=on"; do
  az postgres flexible-server parameter set \
    --resource-group "$RG" --server-name "$SRV" \
    --source user-override \
    --name "${p%%=*}" --value "${p#*=}"
done

# 3. Provision a Microsoft Entra administrator and disable password sign-in.
az postgres flexible-server ad-admin create \
  --resource-group "$RG" --server-name "$SRV" \
  --display-name "dba-team" \
  --object-id "$(az ad group show --group dba-team --query id -o tsv)" \
  --type Group
az postgres flexible-server update \
  --resource-group "$RG" --name "$SRV" \
  --microsoft-entra-auth Enabled --password-auth Disabled

# 4. Ratchet it shut: deny any future PostgreSQL flexible server with public access.
# Look the built-in policy up by its EXACT display name, never paste a guessed GUID.
PID=$(az policy definition list \
  --query "[?displayName=='Public network access should be disabled for PostgreSQL flexible servers'].name" \
  -o tsv)
az policy assignment create \
  --name deny-public-pg-flex \
  --policy "$PID" \
  --params '{"effect":{"value":"Deny"}}' \
  --scope "/subscriptions/00000000-0000-0000-0000-000000000000"