logfiles.retention_days should be greater than 3 for PostgreSQL Servers

What does the recommendation "logfiles.retention_days should be greater than 3 for PostgreSQL Servers" check?

Flags any Azure Database for PostgreSQL flexible server whose 'logfiles.retention_days' server parameter is 3 or fewer, which is the default. That parameter governs how long the engine's downloadable server logs (the query and error logs you fetch through the server logs feature) are kept before they are purged from backup storage. The allowed range is 1 to 7 days. The check looks only at this retention value: it does not verify that the 'logfiles.download_enable' prerequisite is switched on, does not inspect diagnostic settings that ship logs to a Log Analytics workspace or storage account, and does not cover the legacy single server resource, whose equivalent parameter is the undotted 'log_retention_days'.

Why does "logfiles.retention_days should be greater than 3 for PostgreSQL Servers" matter?

Three days of logs is rarely enough to investigate anything. Most intrusions and data-handling mistakes are noticed days or weeks after they happen, by which point a 3-day window has already discarded the evidence. Without the connection attempts, failed authentications and slow or destructive queries from the relevant period, you cannot reconstruct what an attacker touched, prove to an auditor that no personal data was exfiltrated, or even debug a regression that surfaced over a weekend. The business consequence is a breach you can neither scope nor disprove and a compliance finding you cannot close, because regimes such as PCI DSS expect far longer retention than the default provides.

How do I fix "logfiles.retention_days should be greater than 3 for PostgreSQL Servers"?

1. Confirm the downloadable logs feature is on first: run 'az postgres flexible-server parameter set --resource-group <rg> --server-name <server> --name logfiles.download_enable --value on', because retention only applies once logs are actually being captured.
2. Raise the retention value: 'az postgres flexible-server parameter set --resource-group <rg> --server-name <server> --name logfiles.retention_days --value 7' sets it to the maximum the parameter allows. In the portal the same control lives under Server parameters, or behind the retention slider on the Server logs blade.
3. Because 7 days is still short for most audit obligations, add a diagnostic setting that forwards PostgreSQLLogs to a Log Analytics workspace or storage account where you can retain them for months, and bake both the parameter and the diagnostic setting into your Bicep or Terraform module so new servers inherit them by default.

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