Skip to main content

SEC.PRIV.ROUTINE_DEFINER_NO_PATH_IN_MIGRATION — A definer routine that was never told where to look

  • Category: security
  • Severity: high
  • Level: 0 — reachable at any strictness; the security suite is gated by security.min_severity
  • Confidence: deterministic
  • Stability: stable
  • Suites: lint, security
  • Applies to: PostgreSQL

What it reports​

A migration that creates or alters a SECURITY DEFINER routine without a SET search_path clause on it. CREATE FUNCTION, CREATE PROCEDURE and ALTER FUNCTION … SECURITY DEFINER are all reported; EXTERNAL SECURITY DEFINER is the same clause and is reported too.

Why​

SECURITY DEFINER means the routine runs with the privileges of whoever owns it rather than whoever calls it. In a Laravel project the owner is normally the role that runs migrations, which normally holds a great deal. That is the entire point of the feature, and it is fine.

What is not fine is leaving name resolution to the caller. search_path is a session setting, resolved when the routine is called, not when it is written. So an unqualified orders inside the body means whatever the caller's path says it means:

-- The routine, owned by the migration role:
CREATE FUNCTION reset_order_totals() RETURNS void
LANGUAGE plpgsql SECURITY DEFINER
AS $$ BEGIN UPDATE orders SET total_cents = 0; END $$;
-- Any caller who can create a schema object:
CREATE SCHEMA mine;
CREATE TABLE mine.orders (…);
SET search_path = mine, public;
SELECT reset_order_totals();     -- now runs against mine.orders, as the migration role

There is no injection here and no bug in the function. The attack needs a CREATE TABLE in a schema the caller controls, and a routine that was never told where to look.

This is the only rule of its family at high severity. Its catalog siblings find a routine that already exists and ask what is in the path it pinned; this one catches the moment the routine is written with no path at all — the sharpest form, and the only one that is still a decision somebody can change by editing a file.

What to do​

Pin the path on the routine, and qualify what the body touches.

Reported:

DB::statement('CREATE FUNCTION audit_totals() RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$ BEGIN UPDATE orders SET total = 0; END $$');

Not reported:

DB::statement('CREATE FUNCTION audit_totals() RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog AS $$ BEGIN UPDATE public.orders SET total = 0; END $$');

The same thing with the body on its own lines, which is how it usually reads in a migration:

DB::statement(<<<'SQL'
    CREATE FUNCTION reset_order_totals() RETURNS void
    LANGUAGE plpgsql
    SECURITY DEFINER
    SET search_path = pg_catalog
    AS $$ BEGIN UPDATE public.orders SET total_cents = 0; END $$
SQL);

Both halves earn their place. The SET removes the caller's influence; the qualification means the routine still says what it means if somebody later widens the path.

If the routine does not actually need the owner's rights, the better fix is to drop the clause — SECURITY INVOKER is PostgreSQL's default, and a routine that runs as its caller cannot be used to escalate anything.

What it does not see​

  • It reads the signature, not the body. A routine whose every reference is already schema-qualified is safe in practice and is still reported — whether the next edit stays qualified is not something the statement can promise.
  • It does not judge the path that is pinned. SET search_path = public satisfies this rule and is exactly what SEC.PRIV.ROUTINE_DEFINER_UNSAFE_PATH exists for.
  • It says nothing about routines already in the database; a live catalog is the audit suite's subject.

Not reported on MySQL​

MySQL declares routines SQL SECURITY DEFINER and has no search_path at all, so the question this rule asks does not exist there. The rule stands down on what the statement contains rather than on which driver is running — silence on an engine is a property of the data in this package, not a per-driver list.

Suppressing it​

// config/sqlens.php
'ignore' => [
    ['rule' => 'SEC.PRIV.ROUTINE_DEFINER_NO_PATH_IN_MIGRATION', 'reason' => 'every reference in this routine is schema-qualified; reviewed 2026-08'],
],

A suppression needs a reason, and this is one of the rules where the reason ages: it is true of the routine as it is written today, and the next edit is what it stops being true of.