友田 陽大
日本語English
Databases & RLS
Supabase
RLS
PostgreSQL
セキュリティ
テスト

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

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

Published
Reading time
27 min read
Author
友田 陽大
Chia sẻ
Mục lục

「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 をテストで守る」方法論の実装ガイドです。題材として、私が構築した複数人同時編集のリアルタイム試合記録アプリ（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×オフラインファースト設計で扱っています。設計はあちら、検証はこちら、という補完関係です。

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 は両方が要ります。

-- ❌ 危険：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）は読みだけ、それ以外は触れない、というルールです。

-- 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 です。

-- テーブルオーナーは既定で 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 を汚さないよう専用スキーマに作ることを勧めています。

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

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

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

-- 認証済みユーザー 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）：通るべきものが通る

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）：弾かれるべきものが弾かれる ← ここが本丸

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 配布のヘルパで読みやすくします。

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 専用アサーションがあります。

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 における古典的な権限昇格パターンです。

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

-- ✅ 安全な 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 で重点的にテストします。

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 を実行します。

# .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カバレッジ・ゲートです。

#!/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 マイグレーションの静的リンタで、危険なパターンを機械的に検出します。

  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ゲートの構築まで、一気通貫で伴走します。まずは現状のポリシーを一緒に「拒否テスト」にかけてみませんか。お気軽にご相談ください。

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

友田

友田 陽大

Người phát triển sản phẩm đoạt Giải thưởng Bộ trưởng (METI). Với TypeScript + Python + AWS, tôi một mình triển khai trọn gói SaaS, chuyển đổi số công nghiệp và AI tạo sinh (RAG) sẵn sàng cho production — từ phân tích yêu cầu đến hạ tầng và vận hành.

Hồ sơXem dự ánXnoteGitHub

Got a challenge?

From design to implementation and operations — solo × generative AI

Implementation like this article's, end to end from requirements to production. Start with a free 30-minute technical consult and tell me about your situation.

Get a free technical consultFree pre-launch security self-audit checklistXem dự án liên quan

Nhận cả dự án trọn gói lẫn vai trò cố vấn kỹ thuật. Hãy bắt đầu bằng buổi tư vấn miễn phí 30 phút.

最短ルート：カレンダーから直接予約

相談内容が固まっている方は、フォーム送信よりその場で日程を確定する方がスムーズです。下記から空き時間をお選びください。

  • 30分のオンライン無料相談
  • Google Meet / Zoom / Microsoft Teams
  • NDA 商談前締結可・無理な営業はいたしません
無料相談の空き枠を予約する

Bài viết liên quan

Xem tất cả bài trong “Databases & RLS”

Cũng đáng đọc