# Guard Supabase RLS with Tests: Verify Both 'Allow' and 'Deny' with pgTAP, and Stop Authorization Regressions in CI

> A test strategy to trust Supabase/PostgreSQL Row-Level Security (RLS) in production. Switch request.jwt.claims with pgTAP to verify both allow and deny paths, plus the pitfalls of SECURITY DEFINER and search_path, an RLS-coverage CI gate, and migration safety (squawk)—all in real code. RLS isn't done when you write it; it becomes trustworthy in production only when you test it.

- Published: 2026-06-24
- Author: 友田 陽大
- Tags: Supabase, RLS, PostgreSQL, セキュリティ, テスト
- URL: https://tomodahinata.com/en/blog/supabase-rls-testing-pgtap-policy-regression-guide
- Category: Databases & RLS
- Pillar guide: https://tomodahinata.com/en/blog/supabase-production-guide-nextjs-rls-realtime-edge-functions

## Key points

- RLS isn't done when you write it; it becomes trustworthy in production only when you auto-test both allow and deny with pgTAP and stop regressions in CI
- Tests cover the 4 quadrants of read/write × allow/deny, and 80% of the value is in the deny paths. A loosening regression only fails on a deny test
- Impersonate with set local role + request.jwt.claims (or tests.authenticate_as), and keep tests independent and idempotent with begin/rollback
- Assert deny as 0 rows for SELECT/UPDATE/DELETE, and as an exception (42501) for a WITH CHECK violation on INSERT/UPDATE—use the right one for each
- SECURITY DEFINER functions are a hole that can bypass RLS. Lock them down with search_path='' and fully-qualified names and least privilege, and add a deny test that returns false to an outsider

---

"We wrote RLS, so authorization is safe"—I don't take that sentence at face value.

Row-Level Security (RLS) is powerful. With a few lines of policy using `auth.uid()`, you can enforce "you can only read/write your own rows" at the database layer. Unlike app-side filtering such as `if (user.id !== row.ownerId)`, **no matter which client it comes from or what SQL it throws, the DB rejects it as the last line of defense.** This is RLS's value.

But here's what I've seen happen over and over on the front line.

- The `SELECT` policy was written, but the `UPDATE` `WITH CHECK` was forgotten, and **someone could rewrite another person's row to be their own.**
- A test for "what should be allowed is allowed" was written, but **a test for "what should be denied is denied" wasn't**, so a regression that carelessly loosened a policy went unnoticed.
- Complex authorization was offloaded to a `SECURITY DEFINER` function, and that function became **a hole that bypassed RLS and returned all rows.**

The terror of RLS comes down to **"you think you added a policy, but it leaks through another path" and "you can't notice a regression unless you write deny tests."** So my stance is consistent: **RLS isn't done when you write it. It becomes trustworthy in production only after you auto-test both 'what should be allowed is allowed' and 'what should be denied is denied' and stop regressions in CI.**

This article is an implementation guide to that "guard RLS with tests" methodology. As source material, I'll weave in design decisions from a [realtime collaborative match-recording app](/case-studies/realtime-sports-scoring-app) I built (an Expo + Next.js + Supabase monorepo, where multiple stakeholders—players, team admins, scorers, scouts, operators—touch the same data with different views). In this product I **enabled RLS on all 69 public tables and expressed roles, teams, time-limited permissions, and field-level disclosure with 280 policies.** And I tested all of them with **pgTAP**, stopping regressions in CI.

> **The rules of this article**: the specs for RLS, policies, and auth helpers are based on the **Supabase official documentation**, RLS semantics (USING / WITH CHECK / FORCE / policy combination) on the **PostgreSQL official documentation**, and assertion functions on the **pgTAP official reference** (all as of June 2026). Specs can be revised, so always confirm the latest in the official docs before going to production (URLs at the end of the article). The code is arranged for real operation, but secrets are assumed to be in environment variables. **RLS becomes trustworthy only when you test it.**

Note that this article focuses on "how to **test** RLS and stop regressions." How to **design** RLS in the first place—**protecting data offline-first, assuming an untrusted client**—is covered in the sister article, [Postgres RLS × offline-first design assuming an untrusted client](/blog/untrusted-client-postgres-rls-offline-first). Design over there, verification over here—a complementary relationship.

---

## 0. Get this into your head first: USING and WITH CHECK are different things

Before talking about tests, leaving this ambiguous **will make you design your tests wrong.** An RLS policy has two "expressions," with entirely different roles.

