# Supabase RLS Production Design Guide: Practical Patterns for Pushing Multi-Tenant SaaS Authorization into PostgreSQL

> A production design guide for pushing multi-tenant SaaS authorization down to the DB layer with zero trust using Supabase's row-level security (RLS). Explained with real code, in reusable, official-spec-compliant patterns: anon/authenticated/service_role, USING/WITH CHECK, tenant_id isolation, (select auth.uid()) performance optimization, and pgTAP tests.

- Published: 2026-06-24
- Author: 友田 陽大
- Tags: Supabase, PostgreSQL, RLS, アーキテクチャ設計, B2B SaaS, TypeScript
- URL: https://tomodahinata.com/en/blog/supabase-rls-production-multi-tenancy-patterns
- Category: Databases & RLS
- Pillar guide: https://tomodahinata.com/en/blog/supabase-production-guide-nextjs-rls-realtime-edge-functions

## Key points

- Multi-tenant authorization will definitely leak with API if statements. RLS is the last line of defense that applies equally to all paths, structuring zero trust into the DB
- Clients use the anon key + RLS, and service_role is server-only. Forgetting to enable RLS means full exposure, so inspect all tables in CI
- USING is the entrance (rows you can read), WITH CHECK is the exit (rows you can leave). For UPDATE write both, and fix the tenant boundary with AND via as restrictive
- Realize multi-tenant isolation declaratively, immediately, and DRY-ly with a membership table + a security definer (set search_path='') helper
- Write (select auth.uid()) wrapping × indexes × to specification as a pattern, and make boundary leakage a CI blocking condition with pgTAP

---

"Protect authorization with backend if statements" — do this in a multi-tenant SaaS and **the tenant boundary will definitely leak someday**. Because there will be people who forget to write `where tenant_id = ?` every time they add a new endpoint. One leak in one spot directly leads to the most fatal accident in SaaS — "company B's data appears in company A's admin panel."

