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

> Because a SECURITY DEFINER function runs with the definer's privileges, if you don't fix the search_path an attacker can inject a same-named object into a temporary schema or public, bypass RLS, and escalate privileges. This article explains the safe way to write them — set search_path = '', schema-qualify, and minimize GRANT — and how to detect the flaw in migrations/pg_proc, with real SQL.

- Published: 2026-06-28
- Author: 友田 陽大
- Tags: Supabase, PostgreSQL, RLS, セキュリティ
- URL: https://tomodahinata.com/en/blog/supabase-security-definer-function-search-path-guide
- Category: Application-layer security
- Pillar guide: https://tomodahinata.com/en/blog/nextjs-supabase-application-security-guide

## Key points

- A SECURITY DEFINER function executes with 'the definer's privileges.' In Supabase many are owned by postgres, so when called as an RPC they jump over RLS — the difference from SECURITY INVOKER (the default) is the starting point of RLS bypass.
- If you don't fix search_path, the function 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 — fix `set search_path = ''` (or a trusted schema + pg_temp last), schema-qualify all objects in the body (public.foo, auth.uid()), and revoke EXECUTE from PUBLIC and GRANT only to the necessary roles.
- Detection can be mechanized — scan migrations or `pg_proc` and find functions where `prosecdef = true` and search_path is unset (also via `npx @aegiskit/cli scan` or Supabase's Database Linter).
- But 'should that function be SECURITY DEFINER,' 'who should be allowed to call it,' and 'is the body's authorization logic correct' are design judgments. A tool can mechanically point out an unfixed search_path, but it doesn't prove the correctness of the function's privilege design.

---

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](/blog/nextjs-supabase-application-security-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](https://www.postgresql.org/docs/current/sql-createfunction.html)) 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.

| Viewpoint | SECURITY INVOKER (default) | SECURITY DEFINER |
|---|---|---|
| Privileges at execution | the calling user | the function's definer (owner) |
| How RLS applies | applies to the caller | **jumps over it** if the definer is the table owner |
| Typical use | normal queries/computation | processing that "lends" privileges to ordinary users (aggregation, notification, cross-cutting updates) |
| Danger level | low (no privilege expansion) | **high (crosses the privilege wall)** |

`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.

```bash
# データベース関数 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](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)) 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](/blog/supabase-anon-key-service-role-key-exposure-guide), 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."

```sql
-- 例：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

```sql
-- 脆弱：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.

```sql
-- 攻撃者（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](https://www.postgresql.org/docs/current/sql-createfunction.html) 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)

```sql
-- 修正：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.

```sql
-- 代替：信頼スキーマを先に、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.

```sql
-- 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

```sql
-- 危険：ロール昇格を 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

```sql
-- 危険：ダッシュボード用の横断集計を 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)](/blog/nextjs-supabase-application-security-guide) 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.

```sql
-- 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](/blog/supabase-rls-misconfiguration-detection-audit-guide), 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.

```bash
# インストール不要・設定不要でスキャン（未固定の 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)](https://owasp.org/www-project-application-security-verification-standard/) preaches — "security is measured not by 'did you put it in' but by 'can you verify it.'" First visualizing the current state with [Aegis](/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](/aegis/audit). I myself, in the [lumber-distribution-industry DX project](/case-studies/lumber-industry-dx), 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)](https://www.postgresql.org/docs/current/sql-createfunction.html)
- [PostgreSQL — Row Security Policies (the table owner and BYPASSRLS bypass RLS)](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)
- [Supabase Docs — Row Level Security](https://supabase.com/docs/guides/database/postgres/row-level-security)
- [OWASP Application Security Verification Standard (ASVS)](https://owasp.org/www-project-application-security-verification-standard/)