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. One of the most common reasons to reach for a SECURITY DEFINER helper in the first place is to break an RLS infinite-recursion loop — so this search_path risk tends to get introduced exactly when you're fixing a different RLS bug.

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.

What is Supabase's default search_path?

There isn't one value — there's one per role, and that distinction is the whole answer.

Supabase's post-setup init script pins the path at the role level, for exactly two roles:

ALTER ROLE postgres       SET search_path TO "$user",public,extensions;
ALTER ROLE supabase_admin SET search_path TO "$user",public,auth,extensions;

Stock Postgres ships "$user", public. The extensions entry is Supabase's addition, and it's attached to those roles — not to the database. Every other role gets no role-level setting from that script: anon, authenticated, service_role, supabase_auth_admin, dashboard_user. So SHOW search_path in one session tells you nothing about what another session sees, and the dashboard is not a reliable proxy — Supabase documents dashboard_user as the role "for running commands via the Supabase UI," and it has no documented path of its own.

The API path is a separate value again, set by PostgREST per request rather than by the role. Supabase's CLI config exposes it as api.extra_search_path — extra schemas added to the search_path of every request, with public always included — defaulting to ["public", "extensions"]. A request arriving as anon or authenticated resolves unqualified names against what PostgREST set for that request, not against the postgres role's setting.

And $user is a slot, not a schema. Postgres substitutes the schema named by CURRENT_USER only if such a schema exists and the user has USAGE on it; otherwise the entry is ignored. On a default project there's no postgres schema, so it resolves to nothing — and silently, because any name that isn't an existing schema you have USAGE on is silently skipped rather than erroring. It starts resolving the day someone creates a schema whose name matches a role.

Why the extensions schema is on the path

Because that's where extensions live — Supabase installs most of them into extensions, which is why uuid_generate_v4(), crypt(), and pgvector's <=> resolve without qualification.

Being on the path is not the same as being exploitable. What makes a listed schema dangerous is CREATE on it, not membership of the list. The Postgres docs put it plainly: "adding a schema to search_path effectively trusts all users having CREATE privilege on that schema." Supabase's init script grants the API roles usage only:

grant usage on schema public     to postgres, anon, authenticated, service_role;
grant usage on schema extensions to postgres, anon, authenticated, service_role;

Don't treat that as a standing guarantee. CREATE on public was held by PUBLIC by default until Postgres 15 removed it — and that change applies to new clusters and new databases only; upgrading a cluster or restoring a dump preserves public's existing permissions. So a project restored from an older dump, or any project where someone ran grant create on schema public to authenticated, has handed CREATE back to a role your definer functions implicitly trust. Audit by grants, not by schema count:

select nspname as schema,
       has_schema_privilege('anon', oid, 'CREATE')          as anon_create,
       has_schema_privilege('authenticated', oid, 'CREATE') as authenticated_create
from pg_namespace
where nspname not like 'pg\_%' and nspname <> 'information_schema';

Anything returning true is a schema that should not appear on a definer function's path. It's also why Supabase recommends against creating your own entities in extensions — keeping it extension-only is what keeps it a low-risk path entry.

Do trigger functions need search_path set too?

Yes, and for the same reason — there is no trigger-specific rule. Neither CREATE TRIGGER nor the PL/pgSQL trigger documentation mentions search_path or an execution security context at all; the only privileges CREATE TRIGGER discusses are the ones needed to create the trigger (TRIGGER on the table, EXECUTE on the function). For name resolution a trigger function is an ordinary function, so it inherits the path of the session that fired it, and the advisor flags it like any other.

What makes triggers the sharper case is that you don't control the firing session. For a trigger on auth.users, the INSERT comes from Supabase's Auth service, not from your SQL editor — and the post-setup script sets a role-level path only for postgres and supabase_admin. There is no documented search_path for supabase_auth_admin. You cannot infer what your trigger inherits, and SHOW search_path in the dashboard won't tell you. That uncertainty is the argument for pinning: a pinned path is the only path you can reason about.

Why the auth.users trigger needs SECURITY DEFINER

Because the role doing the insert can't reach your table. Supabase's role reference scopes supabase_auth_admin to the auth schema — it's the role the Auth middleware connects with. A signup writes auth.users as that role, so a trigger inserting into public.profiles is running from a role with no documented rights on its target. The elevation is doing real work, which is why this function gets pinned rather than converted to SECURITY INVOKER. The alternative is explicit grants instead of elevation: Supabase's custom access token hook docs take that route, granting supabase_auth_admin rights on the public table the hook reads and revoking them from authenticated, anon, public.

Patching a trigger function: the signature is always empty parens

A trigger function must be declared with no arguments even when CREATE TRIGGER passes some — those arrive via TG_ARGV. So the identity signature you hand ALTER FUNCTION is always (), whatever you wrote in the CREATE TRIGGER statement:

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

ALTER FUNCTION leaves the trigger attached — no drop and recreate. Inside a PL/pgSQL trigger body under an empty path:

• NEW, OLD, TG_OP, TG_ARGV are unaffected. They're PL/pgSQL variables, not names resolved through the path.
• Custom types in a DECLARE block are not — type names do resolve through the path, so write public.my_enum.
• Unqualified CREATE targets fail outright. An object created without a target schema goes into the first valid schema on the path, and an empty path is an error, not a no-op. That fires at call time, not when you define the function.

Test before shipping. Supabase's own warning on this trigger is blunt: if it fails, it can block signups — which turns a security fix into a signup outage.

Why set search_path = '' instead of a fixed schema list?

A fixed list closes the hole. Empty keeps it closed without maintenance.

A named schema is a standing trust grant. Adding a schema to search_path trusts every role holding CREATE on it, so set search_path = pg_catalog, public, extensions isn't a static safety property — it's an assertion about who holds CREATE on those schemas today. Grant CREATE on public to authenticated six months from now and every function pinned to that list is quietly exploitable again, with no new advisor warning, because the lint is satisfied by any pinned value. '' names nothing, so there's nothing to re-audit.

Empty is useful precisely because it breaks. A list lets an unqualified body keep running unchanged: the function works, the advisor goes green, and resolution stays path-dependent. '' forces the qualification that is the actual fix — every error it raises is a reference you'd otherwise have shipped unqualified. It's also why an empty path needs no trailing pg_temp the way a fixed list does: once every reference is qualified, there's no unqualified name left for the temp schema to capture.

Reach for the fixed list only where qualifying is genuinely impractical — an extension operator you'd otherwise have to spell operator(extensions.<=>) — and put pg_temp last when you do.

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.