warn-security-definer-search-path

What It Detects

SECURITY DEFINER function without a pinned search_path lets the caller control name resolution inside a privileged body.

Why It's Dangerous

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.

Bad Example

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

Good Example

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;

Configuration

Disable this rule:

# .migrationpilotrc.yml
rules:
  MP099: false

Or change its severity:

# .migrationpilotrc.yml
rules:
  MP099:
    severity: warning