Extract Data from Couchbase and Transform It with Pandas for Analytics
Query a Couchbase NoSQL bucket with N1QL and reshape the results in Python with Pandas, all in one orchestrated flow.
Find PostgreSQL login roles running without statement, idle-in-transaction or lock timeouts, alert Slack, set them with ALTER ROLE and verify.
PostgreSQL ships with statement_timeout, idle_in_transaction_session_timeout and lock_timeout all at 0, meaning no limit. Three things follow:
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.
| 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:
ALTER ROLE ... IN DATABASE;ALTER ROLE;ALTER DATABASE;pg_file_settings, else the built-in default).Superusers, pg_* roles and excluded_roles are not checked.
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.fixes removes duplicates, since a role-level fix covers every database. summary logs the findings grouped by role.route (io.kestra.plugin.core.flow.If):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.errors alerts Slack when the audit cannot run.idle in transaction sessions blocking vacuum or migrations.ALTER ROLE.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.
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;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 |
pg_db_role_setting and pg_file_settings (by default a superuser). For remediation, a role with CREATEROLE.docker run -d --name pg -p 5432:5432 -e POSTGRES_PASSWORD=pg postgres:16;ALTER ROLE ... SET.POSTGRES_USERNAME / POSTGRES_PASSWORD: credentials used by every query.SLACK_WEBHOOK_URL: used by alert and the errors block.SECRET_.| 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 |
POSTGRES_USERNAME, POSTGRES_PASSWORD and SLACK_WEBHOOK_URL secrets.jdbc_url, and list in excluded_roles the roles that legitimately run long statements.auto_remediate: true and dry_run: false.daily trigger, which always runs in safe mode.statement_timeout of a day.transaction_timeout on PostgreSQL 17.