Leaked Postgres password rotation with OpenBao, approval and login checks

id: openbao-leaked-postgres-credential-rotation
namespace: company.security

inputs:
  - id: role
    type: STRING
    displayName: OpenBao static role
    description: Static role in OpenBao's database secrets engine that manages the
      leaked Postgres password.
    defaults: app

  - id: fingerprint
    type: STRING
    displayName: SHA-512 of the leaked value
    description: Lowercase hex SHA-512 of the exact leaked string, without a
      trailing newline. The scanner job computes it, so the secret itself is
      never sent to Kestra.
    validator: ^[0-9a-f]{128}$

  - id: source
    type: STRING
    displayName: Reported by
    description: The scanner or person that found the leak.
    defaults: manual

  - id: location
    type: STRING
    displayName: Where it leaked
    description: A commit, file and line, a ticket or a URL.
    required: false

variables:
  openbao_addr: http://localhost:8200
  database_mount: database
  pg_host: localhost
  pg_port: "5432"
  pg_database: app
  monitor_user: kestra_monitor
  approval_window: P1D
  psql_image: postgres:17.11-alpine

concurrency:
  limit: 1
  behavior: QUEUE

tasks:
  - id: read_current
    type: io.kestra.plugin.core.http.Request
    description: Reads the role's current password from OpenBao. With `encryptBody`
      the response is stored encrypted in the execution, and later tasks only
      see it decrypted while they render it.
    uri: "{{ vars.openbao_addr }}/v1/{{ vars.database_mount }}/static-creds/{{
      inputs.role }}"
    method: GET
    headers:
      X-Vault-Token: "{{ secret('OPENBAO_TOKEN') }}"
    encryptBody: true

  - id: current
    type: io.kestra.plugin.core.output.OutputValues
    description: Keeps only fields that are not secret. `leaked_is_live` compares
      the SHA-512 of the live password with the reported one, so no password is
      written to an output.
    values:
      username: "{{ fromJson(outputs.read_current.encryptedBody).data.username }}"
      last_rotation: "{{
        fromJson(outputs.read_current.encryptedBody).data.last_vault_rotation
        }}"
      leaked_is_live: "{{ (fromJson(outputs.read_current.encryptedBody).data.password
        | sha512) == inputs.fingerprint }}"

  - id: label_role
    type: io.kestra.plugin.core.execution.Labels
    labels:
      role: "{{ inputs.role }}"

  - id: not_live
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.current.values.leaked_is_live == 'false' }}"
    then:
      - id: label_not_live
        type: io.kestra.plugin.core.execution.Labels
        labels:
          outcome: not-current

      - id: log_not_live
        type: io.kestra.plugin.core.log.Log
        message: >-
          The value reported by {{ inputs.source }}{{ (inputs.location ?? '') ==
          '' ? '' : ' at ' ~ inputs.location }} is not the current password of
          static role {{ inputs.role }} (Postgres user {{
          outputs.current.values.username }}, last rotated {{
          outputs.current.values.last_rotation }}). Either it was rotated out
          already, or the scanner hashed a different string: the fingerprint
          must be the SHA-512 of the exact value, without a trailing newline.
          Nothing was changed.

      - id: stop_not_live
        type: io.kestra.plugin.core.execution.Exit
        state: SUCCESS

  - id: sessions
    type: io.kestra.plugin.jdbc.postgresql.Query
    description: Lists the user's open sessions in every database of the cluster.
      The monitoring role needs `pg_read_all_stats` to see other users' client
      addresses and start times.
    url: "jdbc:postgresql://{{ vars.pg_host }}:{{ vars.pg_port }}/{{
      vars.pg_database }}"
    username: "{{ vars.monitor_user }}"
    password: "{{ secret('PG_MONITOR_PASSWORD') }}"
    sql: |
      select pid, datname, application_name, host(client_addr) as client_addr, state, backend_start
      from pg_stat_activity
      where usename = :username and backend_type = 'client backend'
      order by backend_start
    parameters:
      username: "{{ outputs.current.values.username }}"
    fetchType: FETCH

  - id: label_pending
    type: io.kestra.plugin.core.execution.Labels
    labels:
      outcome: awaiting-approval
      sessions_open: "{{ outputs.sessions.size }}"

  - id: show_leak
    type: io.kestra.plugin.core.log.Log
    message: |
      The value reported by {{ inputs.source }}{{ (inputs.location ?? '') == '' ? '' : ' at ' ~ inputs.location }} is the live password of OpenBao static role {{ inputs.role }} (Postgres user {{ outputs.current.values.username }}, last rotated {{ outputs.current.values.last_rotation }}).
      Open sessions of {{ outputs.current.values.username }}: {{ outputs.sessions.size }}
      {% for s in outputs.sessions.rows %}  pid {{ s.pid }}  {{ s.application_name == '' ? '(no application_name)' : s.application_name }} from {{ s.client_addr ?? 'a local socket' }}, connected since {{ s.backend_start }}, {{ s.state }}, database {{ s.datname }}
      {% endfor %}Rotating the password does not end these sessions. Resume with ROTATE_AND_TERMINATE to rotate it and end every session opened before the rotation, ROTATE to rotate it only, or REJECT to change nothing.

  - id: approve
    type: io.kestra.plugin.core.flow.Pause
    description: Nothing changes in OpenBao or Postgres before someone resumes this
      execution. If nobody answers within `approval_window`, the task fails and
      the `errors` handler labels the execution.
    pauseDuration: "{{ vars.approval_window }}"
    behavior: FAIL
    onResume:
      - id: decision
        type: SELECT
        displayName: Decision
        description: ROTATE_AND_TERMINATE also ends the sessions opened with the leaked
          password, so applications reconnect with the new one. REJECT leaves
          the password and the sessions as they are.
        isRadio: true
        expression: >-
          {{ [ {'label': 'ROTATE_AND_TERMINATE: new password for ' ~
          outputs.current.values.username ~ ', end its ' ~ outputs.sessions.size
          ~ ' sessions', 'value': 'ROTATE_AND_TERMINATE'}, {'label': 'ROTATE:
          new password for ' ~ outputs.current.values.username ~ ', sessions
          stay connected', 'value': 'ROTATE'}, {'label': 'REJECT: change
          nothing', 'value': 'REJECT'} ] }}

      - id: note
        type: STRING
        displayName: Note
        description: Why you decided this (an incident or change ticket, for example).
          Kept in the execution.
        required: false

  - id: rejected
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.approve.onResume.decision == 'REJECT' }}"
    then:
      - id: label_rejected
        type: io.kestra.plugin.core.execution.Labels
        labels:
          outcome: rejected

      - id: log_rejected
        type: io.kestra.plugin.core.log.Log
        level: WARN
        message: "Rotation of static role {{ inputs.role }} was rejected{{
          (outputs.approve.onResume.note ?? '') == '' ? '' : ': ' ~
          outputs.approve.onResume.note }}. The leaked password still logs in as
          {{ outputs.current.values.username }}, and its {{
          outputs.sessions.size }} sessions stay open."

      - id: stop_rejected
        type: io.kestra.plugin.core.execution.Exit
        state: WARNING

  - id: rotate
    type: io.kestra.plugin.core.http.Request
    description: OpenBao generates a new password and sets it on the Postgres user
      with its rotation statement. The password never passes through Kestra on
      the way in.
    uri: "{{ vars.openbao_addr }}/v1/{{ vars.database_mount }}/rotate-role/{{
      inputs.role }}"
    method: POST
    headers:
      X-Vault-Token: "{{ secret('OPENBAO_TOKEN') }}"

  - id: read_new
    type: io.kestra.plugin.core.http.Request
    uri: "{{ vars.openbao_addr }}/v1/{{ vars.database_mount }}/static-creds/{{
      inputs.role }}"
    method: GET
    headers:
      X-Vault-Token: "{{ secret('OPENBAO_TOKEN') }}"
    encryptBody: true

  - id: rotated
    type: io.kestra.plugin.core.output.OutputValues
    values:
      username: "{{ fromJson(outputs.read_new.encryptedBody).data.username }}"
      rotated_at: "{{ fromJson(outputs.read_new.encryptedBody).data.last_vault_rotation }}"

  - id: verify_rotation
    type: io.kestra.plugin.core.execution.Assert
    description: OpenBao must report a new rotation time and a password whose
      SHA-512 differs from the leaked one.
    conditions:
      - "{{ outputs.rotated.values.rotated_at !=
        outputs.current.values.last_rotation }}"
      - "{{ (fromJson(outputs.read_new.encryptedBody).data.password | sha512) !=
        inputs.fingerprint }}"
    errorMessage: "OpenBao still reports the leaked password for static role {{
      inputs.role }} (last rotated {{ outputs.rotated.values.rotated_at }})."

  - id: end_sessions
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.approve.onResume.decision == 'ROTATE_AND_TERMINATE' }}"
    then:
      - id: terminate
        type: io.kestra.plugin.jdbc.postgresql.Query
        description: Ends every session of the user that started before the rotation, so
          it was opened with the leaked password. `pg_terminate_backend` waits
          up to 5 seconds for each one to exit. The monitoring role needs
          `pg_signal_backend`.
        url: "jdbc:postgresql://{{ vars.pg_host }}:{{ vars.pg_port }}/{{
          vars.pg_database }}"
        username: "{{ vars.monitor_user }}"
        password: "{{ secret('PG_MONITOR_PASSWORD') }}"
        sql: |
          select pid, application_name, host(client_addr) as client_addr, backend_start, pg_terminate_backend(pid, 5000) as terminated
          from pg_stat_activity
          where usename = :username and backend_type = 'client backend' and backend_start < cast(:rotated_at as timestamptz)
        parameters:
          username: "{{ outputs.rotated.values.username }}"
          rotated_at: "{{ outputs.rotated.values.rotated_at }}"
        fetchType: FETCH

  - id: new_login
    type: io.kestra.plugin.jdbc.postgresql.Query
    description: Logs in with the new password. Pooling is off, so no connection of
      this user stays open in Kestra.
    url: "jdbc:postgresql://{{ vars.pg_host }}:{{ vars.pg_port }}/{{
      vars.pg_database }}"
    username: "{{ outputs.rotated.values.username }}"
    password: "{{ fromJson(outputs.read_new.encryptedBody).data.password }}"
    connectionPooling: false
    sql: select current_user as logged_in_as
    fetchType: FETCH_ONE

  - id: old_login
    type: io.kestra.plugin.scripts.shell.Commands
    description: Tries the leaked password with `psql`. The task succeeds only when
      Postgres answers "password authentication failed"; a login, or any other
      error such as a refused connection, fails it.
    containerImage: "{{ vars.psql_image }}"
    taskRunner:
      type: io.kestra.plugin.scripts.runner.docker.Docker
      networkMode: host
    env:
      PGHOST: "{{ vars.pg_host }}"
      PGPORT: "{{ vars.pg_port }}"
      PGDATABASE: "{{ vars.pg_database }}"
      PGUSER: "{{ outputs.current.values.username }}"
      PGPASSWORD: "{{ fromJson(outputs.read_current.encryptedBody).data.password }}"
      PGCONNECT_TIMEOUT: "10"
    commands:
      - |
        if out=$(psql -X -tA -c 'select 1' 2>&1); then
          echo "The leaked password still logs in as $PGUSER."
          exit 1
        fi
        case "$out" in
          *"password authentication failed"*) echo "Postgres rejected the leaked password: $(echo "$out" | grep -o 'password authentication failed.*' | head -n 1)" ;;
          *) echo "The login failed for another reason, so the check is inconclusive: $out"; exit 1 ;;
        esac

  - id: leftover
    type: io.kestra.plugin.jdbc.postgresql.Query
    description: Sessions of the user that are still open and started before the rotation.
    url: "jdbc:postgresql://{{ vars.pg_host }}:{{ vars.pg_port }}/{{
      vars.pg_database }}"
    username: "{{ vars.monitor_user }}"
    password: "{{ secret('PG_MONITOR_PASSWORD') }}"
    sql: |
      select pid, application_name, host(client_addr) as client_addr, backend_start
      from pg_stat_activity
      where usename = :username and backend_type = 'client backend' and backend_start < cast(:rotated_at as timestamptz)
      order by backend_start
    parameters:
      username: "{{ outputs.rotated.values.username }}"
      rotated_at: "{{ outputs.rotated.values.rotated_at }}"
    fetchType: FETCH

  - id: verify_logins
    type: io.kestra.plugin.core.execution.Assert
    conditions:
      - "{{ outputs.new_login.row.logged_in_as ==
        outputs.rotated.values.username }}"
      - "{{ outputs.approve.onResume.decision != 'ROTATE_AND_TERMINATE' or
        outputs.leftover.size == 0 }}"
    errorMessage: "After the rotation of static role {{ inputs.role }}, {{
      outputs.leftover.size }} sessions opened with the leaked password are
      still connected."

  - id: label_rotated
    type: io.kestra.plugin.core.execution.Labels
    labels:
      outcome: rotated
      sessions_ended: "{{ outputs.terminate.size ?? 0 }}"
      sessions_left: "{{ outputs.leftover.size }}"

  - id: log_summary
    type: io.kestra.plugin.core.log.Log
    level: "{{ outputs.leftover.size > 0 ? 'WARN' : 'INFO' }}"
    message: |
      Rotated the password of static role {{ inputs.role }} at {{ outputs.rotated.values.rotated_at }}{{ (outputs.approve.onResume.note ?? '') == '' ? '' : ', approved with the note "' ~ outputs.approve.onResume.note ~ '"' }}.
      The new password logs in as {{ outputs.new_login.row.logged_in_as }}, and Postgres rejects the leaked one.
      {% if outputs.approve.onResume.decision == 'ROTATE_AND_TERMINATE' %}Ended {{ outputs.terminate.size }} sessions opened with the leaked password; none remain.{% elseif outputs.leftover.size == 0 %}No session opened with the leaked password is still connected.{% else %}{{ outputs.leftover.size }} sessions opened with the leaked password are still connected:
      {% for s in outputs.leftover.rows %}  pid {{ s.pid }}  {{ s.application_name }} from {{ s.client_addr ?? 'a local socket' }}, connected since {{ s.backend_start }}
      {% endfor %}{% endif %}

