Supabase functions search path with '' vs explicit schema list
On October 21, Supabase merged PR #69 into supabase/vault. Two of its functions changed:
search_path = '' → search_path = pg_catalog, pg_temp. Link to the github PR vault#69
1.What is SECURITY DEFINER?
A function that runs with its owner's rights, not the caller's. On Supabase the owner is usually postgres, which bypasses RLS. It's a hole in the wall you cut on purpose, so it has to stay exactly the size you planned.
2.'' vs explicit An empty search_path does NOT mean "search nothing". Postgres always searches pg_catalog and the caller's own temp schema (pg_temp). If pg_temp isn't listed, it's searched FIRST, even before pg_catalog, for table and type names.
3.What can go wrong with search_path = ''. Anyone with a direct SQL session can create a temp table that shadows a name your function uses:
→ An unqualified table can return the attacker's fake row
→ A temp table named jsonb made auth.uid() fail inside the function. Harmless in itself, but it proves the caller’s objects reach your security definer code.
The Supabase API can't do this. Direct connections can: BI tools, contractors or AI agents with database access.
4.The safe pattern
create function private.can(p_tenant uuid, p_permission text)
returns boolean
language sql stable
security definer
set search_path = pg_catalog, pg_temp
as $$
select exists (
select 1
from private.memberships m -- always schema-qualified
join private.role_permissions rp on rp.role_id = m.role_id
where m.user_id = (select auth.uid())
and m.tenant_id = p_tenant
and rp.permission = p_permission
)
$$;
Both layers matter: the path protects built-in types, and qualified names protect your own tables.
Don’t take my word for it: run bellow lines in the SQL editor and compare the order yourself. The temp schema only appears after you create a temp table.
--temp FIRST
create temp table x (id int);
set search_path = '';
select current_schemas(true); -- {pg_temp_N, pg_catalog}
--temp LAST
set search_path = pg_catalog,pg_temp;
select current_schemas(true); -- {pg_catalog, pg_temp_N}
1 point · 9 comments
Mentions
Supabase functions search path with '' vs explicit schema list On October 21, Supabase merged PR #69 into supabase/vault. Two of its functions changed: search_path = '' → search_path = pg_catalog, pg_temp. Link to the github PR vault#69 1.What is SECURITY DEFINER? A function that runs with its owner's rights, not the caller's. On Supabase the owner is usually postgres, which bypasses RLS. It's a hole in the wall you cut on purpose, so it has to stay exactly the size you planned. 2.'' vs explicit An empty search_path does NOT mean "search nothing". Postgres always searches pg_catalog and the caller's own temp schema (pg_temp). If pg_temp isn't listed, it's searched FIRST, even before pg_catalog, for table and type names. 3.What can go wrong with search_path = ''. Anyone with a direct SQL session can create a temp table that shadows a name your function uses: → An unqualified table can return the attacker's fake row → A temp table named jsonb made auth.uid() fail inside the function. Harmless in itself, but it proves the caller’s objects reach your security definer code. The Supabase API can't do this. Direct connections can: BI tools, contractors or AI agents with database access. 4.The safe pattern create function private.can(p_tenant uuid, p_permission text) returns boolean language sql stable security definer set search_path = pg_catalog, pg_temp as $$ select exists ( select 1 from private.memberships m -- always schema-qualified join private.role_permissions rp on rp.role_id = m.role_id where m.user_id = (select auth.uid()) and m.tenant_id = p_tenant and rp.permission = p_permission ) $$; Both layers matter: the path protects built-in types, and qualified names protect your own tables. Don’t take my word for it: run bellow lines in the SQL editor and compare the order yourself. The temp schema only appears after you create a temp table. --temp FIRST create temp table x (id int); set search_path = ''; select current_schemas(true); -- {pg_temp_N, pg_catalog} --temp LAST set search_path = pg_catalog,pg_temp; select current_schemas(true); -- {pg_catalog, pg_temp_N}