pgaudit.log_statement_once should be set to "on" for Azure Database for PostgreSQL Servers

What does the recommendation "pgaudit.log_statement_once should be set to "on" for Azure Database for PostgreSQL Servers" check?

Flags any Azure Database for PostgreSQL flexible server where the pgaudit extension's 'pgaudit.log_statement_once' server parameter is not set to 'on'. With it 'on', the full statement text and bind parameters are written only with the first audit entry for a given statement and substatement combination, rather than repeated on every entry. The check reads only this one parameter value: it does not confirm that pgaudit is loaded, that 'pgaudit.log' selects any classes, or that audit logs are actually being shipped to a Log Analytics workspace or storage. It applies to the flexible server deployment model, not the retired single server.

Why does "pgaudit.log_statement_once should be set to "on" for Azure Database for PostgreSQL Servers" matter?

When 'pgaudit.log_statement_once' is left at its default of 'off', every audit row for a multi statement query repeats the entire SQL text and its parameters. A batch operation or a procedure with many substatements can balloon a single logical action into hundreds of near identical, bulky log lines. That inflates ingestion costs, slows the queries security and audit teams run during an investigation, and pushes high signal events out of any size capped or retention capped log faster. Setting it to 'on' keeps the statement text available once per statement, joined back to later rows by statement and substatement identifiers, so the audit trail stays complete but lean enough to actually search under incident pressure.

How do I fix "pgaudit.log_statement_once should be set to "on" for Azure Database for PostgreSQL Servers"?

1. Confirm the pgaudit extension is enabled first: add 'pgaudit' to the server's 'azure.extensions' allowlist and to 'shared_preload_libraries', then select the activity to audit with 'pgaudit.log' (for example 'WRITE,DDL,ROLE'); enabling 'shared_preload_libraries' requires a server restart.
2. Set the parameter with the Azure CLI: az postgres flexible-server parameter set --resource-group <rg> --server-name <server> --name pgaudit.log_statement_once --value on --source user-override (this is a dynamic parameter and does not need a restart).
3. Bake it into infrastructure as code so new servers ship compliant: in Bicep declare a child 'Microsoft.DBforPostgreSQL/flexibleServers/configurations' resource named 'pgaudit.log_statement_once' with properties { value: 'on', source: 'user-override' }, and to enforce it fleet wide assign the built-in audit policy by looking up its definition rather than hardcoding a GUID: pid=$(az policy definition list --query "[?displayName=='pgaudit.log_statement_once should be set to \"on\" for Azure Database for PostgreSQL Servers'].name" -o tsv); az policy assignment create --name pgaudit-once --policy "$pid" --scope <scope>.

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