PostgreSQL Query Duration Logging Disabled on Azure

Query duration logging is one of those settings that nobody thinks about until an incident forces them to. A reporting endpoint starts timing out, a customer complains that the dashboard is crawling, or your security team needs to understand whether a long-running query was a runaway report or someone exfiltrating data row by row. In all of those cases, the first question is the same: how long did these queries actually take? If log_duration is off, you cannot answer it.

This Lensix check, postgresql_nologduration, inspects your Azure Database for PostgreSQL servers and flags any where duration logging is disabled.

---

What this check detects

Azure Database for PostgreSQL exposes a server parameter called log_duration. When enabled, PostgreSQL writes the execution time of every completed statement to the server logs. The check looks at this parameter on each PostgreSQL server in scope and reports a failure when it is set to off.

This applies to the Single Server deployment model and to Flexible Server, though the parameter names and tooling differ slightly between them. The underlying concept is identical: PostgreSQL can record how long statements run, and Azure surfaces that capability through a configurable server parameter.

---

Why it matters

Duration logging sits at the intersection of performance, reliability, and security, which is exactly why its absence is worth flagging.

Performance and reliability blind spots

Without duration data, you cannot tell the difference between a database that is healthy and one that is quietly degrading. Slow queries pile up, connection pools exhaust, and the first signal your team gets is an outage rather than a trend. Duration logs let you catch a query that crept from 50 milliseconds to 4 seconds before it takes down an application.

Security and forensic gaps

Long-running or unusual queries are a classic signature of data exfiltration and abusive access. An attacker dumping a large table, or an over-broad analytics job scanning everything, both show up as queries with abnormal duration. If you are responding to an incident and your logs have no timing information, you lose a key piece of evidence about what an attacker did and how long they had access to your data.

Operational cost

Every hour an engineer spends reproducing a slow-query problem in a staging environment is an hour they did not need to spend if the production logs already told the story. Duration logging is cheap insurance against expensive debugging sessions.

---

How to fix it

The remediation is a parameter change. It does not require a server restart for log_duration on most PostgreSQL versions, so the impact is minimal. Pick the path that matches your deployment model.

Azure CLI — Flexible Server

az postgres flexible-server parameter set \
  --resource-group my-resource-group \
  --server-name my-postgres-server \
  --name log_duration \
  --value on

Confirm the change took effect:

az postgres flexible-server parameter show \
  --resource-group my-resource-group \
  --server-name my-postgres-server \
  --name log_duration \
  --query "value" -o tsv

Azure CLI — Single Server

az postgres server configuration set \
  --resource-group my-resource-group \
  --server-name my-postgres-server \
  --name log_duration \
  --value on

Azure Portal

1. Open your PostgreSQL server in the Azure Portal.
2. Under Settings, select Server parameters.
3. Search for log_duration.
4. Set its value to ON.
5. Click Save.

Terraform

If you manage your databases as code, set the parameter in your configuration so it stays enforced. For Flexible Server:

resource "azurerm_postgresql_flexible_server_configuration" "log_duration" {
  name      = "log_duration"
  server_id = azurerm_postgresql_flexible_server.example.id
  value     = "on"
}

For Single Server, use the older configuration resource:

resource "azurerm_postgresql_configuration" "log_duration" {
  name                = "log_duration"
  resource_group_name = azurerm_resource_group.example.name
  server_name         = azurerm_postgresql_server.example.name
  value               = "on"
}

Getting more than just timing

Enabling log_duration alone gives you durations without query text. For practical troubleshooting, set a threshold so PostgreSQL logs the statement text for anything that runs longer than a given number of milliseconds:

az postgres flexible-server parameter set \
  --resource-group my-resource-group \
  --server-name my-postgres-server \
  --name log_min_duration_statement \
  --value 1000

That records the full SQL for any statement taking over one second, which is usually where your real problems live.

---

How to prevent it from happening again

Fixing one server is easy. Keeping the setting in place across every server you spin up is the real goal. A few layers help.

Enforce it with Azure Policy

Azure Policy can audit or deny PostgreSQL servers that do not have duration logging on. There is a built-in policy for the related log_duration setting, and you can assign it to a subscription or management group so new servers are caught automatically.

az policy assignment create \
  --name "require-pg-log-duration" \
  --display-name "Require PostgreSQL log_duration enabled" \
  --policy "eb6f77b9-bd53-4e35-a23d-7f65d5f0e442" \
  --scope "/subscriptions/00000000-0000-0000-0000-000000000000"

Bake it into your IaC modules

If every team provisions PostgreSQL through a shared Terraform or Bicep module, set log_duration to on inside that module as a default. Individual teams then inherit the correct setting without having to know it exists.

Gate it in CI/CD

Scan your Terraform plans before they apply. A tool like Checkov or tfsec can fail a pull request when a PostgreSQL resource is missing the logging configuration:

checkov -d ./infra --framework terraform

Add that step to your pipeline so a misconfigured server never reaches production in the first place.

Monitor continuously

Policy and CI gates cover new resources, but configuration drifts. Someone changes a parameter by hand during an incident and forgets to revert it. Continuous monitoring with Lensix catches that drift on existing servers and re-flags postgresql_nologduration so it does not silently slip back into a failing state.

---

Best practices

• Pair duration with statement logging. log_duration tells you how long, log_min_duration_statement tells you what. Together they are far more useful than either alone.
• Set a threshold, not a firehose. Logging only slow statements keeps signal high and storage cost low.
• Route logs to a central workspace. Send PostgreSQL logs to Azure Monitor or Log Analytics so they survive server replacement and are queryable alongside the rest of your telemetry.
• Set retention deliberately. Match your log retention to your compliance and incident-response needs rather than accepting the default.
• Review durations regularly. Build a dashboard of your slowest queries and review it on a cadence rather than waiting for an outage.
• Treat logging config as code. Parameters managed in Terraform or Bicep do not quietly drift, and changes go through review.

Duration logging is not glamorous, but it is the difference between debugging from evidence and debugging from a hunch. Turn it on once, enforce it everywhere, and your future incidents get shorter.

Enabling log_duration takes one command and almost no overhead. Leaving it off costs you visibility precisely when you need it most. Make it a default across your PostgreSQL fleet and let automation keep it that way.