Query icon
OutputValues icon
Log icon
If icon
SlackIncomingWebhook icon
Loop icon
Fail icon
Schedule icon

PostgreSQL Role Timeout Guard

Find PostgreSQL login roles running without statement, idle-in-transaction or lock timeouts, alert Slack, set them with ALTER ROLE and verify.

Categories
DataInfrastructure

PostgreSQL ships with statement_timeout, idle_in_transaction_session_timeout and lock_timeout all at 0, meaning no limit. Three things follow:

  • an application that opens a transaction and waits on an HTTP call keeps its locks, and blocks vacuum, until someone kills it;
  • a migration waiting for a lock queues every query behind it;
  • a report with a bad plan runs for hours.

Setting the timeouts per role is the usual fix. Knowing which value a role really gets is harder: a role-in-database setting overrides the role, which overrides the database, which overrides the server.

This blueprint resolves that order for every login role and every database it can connect to, and reports the combinations where a timeout is 0. It alerts Slack, and can set the missing timeouts with ALTER ROLE at the scope that currently wins, then audit again to verify.

This blueprint was created by zkasuran.

Findings

Finding Severity Effect of a 0 value
NO_IDLE_IN_TRANSACTION_SESSION_TIMEOUT High An idle transaction keeps its locks and its snapshot forever, blocking vacuum
NO_STATEMENT_TIMEOUT Medium A runaway query runs until killed
NO_LOCK_TIMEOUT Medium A statement waits for a lock forever, and queues everything behind it

The effective value is resolved like PostgreSQL does at login:

  1. ALTER ROLE ... IN DATABASE;
  2. ALTER ROLE;
  3. ALTER DATABASE;
  4. the server configuration (pg_file_settings, else the built-in default).

Superusers, pg_* roles and excluded_roles are not checked.

How it works

  1. audit (io.kestra.plugin.jdbc.postgresql.Query, read-only) crosses every checked login role with every database it can connect to and the three settings. It resolves the effective value from pg_db_role_setting, and returns the combinations at 0 with their source and a fix. The fix is built with format('%I') and %L, at the role-in-database scope when that is the one setting 0, otherwise at the role scope.
  2. fixes removes duplicates, since a role-level fix covers every database. summary logs the findings grouped by role.
  3. route (io.kestra.plugin.core.flow.If):
    • clean: logs it.
    • findings: alert posts to Slack.
      • remediate_gate runs only when auto_remediate is true and dry_run is false.
      • remediate (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) runs each ALTER ROLE in turn. Concurrent ALTER ROLE ... SET on one role collide in pg_db_role_setting.
      • reaudit and verify_result fail if any combination is still at 0.
  4. errors alerts Slack when the audit cannot run.

What you get

  • The timeout every application role really runs with, per database, and where that value comes from.
  • Fixes at the right scope, so an explicit 0 set for one database is replaced rather than left winning.
  • A second audit as proof, and roles you exclude on purpose left alone.

Who it's for

  • Teams whose applications hold transactions open across network calls.
  • DBAs who have had to kill idle in transaction sessions blocking vacuum or migrations.
  • Teams evaluating the PostgreSQL plugin who want an example of a catalog query driving ALTER ROLE.

Why orchestrate this with Kestra

The settings are spread across four levels, and new roles arrive with every service. Kestra resolves them on a schedule, sends the roles at risk to Slack, applies the fix only behind auto_remediate and dry_run, one statement at a time, verifies the result, and keeps a history of every change.

Tested end to end

On Kestra 2.0.5 OSS against PostgreSQL 16 (databases app and postgres), with a local HTTP sink standing in for Slack. Seeded settings:

  • app_rw: statement_timeout=15s;
  • reporting: an explicit idle_in_transaction_session_timeout=0 at role level;
  • etl_nopass: lock_timeout=10s only in app;
  • database app: idle_in_transaction_session_timeout=5min;
  • migration_bot: excluded.
Run Inputs Result
1 defaults 28 combinations at 0 over 6 roles, each with its source. app_rw statement timeout and the database-level idle timeout in app are correctly seen as set. reporting's explicit 0 is reported with source ROLE. Alert sent
2 auto_remediate: true, dry_run: true Same report. pg_db_role_setting unchanged
3 auto_remediate: true, dry_run: false, before concurrencyLimit: 1 verify_result failed on 3 combinations. The PostgreSQL log shows tuple concurrently updated: parallel ALTER ROLE on the same role. The flow now runs them one at a time
4 same, sequential The 3 remaining fixes applied and verified. A new reporting session shows 30s, 1min, 10s. A new app_rw session keeps its own 15s, and pg_sleep(40) as app_rw is cancelled by the timeout
5 defaults Clean. migration_bot still runs with no timeout, as excluded
6 unreachable database errors alert sent

Prerequisites

  • For the audit, a role that can read pg_db_role_setting and pg_file_settings (by default a superuser). For remediation, a role with CREATEROLE.
  • Local testing:
    • docker run -d --name pg -p 5432:5432 -e POSTGRES_PASSWORD=pg postgres:16;
    • create a few login roles, and set timeouts on some of them with ALTER ROLE ... SET.

Secrets

  • POSTGRES_USERNAME / POSTGRES_PASSWORD: credentials used by every query.
  • SLACK_WEBHOOK_URL: used by alert and the errors block.
  • In Kestra OSS, provide each one as a base64-encoded environment variable prefixed SECRET_.

Inputs

Input Default Purpose
jdbc_url jdbc:postgresql://localhost:5432/app Server to audit, through any database
excluded_roles [migration_bot] Roles allowed to run without timeouts
statement_timeout 30s Value applied
idle_in_transaction_session_timeout 60s Value applied
lock_timeout 10s Value applied
auto_remediate false Allow ALTER ROLE
dry_run true Must be false for a statement to run

Quick start

  1. Add the POSTGRES_USERNAME, POSTGRES_PASSWORD and SLACK_WEBHOOK_URL secrets.
  2. Import the flow, set jdbc_url, and list in excluded_roles the roles that legitimately run long statements.
  3. Run it once and read the findings per role.
  4. Agree the values with the application owners, then run with auto_remediate: true and dry_run: false.
  5. Enable the daily trigger, which always runs in safe mode.

How to extend

  • Flag timeouts that are set but too high, such as a statement_timeout of a day.
  • Apply different values per role from a map, for example longer for reporting roles.
  • Also check transaction_timeout on PostgreSQL 17.
  • Loop over a list of servers to check every cluster.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.