Skip to main content

SEC.PRIV.ROUTINE_DEFINER_UNSAFE_PATH — a pinned search_path is not automatically a safe one

  • Category: security
  • Severity: medium
  • Level: 0
  • Confidence: deterministic
  • Downtime class: none — the finding is about a routine's privileges, not about a statement
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18

The clause is there. It is pointing at the wrong place​

SEC.PRIV.ROUTINE_DEFINER_MUTABLE_PATH asks whether a SECURITY DEFINER routine pins its own search_path. This rule asks what is in the path it pinned, and the two are not the same question.

A routine with SET search_path = public passes the first check completely. It is also exactly as exploitable as a routine with no clause at all, the moment somebody other than its owner may create objects in public — because the substitution does not need the caller to control the path. It only needs a schema in the path that the caller can write to.

-- public is writable by anyone here, which was PostgreSQL's own default before 15
GRANT CREATE ON SCHEMA public TO PUBLIC;

CREATE FUNCTION app.grant_access(uid bigint) RETURNS void
    LANGUAGE sql
    SECURITY DEFINER
    -- pinned, and still open: someone else can define now() in public
    SET search_path = public
AS $$
    INSERT INTO memberships (user_id, granted_at) VALUES (uid, now());
$$;

If public grants CREATE to anyone else, that role defines its own now() there, and the routine calls it — as the owner of app.grant_access.

Why this matters more on an older database than a new one​

PostgreSQL changed the default in 15: before that, every database was created with CREATE on public granted to PUBLIC. A database that grew through those versions usually still carries that grant, because removing it is a deliberate act nobody is prompted to take.

So the routine that reads as hardened — somebody added a SET clause on purpose — can be the one that is still open.

pg_temp is the second way, and it needs no grant at all​

The caller's own temporary schema is searched for tables, views and types before the path unless the path names it last, and a path that leaves it out does not name it. So only a path that ends in pg_temp keeps it out. Anywhere else, or missing, the caller creates a temporary table with a name the routine uses unqualified, and the routine reads and writes the caller's table instead of its own:

SET search_path = app              -- pg_temp is searched first, before app
SET search_path = ''               -- the same: pg_temp first, then the catalog
SET search_path = pg_temp, app     -- the caller's schema, named first
SET search_path = app, pg_temp     -- closed: reached only if nothing else matched

Nobody has to grant the attacker anything for the first three lines to work. It is their schema. PostgreSQL never searches pg_temp for functions or operators, so what the caller substitutes is a table, a view or a type.

What this rule measures, and what it refuses to judge​

It does not keep a list of paths that count as safe. It asks the catalog one question per schema in the routine's own path:

may anybody other than this routine's owner create objects here?

pg_namespace.nspacl answers it. A path of pg_catalog, pg_temp, or of schemas only the owner may write to followed by pg_temp, produces nothing at all — and the owner's own CREATE never counts, because a routine whose owner can write to its own path is not exposed by that.

That is why the finding names the schema rather than the path: the schema is the thing you change.

Fixing it​

Any one of these closes it:

CREATE FUNCTION app.grant_access(uid bigint) RETURNS void
    LANGUAGE sql
    SECURITY DEFINER
    -- a path only the owner can write to, ending with pg_temp
    SET search_path = pg_catalog, pg_temp
AS $$
    INSERT INTO app.memberships (user_id, granted_at) VALUES (uid, pg_catalog.now());
$$;

Or take the grant away, if public was never meant to be writable:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

The last one is worth considering on its own merits: on a database created before PostgreSQL 15 it is usually a leftover rather than a decision, and it widens far more than this one routine.

Why medium and not critical​

Both this and its sibling end with code running as the owner. This one needs a second condition that an operator can see and remove — somebody else holds CREATE on a schema in the path. The unpinned case needs no such grant: any caller who can create a schema is already there.