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.
Detect PostgreSQL SECURITY DEFINER functions with no pinned search_path, pin them behind an approval gate, then re-read proconfig to confirm.
A SECURITY DEFINER function runs with the privileges of its owner, not the caller. If it does not pin its own search_path, it resolves unqualified names against the caller's search_path. A user who can create objects in a schema that sits earlier on that path can shadow a table or function the definer function calls by a bare name. Their object then runs with the owner's rights. That is the CVE-2018-1058 hijack class.
The fix is one statement per function: ALTER FUNCTION ... SET search_path = ... so the function uses a fixed, safe path instead of the caller's. This guard reads pg_proc, finds every definer function whose proconfig has no search_path entry, pins a safe path on that set behind a gate, then re-reads proconfig and fails the run if any target is still unpinned.
This blueprint was created by zkasuran.
dry_run is true and auto_remediate is false, so a plain run reports and never changes a function.auto_remediate is true and dry_run is false. It then waits for a human to resume the Pause with approved set true. An unanswered gate reads as not approved.own_schema mode keeps the function's own schema on the path, so references to the function's own objects still resolve. Review the dry-run list before approving.detect (io.kestra.plugin.jdbc.postgresql.Query) reads pg_proc joined to pg_namespace, keeps the SECURITY DEFINER functions whose proconfig has no search_path entry. For each it returns the identity signature plus the exact ALTER FUNCTION statement that pins the chosen safe path.summarize (io.kestra.plugin.core.output.OutputValues) builds the human readable target list and the JSON the remediation loop iterates, so the log, the alert and the loop all read the same set.log_detect (io.kestra.plugin.core.log.Log) logs the count, the targets and the mode.guard (io.kestra.plugin.core.flow.If) branches on the count. With none found, clean logs and the run ends. Otherwise findings_alert posts the finding on every detection.mode (io.kestra.plugin.core.flow.If) enters the remediation branch only when auto_remediate is true and dry_run is false. Otherwise report_only lists what would be pinned and stops.approval_gate (io.kestra.plugin.core.flow.Pause) holds the run until a human resumes it. pin_functions (io.kestra.plugin.core.flow.Loop, concurrencyLimit: 1) then runs one ALTER FUNCTION per target.reverify re-runs the detection and verify_result fails the run through io.kestra.plugin.core.execution.Fail if any targeted function is still unpinned.own_schema keeps the function's own schema on the path, pg_catalog locks it down to pg_catalog and pg_temp.PostgreSQL has no built-in scheduler or approval step. A one-off script pins what it finds without checking the result unless you write that check too. Kestra adds the missing pieces. It schedules the sweep, gates the change behind dry-run and approval, builds the ALTER inside the database so names are quoted correctly, re-reads pg_proc.proconfig to turn a hopeful fix into a verified one and posts a Slack alert the moment a step fails.
Run on Kestra 2.0.5 against a PostgreSQL 16 server, with a local HTTP sink standing in for Slack. The database was seeded with one already pinned SECURITY DEFINER function, one SECURITY INVOKER function plus four unpinned SECURITY DEFINER functions. The four were a no-arg function, a one-arg function, an overload of it with two args plus one that already pins work_mem but not search_path. Counts were confirmed with psql against pg_proc.proconfig.
A PostgreSQL server reachable over JDBC. The connecting user must be able to read pg_proc and must own the functions it pins, which usually means the role that created them or a superuser.
POSTGRES_USERNAME: PostgreSQL user, read when use_secret_auth is true.POSTGRES_PASSWORD: password for that user.SLACK_WEBHOOK_URL: used by findings_alert and the errors block.SECRET_.| Name | Type | Default | Description |
|---|---|---|---|
postgres_url |
STRING | jdbc:postgresql://localhost:5432/postgres |
PostgreSQL JDBC URL for the target database. |
use_secret_auth |
BOOL | true |
Read credentials from secrets. Set false only for inline test credentials. |
schema_filter |
STRING | "" |
Optional single schema filter. Empty scans every user schema. |
pin_mode |
SELECT | own_schema |
own_schema pins to the function's schema plus pg_temp, pg_catalog pins to pg_catalog plus pg_temp. |
dry_run |
BOOL | true |
Report without changing anything. |
auto_remediate |
BOOL | false |
Pin behind the approval gate when dry_run is false. |
POSTGRES_USERNAME, POSTGRES_PASSWORD and SLACK_WEBHOOK_URL as namespace secrets.postgres_url pointed at a database that has SECURITY DEFINER functions. The default dry run reports the unpinned ones.dry_run false and auto_remediate true, then resume the paused execution with approved true.pin_mode to pg_catalog where every function references its objects by qualified name and you want the most locked down path.schema_filter to pin one schema at a time on a large database.prokind = 'p' and building an ALTER ROUTINE statement instead of ALTER FUNCTION.