The pitfall of Supabase's SECURITY DEFINER functions — an unfixed search_path produces RLS bypass and privilege escalation

Let me state the conclusion first. If a Supabase database function (RPC) is defined with SECURITY DEFINER and doesn't fix the search_path, an authenticated ordinary user can swap arbitrary objects under "the definer's (often postgres) privileges," bypass RLS, and escalate privileges. This is not a Supabase-specific bug. It's the classic pitfall that PostgreSQL's SECURITY DEFINER has always had, surfacing as an attack surface in the Supabase environment, which automatically exposes tables and functions as REST/RPC.

Even if you stretch row-level security (RLS) perfectly, this hole opens "outside" it. A SECURITY DEFINER function runs with the definer's privileges, and if the definer is the table owner it jumps over RLS. This article explains, with primary sources and real code: (1) the difference between SECURITY DEFINER and SECURITY INVOKER, (2) why functions/RPCs can bypass RLS, (3) how an unfixed search_path turns into an attack, (4) the real SQL of vulnerable → fixed, and (5) mechanizing detection. And finally it draws an honest line — the "detection" of this hole can be mechanized, but "whether that function's privilege design is correct" is a human judgment. This is part of the overall map of application-layer security; the whole picture is organized in the Next.js × Supabase application-security complete guide.

1. SECURITY DEFINER and SECURITY INVOKER — with whose privileges does it run

A PostgreSQL function has two modes that decide "whose privileges to use" at execution time.

• SECURITY INVOKER (default) — runs with the privileges of the user who called the function. If the caller is authenticated, the function's contents also run with authenticated privileges.
• SECURITY DEFINER — runs with the privileges of the user who created/owns the function (the definer). Just like a Unix setuid program, no matter who the caller is, the contents run with the definer's privileges.

The official documentation (PostgreSQL: CREATE FUNCTION) puts it this way — "because a SECURITY DEFINER function executes with the privileges of the user that owns it, care is needed to ensure it cannot be misused." In other words, SECURITY DEFINER is a feature for intentionally crossing the privilege wall. It's convenient, but if the place you cross to is high-privilege (in Supabase many functions are postgres-owned), a single design mistake makes it a stepping stone for privilege escalation.

SECURITY DEFINER itself isn't evil. There are legitimate uses — cases like "from a table you don't want ordinary users to directly select, return only an aggregate value via a function" or "like an audit log, append rows that the person themselves shouldn't update, only inside the function." The key is to confine the scope of the lent privileges inside the function, and not hand the initiative to an attacker at the place you lent it. An unfixed search_path is precisely the route where the initiative gets stolen at "the place you lent it."

The important point is that the starting point of RLS bypass is this "privilege difference" itself. With a SECURITY INVOKER function, even if the search_path is hijacked, it can't do anything beyond the caller's privileges. It's precisely because it's SECURITY DEFINER that the hijack turns into "privilege escalation." So the first question is always "does this function really need to be SECURITY DEFINER?" (detailed in section 5).

2. Why functions/RPCs "jump over" RLS

Supabase automatically exposes database functions as RPC endpoints via PostgREST. If the anon / authenticated role has EXECUTE privilege, you can call it from the browser like this.

# データベース関数 is_admin() を RPC として呼ぶ（anon キーだけで叩ける）
curl "https://<project>.supabase.co/rest/v1/rpc/is_admin" \
  -H "apikey: <anon-key>" \
  -H "Authorization: Bearer <user-jwt>" \
  -H "Content-Type: application/json" -d '{}'

Here the relationship with RLS becomes the problem. PostgreSQL's RLS is evaluated based on "the current user," but as the official documentation (PostgreSQL: Row Security Policies) clearly states, the table owner usually bypasses RLS (unless FORCE ROW LEVEL SECURITY is specified), and a superuser or a role with the BYPASSRLS attribute always bypasses it.

A function created in the Supabase dashboard or in a migration is in many cases owned by postgres. postgres also owns the tables in the public schema. Therefore —

When a SECURITY DEFINER function (postgres-owned) touches a public table inside it, that query runs as postgres, and RLS doesn't apply.

This means you're creating, inside the database, the same structure as bypassing RLS server-side with a service_role key. The danger of the service_role key and RLS bypass is covered in the exposure of the anon key and service_role key, but a SECURITY DEFINER function is an easily-overlooked hole in that it "creates the same bypass route inside the DB without leaking any key."

-- 例：profiles に完璧な RLS を張っていても…
alter table public.profiles enable row level security;
create policy "read own profile" on public.profiles
  for select to authenticated using ( (select auth.uid()) = id );

