# Supabase RLS をテストで守る：pgTAP で『許可』と『拒否』の両方を検証し、CIで認可の退行を止める

> Supabase/PostgreSQLの行レベルセキュリティ(RLS)を本番で信頼するためのテスト戦略。pgTAPでrequest.jwt.claimsを切り替えて許可と拒否の両方を検証し、SECURITY DEFINERとsearch_pathの落とし穴、RLSカバレッジのCIゲート、マイグレーション安全性(squawk)までを実コードで解説します。RLSは書いて終わりではなく、テストして初めて本番で信頼できます。

- 公開日: 2026-06-24
- 著者: 友田 陽大
- タグ: Supabase, RLS, PostgreSQL, セキュリティ, テスト
- URL: https://tomodahinata.com/blog/supabase-rls-testing-pgtap-policy-regression-guide

## 要点

- RLS は書いて終わりではなく、許可と拒否の両方を pgTAP で自動テストし CI で退行を止めて初めて本番で信頼できる
- テストは読み/書き×許可/拒否の4象限で網羅し、価値の8割は拒否パスにある。緩める退行は拒否テストでしか落ちない
- なりすましは set local role＋request.jwt.claims（または tests.authenticate_as）で行い、begin/rollback で独立・冪等に保つ
- 拒否は SELECT/UPDATE/DELETE なら0件・0行、INSERT/UPDATE の WITH CHECK 違反なら例外（42501）と使い分けてアサートする
- SECURITY DEFINER 関数は RLS をバイパスし得る穴。search_path='' と完全修飾・最小権限で固め、部外者に false を返す拒否テストを付ける

---

「RLS を書いたので認可は安全です」——この一文を、私はそのまま信じません。

行レベルセキュリティ（Row-Level Security, RLS）は強力です。`auth.uid()` を使った数行のポリシーで、「自分の行しか読めない／書けない」をデータベース層に強制できます。アプリ側の `if (user.id !== row.ownerId)` のような出し分けと違い、**どのクライアントから来ようと、どんなSQLを投げようと、DBが最終防衛線として弾く**。これが RLS の価値です。

けれど現場で何度も見てきたのは、こういう事故です。

- `SELECT` のポリシーは書いたが、`UPDATE` の `WITH CHECK` を忘れ、**他人の行を自分のものに書き換えられた**。
- 「許可されるべきが許可される」のテストは書いたが、**「拒否されるべきが拒否される」のテストを書かなかった**ため、ポリシーをうっかり緩めた退行に誰も気づかなかった。
- 複雑な認可を `SECURITY DEFINER` 関数に逃がしたら、その関数が**RLS をバイパスして全行を返す穴**になっていた。

RLS の怖さは、**「ポリシーを足したつもりが、別の経路で漏れる」「拒否のテストを書かないと退行に気づけない」**ことに尽きます。だから私のスタンスは一貫しています——**RLS は書いて終わりではない。「許可されるべきが許可される」と「拒否されるべきが拒否される」の両方を自動テストし、CIで退行を止めて初めて、本番で信頼できる。**

この記事は、その「RLS をテストで守る」方法論の実装ガイドです。題材として、私が構築した[複数人同時編集のリアルタイム試合記録アプリ](/case-studies/realtime-sports-scoring-app)（Expo + Next.js + Supabase のモノレポ、選手・チーム管理者・スコアラー・スカウト・運営という複数ステークホルダーが同じデータを別の見え方で触る）の設計判断を交えます。このプロダクトでは**69の全公開テーブルでRLSを有効化し、280のポリシー**で立場・チーム・期限付き権限・項目別開示を表現しました。そしてそのすべてを **pgTAP** でテストし、CIで退行を止めています。

> **この記事のルール**：RLS・ポリシー・auth ヘルパの仕様は **Supabase 公式ドキュメント**、RLS のセマンティクス（USING / WITH CHECK / FORCE / ポリシー結合）は **PostgreSQL 公式ドキュメント**、アサーション関数は **pgTAP 公式リファレンス**（いずれも2026年6月時点）に基づきます。仕様は改定され得るため、本番投入前に必ず公式で最新を確認してください（記事末尾にURL）。コードは実運用前提で整えていますが、シークレットは環境変数前提です。**RLSはテストして初めて信頼できます。**

