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

"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 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 and the PostgreSQL official documentation.

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

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.

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

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

// ✅ クライアント側：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が守る
);

// ⚠️ サーバー側のみ：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.

-- ❌ これだけでは無防備。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).

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.

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.

-- 自分のレコードだけ操作できる、最小の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."

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

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

-- ユーザーとテナントの多対多。ロールもここに持つ
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.

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

-- 読み取り：所属テナントなら誰でも（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.

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.

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

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

-- ポリシーが 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.

// ✅ 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.

-- ❌ 遅い: 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.

-- ❌ ロール未指定: 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.

-- 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;

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

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

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). The "offline concurrent-editing consistency" side of the same product's design is written in this article.

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