Supabase Function Search Path Mutable, fixed

TL;DR — If Supabase's linter says a function has a "mutable search_path," it means the function resolves unqualified names (profiles, now(), =) using the caller's search_path at call time. For a SECURITY DEFINER function — which runs as its owner, usually the elevated postgres role — an attacker can plant a same-named object in a schema that gets searched before yours (a table in pg_temp, or a function/operator in any schema on the path they can write to) and have their object execute with the owner's privileges. The fix is two parts, always together: pin set search_path = '' on the function and schema-qualify every reference (public.profiles, not profiles). Better still, default to SECURITY INVOKER and let RLS do the work.

What does "Function has a role mutable search_path" mean?

search_path is a per-session, caller-controlled setting that decides which schemas Postgres searches, and in what order, to resolve any unqualified name. Write profiles instead of public.profiles and Postgres walks the search path, picking the first schema that has a match.

A function with a "mutable" search_path is simply one that has no SET search_path clause of its own. Supabase's function_search_path_mutable lint (0011, a WARN) fires by checking pg_proc.proconfig for any function that lacks a pinned search_path= entry. Without that pin, the function inherits whatever search path the caller's session happens to have. That is inconsistent (behavior shifts depending on who calls it) and, for definer functions, a genuine privilege-escalation hole.

Why does SECURITY DEFINER need search_path set?

SECURITY DEFINER means the function executes with the privileges of the role that owns it; the default, SECURITY INVOKER, executes with the privileges of whoever calls it. On Supabase, functions created via the dashboard or migrations are typically owned by postgres, the project's most privileged everyday role. So a definer function is elevated code — and elevated code that resolves attacker-influenceable names is the problem.

Here's the concrete attack chain when a SECURITY DEFINER function references objects unqualified and doesn't pin its path:

1. The body says something like SELECT ... FROM pwds, or calls now(), or uses = — an unqualified table, function, or operator.
2. With no SET search_path clause, the function resolves those names against the caller's session search path at call time.
3. Unqualified relation names (tables, views, types) can resolve from pg_temp, which Postgres searches first for relations and which any session can write to. Unqualified function and operator names are never resolved from pg_temp — they resolve from the first schema on the caller's path that the attacker can create objects in (for example, a schema where CREATE is granted).
4. The attacker plants a shadowing object earlier in resolution: CREATE TEMP TABLE pwds (...) to mask a table, or a same-named function/operator in a writable schema on the path.
5. When the definer function runs, Postgres resolves the unqualified name to the attacker's object — and it executes with the owner's elevated privileges.

This isn't hypothetical Supabase weirdness; it's generic Postgres name resolution, the same class of bug as CVE-2018-1058, where a malicious user plants a trojan-horse function or operator in a schema that runs later with a victim's privileges. The attack surface includes operators, not just tables and functions — which is why the same mechanism keeps resurfacing in other Postgres codebases that ship SECURITY DEFINER helpers without pinning the path.

There's a second reason it stings on Supabase: postgres owns the public tables, and table owners bypass RLS by default (unless you set FORCE ROW LEVEL SECURITY). So a definer function owned by postgres already runs outside RLS. RLS doesn't gate whether the function runs — only GRANT EXECUTE does. A mutable-path definer function that's also API-reachable is the full attack surface.

The fix: pin the path and qualify everything

The primary, Supabase-recommended fix is an empty search path plus fully-qualified names. Empty means nothing resolves via a path, so there's no schema left for an attacker to shadow — and no unqualified relation left for pg_temp to capture.

Before (flagged — mutable path):

create or replace function public.get_my_orders()
returns setof public.orders
language sql
security definer
as $$
  -- "orders" and "auth.uid()" resolve via the CALLER's search_path
  select * from orders where user_id = auth.uid();
$$;

After (safe — empty path + fully qualified):

create or replace function public.get_my_orders()
returns setof public.orders
language sql
security definer
set search_path = ''                     -- pin to empty
as $$
  select *
  from public.orders                     -- schema-qualified table
  where user_id = (select auth.uid());   -- schema-qualified function
$$;

Bare operators and core functions (=, count(), now()) still resolve under search_path = '' because pg_catalog is always searched implicitly. What breaks is anything an extension installs elsewhere — the classic gotcha is pgvector's <=> operator living in the extensions schema, which gives operator does not exist: vector <=> vector. For that case, use a fixed, non-mutable list instead of empty:

create or replace function public.match_documents(query_embedding vector)
returns setof public.documents
language sql
security definer
set search_path = pg_catalog, public, extensions   -- fixed, safe list
as $$
  select * from public.documents
  order by embedding <=> query_embedding
  limit 10;