なお、本記事は「RLS をどう**テスト**して退行を止めるか」に集中します。**RLS をそもそもどう**設計するか**——信頼できないクライアントを前提に、オフラインファーストでデータを守る設計**——は、姉妹記事の[信頼できないクライアント前提のPostgres RLS×オフラインファースト設計](/blog/untrusted-client-postgres-rls-offline-first)で扱っています。設計はあちら、検証はこちら、という補完関係です。

---

## 0. 最初に頭に入れる：USING と WITH CHECK は別物

テストの話をする前に、ここを曖昧にしたままだと**テストの設計を間違えます**。RLS のポリシーには2つの「式」があり、役割がまったく違います。

| 句 | 何を制御するか | 効くコマンド |
| --- | --- | --- |
| `USING (...)` | **読める／触れる行**（可視性・既存行へのアクセス） | `SELECT` / `UPDATE` / `DELETE` |
| `WITH CHECK (...)` | **書ける行**（新しい行データが満たすべき制約） | `INSERT` / `UPDATE` |

PostgreSQL 公式の定義を正確に引くと、こうです。

- `USING` … which rows are **visible** for `SELECT`、そして `UPDATE`/`DELETE` で**アクセスできる**既存行を制御する。
- `WITH CHECK` … which rows can be **inserted or updated**（新しい行データが満たすべき制約）を制御する。**省略すると `USING` と同一**になる。

ここに最初の落とし穴があります。`UPDATE` は**両方**が要ります。

```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 );  -- どんな値に書き換えてよいか
```

そしてもう一つ、Supabase 公式が明記する重要事項：**`UPDATE` を行うには対応する `SELECT` ポリシーも必要**です。`UPDATE` は内部で対象行を可視にする必要があるためです。

この「USING と WITH CHECK の非対称性」こそ、**拒否テストを書かないと気づけない退行の温床**です。`using` だけのポリシーは「読みのテスト」だけだと緑になります。`with check` の穴は、**「他人の値に書き換えようとして失敗するべき」という拒否テスト**を書かない限り、永遠に検出されません。

---

## 1. メンタルモデル：RLS のテストは「2軸 × 2方向」

私が RLS テストを設計するときの枠組みはシンプルです。各テーブル・各コマンドについて、次の**4象限**を埋めます。

| | 許可（Allow：通るべき） | 拒否（Deny：弾かれるべき） |
| --- | --- | --- |
| **読み（SELECT）** | 自分の行が見える | 他人の行が見えない（0件 or 除外） |
| **書き（INSERT/UPDATE/DELETE）** | 自分の行を作れる・直せる | 他人の行を作れない・直せない（エラー） |

凡庸なテストは左半分（許可）しか書きません。**RLS テストの価値の8割は右半分（拒否）にあります。** なぜなら、ポリシーを緩める退行——`using (true)` をうっかり残す、条件を `OR` で広げすぎる、`with check` を消す——は、**拒否テストでしか落ちない**からです。

PostgreSQL の**デフォルト拒否（default-deny）**という性質も押さえておきます。`ENABLE ROW LEVEL SECURITY` した瞬間、ポリシーが1つもなければ**全行が不可視・変更不可**になります。Supabase の言葉では「publishable key 経由では、ポリシーを作るまでデータに一切アクセスできない」。つまり**閉じてから開ける**のが RLS の作法です。テストもこの前提に立ち、「開けたつもりの穴が、意図した範囲だけか」を検証します。

---

## 2. 土台：RLS を有効化し、ポリシーを書く（許可と拒否を意識して）

テスト対象となる最小のスキーマを置きます。試合記録アプリを単純化した `matches`（試合）テーブルで考えます。チーム管理者（`team_admin`）は自チームの試合を読み書きでき、スカウト（`scout`）は読みだけ、それ以外は触れない、というルールです。

```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'
  )
);
```

