# Function Search Path Mutable: Fix the Supabase Warning Properly

URL: https://tuvio.dev/blog/supabase-function-search-path-mutable
Published: 2026-11-18
By the Tuvio team

"Function search path mutable" is a Supabase Security Advisor warning about a Postgres function that doesn't fix its own `search_path`. Without it, the names inside the function (`profiles`, `orders`) are looked up through whatever search path the caller has, so the same function can end up reading a different table than the one you meant. The fix is to add `set search_path = ''` to the function and write every name inside it with its schema, such as `public.profiles`.

Both halves matter. Setting the search path alone makes the warning disappear, and in our tests it left the function either broken or still open to the same trick. This post shows the fix, the checks, and which functions to do first.

## What "function search path mutable" means

In the Security Advisor the lint is `0011_function_search_path_mutable`, level WARN. Its definition in Supabase's open-source lint set says it "Detects functions where the search_path parameter is not set", and each finding reads:

```text
Function `public.is_admin` has a role mutable search_path
```

The docs for the lint summarize it as "Unsecured function search path".

The search path is the list of schemas Postgres goes through when a name has no schema in front of it. Write `select * from profiles` and Postgres looks for `profiles` in each schema on the path, in order, and uses the first match. `public.profiles` skips the lookup and always means the same table.

A function that doesn't set its own search path borrows the caller's. Supabase's [lint docs](https://supabase.com/docs/guides/database/database-linter?lint=0011_function_search_path_mutable) name two problems with that: the function "may behave differently depending on the user's `search_path` settings", and "Malicious users could potentially exploit the `search_path` to direct the function to use unexpected objects".

## Is function search path mutable dangerous?

It depends on the function. The lint fires for every function without the setting, and they don't all carry the same risk.

**`security definer` functions are the ones that matter.** A normal function runs with the caller's rights, so tricking it gains the caller nothing they couldn't do directly. A `security definer` function runs with its owner's rights. On Supabase the owner is usually `postgres`, which bypasses row level security. If a caller can change which table that function reads, they are steering something that runs with far more access than they have.

Here is that happening. A helper that policies use to ask "is this user an admin":

```sql
create function public.is_admin() returns boolean
language sql
security definer
stable
as $$
  select coalesce(
    (select is_admin from profiles where id = auth.uid()),
    false
  )
$$;
```

We ran this on Supabase's Postgres 17 image, as the `authenticated` role, for a user whose row says `is_admin = false`:

```sql
select public.is_admin();   -- false

-- The caller creates their own "profiles" in the temporary schema
create temp table profiles (id uuid, is_admin boolean);
insert into profiles values ('11111111-1111-1111-1111-111111111111', true);

select public.is_admin();   -- true
```

The function now answers from the caller's temporary table, because Postgres searches the temporary schema before `public` for an unqualified table name. Any policy built on `is_admin()` would let this user through.

**How reachable is it?** Less than that demo suggests, which is why the level is WARN and not ERROR. Someone using your app through the Supabase client can call functions and query tables, but can't create a temporary table. The trick needs a way to run SQL in the same session: a direct database login with a low-privilege role, a SQL injection hole elsewhere, or a function that runs SQL it was handed. It is a second step for an attacker, not a first one. It is also cheap to close, so close it on every `security definer` function.

## How to fix function search path mutable

Add `set search_path = ''` and put a schema in front of every name in the body.

```sql
create or replace function public.is_admin() returns boolean
language sql
security definer
stable
set search_path = ''
as $$
  select coalesce(
    (select is_admin from public.profiles where id = (select auth.uid())),
    false
  )
$$;
```

With this version, the same test returned `false` with the caller's temporary `profiles` table in place. An empty search path plus `public.profiles` leaves nothing to look up.

The empty string is what Supabase recommends: "We recommend pinning functions' `search_path` to an empty string, `search_path = ''`, which forces all references within the function's body to be fully qualified."

### What to qualify inside the function

- **Your tables and views:** `public.profiles`, not `profiles`.
- **Your own functions:** `public.is_admin()`, not `is_admin()`.
- **Supabase helpers:** `auth.uid()` and `auth.jwt()` already carry their schema.
- **Extension functions:** on Supabase most extensions live in the `extensions` schema. In our test `gen_random_bytes(16)` from pgcrypto failed with "function gen_random_bytes(integer) does not exist" under an empty search path, and `extensions.gen_random_bytes(16)` worked.
- **Built-ins need nothing.** `now()`, `coalesce`, `count`, `gen_random_uuid()` and the rest of `pg_catalog` are always found.

### How a missed name shows up

If you set the empty search path and miss a name, the error is one of these:

```text
ERROR: relation "profiles" does not exist
ERROR: function gen_random_bytes(integer) does not exist
```

When you see it depends on the function's language. In our test a `language sql` function with an unqualified name was rejected at `create function`, which is the easy case. A `language plpgsql` function was created without complaint and failed only when it was called. So after changing a plpgsql function, call it. For a trigger function, do the insert or update that fires it.

## A cleared warning doesn't prove the function is safe

The lint checks one thing: whether the function has a `search_path` setting at all. Two quick fixes satisfy it without doing the job. We tested both.