| Clause | What it controls | Commands it affects |
| --- | --- | --- |
| `USING (...)` | **Rows you can read / touch** (visibility, access to existing rows) | `SELECT` / `UPDATE` / `DELETE` |
| `WITH CHECK (...)` | **Rows you can write** (the constraint new row data must satisfy) | `INSERT` / `UPDATE` |

Quoting the PostgreSQL official definitions precisely:

- `USING` … controls which rows are **visible** for `SELECT`, and which existing rows are **accessible** for `UPDATE`/`DELETE`.
- `WITH CHECK` … controls which rows can be **inserted or updated** (the constraint new row data must satisfy). **If omitted, it defaults to the same as `USING`.**

Here's the first pitfall. `UPDATE` needs **both.**

```sql
-- ❌ 危険：USING だけだと、行を「掴める」ことは制御できるが、
--    「どんな値に書き換えられるか」は無制限。
--    自分の行を掴んで、user_id を他人のIDに書き換えられてしまう。
create policy "update own profile (broken)"
on profiles for update
to authenticated
using ( (select auth.uid()) = user_id );

-- ✅ 正しい：WITH CHECK で「書き換え後の行」も縛る
create policy "update own profile"
on profiles for update
to authenticated
using ( (select auth.uid()) = user_id )       -- どの行を掴めるか
with check ( (select auth.uid()) = user_id );  -- どんな値に書き換えてよいか
```

And one more thing the Supabase docs make explicit: **to perform an `UPDATE` you also need a corresponding `SELECT` policy.** This is because `UPDATE` internally needs to make the target rows visible.

This "asymmetry of USING and WITH CHECK" is exactly **the breeding ground for regressions you can't notice without deny tests.** A `using`-only policy stays green with just "read tests." The hole in `with check` will never be detected unless you write a **deny test of "attempting to rewrite into someone else's value should fail."**

---

## 1. Mental model: RLS tests are "2 axes × 2 directions"

The framework I use when designing RLS tests is simple. For each table and each command, fill in these **4 quadrants.**

| | Allow (should pass) | Deny (should be rejected) |
| --- | --- | --- |
| **Read (SELECT)** | My rows are visible | Others' rows are not visible (0 rows or excluded) |
| **Write (INSERT/UPDATE/DELETE)** | I can create / fix my rows | I can't create / fix others' rows (error) |

A mediocre test only writes the left half (allow). **80% of the value of RLS tests is in the right half (deny).** Because a regression that loosens a policy—carelessly leaving in `using (true)`, widening a condition too far with `OR`, deleting `with check`—**only fails on a deny test.**

Keep PostgreSQL's **default-deny** nature in mind too. The instant you `ENABLE ROW LEVEL SECURITY`, if there's not a single policy, **all rows become invisible and unmodifiable.** In Supabase's words, "via the publishable key, you can't access any data until you create a policy." In other words, **you close first, then open** is the RLS way. Tests stand on this premise too and verify "is the hole you think you opened only as wide as you intended."

---

## 2. The foundation: enable RLS and write policies (conscious of allow and deny)

Let's place the minimal schema to test. Think with a `matches` table that simplifies the match-recording app. The rule: a team admin (`team_admin`) can read/write their team's matches, a scout (`scout`) can only read, and anyone else can't touch it.

```sql
-- 1) まずは閉じる：有効化した瞬間、ポリシーが無ければ全行不可視
alter table public.matches enable row level security;

-- 2) ロール別の GRANT（RLS とは別レイヤーの権限。両方必要）
grant select on public.matches to anon, authenticated;
grant insert, update, delete on public.matches to authenticated;

-- 3) 読みの許可：自チームの試合だけ見える（scout も team_admin も読める）
create policy "read matches of my team"
on public.matches for select
to authenticated
using (
  team_id in (
    select team_id from public.team_members
    where user_id = (select auth.uid())
  )
);

-- 4) 書きの許可：team_admin だけが自チームの試合を作れる
create policy "team_admin can insert matches"
on public.matches for insert
to authenticated
with check (
  exists (
    select 1 from public.team_members
    where user_id = (select auth.uid())
      and team_id = matches.team_id
      and role = 'team_admin'
  )
);
```

Here I'm also practicing Supabase's official **performance know-how.** Writing `auth.uid()` **as-is** evaluates it per row, but **wrapping it in a subquery** as `(select auth.uid())` evaluates it once at initialization, and the official measurement shows a 94–99% improvement. Indexing policy columns, and making the role explicit with `TO authenticated` to skip evaluation for non-matching roles, also help.