$$;

A fixed list satisfies the linter and closes the hole too — the key is that it's constant and lists only schemas untrusted roles can't write to. The canonical Postgres pattern puts pg_temp last so the always-writable temp schema can never shadow anything: set search_path = admin, pg_temp;.

Patching an existing function

You don't have to redefine the body to pin the path:

alter function public.get_my_orders() set search_path = '';

But ALTER only pins the path — if the body still has unqualified references, the function may now break at call time because those names no longer resolve (and any unqualified relation that does still resolve can be captured from pg_temp). Pair the ALTER with a body rewrite, or use a fixed list when qualifying everything is impractical.

A legitimate SECURITY DEFINER example

The classic valid case: an auth trigger that creates a profile row on signup. The new user has no rights to public.profiles yet, so elevation is genuine. Note the empty path, qualified names, and grant hardening.

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profiles (id, email)
  values (new.id, new.email);
  return new;
end;
$$;

revoke all on function public.handle_new_user() from public;

This is the same shape as the trigger in our guide to fixing "new row violates row-level security policy" — that one already ships set search_path = '' for exactly this reason.

When should I just use SECURITY INVOKER?

Most functions shouldn't be SECURITY DEFINER at all. SECURITY INVOKER is both the default and the safer choice: an invoker function runs with the caller's privileges, so the caller's RLS policies and grants apply — precisely what you want for anything reachable over PostgREST/RPC.

Reserve SECURITY DEFINER for the few functions that must do something the caller genuinely can't: write to an audit table they can't touch, a controlled cross-tenant read, an auth helper. And even for invoker functions, pin the path — the linter flags both, because a mutable path is bad hygiene everywhere; it's just a security boundary for definer functions. This owner-versus-caller distinction is the same one that lets a table owner sidestep policies entirely, which we cover in how an ORM bypasses Supabase RLS via table ownership and BYPASSRLS.

How to find every offender

Use the Supabase Database Linter — this is the right tool for this issue. In the dashboard: Advisors → Security Advisor, look for "Function Search Path Mutable." In CI: supabase db lint --level warning. Or query pg_proc directly, mirroring what the linter checks:

select n.nspname as schema, p.proname as function,
       pg_get_function_identity_arguments(p.oid) as args,
       p.prosecdef as is_security_definer
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname not in ('pg_catalog', 'information_schema')
  and not exists (
    select 1 from unnest(coalesce(p.proconfig, '{}'::text[])) as cfg
    where cfg like 'search_path=%'
  )
order by p.prosecdef desc, n.nspname, p.proname;

Add and p.prosecdef to narrow to the dangerous subset (definer functions with no pinned path).

Where GuardLayer fits — and where it doesn't. GuardLayer is a static scanner, and it does not inspect PL/pgSQL function bodies for a mutable search_path — there is no rule for this, so it won't catch it. Run Supabase's linter for that; it's purpose-built. What GuardLayer covers is the adjacent layer where the bulk of Supabase incidents actually happen: RLS turned off on a public table, over-permissive policies, and service-role keys leaking into client bundles. Pin your definer functions with the linter, then use GuardLayer to make sure the rest of the door is locked.

FAQ

Does this affect SECURITY INVOKER functions too? The linter flags both. The privilege-escalation risk is specific to SECURITY DEFINER (which runs elevated), but pinning search_path on invoker functions is still good hygiene and gives you consistent, path-independent resolution.

Will set search_path = '' break my function? It can. Once the path is empty, every non-pg_catalog reference must be schema-qualified, and extension objects (like pgvector's <=>) won't resolve. Either qualify everything or pin a fixed list that includes the extension schema, e.g. set search_path = pg_catalog, public, extensions.

Is "Function Search Path Mutable" a CVE? No. It's a linter finding, not an assigned CVE. The underlying mechanism is the same class as CVE-2018-1058, but the Supabase lint itself is a warning, not a vulnerability record.

Why is pg_temp dangerous specifically? For relation names (tables, views, types) it's searched first and is writable by anyone with a session, so it's the easiest place to plant a shadowing table. Note it's never searched for function or operator names — those get shadowed through a writable schema listed on the path instead. If you use a fixed list rather than an empty path, always put pg_temp last.

GuardLayer didn't flag my mutable search_path — is that a bug? No. GuardLayer has no rule that parses SQL function bodies for a mutable search_path, so it won't report this. Use Supabase's Database Linter for it. GuardLayer focuses on RLS coverage, policy permissiveness, and exposed secrets.