pgaudit.log should include role, ddl, and misc for Azure Database for PostgreSQL Servers

What does the recommendation "pgaudit.log should include role, ddl, and misc for Azure Database for PostgreSQL Servers" check?

Inspects the 'pgaudit.log' server parameter on each Azure Database for PostgreSQL flexible server and flags any server whose value does not include the ROLE, DDL and MISC statement classes. ROLE captures CREATE, ALTER and DROP ROLE activity, DDL captures schema changes such as CREATE and DROP TABLE, and MISC captures commands like DISCARD, FETCH and VACUUM. It only reads the parameter value, so it does not verify that the pgaudit extension is actually allowlisted, loaded into shared_preload_libraries and created in the target databases, nor that the resulting logs are shipped anywhere via diagnostic settings. A server can therefore pass this check and still produce no usable audit trail if the extension is not installed or logs are not routed to Log Analytics, Event Hubs or storage.

Why does "pgaudit.log should include role, ddl, and misc for Azure Database for PostgreSQL Servers" matter?

Without ROLE, DDL and MISC auditing you have no authoritative record of who created a privileged login, altered a table, dropped an object or ran a maintenance command. When an account is compromised or an insider abuses access, these are exactly the events an investigator needs to reconstruct what happened, and they cannot be recovered after the fact. ROLE auditing matters specifically because pgaudit redacts passwords from CREATE and ALTER ROLE statements, whereas the engine's own log_statement writes them in clear text, so relying on log_statement for the same coverage leaks credentials into your logs. Missing audit coverage also fails common attestations such as CIS, PCI DSS and SOC 2, which can stall a customer deal or trigger audit findings.

How do I fix "pgaudit.log should include role, ddl, and misc for Azure Database for PostgreSQL Servers"?

1. Make pgaudit usable first: allowlist it by setting the 'azure.extensions' parameter to include PGAUDIT, add it to 'shared_preload_libraries' (this requires a server restart), then run CREATE EXTENSION pgaudit in each database you want audited.
2. Set the audit scope by running 'az postgres flexible-server parameter set --resource-group <rg> --server-name <server> --source user-override --name pgaudit.log --value "ROLE,DDL,MISC"'. The classes must be listed individually because Azure does not support the pgaudit minus-sign shortcut; combine with READ and WRITE if you also need data-access auditing.
3. Bake the setting into your Bicep or Terraform module as a 'configurations' resource for pgaudit.log so every new server inherits it, and pair it with a diagnostic setting that forwards PostgreSQLLogs to Log Analytics so the AUDIT entries are actually retained and queryable.

# 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"