And **the most important defense**—`FORCE ROW LEVEL SECURITY`.

```sql
-- テーブルオーナーは既定で RLS をバイパスする。
-- 強制適用して、オーナー権限のコネクションでも RLS を効かせる。
alter table public.matches force row level security;
```

As the PostgreSQL docs make explicit, **a table owner bypasses RLS by default.** Furthermore, a **superuser** and a role with the **`BYPASSRLS` attribute** always bypass it. If a migration or some admin connection runs with owner privileges, the accident of **"slipping through RLS and leaking in production even though tests pass"** happens. `FORCE` plugs that hole.

> **The DRY pitfall**: "since read and write have the same condition," you'll be tempted to consolidate into one policy with `for all`. But since `SELECT` and `INSERT` are governed by different clauses (`USING` vs `WITH CHECK`), `for all` **blurs the intent and makes it harder to separate quadrants in tests.** I split read, create, fix, and delete into separate policies and test each independently (SRP: one policy = one authorization decision).

---

## 3. Install pgTAP and build the test skeleton

Here's the main topic. pgTAP is a test framework that runs on PostgreSQL, where you write assertions in SQL. In Supabase it's built into the local dev stack, and you can run it with `supabase test db`.

First, enable the extension. The official docs recommend creating it in **a dedicated schema so it doesn't pollute `public`.**

```sql
-- public を汚さないよう extensions スキーマに作る
create extension if not exists pgtap with schema extensions;
```

The **skeleton** of a pgTAP test always takes this form. **Wrapping it in `begin` … `rollback`** is meaningful—the data and role switches created during a test don't remain in the DB, and each test stays independent and idempotent.

```sql
begin;
select plan( 5 );  -- このファイルで実行するアサーション数を宣言

-- ... ここにアサーションを並べる ...

select * from finish();  -- 宣言数と実行数の齟齬を検出して終了
rollback;                -- 副作用を破棄
```

Declaring the **number of tests you plan to run in advance with `plan(N)`** is the pgTAP way. This lets you **detect premature failure** like "an exception was thrown partway and only 3 ran." `select * from finish()` does the final tally and TAP output.

Wrapping in `begin/rollback` is the most important pattern for guaranteeing **test independence.** Create match data, switch users, attempt writes—all of it is erased by `rollback`, so it doesn't depend on test order and gives the same result no matter how many times you run it in CI (reproducibility, idempotency).

---

## 4. The core: switch identity with `request.jwt.claims` and assert both allow and deny

The heart of RLS testing is to **try policies by "impersonating another user."** For requests via PostgREST, Supabase injects `role` (`anon`/`authenticated`) and the JWT claims as GUCs (runtime configuration parameters). In tests, you can simulate any user by **setting these by hand.**

### 4.1 The low-level API to switch identity

```sql
-- 認証済みユーザー u1 になりすます
set local role authenticated;
set local request.jwt.claims = '{ "sub": "00000000-0000-0000-0000-000000000001", "role": "authenticated" }';
-- この状態では auth.uid() が上記 sub を返す

-- 未認証（anon）に戻す
set local role anon;
set local request.jwt.claims = '';
```

Two points.

1. **Use `set local`.** Adding `local` makes it valid **only within the current transaction**, and `rollback` reliably reverts it. With `set` (global) it persists on the connection and contaminates between tests.
2. **`auth.uid()` reads the `sub` of `request.jwt.claims`**, so swapping `claims` makes you "another person." If you put authorization info in `app_metadata`, including it here reproduces a policy's `auth.jwt()` reference too.

> Why `app_metadata`: Supabase's docs state clearly that a JWT's `user_metadata` (= `raw_user_meta_data`) **can be rewritten by the user themselves**, so don't use it for authorization; `app_metadata` (= `raw_app_meta_data`) **cannot be rewritten by the user**, so it's appropriate as the place for authorization info. Reproduce this distinction in tests too.

In a real project, writing these raw claim settings every time is tedious and error-prone, so using the **helpers** distributed by `supabase/tests` (`tests.create_supabase_user()`, `tests.authenticate_as()`, `tests.authenticate_as_service_role()`, `tests.clear_authentication()`, `tests.get_supabase_uid()`, etc.) makes it declarative. Below, to show the mechanism, I present the raw claims first, then the helper version.

### 4.2 The allow path (Allow): what should pass, passes

