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

PostgreSQL SECURITY DEFINER search_path Guard

Detect PostgreSQL SECURITY DEFINER functions with no pinned search_path, pin them behind an approval gate, then re-read proconfig to confirm.

Categories
DataInfrastructure

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.

Safety

  • Read-only by default. dry_run is true and auto_remediate is false, so a plain run reports and never changes a function.
  • A change happens only when 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.
  • Pinning is non-destructive and idempotent. ALTER FUNCTION ... SET search_path adds a setting to the function and keeps every other setting, so a function that already pins work_mem keeps it. Running the guard twice changes nothing the second time.
  • Pinning does change name resolution inside the function. The default 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.
  • The re-read of pg_proc.proconfig is the real gate, so an ALTER the connecting role is not allowed to run surfaces as a failed run rather than a false success.
  • Scope is plain functions (pg_proc.prokind = 'f'). A SECURITY DEFINER procedure would use ALTER ROUTINE and is left for the extension below.

How it works

  1. 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.
  2. 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.
  3. log_detect (io.kestra.plugin.core.log.Log) logs the count, the targets and the mode.
  4. 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.
  5. 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.
  6. 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.
  7. 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.

What you get

  • A check that reads the real definer set from the catalog, so it catches the functions that run as owner against an attacker-controlled search_path.
  • A generated ALTER FUNCTION per finding, built with format('%I') inside PostgreSQL, so schema and function names are quoted by the database and overloaded functions are pinned by their exact signature.
  • Two pinning modes. own_schema keeps the function's own schema on the path, pg_catalog locks it down to pg_catalog and pg_temp.
  • A real verification step. The run passes only when the second read of pg_proc.proconfig shows every target pinned.
  • Slack alerts on the findings and on any failure.

Who it's for

  • DBAs and platform engineers who inherit databases full of SECURITY DEFINER helper functions and need to know which ones can be hijacked.
  • Security and compliance teams who need evidence that definer functions pin a safe search_path.
  • Teams evaluating the PostgreSQL plugin who want an example that reads the catalog and drives gated, verified DDL.

Why orchestrate this with Kestra

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.

Tested end to end

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.

  • Detector can fail: against the healthy state (only the pinned definer and the invoker present) the detector returned 0. After the four unpinned definers were added it returned exactly 4, never the pinned one or the invoker, confirmed with psql.
  • Dry run detected the four, alerted and left every proconfig unchanged.
  • Remediate with approval pinned all four and the re-read reported 0 of them still unpinned. The already pinned function and the invoker were untouched. The work_mem function kept work_mem and gained search_path.
  • A role that could read the catalog but did not own the functions turned every ALTER into a no-op, the re-read found the targets still unpinned and the run failed and alerted.
  • An unreachable host failed the first query and fired the Slack alert.

Prerequisites

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.

Secrets

  • 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.
  • In Kestra OSS, provide each secret as a base64-encoded environment variable prefixed SECRET_.

Inputs

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.

Quick start

  1. Set POSTGRES_USERNAME, POSTGRES_PASSWORD and SLACK_WEBHOOK_URL as namespace secrets.
  2. Import this flow.
  3. Run it with postgres_url pointed at a database that has SECURITY DEFINER functions. The default dry run reports the unpinned ones.
  4. To pin them, run again with dry_run false and auto_remediate true, then resume the paused execution with approved true.

How to extend

  • Switch pin_mode to pg_catalog where every function references its objects by qualified name and you want the most locked down path.
  • Set schema_filter to pin one schema at a time on a large database.
  • Cover SECURITY DEFINER procedures by detecting prokind = 'p' and building an ALTER ROUTINE statement instead of ALTER FUNCTION.
  • Fail the run as soon as detection finds anything, for a read-only drift check with no remediation.

Links

See How

New to Kestra?

Use blueprints to kickstart your first workflows.