errors:
  - id: label_failed
    type: io.kestra.plugin.core.execution.Labels
    labels:
      outcome: failed

  - id: log_failure
    type: io.kestra.plugin.core.log.Log
    level: ERROR
    message: "Responding to the leak of static role {{ inputs.role }} failed in {{
      (errorLogs() | last).taskId ?? 'an unknown task' }}: {{ (errorLogs() |
      last).message ?? 'see the execution logs' }}. {{ (outputs.rotate.code ??
      0) == 204 ? 'The password was rotated before the failure.' : 'The password
      was not rotated.' }}"

outputs:
  - id: rotated_at
    type: STRING
    value: "{{ outputs.rotated.values.rotated_at ?? '' }}"

  - id: sessions_ended
    type: STRING
    value: "{{ outputs.terminate.size ?? '' }}"

triggers:
  - id: on_leak
    type: io.kestra.plugin.core.trigger.Webhook
    description: The scanner job posts `{"role", "sha512", "source", "location"}`.
      Calls without a role name or a 128-character hex fingerprint start
      nothing.
    key: "{{ secret('LEAK_WEBHOOK_KEY') }}"
    when: >-
      {{ ((trigger.body.role ?? '') | regexMatch(regex='^[A-Za-z0-9_.-]+$')) and
      (((trigger.body.sha512 ?? '') | lower) |
      regexMatch(regex='^[0-9a-f]{128}$')) }}
    inputs:
      role: "{{ trigger.body.role }}"
      fingerprint: "{{ trigger.body.sha512 | lower }}"
      source: "{{ trigger.body.source ?? 'webhook' }}"
      location: "{{ trigger.body.location ?? '' }}"

When a scanner finds a database password in a commit, rotating it is only half of the response: sessions opened with the leaked password stay connected after the password changes, and you want proof that the leaked value no longer works. This flow takes the finding as a SHA-512 fingerprint, checks it against the live password of an OpenBao static role, shows the sessions that use it, and after approval rotates the password through OpenBao's database secrets engine, ends those sessions, and tests the new and the old password against Postgres.

This blueprint was created by kruxarth.

How it works

on_leak (Webhook) receives {"role", "sha512", "source", "location"} from the scanner job. The secret itself never reaches Kestra. Calls without a role name and a 128-character hex fingerprint get a 204 and start nothing.
read_current (http.Request with encryptBody) reads the role's credentials from OpenBao, and current compares the SHA-512 of the live password with the fingerprint.
No match: the leaked value is not the current password. The execution ends with outcome: not-current and changes nothing.
Match: sessions (PostgreSQL Query) lists the user's sessions from pg_stat_activity, show_leak logs them, and approve (Pause) waits for ROTATE_AND_TERMINATE, ROTATE or REJECT, and a note. REJECT ends in WARNING; no answer within approval_window (1 day) fails the execution.
rotate calls OpenBao's rotate-role, and verify_rotation (Assert) requires a new last_vault_rotation and a different password.
ROTATE_AND_TERMINATE: terminate runs pg_terminate_backend on every session of the user that started before the rotation.
new_login logs in with the new password, old_login runs psql with the leaked one and passes only on "password authentication failed", and verify_logins checks that no session opened before the rotation is left.

What you get

A stale finding, or one hashed from a different string, never rotates anything.
Proof in every rotated execution: the new password logs in, Postgres rejects the leaked one, and the sessions opened with it are gone.
No password in clear text: OpenBao responses are stored encrypted, and the tasks keep only hashes, usernames and timestamps.
One execution at a time, so a second report of the same leak waits and then finds its fingerprint stale instead of rotating again.
Labels outcome, role, sessions_open, sessions_ended and sessions_left on every execution.

Who it's for

Security and platform teams that manage Postgres application passwords with OpenBao static roles and receive secret-scanning findings.
Teams that need an approved, recorded response to a leaked credential, with evidence that the old one no longer works.

Why orchestrate this with Kestra

The scanner calls Kestra directly, and each leak becomes its own execution with logs, labels and an approval form.
encryptBody, secret() and hash comparisons keep the credentials out of the execution data.
OpenBao, Postgres and psql are called directly from one YAML file, which is also the runbook.

Prerequisites

OpenBao with the database secrets engine at database/ and a static role for the application user:

bao secrets enable database
bao write database/config/app-db plugin_name=postgresql-database-plugin \
  connection_url='postgresql://{{username}}:{{password}}@localhost:5432/app' \
  username=bao_admin password=<password> password_authentication=scram-sha-256 allowed_roles=app
bao write database/static-roles/app db_name=app-db username=app_user rotation_period=24h

A Postgres role for Kestra: create role kestra_monitor login password '...'; grant pg_read_all_stats, pg_signal_backend to kestra_monitor;
kestra.encryption.secret-key in the Kestra configuration (for example openssl rand -base64 32). encryptBody needs it.
Docker on the Kestra host for the psql check.
A scanner job that sends the fingerprint, for example after gitleaks: jq -j '.[0].Secret' leaks.json | sha512sum | cut -d' ' -f1.

Secrets

OPENBAO_TOKEN: a token whose policy allows only read on database/static-creds/<role> and update on database/rotate-role/<role>.
PG_MONITOR_PASSWORD: the password of monitor_user.
LEAK_WEBHOOK_KEY: the key in the webhook URL.

Quick start

Configure OpenBao and Postgres, add the secrets, set openbao_addr and the pg_* variables, and save the flow.
Post a fingerprint of the current password to /api/v1/main/executions/webhook/company.security/openbao-leaked-postgres-credential-rotation/<LEAK_WEBHOOK_KEY>.
The execution pauses and its logs list the sessions. Click Resume, choose ROTATE_AND_TERMINATE and add a note.
The summary log and the labels show the rotation time and the sessions that were ended. Post the same fingerprint again: it ends with outcome: not-current.

Things to know

Applications must read the password from OpenBao when they connect. ROTATE leaves their sessions alone, to reconnect at the next deploy.
Sessions are compared with OpenBao's last_vault_rotation, so keep the OpenBao and Postgres clocks in sync.
While one leak waits for approval, other reports queue behind it.
The fingerprint is an unsalted SHA-512: fine for the random passwords OpenBao generates, not for short human-chosen ones.
old_login fails the execution if the leaked password still logs in, for example when pg_hba.conf trusts the client's address.

How to extend

Notify the approvers from onPause on approve.
Close the scanner alert after a successful rotation.
Use rotate-root when the leaked secret is the engine's own connection password.

Rotate a leaked Postgres password with OpenBao after approval, and prove the old one is dead

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Prerequisites

Secrets

Quick start

Things to know

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

Rotate a leaked Postgres password with OpenBao after approval, and prove the old one is dead

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Prerequisites

Secrets

Quick start

Things to know

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Infrastructure Blueprints

Netdata Node Health and System Alarm Sentinel

NetBox Drift Reconciliation with GitHub Audit Trail

Run Ansible Against NetBox Devices by Role and Site

Netlify Deploy Failure Sentinel

Provision a VM and Register It in NetBox

Audit NetworkPolicy Coverage Across Every Namespace

New to Kestra?