ここで Supabase 公式の**パフォーマンス作法**も実践しています。`auth.uid()` を**そのまま**書くと行ごとに評価されますが、`(select auth.uid())` と**サブクエリで包む**と初期化時に一度だけ評価され、公式の計測で94〜99%の改善が出ます。さらにポリシー列にインデックスを張る、`TO authenticated` でロールを明示して非該当ロールでの評価を省く、といった点も効きます。

そして**最も重要な防御**——`FORCE ROW LEVEL SECURITY` です。

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

PostgreSQL 公式が明記するとおり、**テーブルオーナーは既定で RLS をバイパス**します。さらに**スーパーユーザー**と **`BYPASSRLS` 属性を持つロール**は常にバイパスします。マイグレーションや一部の管理接続がオーナー権限で走ると、**RLS をすり抜けたまま「テストは通っているのに本番で漏れる」**事故が起きます。`FORCE` はその穴を塞ぎます。

> **DRY の落とし穴**：「読みと書きで同じ条件だから」と `for all` で1ポリシーにまとめたくなります。が、`SELECT` と `INSERT` では効く句（`USING` vs `WITH CHECK`）が違うため、`for all` は**意図がぼやけ、テストで象限を分離しにくくなります**。私は読み・作り・直し・消しを別ポリシーに分け、各々を独立にテストします（SRP：1ポリシー＝1つの認可判断）。

---

## 3. pgTAP を入れて、テストの骨格を作る

ここから本題です。pgTAP は PostgreSQL 上で動くテストフレームワークで、SQL でアサーションを書きます。Supabase ではローカル開発スタックに組み込まれており、`supabase test db` で実行できます。

まず拡張を有効化します。公式は **`public` を汚さないよう専用スキーマ**に作ることを勧めています。

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

pgTAP テストの**骨格**は常にこの形です。**`begin` … `rollback` で囲む**ことに意味があります——テスト中に作ったデータやロール切替がDBに残らず、各テストが独立・冪等になります。

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

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

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

`plan(N)` で**実行予定のテスト数を先に宣言**するのが pgTAP の流儀です。これにより「途中で例外が出て3本しか走らなかった」といった**尻切れ（premature failure）を検出**できます。`select * from finish()` が最終的な集計とTAP出力を行います。

`begin/rollback` で囲うのは、**テストの独立性**を保証する最重要パターンです。試合データを作り、ユーザーを切り替え、書き込みを試す——それらすべてが `rollback` で消えるので、テスト順序に依存せず、CIで何度回しても同じ結果になります（再現性・冪等性）。

---

## 4. 核心：`request.jwt.claims` で立場を切り替え、許可と拒否を両方アサートする

RLS テストの肝は、**「別のユーザーになりすまして」ポリシーを試す**ことです。Supabase は PostgRES 経由のリクエストで、`role`（`anon`/`authenticated`）と JWT のクレームを GUC（実行時設定パラメータ）として注入します。テストでは、これを**手で設定**することで任意のユーザーをシミュレートできます。

### 4.1 立場を切り替える低レベルAPI

```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 = '';
```

ポイントは2つです。

1. **`set local`** を使うこと。`local` を付けると**現在のトランザクション内だけ**有効になり、`rollback` で確実に元に戻ります。`set`（global）だと接続に残り、テスト間で汚染します。
2. **`auth.uid()` は `request.jwt.claims` の `sub` を読む**ヘルパなので、`claims` を差し替えれば「別人」になります。`app_metadata` に権限情報を入れている場合は、ここに `app_metadata` を含めればポリシーの `auth.jwt()` 参照も再現できます。

> なぜ `app_metadata` か：Supabase 公式は、JWT の `user_metadata`（=`raw_user_meta_data`）は**ユーザー自身が書き換えられる**ため認可に使うな、`app_metadata`（=`raw_app_meta_data`）は**ユーザーが書き換えられない**ので認可情報の置き場として適切、と明言しています。テストでもこの区別を再現します。

実プロジェクトでは、この生のクレーム設定を毎回書くのは面倒で間違いの元なので、`supabase/tests` で配布される**ヘルパ**（`tests.create_supabase_user()`、`tests.authenticate_as()`、`tests.authenticate_as_service_role()`、`tests.clear_authentication()`、`tests.get_supabase_uid()` など）を使うと宣言的になります。下では仕組みを見せるため、まず生のクレーム、続いてヘルパ版を示します。

