Bulk Load CSV Data into PostgreSQL Using COPY for Fast ETL Ingestion
Bulk load a CSV file into PostgreSQL with the native COPY protocol for fast, reliable ETL ingestion.
Find expired logins, unexpected superusers, PUBLIC schema and table grants in PostgreSQL, alert Slack, apply quoted fixes and re-audit.
Security reviews of a PostgreSQL database keep finding the same things:
VALID UNTIL passed months ago, still LOGIN;CREATEROLE;CREATE on schema public still granted to PUBLIC (the search_path issue behind CVE-2018-1058) on databases created before PostgreSQL 15;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.
| 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.
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.summary logs every finding.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) 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.errors alerts Slack when the audit cannot run.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.
On Kestra 2.0.5 OSS against PostgreSQL 16, with a local HTTP sink standing in for Slack. Seeded with:
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 |
CREATEROLE and ownership of, or GRANT OPTION on, the objects, usually the database owner.docker run -d --name pg -p 5432:5432 -e POSTGRES_PASSWORD=pg postgres:16.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 |
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.
POSTGRES_USERNAME, POSTGRES_PASSWORD and SLACK_WEBHOOK_URL secrets.jdbc_url and privileged_allowlist.auto_remediate: true and dry_run: false to apply the fixes.daily trigger, which always runs in safe mode.BYPASSRLS, default privileges granted to PUBLIC.Pause before running fixes on production.