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

PostgreSQL Role and Privilege Hygiene Guard

Find expired logins, unexpected superusers, PUBLIC schema and table grants in PostgreSQL, alert Slack, apply quoted fixes and re-audit.

Categories
DataInfrastructure

Security reviews of a PostgreSQL database keep finding the same things:

  • a contractor's login role whose VALID UNTIL passed months ago, still LOGIN;
  • a DBA's personal role that is superuser;
  • a migration bot with CREATEROLE;
  • CREATE on schema public still granted to PUBLIC (the search_path issue behind CVE-2018-1058) on databases created before PostgreSQL 15;
  • a table someone granted to PUBLIC while debugging.

Each one takes one statement to fix, if anyone looks.

This blueprint looks every day. It audits the catalog, alerts Slack, and can run the generated fix statements one by one. It then audits again, so a fix that did not take fails the run.

This blueprint was created by zkasuran.

Findings

Finding Severity Rule Fix
EXPIRED_LOGIN High Login role past VALID UNTIL ALTER ROLE ... NOLOGIN
EXPIRING_LOGIN Low Login expires within expiring_days Report only
UNEXPECTED_SUPERUSER High Superuser outside privileged_allowlist ALTER ROLE ... NOSUPERUSER
UNEXPECTED_CREATEROLE High CREATEROLE outside the allowlist ALTER ROLE ... NOCREATEROLE
PUBLIC_CREATE_ON_SCHEMA High PUBLIC can create in a user schema REVOKE CREATE ON SCHEMA ... FROM PUBLIC
PUBLIC_TABLE_GRANT Medium A table, view or materialized view has privileges granted to PUBLIC REVOKE ALL ON TABLE ... FROM PUBLIC

Every fix statement is built inside PostgreSQL with format('%I') or regclass, so role and table names are quoted by the database itself. A role called Ops "Admin" is fixed as ALTER ROLE "Ops ""Admin""" NOSUPERUSER.

How it works

  1. audit (io.kestra.plugin.jdbc.postgresql.Query, read-only) runs one catalog query over pg_roles, pg_namespace and pg_class ACLs. It returns each finding with its fix.
  2. summary logs every finding.
  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) runs each fix in its own Query, so one failure does not stop the others.
      • reaudit runs the same audit again, and verify_result fails if any fixed finding is still there.
  4. errors alerts Slack when the audit cannot run.

What you get

  • A daily audit of the role and grant findings security reviews ask about, with severity and a reason for each.
  • A fix statement per finding, quoted by PostgreSQL itself.
  • Fixes run one by one behind two gates, then a second audit that fails the run if a fix did not take.

Who it's for

  • DBAs and platform teams responsible for many PostgreSQL databases.
  • Security and compliance teams who need evidence that expired accounts and broad grants are removed.
  • Teams evaluating the PostgreSQL plugin who want an example of catalog queries driving administrative statements.

Why orchestrate this with Kestra

The catalog query is easy to write once. Kestra runs it every day on every database, sends the result to Slack, runs the fixes with a gate and an audit trail, re-audits to prove them, and keeps the execution history as evidence for the next review.

Tested end to end

On Kestra 2.0.5 OSS against PostgreSQL 16, with a local HTTP sink standing in for Slack. Seeded with:

  • an expired contractor login and a login expiring in 11 days;
  • a superuser dba_jane and a CREATEROLE migration_bot;
  • CREATE ON SCHEMA public TO PUBLIC, and ALL ON customers TO PUBLIC.
Run Inputs Result
1 defaults 6 findings, postgres not flagged (allowlisted). Alert sent
2 auto_remediate: true, dry_run: true Same report. psql shows dba_jane still superuser
3 auto_remediate: true, dry_run: false 5 fixes run, re-audit confirms each one. Only the expiring login stays open (report only). psql shows NOLOGIN, NOSUPERUSER, NOCREATEROLE, no PUBLIC create on public, and customers ACL {postgres=arwdDxt/postgres}
4 a new superuser named Ops "Admin" Fixed with the correctly quoted statement
5 unreachable database errors alert sent

Prerequisites

  • For the audit, a role that can read the catalog (any role can). For remediation, a role with CREATEROLE and ownership of, or GRANT OPTION on, the objects, usually the database owner.
  • Local testing: docker run -d --name pg -p 5432:5432 -e POSTGRES_PASSWORD=pg postgres:16.

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 Database
privileged_allowlist [postgres] Roles allowed superuser or CREATEROLE
expiring_days 14 Expiry warning window
auto_remediate false Allow fixes
dry_run true Must be false for a statement to run

Roles are cluster-wide, but schemas and tables are per database: run one execution per database. The daily trigger is shipped disabled and always runs in safe mode.

Quick start

  1. Add the POSTGRES_USERNAME, POSTGRES_PASSWORD and SLACK_WEBHOOK_URL secrets.
  2. Import the flow and set jdbc_url and privileged_allowlist.
  3. Run it once and read the findings.
  4. Run it with auto_remediate: true and dry_run: false to apply the fixes.
  5. Enable the daily trigger, which always runs in safe mode.

How to extend

  • Loop over a list of JDBC URLs to audit every database of a cluster in one execution.
  • Add rules: roles that never logged in (from your connection logs), BYPASSRLS, default privileges granted to PUBLIC.
  • Store each audit in a table to report how long findings stayed open.
  • Ask for approval with a Pause before running fixes on production.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.