### 4.2 許可パス（Allow）：通るべきものが通る

```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)` は**例外が出ないこと**、`results_eq(sql, expected, desc)` は**結果集合が完全一致**すること（内容も行順も）、`isnt_empty` / `is_empty` は**行があること／無いこと**を検証します。許可パスは「期待どおり通る」ことの確認です。ここまでは多くの人が書きます。

### 4.3 拒否パス（Deny）：弾かれるべきものが弾かれる ← ここが本丸

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

ここが**RLS テストの核心**です。注目点を整理します。

- `throws_ok(sql, '42501', msg, desc)` … `INSERT`/`UPDATE` が `WITH CHECK` に違反すると、PostgreSQL は **SQLSTATE `42501`（insufficient_privilege）**で `new row violates row-level security policy ...` を投げます。**「弾かれること」をエラーコードで正確にアサート**します。
- `SELECT`/`UPDATE`/`DELETE` の拒否は、**例外ではなく「対象行が見えない」=「0件・0行」**として現れます。だから他チーム更新は `throws_ok` ではなく**「0行更新」を `results_eq` で確認**します。これは見落としやすい——「エラーにならない＝OK」ではなく、**「0行であること」が正しい拒否**なのです。
- `anon` の `is_empty` … 未認証で全件が消えることを確認。**閉じてから開ける**設計の検証です。

> **拒否パスを書かないと何が起きるか**：誰かが `read matches of my team` ポリシーを `using (true)` に書き換えたとします。許可テスト（4.2）は**全部緑のまま**です（むしろ「より見える」ので通る）。落ちるのは 4.3 の `is_empty('他チームの試合は閲覧できない')` だけ。**拒否テストが、認可を緩める退行を捕まえる唯一のセンサー**です。

### 4.4 ヘルパで宣言的に書く

生クレームは仕組みの理解に有用ですが、本番のテストスイートでは Supabase 配布のヘルパで読みやすくします。

```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()` は内部で `role` と `request.jwt.claims`（正しい `sub`）を設定してくれるので、**「誰として」が一行で宣言**できます。読み手にとっての可読性が段違いです（ETC：立場を増やしても差分が局所化する）。

---

## 5. ポリシーの「メタ」をテストする：構造ドリフトを止める

行データの許可/拒否に加えて、**「ポリシーがそもそも存在し、想定どおりのロール・コマンドに効いているか」**という構造レベルのテストも有効です。pgTAP には RLS 専用アサーションがあります。

```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)` … テーブルの **RLS ポリシー集合が期待集合と完全一致**することを検証。**ポリシーを消した／名前を変えた退行**を即座に落とします。
- `policy_roles_are(...)` … ポリシーが**効くロール**を固定。`TO authenticated` を誤って `TO anon` に広げた退行を捕捉。
- `policy_cmd_is(...)` … ポリシーが**効くコマンド**（`SELECT`/`INSERT`/...）を固定。

これらは「行の許可/拒否」テストと**補完関係**です。前者は振る舞いを、後者は**構造のドリフト**を守ります。280ポリシー規模になると、`policies_are` による「集合の固定」が、リファクタ時の安心材料として効いてきます。

---

## 6. 最大の落とし穴：`SECURITY DEFINER` 関数は RLS をバイパスし得る

複雑な認可（多段のチーム階層、期限付き権限、項目別開示）をポリシー式に直書きすると、式が肥大化し可読性が死にます。そこで**ヘルパ関数**に逃がすのは正しい判断です——が、ここに**RLS 最大の地雷**があります。

`SECURITY DEFINER` 関数は**定義者（多くはオーナー）の権限**で実行されます。PostgreSQL 公式の言うとおり、**式は実行ユーザーの権限で評価される（security-definer 関数を使わない限り）**。裏を返せば、`SECURITY DEFINER` 関数の中では**呼び出し元ではなくオーナーの権限**になり、オーナーは既定で**RLS をバイパス**します。つまり、