```sql
begin;
select plan( 6 );

-- 前提データ：チーム t1 と、その team_admin である u1、scout である u2
-- （実際は seed か insert で用意。auth.users への投入はヘルパ推奨）
insert into public.team_members (user_id, team_id, role) values
  ('00000000-0000-0000-0000-000000000001', 't1', 'team_admin'),
  ('00000000-0000-0000-0000-000000000002', 't1', 'scout');

-- === team_admin (u1) として ===
set local role authenticated;
set local request.jwt.claims = '{ "sub": "00000000-0000-0000-0000-000000000001", "role": "authenticated" }';

-- 許可：自チームの試合を作れる
select lives_ok(
  $$ insert into public.matches (id, team_id, name) values ('m1', 't1', '初戦') $$,
  'team_admin は自チームの試合を作成できる'
);

-- 許可：作った試合が読める
select results_eq(
  $$ select count(*)::int from public.matches where team_id = 't1' $$,
  $$ values (1) $$,
  'team_admin は自チームの試合が見える'
);

-- === scout (u2) として ===
set local request.jwt.claims = '{ "sub": "00000000-0000-0000-0000-000000000002", "role": "authenticated" }';

-- 許可：scout も自チームの試合は読める
select isnt_empty(
  $$ select id from public.matches where team_id = 't1' $$,
  'scout は自チームの試合を閲覧できる'
);

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

`lives_ok(sql, desc)` verifies **no exception is thrown**, `results_eq(sql, expected, desc)` verifies **the result set matches exactly** (content and row order), and `isnt_empty` / `is_empty` verify **there are / aren't rows.** The allow path is confirmation that "it passes as expected." Up to here, many people write tests.

### 4.3 The deny path (Deny): what should be rejected is rejected ← this is the main event

```sql
begin;
select plan( 4 );

insert into public.team_members (user_id, team_id, role) values
  ('00000000-0000-0000-0000-000000000001', 't1', 'team_admin'),
  ('00000000-0000-0000-0000-000000000003', 't2', 'team_admin');  -- 別チーム
insert into public.matches (id, team_id, name) values ('m1', 't1', '初戦');

-- === 別チーム t2 の team_admin (u3) として ===
set local role authenticated;
set local request.jwt.claims = '{ "sub": "00000000-0000-0000-0000-000000000003", "role": "authenticated" }';

-- 拒否：他チームの試合は見えない（0件）
select is_empty(
  $$ select id from public.matches where team_id = 't1' $$,
  '他チームの試合は閲覧できない'
);

-- 拒否：他チームの試合を更新しようとしても、対象行が見えないので 0 行更新（漏洩なし）
select results_eq(
  $$ with u as (update public.matches set name = 'のっとり' where id = 'm1' returning 1)
     select count(*)::int from u $$,
  $$ values (0) $$,
  '他チームの試合は更新できない（0行）'
);

-- === scout (u2) として ===
set local request.jwt.claims = '{ "sub": "00000000-0000-0000-0000-000000000002", "role": "authenticated" }';
insert into public.team_members (user_id, team_id, role) values
  ('00000000-0000-0000-0000-000000000002', 't1', 'scout');

-- 拒否：scout は試合を作れない → WITH CHECK 違反で 42501 が飛ぶべき
select throws_ok(
  $$ insert into public.matches (id, team_id, name) values ('m2', 't1', '不正作成') $$,
  '42501',
  'new row violates row-level security policy for table "matches"',
  'scout は試合を作成できない（RLS違反でエラー）'
);

-- === 未認証 (anon) として ===
set local role anon;
set local request.jwt.claims = '';

-- 拒否：anon には何も見えない
select is_empty(
  $$ select id from public.matches $$,
  '未認証ユーザーには一切見えない'
);

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

This is **the core of RLS testing.** Let me organize the points of note.

- `throws_ok(sql, '42501', msg, desc)` … when `INSERT`/`UPDATE` violates `WITH CHECK`, PostgreSQL throws **SQLSTATE `42501` (insufficient_privilege)** with `new row violates row-level security policy ...`. **Assert "it's rejected" precisely by error code.**
- The deny of `SELECT`/`UPDATE`/`DELETE` manifests **not as an exception but as "the target rows aren't visible" = "0 rows."** So the other-team update is confirmed with **"0 rows updated" via `results_eq`**, not `throws_ok`. This is easy to miss—"no error = OK" is wrong; **"being 0 rows" is the correct deny.**
- `anon`'s `is_empty` … confirm that everything disappears when unauthenticated. This is verification of the **close-then-open** design.