This article is a collection of reusable production design patterns for **structurally enforcing that boundary not with human discipline but with PostgreSQL's (Supabase's) row-level security = RLS**. I built a [real-time game-scoring app](/case-studies/realtime-sports-scoring-app) for amateur baseball alone in a Supabase + Expo + Next.js monorepo, and operate it with **RLS enabled on all 69 tables and about 280 policies**, pushing zero-trust authorization down to the DB layer. The patterns in this article are faithful to that implementation and to the [Supabase official documentation](https://supabase.com/docs/guides/database/postgres/row-level-security) and the [PostgreSQL official documentation](https://www.postgresql.org/docs/current/ddl-rowsecurity.html).

> Note that there's a separate article on the same product that delves into "**the consistency of offline concurrent editing**" and "idempotency keys" ([designing for an untrusted client](/blog/untrusted-client-postgres-rls-offline-first)). This article doesn't overlap with that and concentrates on **reusable patterns of RLS itself**. The relationship is: the authorization philosophy is shared, the layer handled differs.

## 0. The big picture: 5 things to decide in RLS design

Using RLS in production effectively means designing these 5 things. The order has meaning.

| What to decide | Central concept | This article's section |
| --- | --- | --- |
| **Where to authorize** | Zero trust, consolidation to the DB layer | §1 |
| **As whom it executes** | `anon` / `authenticated` / `service_role` | §2 |
| **What to allow** | `USING` / `WITH CHECK` × per-operation policies | §3 |
| **How to isolate tenants** | `tenant_id` isolation, membership table, `security definer` | §4, §5 |
| **Does it run fast and correctly** | `(select auth.uid())` optimization, indexes, pgTAP tests | §6, §7 |

---

## 1. Why push authorization into the DB: the zero-trust boundary

Where you place authorization is a declaration of **"which layer you trust."** Let me honestly compare the options.

| Where authorization is placed | Enforcement | Ease of leaking | Evaluation |
| --- | --- | --- | --- |
| Front-end UI gating | None (breakable with DevTools) | Worst | ❌ Only a UX aid |
| BFF / API if statements | Valid if via the app | High (leaks via forgotten writes / new paths) | ⚠️ Fragile alone |
| **DB RLS** | **Always valid, on all paths** | **Low (the DB rejects last)** | ✅ The last line of defense |

The decisive difference is **"can it be bypassed."** A UI if statement disappears if you hit `fetch` directly. An API check loses meaning the instant a new endpoint, an admin script, or a future different client **reaches the DB without going through that check**.

RLS is different. **A policy is tied to the table and applies equally to a query coming from any path.** In PostgreSQL's official words, enable RLS and "all normal access to the table must be allowed by a row security policy." No matter how many lines the app's code has, **the DB holds the veto last** — this is the substance of zero trust.

> What I don't want misunderstood is, this isn't a story of "don't write API validation." It's **defense-in-depth.** Input validation at the API, the final enforcement of authorization at the DB. RLS is not "a substitute for if statements" but "a floor that doesn't collapse even if you forget to write an if statement."

---

## 2. The basics of RLS: roles and the terror of "forgetting to enable"

### 2-1. Supabase's 3 roles

A request a Supabase client issues executes as **one of Postgres's 3 roles** depending on the JWT. RLS policies gate by "as whom it's executing," so first grasp this accurately ([Supabase Auth](https://supabase.com/docs/guides/auth)).

| Role | Who | RLS treatment | Client exposure |
| --- | --- | --- | --- |
| `anon` | Unauthenticated (before login) | **Applied** | Publishable (anon key) |
| `authenticated` | Logged-in user | **Applied** | Publishable (same, identified by JWT) |
| `service_role` | Server-only privilege | **Bypasses it** | **Absolutely not publishable** |

Here's the **most important safety rule**: the `service_role` key **completely ignores RLS.** The moment you expose it to a client (mobile, browser), RLS is as good as nonexistent. Always use **the anon key + RLS** for clients, and confine `service_role` inside a trusted server (Edge Function / backend) only (re-mentioned in the §8 pitfalls in detail).

```ts
// ✅ クライアント側：anon key。RLSが効く前提で全データアクセスを設計する
import { createClient } from '@supabase/supabase-js';

export const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!, // 公開してよい。RLSが守る
);
```

```ts
// ⚠️ サーバー側のみ：service_role はRLSをバイパスする。環境変数はサーバー限定
// このキーがバンドルに混入したら全テナントのデータが筒抜けになる
import { createClient } from '@supabase/supabase-js';

export const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!, // NEXT_PUBLIC_ を絶対に付けない
  { auth: { persistSession: false } },
);
```

### 2-2. Forgetting to enable = full exposure

Merely creating a new table does **not enable** RLS. And an "RLS-disabled table" is **readable and writable for all rows** even from the anon key.

```sql
-- ❌ これだけでは無防備。anon からでも全行アクセスできてしまう
create table public.invoices (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null,
  amount integer not null
);

-- ✅ 必ずテーブル作成と同じマイグレーションで有効化する
alter table public.invoices enable row level security;
```

Here lies **RLS's most frightening asymmetry.** In PostgreSQL, if you enable RLS and write no policy at all, it becomes "default-deny" and **all rows become invisible** (it falls to the safe side). On the other hand, **forgetting to enable RLS itself means full exposure** (it falls to the dangerous side). That is, **the direction of an accident is "full exposure."**

> **An operational iron rule**: inspect in CI "whether RLS is enabled on all tables in the `public` schema." Supabase's dashboard warns about disabled tables, but don't rely on human visual inspection. The reason I could thoroughly enable **RLS on all 69 tables** in my project is that I automated this inspection (I post the query in §7).

---

## 3. USING, WITH CHECK, and per-operation policies

What first confuses you in RLS is "the difference between `USING` and `WITH CHECK`." Understand this accurately and policy design becomes clear at once ([PostgreSQL: ddl-rowsecurity](https://www.postgresql.org/docs/current/ddl-rowsecurity.html)).

| Clause | What it judges | Operations it affects |
| --- | --- | --- |
| `USING` | Whether **an existing row** is visible/touchable (the filter at read, the target judgment for update/delete) | SELECT / UPDATE / DELETE |
| `WITH CHECK` | Whether **the row to be written (the new value)** is allowed | INSERT / UPDATE |

Intuitively, **`USING` is "the entrance (which rows you can handle)," and `WITH CHECK` is "the exit (what rows you can leave)."**

Tabulating the clauses needed per operation becomes a design guideline.

| Operation | `USING` | `WITH CHECK` | Note |
| --- | --- | --- | --- |
| `SELECT` | ✅ Required | — | Narrow the visible rows |
| `INSERT` | — | ✅ Required | Reject the insertion of invalid rows |
| `UPDATE` | ✅ (target rows) | ✅ (post-update values) | Writing **both** is safe |
| `DELETE` | ✅ Required | — | Narrow the deletable rows |

That `UPDATE` requires both is the key point. With `USING` alone, you can't prevent the attack of "rewriting your tenant's row's tenant_id to another tenant's to **let it escape**." Constrain the post-update value too with `WITH CHECK`.

```sql
-- 自分のレコードだけ操作できる、最小の4ポリシー（公式の基本形）
-- 参照: https://supabase.com/docs/guides/database/postgres/row-level-security

create policy "select own profile"
on public.profiles for select
to authenticated
using ( (select auth.uid()) = user_id );

create policy "insert own profile"
on public.profiles for insert
to authenticated
with check ( (select auth.uid()) = user_id );

create policy "update own profile"
on public.profiles for update
to authenticated
using ( (select auth.uid()) = user_id )        -- 対象は自分の行だけ
with check ( (select auth.uid()) = user_id );    -- 更新後も自分の行のまま

create policy "delete own profile"
on public.profiles for delete
to authenticated
using ( (select auth.uid()) = user_id );
```

> **Why not bundle into a single `for all`**: you can write it with `for all`, but splitting by operation lets you express **asymmetric authorization** like "loose on reads, strict on writes," and makes it easier to test (SRP-wise, "one policy, one responsibility"). In real operation, I recommend per-operation. Note the official caution that **to do UPDATE you also need a corresponding SELECT policy** (you can't update a row you can't read).

### Policies are composed with OR/AND

When multiple policies apply to the same operation, PostgreSQL composes **permissive (default) with OR and restrictive (`as restrictive`) with AND.** This is a powerful design lever.

- **permissive (OR)**: when you want to **add up permissions**, like "own rows **or** with a public flag."
- **restrictive (AND)**: when you want to apply a **constraint cutting across all policies**, like "MFA (aal2) required **in all cases**."

```sql
-- restrictive: テナント分離は「絶対条件」。他のpermissiveポリシーと AND される
create policy "tenant boundary (hard constraint)"
on public.invoices as restrictive
to authenticated
using ( tenant_id = (select private.current_tenant_id()) )
with check ( tenant_id = (select private.current_tenant_id()) );
```

Use `as restrictive` for the "tenant boundary," and **no matter what permissive policy you add later, the tenant boundary alone never loosens.** This pays off in multi-tenant SaaS (`current_tenant_id()` is defined in §5).

---

## 4. Multi-tenancy pattern ①: isolation by tenant_id

The most basic multi-tenant isolation is giving all tables a `tenant_id` (`org_id`) and enforcing **"you can only touch rows of the tenant you belong to"** with RLS.

How do you know "your tenant." Naively, "JOIN a user→tenant correspondence table," but writing that in every policy is **duplication (a DRY violation)** and **slow.** What Supabase officially recommends is the form of pulling it in one shot from the JWT's `app_metadata` or the `security definer` helper described later.

`app_metadata` is an area **the user themselves can't rewrite**, so it's suitable for storing authorization information (`user_metadata` can be altered by the user, so don't use it for authorization).

```sql
-- JWTの app_metadata から tenant_id を取る最速パターン
-- 参照: https://supabase.com/docs/guides/database/postgres/row-level-security#helper-functions
create policy "isolate by tenant via JWT"
on public.invoices for select
to authenticated
using (
  tenant_id = ((select auth.jwt()) -> 'app_metadata' ->> 'tenant_id')::uuid
);
```

> **The pitfall of the JWT pattern**: the JWT is **not updated immediately.** Remove a user from a tenant, and that change is **not reflected in `auth.jwt()` until the JWT is refreshed** (officially stated). For requirements where "permission revocation should take effect immediately," use a **helper function that references the DB** (§5), not the JWT. My project made "immediate reflection of permission changes" a requirement, so I made the latter the mainstay.

---

## 5. Multi-tenancy pattern ②: a membership table + a security definer helper

A real-operation SaaS normally has "one user belongs to multiple tenants" and "permissions differ per role." This can't be fully expressed with the JWT alone, and you need **a join table representing membership.**

```sql
-- ユーザーとテナントの多対多。ロールもここに持つ
create table public.memberships (
  user_id   uuid not null references auth.users (id) on delete cascade,
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  role      text not null check (role in ('owner', 'member', 'viewer')),
  primary key (user_id, tenant_id)
);
alter table public.memberships enable row level security;
```

### Why a security definer helper is needed

Directly JOIN `memberships` inside a policy and **`memberships`' RLS also takes effect on that JOIN itself, inducing infinite recursion or complication.** What cuts this off is a **`security definer` function.** It **executes with the definer's (= the admin's) permissions and bypasses RLS** to pull membership. Being able to consolidate the knowledge in one place (DRY) is also a benefit.

There are two practices the official docs strongly recommend.

1. **Don't place it in the public schema.** Place it in a `private` schema and don't include it in Exposed schemas (so it can't be hit from outside via RPC).
2. **Always attach `set search_path = ''`.** To prevent a function-swap attack via the search path, write all objects schema-qualified.

```sql
-- private スキーマ（API非公開）に認可ヘルパーを集約する
create schema if not exists private;

-- 「このユーザーは、このテナントで指定ロール以上を持つか」
create or replace function private.has_tenant_role(
  p_tenant_id uuid,
  p_min_role  text
)
returns boolean
language sql
stable
security definer        -- ★定義者権限で実行＝memberships のRLSをバイパス
set search_path = ''    -- ★必須：search_path 経由の攻撃を封じる
as $$
  select exists (
    select 1
    from public.memberships m
    where m.user_id   = (select auth.uid())
      and m.tenant_id = p_tenant_id
      and case p_min_role
            when 'viewer' then m.role in ('viewer', 'member', 'owner')
            when 'member' then m.role in ('member', 'owner')
            when 'owner'  then m.role = 'owner'
            else false
          end
  );
$$;

-- よく使う「現在のテナント（単一所属前提のショートカット）」
create or replace function private.current_tenant_id()
returns uuid
language sql
stable
security definer
set search_path = ''
as $$
  select m.tenant_id
  from public.memberships m
  where m.user_id = (select auth.uid())
  limit 1;
$$;
```

With this, policies become **declarative and fast.**

```sql
-- 読み取り：所属テナントなら誰でも（viewer以上）
create policy "members can read invoices"
on public.invoices for select
to authenticated
using ( (select private.has_tenant_role(tenant_id, 'viewer')) );

-- 作成：member 以上、かつ自テナントの行に限る
create policy "members can create invoices"
on public.invoices for insert
to authenticated
with check ( (select private.has_tenant_role(tenant_id, 'member')) );

-- 削除：owner だけ
create policy "owners can delete invoices"
on public.invoices for delete
to authenticated
using ( (select private.has_tenant_role(tenant_id, 'owner')) );
```

> The reason for **further wrapping with `(select auth.uid())`** inside the `security definer` function is to make §6's performance optimization (initPlan caching) take effect inside the function too. In an official benchmark, wrapping a security definer function with `(select ...)` inside a policy produces a dramatic difference of **178,000ms → 12ms (99.993% improvement).**

---

## 6. Role / permission pattern: owner / member / viewer

On the foundation of §5's helper, let me design the permission matrix of the typical 3 roles (owner/member/viewer). The shortest path to prevent leakage and excessive permissions is to **define permissions in an "operation × role" table and drop it straight into policies.**

| Operation | viewer | member | owner | Policy condition |
| --- | --- | --- | --- | --- |
| View an invoice (SELECT) | ✅ | ✅ | ✅ | `has_tenant_role(tenant_id, 'viewer')` |
| Create an invoice (INSERT) | ❌ | ✅ | ✅ | `has_tenant_role(tenant_id, 'member')` |
| Edit an invoice (UPDATE) | ❌ | ✅ | ✅ | `has_tenant_role(tenant_id, 'member')` |
| Delete an invoice (DELETE) | ❌ | ❌ | ✅ | `has_tenant_role(tenant_id, 'owner')` |
| Invite a member | ❌ | ❌ | ✅ | `has_tenant_role(tenant_id, 'owner')` |

This table is **the spec, the test cases, and the policy definition** (in §8 I convert this table straight into pgTAP tests). Update the table and update both the policies and the tests — this is the trick to keep role design from rotting.

```sql
-- UPDATE は member 以上。USING(対象行) と WITH CHECK(更新後) の両方を縛る
create policy "members can update invoices"
on public.invoices for update
to authenticated
using ( (select private.has_tenant_role(tenant_id, 'member')) )
with check ( (select private.has_tenant_role(tenant_id, 'member')) );
```

---

## 7. Performance: RLS changes 100× by "how you write it"

RLS is convenient, but **write it wrong and the function runs per row on every table scan**, becoming fatally slow. Supabase's official [performance section](https://supabase.com/docs/guides/database/postgres/row-level-security#performance) lists optimizations with benchmarks. In the order they pay off in practice.

### 7-1. Wrap `auth.uid()` with `(select auth.uid())` (most important)

Write `auth.uid()` bare and it's **evaluated per row.** Wrap it with `(select ...)` and the Postgres optimizer sets up an **initPlan** and **evaluates it once per statement and caches it.**

```sql
-- ❌ Before: 行ごとに auth.uid() が走る
using ( auth.uid() = user_id );

-- ✅ After: initPlan キャッシュ。179ms → 9ms（公式ベンチで約95%改善）
using ( (select auth.uid()) = user_id );
```

This takes effect for `auth.uid()` / `auth.jwt()` / `security definer` functions all alike. **Put it into your body as the first pattern when writing RLS.**

### 7-2. Index the columns used in the policy

Columns that appear in the policy's condition, like `tenant_id` or `user_id`, need an index unless they're the primary key.

```sql
-- ポリシーが tenant_id で絞るなら、その列の索引は必須
create index invoices_tenant_id_idx
on public.invoices using btree (tenant_id);
-- 公式ベンチ: 171ms → <0.1ms（約99.94%改善）
```

### 7-3. Write an explicit filter on the query side too

Without relying entirely on RLS's implicit WHERE, **write the same condition in the app's query** too, and Postgres sets up a better execution plan.

```ts
// ✅ RLSが守るのは前提。その上でクエリにも tenant_id を明示する
const { data } = await supabase
  .from('invoices')
  .select('*')
  .eq('tenant_id', tenantId); // 公式ベンチ: 171ms → 9ms
```

### 7-4. Avoid JOINs in the policy, rewrite to IN/ANY

Rather than JOINing "source table → target table," **pulling the needed set of ids first and applying it with `in`** is faster.

```sql
-- ❌ 遅い: auth.uid() を team_user に JOIN して当てにいく
using (
  (select auth.uid()) in (
    select user_id from team_user where team_user.team_id = team_id
  )
);

-- ✅ 速い: 自分の所属 team_id 集合を引いて in で当てる
using (
  team_id in (
    select team_id from team_user where user_id = (select auth.uid())
  )
);
-- 公式ベンチ: 9,000ms → 20ms（約99.78%改善）
```

### 7-5. Always specify `to <role>`

Omit the role and **that policy is evaluated for all roles.** Write `to authenticated` and an anon request doesn't even run the policy evaluation.

```sql
-- ❌ ロール未指定: anon でも policy が評価される
create policy "p" on rls_test using ( auth.uid() = user_id );

-- ✅ to authenticated: anon は即座にスキップ。170ms → <0.1ms
create policy "p" on rls_test
to authenticated
using ( (select auth.uid()) = user_id );
```

> These **multiply in effect.** Write "`(select ...)` wrapping × indexes × `to` specification" as a pattern from the start, and the situation of RLS being the cause of slowness almost never happens. Conversely, making it take effect later is quietly a hassle (rewriting existing policies + adding indexes + measuring). **The first pattern is everything.**

---

## 8. Testing: make RLS a "verification gate" (pgTAP)

RLS is authorization's last line of defense. **An untested last line of defense is out of the question.** Supabase officially supports **pgTAP** (a testing framework for PostgreSQL), and you can mechanically verify in CI "this role can / can't see this row." Drop §6's permission matrix straight into tests.

```sql
-- supabase/tests/rls_invoices.test.sql
begin;
select plan(4);

-- ロールと「現在のユーザー」を擬似的に切り替えるヘルパー（Supabase慣例）
create or replace function tests.authenticate_as(p_user uuid)
returns void language sql security definer set search_path = '' as $$
  select set_config('role', 'authenticated', true),
         set_config('request.jwt.claims',
                    json_build_object('sub', p_user::text)::text, true);
$$;

-- 前提データ: tenant_a に viewer の user_v、member の user_m
-- （省略: insert ...）

-- ① viewer は自テナントの請求書を「見える」
select tests.authenticate_as('00000000-0000-0000-0000-0000000000v1');
select isnt_empty(
  $$ select 1 from public.invoices where tenant_id = '...tenant_a...' $$,
  'viewer can read own-tenant invoices'
);

-- ② viewer は INSERT できない（member 未満）
select tests.authenticate_as('00000000-0000-0000-0000-0000000000v1');
select throws_ok(
  $$ insert into public.invoices(tenant_id, amount) values ('...tenant_a...', 100) $$,
  '42501',  -- insufficient_privilege
  null,
  'viewer cannot insert'
);

-- ③ 他テナントの請求書は「1行も見えない」(テナント境界)
select tests.authenticate_as('00000000-0000-0000-0000-0000000000v1');
select is_empty(
  $$ select 1 from public.invoices where tenant_id = '...tenant_b...' $$,
  'cannot read other-tenant invoices (isolation holds)'
);

-- ④ member は INSERT できる
select tests.authenticate_as('00000000-0000-0000-0000-0000000000m1');
select lives_ok(
  $$ insert into public.invoices(tenant_id, amount) values ('...tenant_a...', 200) $$,
  'member can insert into own tenant'
);

select * from finish();
rollback;
```

```bash
# CIで実行する検証ゲート（Supabase CLI）
supabase test db
```

Make this a **merge blocking condition** and "a PR that carelessly loosens a policy" can't get into main. The reason I could keep adding **about 280 policies** with peace of mind in my project is that I stretched a tenant-boundary test (equivalent to ③ above) over all major tables.

Furthermore, put a query that mechanically detects **forgetting to enable RLS** into the verification gate too.

```sql
-- public スキーマで RLS が無効なテーブルを検出（1行でも出たらCI失敗にする）
select c.relname as table_without_rls
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
  and c.relkind = 'r'
  and c.relrowsecurity = false;
```

> **The crux of test strategy**: RLS should test "**that the boundary isn't leaking**" more than "that a policy **exists**." `is_empty(another tenant's data)` is the real prize. Write only `isnt_empty(your own data)` and be satisfied, and you **miss the hole in the boundary.**

---

## 9. Common pitfalls

The typical ones that directly lead to tenant leaks / accidents in practice, paired with countermeasures.

| Pitfall | What happens | Countermeasure |
| --- | --- | --- |
| **Forgetting to enable RLS** | A new table is fully exposed from anon | Put §8's detection query into a CI gate |
| **`service_role` key mixed into the client** | RLS fully bypassed = all tenants exposed | The key is server-only. Don't attach `NEXT_PUBLIC_` |
| **Missing `with check` on UPDATE** | Rewrite your row to another tenant and it leaks | UPDATE has both `using` and `with check` |
| **Bare `auth.uid()`** | Per-row evaluation, terribly slow | Wrap with `(select auth.uid())` |
| **Authorizing with `user_metadata`** | The user alters their own permission | Authorize with `app_metadata` or DB membership |
| **Expecting immediate revocation with the JWT** | Permission revocation doesn't take effect until refresh | If immediacy is needed, use a DB-reference helper |
| **Null's implicit failure** | Unauthenticated, `null = user_id` is always false (looks safe but diverges from intent) | If needed, make `auth.uid() is not null` explicit |

### Supplement on the pitfall: `service_role` leakage is "the one worst hit"

Whereas other pitfalls take effect gradually, mixing the `service_role` key into the client **disables all RLS by itself.** Put an inspection like `grep -r "SERVICE_ROLE" apps/` into lint/CI, and structurally guarantee that this string isn't included in the front-end bundle. In Supabase's design too, via the client library it's said to **follow the logged-in user's RLS**, but it's a different story if you expose the key itself. **"Don't leak the key" is the premise of everything.**

### Supplement on the pitfall: harden the tenant boundary with `as restrictive`

As you add permissive policies with OR, **one of them is loose and pierces the tenant boundary** — this is the most easily occurring leak. As shown in §3, keep **only the tenant boundary `as restrictive`**, and whatever permissive policy you add later, the boundary **always survives by AND composition.** It's a cheap, powerful insurance that changes a "loosens by addition" design into a "tightens by multiplication" design.

---

## 10. Cross-cutting concerns: observability and table-owner bypass

Finally, let me supplement two points that pay off in production.

- **Table-owner bypass**: in PostgreSQL, **the table owner, a BYPASSRLS-attribute role, and a superuser normally bypass RLS.** If the role running migrations is the owner, and you want to **enforce RLS even on the owner itself**, use `alter table ... force row level security;`. In Supabase's normal operation, the app runs as `authenticated`/`anon`, so it's mostly not a problem, but you need to be conscious in a design that runs batches as the owner role.
- **Observability**: access rejected by RLS appears as "0 rows" or `42501` (insufficient_privilege). Put **"unexpected empty results / permission errors"** on logs / monitoring on the app side, and you can detect policy bugs and tenant-boundary anomalies early. RLS rejects silently, so **making the silence observable** is the operational practice.

---

## Summary: RLS is the technique of building "a floor that doesn't collapse even if you forget to write it"

Multi-tenant SaaS authorization is too fragile to protect with human discipline. RLS embeds that boundary **as structure into PostgreSQL, and no matter which path it comes from, the DB rejects last** — an implementation of zero trust. The key points in five lines.

1. **Push authorization into the DB.** UI/API if statements are bypassed. RLS is the last line of defense that applies equally to all paths.
2. **Use roles correctly.** Clients use `anon` + RLS, and `service_role` is server-only (leakage = full exposure).
3. **`USING` = the entrance, `WITH CHECK` = the exit.** UPDATE has both. Fix the tenant boundary with AND via `as restrictive`.
4. **Multi-tenancy is membership + `security definer` (`set search_path = ''`)** — declaratively, immediately, and DRY-ly.
5. **Make `(select auth.uid())` wrapping × indexes × `to` specification a pattern, and make boundary leakage a CI gate with pgTAP.**

With this design, I've built and operate **RLS on all 69 tables and about 280 policies** alone (the subject is the [real-time game-scoring app](/case-studies/realtime-sports-scoring-app)). The "offline concurrent-editing consistency" side of the same product's design is written in [this article](/blog/untrusted-client-postgres-rls-offline-first).

"With one person × generative AI (Claude Code), fast, cheap, and safe" — for consultation on authorization design, RLS auditing, and performance improvement of a multi-tenant SaaS using Supabase / PostgreSQL, reach out from [contact](/contact).