**無防備な `SECURITY DEFINER` 関数は、RLS を素通りして全行を返す穴になり得ます。**

さらに悪名高いのが **`search_path` ハイジャック**です。関数が `search_path` を固定していないと、攻撃者が同名のテーブルや関数を別スキーマに作り、`SECURITY DEFINER` の高権限で**意図しない対象を操作させる**ことができます。これは PostgreSQL における古典的な権限昇格パターンです。

対策は**定石が決まっています**。

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

ルールはこうです。

1. **`set search_path = ''`** で `search_path` を**空に固定**する。`public,pg_temp` ですら油断材料なので、関数内のオブジェクトは**すべて完全修飾**（`public.team_members`、`auth.uid()`）で書く。
2. **本当に DEFINER が要るかを問う**。RLS をバイパスしたい正当な理由（再帰ポリシーの回避、ヘルパテーブル参照）がある場合だけ使う。要らないなら `SECURITY INVOKER`（既定）にして RLS を効かせる（YAGNI / 最小権限）。
3. **`revoke ... from public` + `grant execute` を必要ロールに限定**する。誰でも呼べる高権限関数を作らない。
4. **関数入口で `auth.uid()` とロール、必要なら MFA を検証**する。試合記録アプリの RPC は `SECURITY DEFINER` ＋ `search_path` 固定の定石で書き、入口で `auth.uid()`・ロール・`require_mfa()` を必ずチェックしています。

そして——**`SECURITY DEFINER` 関数こそ pgTAP で重点的にテストします**。

```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)` は **`IS NOT DISTINCT FROM` で2値を比較**します。ここで**部外者に `false` が返ること**を固定するのが要点です。もし誰かが関数内のクエリを壊して RLS バイパスの穴を広げたら、この `is(..., false, ...)` が落ちます。**DEFINER 関数の拒否テストは、最も価値が高い拒否テスト**です。

---

## 7. 認可をどこで強制するか：アプリ / RPC / RLS の役割分担

「RLS だけで全部やるべきか」という問いには、はっきり立場があります。**最終防衛線は必ず RLS。ただしUXとパフォーマンスのためにアプリ側にも出し分けは置く。両者は冗長であるべきで、矛盾してはならない。**

| 強制ポイント | 役割 | 信頼度 | テスト手段 |
| --- | --- | --- | --- |
| **クライアント（Expo/Next）** | UX：見せない・押させない | **信頼しない**（迂回可能） | UIテスト（補助的） |
| **RPC（SECURITY DEFINER）** | 複雑な認可・MFAゲート・監査 | 中（実装に依存） | pgTAP（入口検証・拒否） |
| **RLS ポリシー** | **最終防衛線**：DB層の行制御 | **高**（迂回不能） | **pgTAP（許可＋拒否）** |

クライアントの出し分けは**信頼の対象ではありません**。「スカウトには更新ボタンを出さない」のは親切ですが、**ボタンが無くても API は叩けます**。だから RLS が無ければ意味がない。逆に RLS があれば、クライアントが何をしようと DB が弾く。**信頼の重心は下（DB）に置き、上（UI）は利便性のため**——この原則を守ると、テストの優先順位も自ずと決まります（**pgTAP の拒否テストが最優先**）。

試合記録アプリでは、機微操作（権限変更、選手情報の開示）を**メールOTPのMFAでゲート**しています。`custom_access_token_hook` が JWT に `mfa_verified` を注入し、RPC 入口の `require_mfa()` がそれを強制。スカウトへの選手情報開示は「**選手本人の承認 ∩ チーム管理者の承認**」の項目別グラント＋**追記専用の監査ログ**で表現しています。これらも当然、pgTAP で「MFA 未検証なら拒否」「片方の承認だけでは開示されない」という**拒否パス**を固定しています。

---

## 8. CIで退行を止める：RLSカバレッジ・マイグレーション安全性のゲート

テストは「ローカルで走る」だけでは退行を止められません。**CIで強制し、緑でなければマージできない**状態にして初めて防壁になります。

### 8.1 pgTAP を CI で回す

