warn-security-definer-search-path

What triggers it

A CreateFunctionStmt whose options contain a DefElem with defname security and arg.Boolean.boolval === true, and contain no DefElem with defname set whose arg.VariableSetStmt.name is search_path. is_procedure only selects the word Procedure or Function in the message. CREATE OR REPLACE FUNCTION parses to the same node and is covered.

What does not

A function with no security option, or an explicit SECURITY INVOKER (boolval false). A function that carries a SET search_path clause of any kind. An unrelated SET clause such as SET work_mem does not satisfy the pin check and does not suppress the finding. ALTER FUNCTION ... SECURITY DEFINER on an already-existing function parses as a different node and is never seen. No catalog is consulted, so this fires with or without --database-url.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

No table lock at all. CREATE FUNCTION writes a pg_proc row and touches no user table. This rule is about privilege escalation rather than blocking: the body runs later, with the owner's rights, against whatever the caller's search_path made its unqualified names resolve to.

Why it matters

SECURITY DEFINER runs the body with the privileges of the function's owner rather than the caller's, and search_path decides what every unqualified name in that body resolves to. Leave it unpinned and the caller supplies it — the caller being precisely the person who does not have the owner's privileges. Creating a schema with a shadowing table and putting it first in search_path redirects the function's writes, with the owner's rights. EXECUTE on new functions is granted to PUBLIC by default, so that is usually everyone.

Unsafe, and safe

CREATE FUNCTION promote_user(uid int) RETURNS void AS $$
  UPDATE users SET role = 'admin' WHERE id = uid;
$$ LANGUAGE sql SECURITY DEFINER;
-- Caller decides which "users" this writes to

CREATE FUNCTION promote_user(uid int) RETURNS void AS $$
  UPDATE public.users SET role = 'admin' WHERE id = uid;
$$ LANGUAGE sql
  SECURITY DEFINER
  SET search_path = pg_catalog, public;

What it assumes

The function body is never parsed. A body that schema-qualifies every single reference is still flagged, because the rule cannot prove that it does — and the SET clause is the guarantee while qualification is belt and braces. Only the presence of a search_path SET is checked, never its value, so SET search_path = public counts as pinned even though public is the schema an attacker is most likely to be able to write to. In the other direction, the rule cannot see who will own the function or who holds EXECUTE, so it cannot separate a superuser-owned escalation path from a SECURITY DEFINER function owned by an unprivileged role.

What the CLI prints

✗ [MP099] CRITICAL (line 1)
  Function "promote_user" is SECURITY DEFINER with no SET search_path. The caller controls how unqualified names in the body resolve, so they can shadow the objects it reads and have it act on theirs with the owner's privileges.

  Safe alternative:
  -- Pin search_path on the function so the caller cannot influence it:
  CREATE FUNCTION promote_user(...) RETURNS ...
    LANGUAGE plpgsql
    SECURITY DEFINER
    SET search_path = pg_catalog, public
  AS $$ ... $$;
  
  -- Then restrict who can call it: SECURITY DEFINER functions are
  -- executable by PUBLIC by default:
  REVOKE EXECUTE ON FUNCTION promote_user FROM PUBLIC;
  GRANT EXECUTE ON FUNCTION promote_user TO app_role;
  
  -- Schema-qualifying every reference inside the body is worth doing as well;
  -- the SET clause is the guarantee, qualification is the belt and braces.

  Why: SECURITY DEFINER runs the body with the owner's privileges, and search_path decides what the unqualified names in that body point at. Leave it unpinned and the caller supplies it: they create a table that shadows one the function reads, put their schema first on the path, and the function does privileged work against their object instead of yours. Functions owned by a superuser or by the schema owner turn this into privilege escalation for anyone who can call them. PostgreSQL accepts the function without a word, so nothing surfaces until someone goes looking, and by then the function is deployed and callable.
  Docs: https://migrationpilot.dev/rules/mp099

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP099
CREATE FUNCTION promote_user(uid int) RETURNS void AS $$

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP099: false

# or keep it, and downgrade it
rules:
  MP099:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP073ban-superuser-rolesuperuser privileges in migrations
• MP085warn-grant-wideningoverly broad grants, including to PUBLIC
• MP057rls-enabled-without-policyanother silent access-control failure