-- この SECURITY DEFINER 関数経由なら、RLS を素通りして全行を集計できてしまう
create function public.count_all_profiles()
returns bigint language sql security definer as $$
  select count(*) from public.profiles;  -- postgres 権限＝RLS が効かない
$$;

count_all_profiles() on its own looks harmless — "it just returns a count." But opening "an execution context where RLS doesn't apply" as an RPC to the outside is itself the substance of the risk. As we'll see in the next section, when the search_path is unfixed, this execution context gets hijacked by an attacker.

3. The attack surface of an unfixed search_path

search_path is the setting that decides from which schemas, and in what order, to look up object names (tables, functions, types, operators) written without a schema qualifier. If you don't attach SET search_path to a SECURITY DEFINER function, the function inherits the caller's search_path as-is.

Here the room for attack is born. If a SECURITY DEFINER function's body references an object unqualified, that name can be resolved to "a different thing the attacker prepared."

3-1. The vulnerable function: a SECURITY DEFINER with an unqualified reference

-- 脆弱：SECURITY DEFINER なのに search_path 未固定。本体は profiles を非修飾で参照
create function public.is_admin()
returns boolean
language plpgsql
security definer                 -- 定義者（postgres）権限で動く＝RLS を飛び越える
as $$
declare
  result boolean;
begin
  -- ↓ 非修飾の "profiles"。search_path 次第で別のオブジェクトに解決されうる
  select role = 'admin' into result
  from profiles
  where id = auth.uid();
  return coalesce(result, false);
end;
$$;

3-2. The attack: inject "a same-named different thing" into a temporary schema or public

The attacker can be an authenticated ordinary user. What they do is swap the unqualified profiles inside is_admin() for a same-named object they created.

-- 攻撃者（authenticated）：一時テーブルで profiles を差し替える
create temp table profiles (id uuid, role text);
insert into profiles values (auth.uid(), 'admin');

-- pg_temp（一時スキーマ）は、非修飾のテーブル参照では既定で「最初」に探索される。
-- そのため is_admin() 内の "profiles" が攻撃者の一時テーブルに化け、
-- 定義者（postgres）権限で実行されている関数が "admin" を返す
select public.is_admin();   -- → true（本来 admin でないのに昇格）

Why does a temporary table work? This is an easily-misunderstood point even among PostgreSQL's lookup rules, so let's pin it down precisely.

• Relation names like tables, views, and types: the temporary schema pg_temp, when not explicitly listed in search_path, is searched first by default (ahead of pg_catalog). And since the privilege to create temporary tables (TEMP) is granted to all users by default, anyone can perform this swap. This is the most universally-holding attack vector.
• Function and operator names: these aren't resolved from pg_temp. However, if search_path includes "a schema where the attacker has CREATE privilege" (typically public), they can plant a same-named function there and hijack it.

In other words, the essence of an unfixed-search_path SECURITY DEFINER function is that the definer-privileged code gets hijacked by "a same-named object placed in a schema the attacker can write to." If, like is_admin(), the return value is used in an authorization decision, it leads to privilege escalation; if the SECURITY DEFINER function performs an update or EXECUTE inside, it leads to arbitrary-processing execution. The official CREATE FUNCTION also lists "not including a schema that untrusted users can write to in the search path" as an essential precaution for SECURITY DEFINER.

4. The safe pattern — the real SQL of vulnerable → fixed

The core of the defense is two points: "don't entrust the search path to the caller" and "pin down the objects so an attacker can't swap them." Concretely, apply three measures simultaneously.

