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 belongs at the end of a search path, or not in it. Placed earlier, the caller creates a function there and the routine resolves to it before reaching the schema it meant:

SET search_path = pg_temp, app     -- the caller owns pg_temp
SET search_path = app, pg_temp     -- fine: reached only if nothing else matched

Nobody has to grant the attacker anything for the first line to work. It is their schema.

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, or of a schema only its owner may write to, 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, and pg_temp last if it is needed at all
    SET search_path = pg_catalog
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.