> **What happens if you don't write deny paths**: suppose someone rewrites the `read matches of my team` policy to `using (true)`. The allow tests (4.2) **all stay green** (they pass, since it's "more visible"). The only thing that fails is 4.3's `is_empty('他チームの試合は閲覧できない')`. **A deny test is the sole sensor that catches a regression loosening authorization.**

### 4.4 Write declaratively with helpers

Raw claims are useful for understanding the mechanism, but in a production test suite, make it readable with Supabase's distributed helpers.

```sql
begin;
select plan( 2 );

select tests.create_supabase_user('admin@t1.test');
select tests.create_supabase_user('scout@t1.test');

-- team_admin として
select tests.authenticate_as('admin@t1.test');
select lives_ok(
  $$ insert into public.matches (id, team_id, name) values ('m1', 't1', '初戦') $$,
  'team_admin は試合を作れる'
);

-- scout として（拒否）
select tests.authenticate_as('scout@t1.test');
select throws_ok(
  $$ insert into public.matches (id, team_id, name) values ('m2', 't1', '不正') $$,
  '42501', null,
  'scout は試合を作れない'
);

select tests.clear_authentication();
select * from finish();
rollback;
```

`authenticate_as()` internally sets `role` and `request.jwt.claims` (with the correct `sub`), so **"as whom" is declared in one line.** The readability for the reader is on another level (ETC: even adding identities, the diff stays localized).

---

## 5. Test the policy "meta": stop structural drift

In addition to row-data allow/deny, **structural-level tests of "does the policy even exist, and does it apply to the intended roles and commands"** are also effective. pgTAP has RLS-specific assertions.

```sql
begin;
select plan( 4 );

-- このテーブルに「このポリシー集合」が過不足なく存在することを固定
select policies_are(
  'public',
  'matches',
  array[
    'read matches of my team',
    'team_admin can insert matches'
  ],
  'matches のポリシー集合が想定どおり'
);

-- ポリシーが想定ロールに効いている
select policy_roles_are(
  'public', 'matches', 'read matches of my team', array['authenticated'],
  '読みポリシーは authenticated のみ'
);

-- ポリシーが想定コマンドに効いている
select policy_cmd_is(
  'public', 'matches', 'team_admin can insert matches', 'INSERT',
  '作成ポリシーは INSERT 用'
);

-- RLS が有効であること自体も検査（has_table 等と同系統の構造テスト）
select has_table('public', 'matches', 'matches テーブルが存在');

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

- `policies_are(schema, table, policies[], desc)` … verifies that the table's **RLS policy set matches the expected set exactly.** Immediately fails **a regression that deleted / renamed a policy.**
- `policy_roles_are(...)` … pins the **roles a policy affects.** Catches a regression that mistakenly widened `TO authenticated` to `TO anon`.
- `policy_cmd_is(...)` … pins the **command a policy affects** (`SELECT`/`INSERT`/...).

These are **complementary** to the "row allow/deny" tests. The former guards behavior, the latter **structural drift.** At a scale of 280 policies, "pinning the set" with `policies_are` becomes a source of reassurance during refactoring.

---

## 6. The biggest pitfall: `SECURITY DEFINER` functions can bypass RLS

Writing complex authorization (multi-level team hierarchies, time-limited permissions, field-level disclosure) directly into a policy expression bloats the expression and kills readability. Offloading it to a **helper function** is the right call—but here lies **RLS's biggest landmine.**

A `SECURITY DEFINER` function runs with **the definer's (often the owner's) privileges.** As the PostgreSQL docs say, **the expression is evaluated with the executing user's privileges (unless you use a security-definer function).** Flip that around: inside a `SECURITY DEFINER` function, **you have the owner's privileges, not the caller's**, and the owner bypasses RLS by default. In other words,

**An unguarded `SECURITY DEFINER` function can become a hole that slips past RLS and returns all rows.**

Even more notorious is **`search_path` hijacking.** If a function doesn't fix its `search_path`, an attacker can create a same-named table or function in another schema and **make the high-privileged `SECURITY DEFINER` operate on an unintended target.** This is a classic privilege-escalation pattern in PostgreSQL.

The countermeasures are **a fixed playbook.**

```sql
-- ✅ 安全な SECURITY DEFINER の書き方
create or replace function public.is_team_admin(p_team_id text)
returns boolean
language sql
stable
security definer
set search_path = ''   -- ★ search_path を空に固定（注入を封じる）
as $$
  -- すべて完全修飾（public.team_members など）で参照する
  select exists (
    select 1 from public.team_members
    where user_id = (select auth.uid())
      and team_id = p_team_id
      and role = 'team_admin'
  );