GitHub Actions で Supabase ローカルスタックを立て、`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` は `supabase/tests/` 配下の SQL を pgTAP として実行し、**1本でも落ちれば非ゼロ終了**します。これで PR ごとに許可・拒否の双方が検証されます。

### 8.2 RLSカバレッジを「ゲート」にする

ここが私の核心的な主張です。**「RLS を有効化した全テーブルに、ポリシーのテストが存在すること」をCIで強制**します。テストの**有無**を機械検査するのです。新しいテーブルを `enable row level security` したのにテストを書き忘れたら、CIが落ちる——これが**RLSカバレッジ・ゲート**です。

```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有効な全テーブルにテストが存在します"
```

これは粗いヒューリスティック（表名の grep）ですが、**「RLSを有効化したのにテストゼロ」という最悪パターン**を確実に止めます。`pg_class.relrowsecurity` が真のテーブルを真実の源とするので、テーブルを増やせば自動で対象が増えます（ETC：手で台帳を更新しなくてよい）。試合記録アプリでは、この発想で**69テーブル全RLSにテストが付いていること**をCIが保証します。

### 8.3 マイグレーション安全性：`squawk` で危険なDDLを止める

RLS の退行は、ポリシーだけでなく**マイグレーションの書き方**でも起きます。`drop policy`、`alter table ... disable row level security`、ロック時間の長いDDL——これらをレビューだけに頼ると、いつか漏れます。**`squawk`** は PostgreSQL マイグレーションの静的リンタで、危険なパターンを機械的に検出します。

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

`squawk` 単体は RLS 専用ルールを持つわけではありませんが、**「破壊的・高ロックなDDLをCIで止める」**第一の防壁として機能します。加えて、`disable row level security` や無防備な `drop policy` を**禁止パターンとして grep でブロックする**自前ルールを足すと、RLS の取り外しを物理的に防げます。

### 8.4 11本のチェックで多層に守る

試合記録アプリのCIは、**11本のGitHub Actions**で多層に守っています——型チェック、**pgTAP**、**RLSカバレッジ**、**マイグレーション安全性検査（squawk）**、スキーマdrift検出（ローカルマイグレーションとリモートスキーマの乖離検知）など。1本1本は単機能（SRP）ですが、合わさると「**認可の退行が本番に出る経路**」をほぼ塞ぎます。

> **なぜ多層か**：単一のチェックは必ず穴を持ちます。pgTAP は振る舞いを、`policies_are` は構造を、RLSカバレッジは「テストの不在」を、squawk は危険DDLを、drift検出は「ローカルと本番の乖離」を守る。**独立した複数のセンサーの「どれかが鳴る」**ことに賭けるのが、信頼性設計の定石です（単一障害点の排除）。

---

## 9. 運用で効くプラクティス集

実戦で繰り返し効いた、細かいが重要な点をまとめます。

- **拒否パスを「象限」で網羅する**：テーブル×コマンドごとに「自分＝許可／他人＝拒否／未認証＝拒否」を機械的に埋める。抜けやすいのは `UPDATE` の `WITH CHECK`（値の書き換え）と `DELETE`。
- **「0件」と「例外」を取り違えない**：`SELECT`/`UPDATE`/`DELETE` の拒否は**0件・0行**、`INSERT`/`UPDATE` の `WITH CHECK` 違反は**例外（42501）**。アサーション（`is_empty`/`results_eq` vs `throws_ok`）を使い分ける。
- **`FORCE ROW LEVEL SECURITY` を全テーブルに**：オーナー接続でのバイパスを塞ぐ。テストはオーナー権限で走りがちなので、`FORCE` が無いと**テストすら RLS を素通り**して偽の緑になる。
- **`SECURITY DEFINER` は最小限・`search_path=''`・完全修飾・最小権限**。そして**拒否テストを必ず付ける**。
- **監査ログは追記専用**：開示・権限変更は `INSERT` のみ許可するポリシーにし、`UPDATE`/`DELETE` を禁止。「ログを書ける／消せない」も pgTAP で固定する。
- **マイグレーションと同じPRでテストを更新**：ポリシー変更とテスト変更を**同一コミット**にするルール。RLSの変更には必ず pgTAP を伴わせる——これを文化ではなく**CIゲート**にする。
- **`begin/rollback` を徹底**：テストは副作用ゼロ・冪等に。`set local` を使い、グローバル `set` を禁止する。