**`alter function` with unqualified names left in the body.** This is the shortest way to make the warning go away:

```sql
alter function public.is_admin() set search_path = '';
```

`alter function` doesn't re-check the body. With `profiles` still unqualified, the function failed with `relation "profiles" does not exist` for a normal caller. For the caller with the temporary `profiles` table it still returned `true`. Postgres searches the temporary schema first for table names even when the search path is empty. The warning was gone, the app was broken, and the trick still worked.

**`set search_path = public`.** AI tools often write this, and many tutorials do too. It keeps unqualified names working, and it clears the lint. In our test the temporary table still won: `is_admin()` returned `true`. If you want a non-empty path, put the temporary schema last so it can't mask anything:

```sql
set search_path = public, pg_temp
```

With that, the same test returned `false`. The [Postgres docs on writing `security definer` functions safely](https://www.postgresql.org/docs/current/sql-createfunction.html) give the same advice. The empty path with qualified names is simpler to reason about, and it's the form Supabase's docs use.

## Find every function with a mutable search path

The Security Advisor lists them one by one. This query gives you the whole list at once, `security definer` functions first:

```sql
select
  n.nspname as schema,
  p.proname as function,
  pg_get_function_identity_arguments(p.oid) as arguments,
  p.prosecdef as security_definer
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
left join pg_depend d on d.objid = p.oid and d.deptype = 'e'
where n.nspname = 'public'
  and d.objid is null
  and not exists (
    select 1
    from unnest(coalesce(p.proconfig, '{}')) as c
    where c like 'search_path=%'
  )
order by p.prosecdef desc, p.proname;
```

It uses the same test as the lint (no `search_path` entry in the function's settings) and skips functions that belong to extensions. Change `'public'` if you keep functions in other schemas.

To see a function's current body before you edit it:

```sql
select pg_get_functiondef('public.is_admin()'::regprocedure);
```

### Fixing many functions at once

This generates one `alter` statement per flagged function:

```sql
select format(
  'alter %s %I.%I(%s) set search_path = '''';',
  case p.prokind when 'p' then 'procedure' else 'function' end,
  n.nspname,
  p.proname,
  pg_get_function_identity_arguments(p.oid)
) as fix
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
left join pg_depend d on d.objid = p.oid and d.deptype = 'e'
where n.nspname = 'public'
  and d.objid is null
  and p.prokind in ('f', 'p')
  and not exists (
    select 1
    from unnest(coalesce(p.proconfig, '{}')) as c
    where c like 'search_path=%'
  );
```

We ran its output and the flagged count went to zero. But it is exactly the shortcut from the section above: it sets the path and doesn't touch the bodies. Use it only for functions whose bodies you have already read and qualified, or that touch nothing but built-ins, such as a trigger that sets `new.updated_at = now()`. For the rest, edit the function, then call it.

## Put the fix in a migration

Changing a function in the SQL Editor fixes this database. If the function was created by a migration file in your repo, the next environment gets the old version back. Add a new migration with the `create or replace function` from above, and leave the old file alone.

If an AI tool writes your SQL, give it the rule so new functions arrive correct:

```text
Every Postgres function sets search_path = '' and schema-qualifies every
table, view and function it uses (public.profiles, extensions.gen_random_bytes).
Use security definer only when the function must bypass RLS, and then revoke
execute from public, anon and authenticated unless that role must call it.
```

## Related warnings on the same functions

A `security definer` function usually collects more than one finding. The Security Advisor also has "Public Can Execute SECURITY DEFINER Function" and its signed-in twin, for functions that anyone can call through the API. Those are about who may run the function. This one is about what the function reads once it runs. Fix both on the same pass:

```sql
revoke execute on function public.is_admin() from public, anon;
grant execute on function public.is_admin() to authenticated;
```

Keep the grant for a role only if that role's policies or app code call the function. The full list of warnings and the order to take them in is in [the Supabase Security Advisor guide](https://tuvio.dev/blog/supabase-security-advisor). For a helper function used inside policies, [Supabase RLS policy examples](https://tuvio.dev/blog/supabase-rls-policy-examples) has a complete team-membership example in a private schema.

## Fix checklist

1. Run the query and list every function without a `search_path`.
2. Start with the `security definer` ones.
3. Add `set search_path = ''` to each.
4. Put a schema in front of every table, view and function in the body, including `extensions.` for extension functions.
5. Call each function, and fire each trigger, to catch a missed name.
6. Revoke `execute` from roles that don't need it.
7. Save the new definitions as a migration.
8. Open the Security Advisor again and confirm the warning is gone.

## Checking the functions in your repository

The Security Advisor checks the database as it is today. A free Tuvio [scan](https://tuvio.dev/scan) needs no account and reads the `.sql` migrations in your GitHub repo, where the next version of the database comes from. It flags `security definer` functions that don't set a `search_path`, and `security definer` functions that can be called through the API and never check who is calling.

It is narrower than the Advisor here on purpose: it doesn't flag ordinary functions without a search path, and a function written with a single-quoted body or `begin atomic` isn't checked for it. It also can't see a function you created in the dashboard and never saved to a file. What it checks is listed on the [coverage page](https://tuvio.dev/coverage).