$$;

-- 実行権限は必要なロールにだけ付与（最小権限）
revoke all on function public.is_team_admin(text) from public;
grant execute on function public.is_team_admin(text) to authenticated;
```

The rules are these.

1. **Fix `search_path` to empty with `set search_path = ''`.** Even `public,pg_temp` is a source of carelessness, so write objects inside the function **all fully-qualified** (`public.team_members`, `auth.uid()`).
2. **Ask whether you really need DEFINER.** Use it only when there's a legitimate reason to bypass RLS (avoiding recursive policies, referencing helper tables). If you don't need it, use `SECURITY INVOKER` (the default) and let RLS take effect (YAGNI / least privilege).
3. **`revoke ... from public` + `grant execute` limited to the necessary roles.** Don't make a high-privileged function anyone can call.
4. **Validate `auth.uid()`, the role, and MFA if needed at the function entrance.** The match-recording app's RPCs are written with the `SECURITY DEFINER` + `search_path`-fixed playbook, always checking `auth.uid()`, role, and `require_mfa()` at the entrance.

And—**test `SECURITY DEFINER` functions with pgTAP intensively.**

```sql
begin;
select plan( 2 );

select tests.create_supabase_user('admin@t1.test');
select tests.create_supabase_user('outsider@t9.test');

insert into public.team_members (user_id, team_id, role)
  values (tests.get_supabase_uid('admin@t1.test'), 't1', 'team_admin');

-- 許可：team_admin に対して true を返す
select tests.authenticate_as('admin@t1.test');
select is( public.is_team_admin('t1'), true, 'team_admin には true' );

-- 拒否：無関係ユーザーに対して true を返さない（=DEFINERで漏れていない）
select tests.authenticate_as('outsider@t9.test');
select is( public.is_team_admin('t1'), false, '部外者には false（DEFINERで全行を漏らさない）' );

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

`is(have, want, desc)` **compares two values with `IS NOT DISTINCT FROM`.** The point here is to pin that **an outsider gets `false`.** If someone breaks the query inside the function and widens the RLS-bypass hole, this `is(..., false, ...)` fails. **A deny test for a DEFINER function is the highest-value deny test.**

---

## 7. Where to enforce authorization: the division of roles among app / RPC / RLS

To the question "should RLS do everything," I have a clear stance. **The last line of defense is always RLS. But for UX and performance, you also place filtering on the app side. The two should be redundant and must not contradict.**

| Enforcement point | Role | Trust level | Test method |
| --- | --- | --- | --- |
| **Client (Expo/Next)** | UX: don't show, don't let them press | **Don't trust** (bypassable) | UI tests (auxiliary) |
| **RPC (SECURITY DEFINER)** | Complex authorization, MFA gates, auditing | Medium (depends on implementation) | pgTAP (entrance validation, deny) |
| **RLS policy** | **Last line of defense**: row control at the DB layer | **High** (unbypassable) | **pgTAP (allow + deny)** |

Client-side filtering is **not the object of trust.** "Don't show the update button to a scout" is kind, but **the API can be hit even without the button.** So it's meaningless without RLS. Conversely, if RLS exists, the DB rejects whatever the client does. **Place the center of gravity of trust at the bottom (the DB), and keep the top (the UI) for convenience**—keep this principle and the test priorities decide themselves (**the pgTAP deny tests are top priority**).

In the match-recording app, sensitive operations (permission changes, disclosing player info) are **gated by email-OTP MFA.** `custom_access_token_hook` injects `mfa_verified` into the JWT, and the RPC entrance's `require_mfa()` enforces it. Disclosing player info to a scout is expressed with a field-level grant of "**the player's own approval ∩ the team admin's approval**" plus an **append-only audit log.** These too are of course pinned with pgTAP **deny paths**: "deny if MFA not verified" and "not disclosed with only one of the two approvals."

---

## 8. Stop regressions in CI: gates for RLS coverage and migration safety

Tests can't stop regressions if they only "run locally." They become a barrier only when you **enforce them in CI and make it un-mergeable unless green.**

### 8.1 Run pgTAP in CI