---

## 10. まとめ：RLSテスト・チートシート

迷ったときの早見表です。

- **書く前に**：`enable row level security` ＋ `force row level security`。閉じてから、必要な穴だけ開ける。
- **ポリシー設計**：`USING`＝読める行、`WITH CHECK`＝書ける行。`UPDATE` は**両方**＋対応する `SELECT` も必要。`(select auth.uid())` で包む。
- **テストは2軸×2方向**：読み/書き × 許可/拒否。**価値の8割は拒否パス**。
- **なりすまし**：`set local role` ＋ `set local request.jwt.claims`（または `tests.authenticate_as()`）。`begin/rollback` で囲って独立・冪等に。
- **アサーション**：許可＝`lives_ok`/`results_eq`/`isnt_empty`、拒否（読み）＝`is_empty`/「0行」、拒否（書き）＝`throws_ok(..., '42501', ...)`、構造＝`policies_are`/`policy_roles_are`/`policy_cmd_is`、関数＝`is(...)`。
- **DEFINER関数**：本当に要るか問う→要るなら `search_path=''`＋完全修飾＋最小権限＋**部外者に false を返す拒否テスト**。
- **CIで止める**：`supabase test db` を必須化、**RLSカバレッジ・ゲート**でテストの不在を検出、**squawk** で危険DDLを止め、**スキーマdrift**を検知。多層で守る。

RLS は「数行のポリシーで認可が完成する」ように見えて、実際は**「許可と拒否の両方を、退行が起きるたびに自動で再検証し続ける」仕組み**を作る仕事です。`using (true)` を一行残すだけで全件が漏れる世界では、**書いたときに正しい**ことより、**変更しても正しいままである**ことの方がはるかに重要です。それを保証するのは、人間のレビューではなく、**拒否テストとCIゲート**です。

私は一人で、生成AI（Claude Code）を相棒に開発しますが、**認可だけは絶対に「たぶん大丈夫」で通しません**。試合記録アプリでは、69テーブル全RLS・280ポリシーを pgTAP で許可・拒否の両面から検証し、RLSカバレッジとマイグレーション安全性をCIで強制し、`SECURITY DEFINER` 関数の穴も拒否テストで固定しています。**ゼロトラスト前提の RLS を、フルカバレッジの pgTAP で守る**——ここに迷いはありません。

**「自社のSupabase、RLSは本当に拒否されるべきを拒否できていますか？」** その検証から、設計の見直し、CIゲートの構築まで、一気通貫で伴走します。まずは現状のポリシーを一緒に「拒否テスト」にかけてみませんか。お気軽にご相談ください。

---

### 参考（公式ドキュメント）

- [Row Level Security（Supabase 公式）](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`、パフォーマンス作法
- [pgTAP（Supabase 公式）](https://supabase.com/docs/guides/database/extensions/pgtap) — 拡張の有効化、`begin/plan/finish/rollback` パターン、`policies_are` 等
- [Advanced pgTAP Testing（Supabase 公式）](https://supabase.com/docs/guides/local-development/testing/pgtap-extended) — `tests.create_supabase_user()` / `authenticate_as()` ヘルパ、`lives_ok`/`results_eq`/`throws_ok` の実例
- [Row Security Policies（PostgreSQL 公式）](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) — USING/WITH CHECK のセマンティクス、PERMISSIVE/RESTRICTIVE の結合（OR/AND）、`FORCE ROW LEVEL SECURITY`、オーナー/スーパーユーザー/`BYPASSRLS` のバイパス
- [pgTAP Documentation](https://pgtap.org/documentation.html) — `plan`/`finish`/`ok`/`is`/`results_eq`/`throws_ok`/`lives_ok`/`is_empty`/`policies_are`/`policy_roles_are`/`policy_cmd_is` の関数リファレンス