SECURITY DEFINER functions with an unset search_path

The vulnerability shape

Inside a definer function, an unqualified SELECT FROM orders resolves through the session search_path at execution time. If resolution can be influenced — a writable first-entry schema, or a name shadowing trick — attacker-chosen logic executes as the function owner, which typically outranks the caller considerably.

The exploit requires preconditions (writable temp/public placement, callable function privileges), which is why it is an audit item rather than a five-alarm fire. Audits age; the pin habit does not.

Resolution follows the path position by position, first match wins, and it applies to more than table names: helper functions called unqualified inside the body resolve through the same path, as do operators and types. An unset path leaves the ambient default in charge — typically $user followed by public — and any writable schema ahead of your trusted ones becomes a redirection point. EXECUTE privilege is the other half of the gate: a hardened function granted to public is reachable by everyone who can reach PostgREST's RPC surface.

Auditing your functions

prosecdef marks definer functions; proconfig holds their SET clauses. Missing or mutable entries are your list, and the query deliberately skips the system schemas where platform-owned helpers live:

Sort the output by reachability: cross-reference it against EXECUTE grants to anon and authenticated, because an unpinned definer function no caller can invoke is latent rather than active. Latent is not safe — it sits one grant away from active — but ordering lets you fix what is exposed first.

SELECT n.nspname AS schema, p.proname AS function_name,
       p.proconfig AS config
FROM pg_catalog.pg_proc p
JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.prosecdef
  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'extensions')
  AND (p.proconfig IS NULL
       OR NOT EXISTS (
         SELECT 1 FROM unnest(p.proconfig) cfg
         WHERE cfg LIKE 'search_path=%'
       ));

The pin pattern

Set the path to trusted schemas only, with pg_temp excluded by listing it last or not at all — an empty trailing element or explicit pg_temp placement defeats temp-object shadowing:

Pin narrowly: name only the schemas the body actually touches. A path pinned to everything-trusted recreates the original problem one schema at a time, because trust expands with each addition to the list.

CREATE OR REPLACE FUNCTION public.grant_team_seat(team_id uuid, member_id uuid)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
  IF NOT exists_team_owner((SELECT auth.uid()), team_id) THEN
    RAISE EXCEPTION 'not authorised';
  END IF;
  INSERT INTO public.team_seats (team_id, member_id) VALUES (team_id, member_id);
END;
$$;

Roadmap honesty

Function analysis — definer status, pinned paths, anon-executable grants — is a candidate rule in planning, with no date promised. Until it ships, the advisor covers this check well; run it alongside RowShield rather than instead of it, and revisit this page's queries whenever functions land through generated code.

Common false leads

Pinning the path is necessary and insufficient on its own. Qualify every name inside the body — including calls to helper functions, which resolve through the path like anything else — and review EXECUTE grants, since a definer helper callable by anon leaves the gate standing open behind its better lock. Treating the advisor warning as perpetual noise is the quiet failure mode: it stays open until the SET clause exists, and closing one function does nothing about the template used for the next.

Verifying the pin

After replacing the function, confirm the record and then the behaviour: proconfig should now list search_path among the SET clauses, and exercising the RPC as the anon caller should return the authorised result — or the refusal your guard raises — rather than an opaque error. Re-run the audit query after every generated function lands, because generated code rediscovers unpinned paths with remarkable regularity.

If the RPC surface is wide, script the exercise so every callable definer function is exercised after each pin migration, failing loudly on any opaque error.

Frequently asked

Does RowShield detect this today?

No. Function-level analysis is not among the shipped rules, and this page exists to hand you the queries rather than imply a check. The capability manifest states the limitation verbatim, and the roadmap candidate covers definer status, pinned paths and anon-executable grants.

Why pin pg_temp explicitly?

Temp-schema shadowing is the classic redirect: objects created in pg_temp resolve ahead of trusted schemas when the path is unpinned, and anyone who can trigger function calls can plant lookalike names there. Listing pg_temp last, or omitting it entirely, removes the ambiguity with one clause.

Is SECURITY DEFINER itself bad?

No — it is the standard tool for controlled elevation, especially helper functions referenced by policies. Unpinned search paths are the defect; the mechanism is sound when the path names trusted schemas only and the grants are deliberate.