Stand up the Supabase local stack in GitHub Actions and run `supabase test db`.

```yaml
# .github/workflows/db-tests.yml（要点のみ）
name: db-tests
on: [pull_request]
jobs:
  pgtap:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: supabase/setup-cli@v1
        with: { version: latest }
      - run: supabase start          # ローカル Postgres + マイグレーション適用
      - run: supabase test db        # supabase/tests/**/*.sql の pgTAP を実行
```

`supabase test db` runs the SQL under `supabase/tests/` as pgTAP and **exits non-zero if even one fails.** This verifies both allow and deny per PR.

### 8.2 Make RLS coverage a "gate"

Here's my core claim. **Enforce in CI that "every table with RLS enabled has a policy test."** You machine-check the **existence** of tests. If you `enable row level security` on a new table but forget to write the test, CI fails—this is the **RLS-coverage gate.**

```bash
#!/usr/bin/env bash
# rls-coverage-gate.sh —— RLS有効テーブルに pgTAP テストがあるか検査
set -euo pipefail

# 1) RLS を有効化している public テーブルを列挙
mapfile -t rls_tables < <(
  psql "$DATABASE_URL" -tAc "
    select tablename from pg_tables t
    join pg_class c on c.relname = t.tablename
    where t.schemaname = 'public' and c.relrowsecurity = true
    order by 1;"
)

missing=()
for tbl in "${rls_tables[@]}"; do
  # 2) その表名に言及する pgTAP テストファイルが存在するか
  if ! grep -rqlF "$tbl" supabase/tests/; then
    missing+=("$tbl")
  fi
done

if (( ${#missing[@]} > 0 )); then
  echo "❌ RLS有効だがテスト未整備のテーブル:"
  printf '   - %s\n' "${missing[@]}"
  echo "→ supabase/tests/ に許可・拒否の pgTAP を追加してください"
  exit 1
fi
echo "✅ RLS有効な全テーブルにテストが存在します"
```

This is a coarse heuristic (grepping the table name), but it reliably stops **the worst pattern of "RLS enabled but zero tests."** Since it takes tables where `pg_class.relrowsecurity` is true as the source of truth, the targets automatically grow as you add tables (ETC: no need to update a ledger by hand). In the match-recording app, this idea lets CI guarantee that **all 69 RLS tables have tests.**

### 8.3 Migration safety: stop dangerous DDL with `squawk`

RLS regressions happen not only via policies but via **how migrations are written.** `drop policy`, `alter table ... disable row level security`, long-locking DDL—relying on review alone will eventually let one slip. **`squawk`** is a static linter for PostgreSQL migrations that mechanically detects dangerous patterns.

```yaml
  squawk:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: npx squawk@latest supabase/migrations/*.sql
        # 例：危険なロック取得、NOT NULL 追加時の全行書き換え、
        #     型変更などを警告/エラーにできる
```

`squawk` itself doesn't have RLS-specific rules, but it functions as a first barrier that **"stops destructive, high-lock DDL in CI."** Adding your own rule that **blocks `disable row level security` and unguarded `drop policy` as forbidden patterns via grep** physically prevents detaching RLS.

### 8.4 Guard in many layers with 11 checks

The match-recording app's CI guards in many layers with **11 GitHub Actions**—type checking, **pgTAP**, **RLS coverage**, **migration safety checks (squawk)**, schema-drift detection (detecting divergence between local migrations and the remote schema), and more. Each one is single-purpose (SRP), but combined they nearly block **the paths by which an authorization regression reaches production.**

> **Why many layers**: a single check always has a hole. pgTAP guards behavior, `policies_are` structure, RLS coverage "the absence of tests," squawk dangerous DDL, and drift detection "divergence between local and production." Betting on **"some one of multiple independent sensors going off"** is the playbook of reliability design (eliminating single points of failure).

---

## 9. A collection of practices that pay off in operation

Let me summarize the fine but important points that paid off repeatedly in real combat.