4-1. Fix search_path and schema-qualify everything (Supabase's recommended strictest)

-- 修正：search_path を空に固定し、本体のオブジェクトをすべてスキーマ修飾する
create or replace function public.is_admin()
returns boolean
language plpgsql
security definer
set search_path = ''             -- ← 呼び出し側の search_path を無効化する
as $$
declare
  result boolean;
begin
  select role = 'admin' into result
  from public.profiles            -- ← スキーマ修飾。一時スキーマや public 差し込みに化けない
  where id = (select auth.uid()); -- ← auth.uid() も auth スキーマで修飾済み
  return coalesce(result, false);
end;
$$;

Here there's an important point to add honestly. set search_path = '' is not a "magic shield." When you empty it, unqualified names can hardly be resolved except pg_catalog (built-in functions), and forgetting to write a qualifier surfaces as an error — that's the aim. But as mentioned, an unqualified relation name is searched in pg_temp first even with an empty search_path. Therefore what's actually neutralizing the attack is "schema qualification" itself, and set search_path = '' is the foundation for "not silently dropping forgotten qualifiers into public, but forcing them to be explicit." If you just attach '' but leave unqualified references in the body, it can still be hijacked. The two are always a set.

Since pg_catalog is implicitly searched even with an empty search_path, built-in functions like now() and lower() can still be used. What needs qualification are objects in your own/extension schemas like public and auth.

4-2. Alternative: fix to a trusted schema and always place pg_temp last

If set search_path = '' is too strict (there are many existing unqualified references), the practice PostgreSQL officially shows is "fix to a trusted schema and place pg_temp at the end." Placing it at the end means the temporary schema is searched only last, neutralizing relation swapping.

-- 代替：信頼スキーマを先に、pg_temp を最後に固定する（公式 CREATE FUNCTION の作法）
create or replace function public.is_admin()
returns boolean
language plpgsql
security definer
set search_path = public, pg_temp   -- 信頼スキーマ → pg_temp は必ず末尾
as $$ ... $$;

However, this form presupposes that public itself isn't writable by an attacker. Since PostgreSQL 15 and later no longer grant CREATE on public to PUBLIC by default, it holds more easily, but in projects that inherited an old configuration it needs confirmation. When in doubt, choosing the strictest set search_path = '' + full qualification is the safe side.

4-3. Minimize GRANT — narrow who can call it

CREATE FUNCTION by default grants EXECUTE to PUBLIC (all roles). In Supabase, this directly becomes "an RPC that even anon can hit." For a SECURITY DEFINER function, first strip the implicit EXECUTE and grant it back only to the necessary roles.

-- GRANT 最小化：PUBLIC への暗黙の EXECUTE を剥がし、必要なロールにだけ許可する
revoke execute on function public.is_admin() from public;
grant execute on function public.is_admin() to authenticated;
-- anon（未ログイン）に開ける必要が無いなら、絶対に grant しない

This is a defense on a different axis from "fixing the search path." Even if you fix search_path, if a privilege-escalation function that doesn't need to be open to anon in the first place is hittable by anon, the attack surface stays wide. "With whose privileges does it run (DEFINER)" and "who can call it (GRANT)" are minimized independently.

4-4. Ask whether SECURITY DEFINER is needed at all

The most effective thing is to get by without using the dangerous feature. Many functions are sufficient with SECURITY INVOKER (the default), in which case RLS applies correctly to the caller, and a search_path hijack doesn't become privilege escalation (because it can't exceed the caller's privileges). Choose SECURITY DEFINER only when there's a clear reason to "have it limitedly stand in for processing that the ordinary user's privileges can't reach." Since PostgreSQL 15 and later you can explicitly state SECURITY INVOKER, leaving the intent in the code makes review easier.

5. Dangerous RPC patterns you see in Supabase

Let me list forms repeatedly seen in the actual field. All of them "work," so they slip past demos and reviews.

Pattern A: an admin feature is exposed with SECURITY DEFINER, and GRANT isn't tightened either

-- 危険：ロール昇格を SECURITY DEFINER で公開。search_path 未固定 ＋ 既定で PUBLIC に EXECUTE
create function public.promote_to_admin(target uuid)
returns void
language sql
security definer            -- RLS を飛び越えて profiles を更新できる
as $$
  update profiles set role = 'admin' where id = target;  -- 非修飾＆所有権チェックなし
$$;
-- 既定の PUBLIC EXECUTE が残るため、anon/authenticated から rpc/promote_to_admin を叩ける

This function has three flaws coexisting: (1) unfixed search_path (unqualified profiles), (2) the EXECUTE to PUBLIC remains, and (3) the flaw of the authorization logic itself, that "whoever the caller is, they can make any target an admin."

This is the fork in the road. (1) and (2) can be detected and fixed mechanically. Just add set search_path = '' and revoke ... from public. But (3) remains even if you perfectly fix the search_path. "Who may call this function, and whom may they promote?" — an authorization decision like update public.profiles set role = 'admin' where id = target and <a condition that the caller is an admin> has an answer only in your business rules. This is the honest core of this article (section 8).

Pattern B: it opens an RLS-bypassing aggregation/cross-cutting fetch straight through

-- 危険：ダッシュボード用の横断集計を SECURITY DEFINER で公開（search_path 未固定）
create function public.org_dashboard(org uuid)
returns table(total int, revenue numeric)
language sql
security definer
as $$
  select count(*), coalesce(sum(amount), 0)
  from invoices where org_id = org;   -- 非修飾＆「呼び出し元がこの org に属するか」未検証
$$;

The more convenient the "cross-cutting fetch," the more it tends to be made SECURITY DEFINER, and moreover it receives org from the client without verifying ownership — this is an IDOR (object-level authorization flaw) raised inside the function. The fix is, in addition to fixing the search_path + schema-qualification, to put an ownership condition like and exists (select 1 from public.memberships where user_id = auth.uid() and org_id = org) in the body. Here too, what can be mechanized is only the first half (making the search path safe); defining the ownership condition is design.

6. Detection — static verification of migrations and pg_proc

The form "SECURITY DEFINER and search_path unfixed" can be mechanically found. This is the domain where automation shows its true worth.

6-1. Investigate the running DB directly (pg_proc)

The catalog pg_proc records whether a function is SECURITY DEFINER (prosecdef) and what SET it has (proconfig). Using this, you can list them.

-- SECURITY DEFINER 関数のうち search_path を固定していないものを洗い出す
select
  n.nspname  as schema,
  p.proname  as function,
  pg_get_userbyid(p.proowner) as owner,   -- 定義者＝この権限で動く
  p.proconfig as config                   -- SET 句。null なら呼び出し側を継承
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where p.prosecdef                          -- SECURITY DEFINER のみ
  and n.nspname not in ('pg_catalog', 'information_schema')
  and (
    p.proconfig is null
    or not exists (
      select 1 from unnest(p.proconfig) as cfg
      where cfg like 'search_path=%'
    )
  )
order by 1, 2;

With this, you can confirm, as a pre-release check, that there are zero "unfixed SECURITY DEFINER functions." Supabase's Database Linter also has a function_search_path_mutable rule that warns about the same kind.

6-2. Statically verify migrations (let CI stand guard in CI)

Even without connecting to the running DB, you can find the same flaw by reading supabase/migrations/**.sql. The systematic surfacing of RLS misconfigurations is summarized in detecting and auditing RLS misconfigurations, but for SECURITY DEFINER functions, mechanically flag especially the following three points from migrations.

• includes security definer but the same definition has no set search_path
• has set search_path but it's neither empty nor pg_temp last (= an attacker-writable schema could be at the head)
• there's no revoke execute ... from public after the function definition (the default PUBLIC EXECUTE remains)

The OSS Aegis I publish parses supabase/migrations and detects these. It runs with no installation.

# インストール不要・設定不要でスキャン（未固定の SECURITY DEFINER／過剰 GRANT を可視化）
npx @aegiskit/cli scan

Since regex alone misses the $$ ... $$ body and multi-line definitions, for practical use it's reliable to parse the SQL and look at attributes "per definition block." The important thing is to station this primary filter permanently in CI and stop the build if a new unfixed SECURITY DEFINER slips in. Let the machine, not human memory, stand guard.

7. Pre-production checklist

Whether outsourced or AI-generated, confirm at least this before shipping a SECURITY DEFINER function to production.

• Is SECURITY DEFINER needed at all? Did you first ask whether SECURITY INVOKER (the default) suffices
• Every SECURITY DEFINER function explicitly has set search_path = '' (or a trusted schema + pg_temp last)
• The body's tables, types, and functions are all schema-qualified (public.foo, auth.uid()). Zero unqualified references
• You're not satisfied "just attaching" set search_path = '' — it's set together with qualification
• Right after the function definition you revoke execute ... from public and grant only to the necessary roles
• A function that doesn't need to be open to anon (not logged in) isn't in a state callable by anon
• The function body has a condition that verifies the caller's ownership/privilege (especially for escalation, cross-cutting fetch, and update kinds)
• You confirmed with the pg_proc query (section 6) that there are zero "unfixed SECURITY DEFINER"
• You have permanently stationed in CI the static verification of migrations (npx @aegiskit/cli scan, etc.)

From the client's/reviewer's viewpoint, the three most effective questions are "Why is this function SECURITY DEFINER?" "Is the search_path fixed?" and "Who can call this (can even anon hit it)?" A developer who understands the design can answer immediately.

8. How far can it be mechanized, and where does design begin (honestly)

Finally, let me draw the line. Since exaggeration damages trust, I'll separate what can and can't be done.

What can be mechanized (detection, warning). "SECURITY DEFINER and search_path unfixed," "the persistence of unqualified references," "excessive EXECUTE to PUBLIC" — these are problems of form, so they can be mechanically surfaced by the static verification of pg_proc or migrations. This layer fits well with the discipline OWASP's Application Security Verification Standard (ASVS) preaches — "security is measured not by 'did you put it in' but by 'can you verify it.'" First visualizing the current state with Aegis (free OSS, npx @aegiskit/cli scan) is the most cost-effective first step.

What can't be mechanized (design judgment). On the other hand, questions a tool can't answer remain — "should this function really be SECURITY DEFINER," "how much, and to what extent, may it stand in for with the definer's privileges," "who may call this RPC," "is the body's authorization logic (ownership/privilege conditions) correct." As promote_to_admin in section 5 showed, even if you perfectly fix the search_path, if the function's logic is 'anyone can escalate anyone,' it's still vulnerable. These can only be judged by a human who understands your data model and business rules. No tool, even if it can detect the trap of an unfixed search_path, proves that the function's privilege design is 'correct.' "The scan passed = it doesn't step on the common traps," not "it became safe" — a product that blurs this distinction breeds complacency instead.

That's exactly why a line is needed. How far to firm up with automatic detection, and where human review is needed. If you need privilege design for SECURITY DEFINER functions, or an authorization/RLS review of an existing Supabase app, I take it on with a security audit. I myself, in the lumber-distribution-industry DX project, designed and verified data-layer authorization including RLS, tenant isolation, and privilege boundaries in actual operation. Mechanizing detection is the foundation for humans to focus on the genuinely-hard design judgments.

Frequently asked questions (FAQ)

Q. If I attach set search_path = '', is it safe with that? A. That alone is insufficient. The purpose of emptying it is "to surface unqualified references as errors and force schema qualification," and what's actually neutralizing the attack is schema qualification itself. Since an unqualified relation name has the temporary schema (pg_temp) searched first even with an empty search_path, even with '' attached, if unqualified references remain in the body it can be hijacked. Always make '' (fixed) and full qualification a set.

Q. Should I fix search_path in all functions? A. It's mandatory in SECURITY DEFINER functions. Even in SECURITY INVOKER (the default) the habit of fixing it is good (behavior stops being swayed by the caller), but the priority is overwhelmingly SECURITY DEFINER first. That's the one directly tied to privilege escalation.

Q. If I just stretch RLS correctly, do I not need to worry about SECURITY DEFINER? A. No. A SECURITY DEFINER function runs with the definer's privileges (often postgres = the table owner), so it jumps over RLS. RLS applies to "the caller's privileges," and doesn't apply to code executed with the definer's privileges. Independently of RLS, the function side must guarantee search_path fixing, qualification, GRANT minimization, and authorization logic.

Q. Can an ordinary user really create a temporary table? A. They can. The privilege to create temporary objects (TEMP) is granted to all roles by default in PostgreSQL. While CREATE on public is often tightened in newer environments, relation swapping via pg_temp holds more universally, so it should always be assumed as a major attack surface of SECURITY DEFINER.

Q. How should I take stock of a large number of existing functions? A. First extract all "unfixed SECURITY DEFINER" with the pg_proc query in section 6 and grasp the count. Next, cross-check the migrations side with npx @aegiskit/cli scan and permanently station it in CI to stop recurrence. The fix proceeds mechanically if you template "fix + qualify + GRANT minimization," but the validity of each function's authorization logic alone must be confirmed by a human, one by one.

Summary: don't entrust the search path to the caller

Let me organize the key points.

• A SECURITY DEFINER function runs with the definer's privileges. In Supabase many are postgres-owned, so when called as an RPC they jump over RLS. The starting point of RLS bypass is the "privilege difference" itself from SECURITY INVOKER (the default).
• If you don't fix search_path, the body's unqualified table/function references are resolved by the caller's search path. An attacker can inject a same-named object into a temporary schema (pg_temp) or public and run their own code with the definer's privileges (privilege escalation).
• The safe pattern is a three-piece set — (1) fix set search_path = '' (or a trusted schema + pg_temp last), (2) schema-qualify all objects, (3) strip EXECUTE from PUBLIC and grant only to the necessary roles. '' is a device to force qualification, and it works only as a set with qualification.
• The form "unfixed SECURITY DEFINER" can be mechanically detected by the static verification of pg_proc or migrations, and you can let CI stand guard.
• But "should that function be SECURITY DEFINER," "who to have call it," and "is the body's authorization logic correct" are design judgments. A tool can detect the trap but doesn't prove the correctness of the privilege design.

Building fast with AI is itself correct. If you need to detect and fix the SECURITY DEFINER pitfalls lurking in the data layer of a quickly-built Supabase app, and review the privilege design, please feel free to consult me.

References

• PostgreSQL — CREATE FUNCTION (precautions for SECURITY DEFINER and search_path)
• PostgreSQL — Row Security Policies (the table owner and BYPASSRLS bypass RLS)
• Supabase Docs — Row Level Security
• OWASP Application Security Verification Standard (ASVS)