- **Cover the deny paths by "quadrant"**: mechanically fill in "self = allow / others = deny / unauthenticated = deny" per table × command. Easy to miss: `UPDATE`'s `WITH CHECK` (value rewrite) and `DELETE`.
- **Don't mix up "0 rows" and "exception"**: the deny of `SELECT`/`UPDATE`/`DELETE` is **0 rows**, and a `WITH CHECK` violation of `INSERT`/`UPDATE` is **an exception (42501).** Use the right assertion (`is_empty`/`results_eq` vs `throws_ok`).
- **`FORCE ROW LEVEL SECURITY` on all tables**: plug the bypass on owner connections. Since tests tend to run with owner privileges, without `FORCE` **even tests slip past RLS** and turn falsely green.
- **`SECURITY DEFINER` is minimal, `search_path=''`, fully-qualified, least-privileged.** And **always add deny tests.**
- **Audit logs are append-only**: make disclosure and permission changes `INSERT`-only policies, and forbid `UPDATE`/`DELETE`. Pin "can write the log / can't delete it" with pgTAP too.
- **Update tests in the same PR as the migration**: a rule making policy changes and test changes the **same commit.** RLS changes must always come with pgTAP—make this a **CI gate, not a culture.**
- **Be thorough with `begin/rollback`**: keep tests side-effect-free and idempotent. Use `set local` and forbid global `set`.

---

## 10. Summary: RLS testing cheat sheet

A quick reference for when you're unsure.

- **Before writing**: `enable row level security` + `force row level security`. Close first, then open only the necessary holes.
- **Policy design**: `USING` = rows you can read, `WITH CHECK` = rows you can write. `UPDATE` needs **both** + a corresponding `SELECT`. Wrap with `(select auth.uid())`.
- **Tests are 2 axes × 2 directions**: read/write × allow/deny. **80% of the value is the deny path.**
- **Impersonation**: `set local role` + `set local request.jwt.claims` (or `tests.authenticate_as()`). Wrap in `begin/rollback` for independence and idempotency.
- **Assertions**: allow = `lives_ok`/`results_eq`/`isnt_empty`, deny (read) = `is_empty`/"0 rows", deny (write) = `throws_ok(..., '42501', ...)`, structure = `policies_are`/`policy_roles_are`/`policy_cmd_is`, function = `is(...)`.
- **DEFINER functions**: ask whether you really need them → if so, `search_path=''` + fully-qualified + least-privileged + **a deny test returning false to an outsider.**
- **Stop in CI**: make `supabase test db` mandatory, detect the absence of tests with an **RLS-coverage gate**, stop dangerous DDL with **squawk**, and detect **schema drift.** Guard in many layers.

RLS looks like "authorization is complete in a few lines of policy," but in reality it's the work of building **"a mechanism that automatically re-verifies both allow and deny every time a regression occurs."** In a world where leaving in one line of `using (true)` leaks everything, **being correct when you changed it** matters far more than **being correct when you wrote it.** What guarantees that is not human review but **deny tests and CI gates.**

I develop solo with generative AI (Claude Code) as my partner, but **authorization is the one thing I never let through on "probably fine."** In the match-recording app, I verified all 69 RLS tables and 280 policies from both allow and deny sides with pgTAP, enforced RLS coverage and migration safety in CI, and pinned the holes of `SECURITY DEFINER` functions with deny tests too. **Guard zero-trust RLS with full-coverage pgTAP**—on this I have no hesitation.

**"Your Supabase—is its RLS really denying what should be denied?"** From that verification, through design review and building CI gates, I'll accompany you end-to-end. First, why not put your current policies through "deny tests" together? Feel free to reach out.

---

### References (official documentation)

- [Row Level Security (Supabase official)](https://supabase.com/docs/guides/database/postgres/row-level-security) — `enable row level security`, `CREATE POLICY` (USING/WITH CHECK), `auth.uid()`/`auth.jwt()`, `app_metadata` vs `user_metadata`, performance know-how
- [pgTAP (Supabase official)](https://supabase.com/docs/guides/database/extensions/pgtap) — enabling the extension, the `begin/plan/finish/rollback` pattern, `policies_are`, etc.
- [Advanced pgTAP Testing (Supabase official)](https://supabase.com/docs/guides/local-development/testing/pgtap-extended) — the `tests.create_supabase_user()` / `authenticate_as()` helpers, examples of `lives_ok`/`results_eq`/`throws_ok`
- [Row Security Policies (PostgreSQL official)](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) — USING/WITH CHECK semantics, PERMISSIVE/RESTRICTIVE combination (OR/AND), `FORCE ROW LEVEL SECURITY`, owner/superuser/`BYPASSRLS` bypass
- [pgTAP Documentation](https://pgtap.org/documentation.html) — the function reference for `plan`/`finish`/`ok`/`is`/`results_eq`/`throws_ok`/`lives_ok`/`is_empty`/`policies_are`/`policy_roles_are`/`policy_cmd_is`