SaaS向けPostgreSQLの行レベルセキュリティ — 実務で使えるポリシー

RLSがSaaSアプリで解こうとする本当の問題\n\nSaaSアプリで最も危険なセキュリティバグは、スケールした後に出てくるものです。最初は「ユーザーは自分のテナントのデータだけ見られる」という単純なルールで始めますが、新しいエンドポイントを急いで出すとき、レポート用のクエリを追加するとき、あるいはチェックを抜かす結合を導入するときに、そのルールが静かに飛ばされることがあります。\n\nアプリ側だけの認可はプレッシャーに弱く、ルールが散らばってしまうから壊れます。あるコントローラーはtenant_idをチェックし、別のコントローラーはメンバーシップを確認し、バックグラウンドジョブは忘れ、そして「admin export」経路が何ヶ月も「一時的」なまま残る。注意深いチームでも見落としは起きます。\n\nPostgreSQLの行レベルセキュリティ（RLS）は特定の問題を解決します：データベース側でどの行があるリクエストに対して見えるかを強制するのです。考え方は単純で、すべてのSELECT、UPDATE、DELETEがポリシーによって自動的にフィルタされます。これは認証ミドルウェアがリクエストをフィルタするのと同じイメージです。\n\n「行（rows）」という点が重要です。RLSはすべてを守るわけではありません：\n\n- 特定の列をそのまま隠すわけではありません（列レベルはビューや列権限で扱ってください）。\n- 危険な関数を安全にするわけではありません（権限が上がって実行される関数は依然としてデータを漏らす可能性があります）。\n- ビジネスルールの検証を置き換えるわけではありません（例：「オーナーだけが請求設定を変更できる」など）。\n\n具体例：ダッシュボード用にプロジェクトを一覧表示し、請求書と結合したエンドポイントを追加したとします。アプリ側だけの認可だとprojectsはテナントでフィルタしていてもinvoicesをフィルタし忘れたり、テナントを跨ぐキーで結合してしまったりが起こり得ます。RLSなら両方のテーブルでテナント分離を強制できるため、データが漏れるのではなく安全に失敗します。\n\nトレードオフは現実的です。繰り返しの認可コードを減らし、漏洩リスクのある箇所を減らせますが、新たな作業も増えます：ポリシーを慎重に設計し、早めにテストし、ポリシーが期待したクエリをブロックする可能性を受け入れる必要があります。\n\n## RLSが認可を簡単にする場合（と負担になる場合）\n\nRLSはアプリが数個のエンドポイントを超えるまでは余計な仕事に感じるかもしれません。もし厳格なテナント境界があり、クエリ経路が多数（一覧画面、検索、エクスポート、管理ツール）あるなら、ルールをデータベースに置くと、どこでも同じフィルタを入れ忘れる必要がなくなります。\n\nRLSはルールが退屈で普遍的な場合に向いています：「ユーザーは自分のテナントの行しか見られない」や「ユーザーは自分がメンバーのプロジェクトだけ見られる」といったケースです。そうした状況では、ポリシーを入れることで間違いが減ります。なぜなら後からクエリが追加されても、すべてのSELECT、UPDATE、DELETEが同じゲートを通るからです。\n\nまた読み取りが多く、フィルタロジックが一貫するアプリにも有利です。請求書の読み込みに15通りの方法（ステータス、日付、顧客別、検索など）があるなら、RLSでテナントフィルタを毎回再実装する手間を省けます。\n\n一方で、ルールが行ベースでない場合は負担が増えます。フィールドごとのルール（「給与は見られるがボーナスは見えない」や「HR以外はこの列をマスクする」など）は、SQLが不格好になり、例外管理が難しくなりがちです。\n\nまた広範なレポーティングで正当に広いアクセスが必要な場合も相性が悪いです。チームは「このジョブだけ」のために回避ロールを作りがちで、そこからミスが積み重なります。\n\n導入前に、データベースを最終的な守り手にするかどうかを決めてください。Yesなら、運用上の規律を計画しましょう：データベース挙動をテストする（APIレスポンスだけでなく）、マイグレーションをセキュリティ変更として扱う、安易な回避を避ける、バックグラウンドジョブの認証方法を決める、ポリシーを小さく再利用可能に保つなどです。\n\nバックエンドを自動生成するツールを使っている場合、提供は早まりますが明確なロール、テスト、シンプルなテナントモデルの必要は消えません（例えば Koder.ai は生成バックエンドに Go と PostgreSQL を使う例ですが、RLSは後で「ちょっと追加する」ではなく意図的に設計すべきです）。\n\n## RLSポリシーを管理しやすくするデータモデルの基本\n\nスキーマがすでに誰が何を所有しているかを明確に示していると、RLSは最も扱いやすくなります。あいまいなモデルからポリシーで「直そう」とすると、通常は遅いクエリや混乱したバグになります。\n\n### テナントキーは必要な場所にすべて置く\n\n一つのテナントキー（例: org_id）を選び、徹底して使ってください。多くのテナント所有テーブルはそれを持つべきで、たとえ別のテーブルを参照していても同様です。これによりポリシー内でのジョインを避け、USINGチェックが単純になります。\n\n実用的なルール：顧客が解約したらその行が消えるべきなら、おそらくorg_idが必要です。\n\n### メンバーシップを明示的にモデル化する\n\nRLSポリシーは通常「このユーザーはこのorgのメンバーか、そして何ができるか」を答えます。これはアドホックな列から推測するのは難しいです。\n\nコアテーブルは小さく単純に保ちましょう：\n\n- users（人ごとに1行）\n- orgs（テナントごとに1行）\n- org_memberships（user_id, org_id, role, status）\n- 必要ならプロジェクト単位のアクセス用にproject_memberships\n\nこうしておくと、ポリシーはインデックス付きの単一ルックアップでメンバーシップを確認できます。\n\n### 共有参照データとテナント所有データを分離する\n\nすべてのテーブルにorg_idが必要なわけではありません。国、製品カテゴリ、プラン種類などの参照テーブルは多くの場合テナント間で共有されます。これらはほとんどのロールで読み取り専用にして、特定のorgに結びつけないでください。\n\nテナント所有のデータ（プロジェクト、請求書、チケットなど）は、共有テーブルを通じてテナント固有の詳細を引っ張らないようにしましょう。共有テーブルは最小で安定したものにします。\n\n### 外部キー、カスケード、インデックス\n\n外部キーはRLSと一緒に動作しますが、削除は削除するロールが依存行を「見えない」場合に驚きを生むことがあります。カスケードを計画し、本番の削除フローをテストしてください。\n\nポリシーがフィルタする列、特にorg_idやメンバーシップキーにはインデックスを張ってください。WHERE org_id = ...のようなポリシーが、テーブルが数百万行になったときにフルテーブルスキャンになってはいけません。\n\n## 怖くないようにRLSの仕組みを説明すると\n\nRLSはテーブル単位のスイッチです。一度有効化すると、Postgresはアプリコードがテナントフィルタを覚えていることを信用しなくなります。すべてのSELECT、UPDATE、DELETEはポリシーでフィルタされ、すべてのINSERTとUPDATEはポリシーによって検証されます。\n\n最大の心的変化は：RLSをオンにすると、これまでデータを返していたクエリがエラーにならずゼロ行を返すようになることがある点です。これはPostgresのアクセス制御が働いているからです。\n\n### ポリシーが実際にすること\n\nポリシーはテーブルに付随する小さなルールで、2種類のチェックを使います：\n\n- USING は読み取りフィルターです。行がUSINGに合致しなければ、その行はSELECTでは見えませんし、UPDATEやDELETEのターゲットにもなりません。\n- WITH CHECK は書き込みゲートです。INSERTやUPDATEで新規や変更された行が許可されるかを決めます。\n\nSaaSの一般的なパターン：USINGで自分のテナントの行しか見られないようにし、WITH CHECKで他人のテナントに勝手に行を作れないようにします。\n\n### PERMISSIVEとRESTRICTIVEを一言で\n\n後からポリシーを追加するときに重要になります：\n\n- PERMISSIVE（デフォルト）：いずれかのポリシーが許可すれば行は許可されます。\n- RESTRICTIVE：制限ポリシーはすべて許可しなければ行は許可されません（パーミッシブな振る舞いに加えて）。\n\nテナント一致＋ロールチェック＋プロジェクトメンバーシップのようにルールを重ねるなら、RESTRICTIVEは意図を明確にする一方で、1つ条件を忘れると自分で締め出されやすくなります。\n\n### Postgresが誰が呼んでいるかをどう知るか\n\nRLSは信頼できる「呼び出し元」を必要とします。一般的な選択肢：\n\n- リクエストごとに設定されるセッション変数（例: app.user_id と app.tenant_id）。\n- API層でJWTクレームをセッション設定にマッピングする。\n- ロール切り替え（SET ROLE ...）をリクエストごとに行う方法（運用負荷が増える）。\n\n1つの方法を選び、すべての場所で適用してください。サービス間でアイデンティティソースを混ぜるのは混乱の元です。\n\n### デバッグしやすいポリシー名を付ける\n\nスキーマダンプやログが読みやすいように予測可能な命名規則を使いましょう。例えば{table}__{action}__{rule}（projects__select__tenant_matchのように）です。\n\n## ステップバイステップ：1つのテーブルにRLSを追加して動作を証明する\n\nRLSが初めてなら、まず1つのテーブルと小さな検証を始めてください。目標は完璧なカバレッジではなく、アプリのバグがあってもデータベースがクロステナントアクセスを拒否することを証明することです。\n\n### 練習用の小さなテーブル\n\n簡単なprojectsテーブルを想定します。まずtenant_idを書き込みを壊さずに追加します。\n\nsql\nALTER TABLE projects ADD COLUMN tenant_id uuid;\n\n-- Backfill existing rows (example: everyone belongs to a default tenant)\nUPDATE projects SET tenant_id = '11111111-1111-1111-1111-111111111111'::uuid\nWHERE tenant_id IS NULL;\n\nALTER TABLE projects ALTER COLUMN tenant_id SET NOT NULL;\n\n\n次に所有とアクセスを分けます。一般的なパターンは：テーブル所有者となるロール（app_owner）と、APIが使うロール（app_user）を分けることです。APIロールがテーブル所有者であってはいけません。所有者だとポリシーをバイパスできてしまいます。\n\nsql\nALTER TABLE projects OWNER TO app_owner;\nREVOKE ALL ON projects FROM PUBLIC;\nGRANT SELECT, INSERT, UPDATE, DELETE ON projects TO app_user;\n\n\n次にリクエストがPostgresにどのテナントを扱っているかをどう伝えるか決めます。簡単な方法の一つはリクエストスコープの設定です。アプリはトランザクションを開いた直後にそれを設定します。\n\nsql\n-- inside the same transaction as the request\nSELECT set_config('app.current_tenant', '22222222-2222-2222-2222-222222222222', true);\n\n\nRLSを有効にしてまず読み取りアクセスから始めます。\n\nsql\nALTER TABLE projects ENABLE ROW LEVEL SECURITY;\n\nCREATE POLICY projects_tenant_select\nON projects\nFOR SELECT\nTO app_user\nUSING (tenant_id = current_setting('app.current_tenant')::uuid);\n\n\n動作を確認するには、異なる2つのテナントで行数が変わることを確かめてください。\n\n### 書き込みルール（WITH CHECK）を追加する\n\n読み取りポリシーだけでは書き込みは守れません。WITH CHECKを追加して、挿入や更新で別テナントに行をなりすますことができないようにします。\n\nsql\nCREATE POLICY projects_tenant_write\nON projects\nFOR INSERT, UPDATE\nTO app_user\nWITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);\n\n\n動作（失敗を含む）を確かめる簡単な方法は、各マイグレーション後に再実行できる小さなSQLスクリプトを保管しておくことです：\n\n- BEGIN; SET LOCAL ROLE app_user;\n- SELECT set_config('app.current_tenant', '\u003ctenant A\u003e', true); SELECT count(*) FROM projects;\n- INSERT INTO projects(id, tenant_id, name) VALUES (gen_random_uuid(), '\u003ctenant B\u003e', 'bad');（失敗するはず）\n- UPDATE projects SET tenant_id = '\u003ctenant B\u003e' WHERE ...;（失敗するはず）\n- ROLLBACK;\n\nそのスクリプトを実行して毎回同じ結果が得られれば、他のテーブルにRLSを広げる前に信頼できるベースラインがあります。\n\n## SaaSアプリで再利用するポリシーパターン\n\n多くのチームが同じ認可チェックをあらゆるクエリで繰り返すことに疲れてRLSを導入します。良いニュースは、必要なポリシーの形は一貫していることが多い点です。\n\n### オーナー行とメンバーシップ行\n\n一部のテーブルは自然に一人のユーザーが所有します（ノート、APIトークン）。他はテナントに属し、アクセスはメンバーシップに依存します。これらは別のパターンとして扱ってください。\n\nオーナーのみのデータでは、ポリシーはcreated_by = app_user_id()のようにすることが多いです。テナントデータでは、ユーザーがorgのメンバーかどうかを確認するポリシーが一般的です。\n\nポリシーを読みやすく保つ実践的な方法は、小さなSQLヘルパーにアイデンティティを集中させて再利用することです：\n\nsql\n-- Example helpers\ncreate function app_user_id() returns uuid\nlanguage sql stable as $$\n select current_setting('app.user_id', true)::uuid\n$$;\n\ncreate function app_is_admin() returns boolean\nlanguage sql stable as $$\n select current_setting('app.is_admin', true) = 'true'\n$$;\n\n\n### 読み取りルールと書き込みルールを分ける\n\n読み取りは書き込みより広いことが多いです。例えば、組織のメンバーは誰でもSELECTでプロジェクトを見られるが、編集者だけがUPDATEでき、オーナーだけがDELETEできる、といった具合です。\n\n明示的に保ちましょう：SELECT用のポリシー1つ（メンバーシップ）、INSERT/UPDATE用のWITH CHECKポリシー1つ（役割）、DELETE用のさらに厳しいポリシー、のように分けます。\n\n### RLSを無効にせずに管理者オーバーライドする\n\n「管理者のためにRLSをオフにする」ことは避けてください。代わりにポリシー内にapp_is_admin()のような脱出口を設けると、安全に扱えます。これにより共有サービスロールに誤って全権限を与えることを防げます。\n\n### ソフトデリートやステータスフラグ\n\ndeleted_atやstatusを使う場合はそれをSELECTポリシーに組み込んでください（deleted_at is nullなど）。そうしないと、アプリが最終だと仮定していたフラグを操作して誰かが行を「復活」させられる可能性があります。\n\n### UPSERT：WITH CHECKに優しい形にする\n\nINSERT ... ON CONFLICT DO UPDATEは、書き込み後の行がWITH CHECKを満たす必要があります。ポリシーがcreated_by = app_user_id()を要求するなら、upsertのINSERT側でcreated_byを設定し、UPDATE側でそれを上書きしないようにしてください。\n\nバックエンドを生成するなら、これらのパターンを内部テンプレート化して新しいテーブルが安全なデフォルトで開始されるようにする価値があります。\n\n## デバッグで時間を無駄にする一般的なフットガン\n\nRLSは小さな詳細が原因で「Postgresがランダムにデータを隠す/見せる」ように見えてしまうことがあります。以下のミスが最も時間を浪費します。\n\n### サイレントなテナント漏洩を招くフットガン\n\n最初の罠は挿入・更新時のWITH CHECKの忘却です。USINGは見えるかどうかを制御するだけで、作成できる行を制御しません。WITH CHECKがないと、アプリのバグで間違ったテナントに行を書き込めてしまい、そのユーザーはその行を読み取れないため気づかないことがあります。\n\nもう一つの一般的な漏洩は「漏れる結合」です。projectsを正しくフィルタしていても、invoicesやnotes、filesが同じように保護されていないと結合で情報漏れが起きます。対策は厳格ですが単純です：テナントデータを露出させ得るすべてのテーブルに固有のポリシーが必要であり、ビューは一つのテーブルだけが安全だからといってそれに依存すべきではありません。\n\nよくある失敗パターン：\n\n- 読み取りポリシーはあるが、書き込みポリシーにWITH CHECKがない。\n- ポリシー条件が保護されていない別テーブルへのジョインを使っている。\n- アクセスをビューで強制しているが、基になるテーブルはまだ開いている。\n\n- 「アプリが常にtenant_idを設定する」と頼りすぎていて、あるバックグラウンドジョブが忘れる。\n\n- スーパーユーザーでテストしていて、本来の振る舞いを見ていない。\n\n### 挙動を混乱させるフットガン\n\n同じテーブルを参照するポリシー（直接あるいはビュー経由）は再帰的な驚きを生むことがあります。ポリシーが保護されたテーブルを読んでしまうビューを参照すると、エラー、遅いクエリ、あるいは決してマッチしないポリシーになることがあります。\n\nロール設定も混乱の元です。テーブル所有者や昇格したロールはRLSをバイパスできるため、テストは通るが実ユーザーは失敗する（またはその逆）といったことが起きます。常にアプリが使う低権限ロールでテストしてください。\n\nSECURITY DEFINER関数には注意を払いましょう。これらは関数所有者の権限で動くため、current_tenant_id()のような単純なヘルパーは問題ないことが多いですが、データを読む「便利」関数はRLSを無視してテナントを跨いで読めてしまうことがあります。\n\nまたSECURITY DEFINER関数の中では安全なsearch_pathを設定してください。設定しないと同名の別オブジェクトを拾ってしまい、ポリシーロジックがセッション状態によって静かに別のものを参照する可能性があります。\n\n## RLSをデバッグする：何が起きているかを実地で見る方法\n\nRLSのバグは多くの場合コンテキストの欠如であって「SQLが悪い」わけではありません。ポリシーは紙の上では正しくても、セッションロールが違ったり、ポリシーが頼るテナントやユーザーの値が設定されていなかったりすると失敗します。\n\n本番のレポートを再現する信頼できる方法は、同じセッションセットアップをローカルで用意し、同じクエリを実行することです。通常は次の通り：\n\n- SET ROLE app_user;（または実際のAPIロール）\n- SELECT set_config('app.tenant_id', 't_123', true); と SELECT set_config('app.user_id', 'u_456', true);\n- アプリが実行したのと同じSQL（パラメータ含む）を実行\n- Postgresが見ているものを確認：SELECT current_user, current_setting('app.tenant_id', true), current_setting('app.user_id', true);\n\nどのポリシーが適用されているかわからない場合は推測せずカタログを見てください。pg_policiesは各ポリシー、コマンド、USINGとWITH CHECKの式を表示します。これをpg_classと組み合わせて、そのテーブルでRLSが有効かどうか、バイパスされていないかを確認してください。\n\nパフォーマンスの問題は認可問題のように見えることがあります。メンバーシップテーブルをジョインしたり関数を呼ぶポリシーは、テーブルが大きくなると遅くなり得ます。再現したクエリでEXPLAIN (ANALYZE, BUFFERS)を使い、シーケンシャルスキャンや予期しないネストループ、遅く適用されるフィルタを探してください。(tenant_id, user_id)やメンバーシップテーブルのインデックス不足がよくある原因です。\n\nアプリ層でリクエストごとに3つの値（テナントID、ユーザーID、リクエストに使われるデータベースロール）をログに残すことも助けになります。これらが期待しているものと一致しないと、RLSは「間違った」動きをします。\n\nテスト用にはいくつかのシードテナントを置き、失敗を明示的にします。小さなテスト群に含めると良い項目：\n\n- 「テナントAのユーザーがテナントBを読めない」\n- 「メンバーシップのないユーザーはプロジェクトを見られない」\n\n- 「オーナーは更新できるがビューアはできない」\n\n- 「挿入はコンテキストと一致しないtenant_idをブロックする」\n\n- 「管理者オーバーライドは意図した場合にのみ機能する」\n\n## 本番前チェックリスト：RLSを本番に入れる前に\n\nRLSはシートベルトのように扱ってください。小さな見落としが「全員が全員のデータを見られる」か「すべてがゼロ行を返す」になり得ます。\n\n### データモデルとポリシー形状\n\nテーブル設計とポリシールールがテナントモデルと合致していることを確認してください。\n\n- すべてのテナント所有テーブルに明確なテナントキー（通常はtenant_id）があること。ない場合は理由を書き留めてください（例：グローバル参照テーブル）。\n- 「主要」テーブルだけでなく、すべてのテナント所有テーブルでRLSを有効にすること。絶対にバイパスしてはいけない経路があるなら、FORCE ROW LEVEL SECURITYを検討してください。\n- 読み取りと書き込みルールを分ける。読み取りはUSING、書き込みはWITH CHECKで挿入・更新が別テナントに行けないようにすること。\n- ポリシー述語はインデックスに優しい形に。ポリシーがtenant_idやメンバーシップジョインでフィルタするなら、対応するインデックスを追加してください。\n\n簡単なサニティシナリオ：テナントAのユーザーは自分の請求書を読み取れ、請求書を挿入できるが挿入はテナントAにしかできず、請求書のtenant_idを変更して別テナントに移すことはできない。\n\n### ロール、パフォーマンス、テスト\n\nRLSはアプリが使うロールの強さに依存します。\n\n- アプリがスーパーユーザー、テーブル所有者、またはbypassrls権限を持つロールで接続していないことを確認してください。\n- 本番に近いデータ量でいくつかの実クエリを実行し、クエリプランを確認してください。\n- クロステナントアクセスが失敗することを証明する自動化されたネガティブテストを追加してください。\n\n## 例：メンバーシップベースのアクセスを持つマルチテナントプロジェクトアプリ\n\n企業（org）がプロジェクトを持ち、プロジェクトがタスクを持つB2Bアプリを想像してください。ユーザーは複数のorgに属し、あるユーザーはあるプロジェクトのメンバーで別のプロジェクトのメンバーでないかもしれません。APIエンドポイントがフィルタを忘れてもデータベースがテナント分離を強制できるため、このケースはRLSに向きます。\n\nシンプルなモデルは：orgs, users, org_memberships (org_id, user_id, role), projects (id, org_id), project_memberships (project_id, user_id), tasks (id, project_id, org_id, ...)です。tasksにorg_idを置くのは意図的です。ポリシーを単純にし、結合時の驚きを減らします。\n\nクラシックな漏洩はタスクがproject_idしか持たず、アクセス判定をprojectsとのジョインで行っている場合に起きます。一つのミス（projectsで過度に緩いポリシー、条件が落ちる結合、コンテキストを変えるビューなど）で別のorgのタスクが露出します。\n\n安全なマイグレーション経路は本番トラフィックを壊さないようにします：\n\n- まずスキーマ変更を出す（例：tasksにorg_idを追加、メンバーシップテーブルを追加）。\n- tasks.org_idをprojects.org_idからバックフィルし、NOT NULLを付ける。\n- ポリシーを追加するが、ステージングでテストする間はRLSを無効に保つ。\n- RLSを有効にし、その後強制（force）し、初めて古いアプリ側のフィルタを取り除く。\n\nサポートアクセスはRLSを無効にすることでなく、狭いブレイクグラス用ロールで扱うのがベターです。通常のサポートアカウントからは分け、使用時に明示的に記録を残してください。\n\nポリシーがどのように漂白（drift）しないかをドキュメント化しておきましょう：どのセッション変数が設定されるべきか（user_id, org_id）、どのテーブルがorg_idを持つべきか、「メンバー」とは何か、間違ったorgで実行すると0行になるべきいくつかのSQL例など。\n\n## 次のステップ：安全にRLSを展開し、維持可能にする\n\nRLSは製品変更として扱うと運用しやすくなります。小さな塊で展開し、テストで動作を証明し、なぜ各ポリシーがあるのかを明確に記録しておきましょう。\n\nうまくいく展開プランの例：\n\n- 明確なテナント所有がある1つのテーブル（例：projects）から始め、ロックダウンする。\n- 所有・メンバー・アウトサイダーなど数ロールについて読み取りと書き込みで許可/拒否のテストを追加する。\n- 機能領域ごとにバッチで拡張し、単一セッションでデバッグできる範囲にする。\n- 展開中は許可エラーを監視し、リスクの低い時間帯にデプロイする。\n\n最初のテーブルが安定したら、ポリシー変更は慎重に行ってください。マイグレーションにポリシーレビューのステップを追加し、各変更に「誰が何にアクセスすべきか、その理由」短い説明を書き、対応するテストを更新することで、知らず知らずのうちに「ただ別のORを追加する」ことで穴が広がるのを防げます。\n\nスピード重視なら Koder.ai（koder.ai）のようなツールでGo + PostgreSQLの出発点を生成し、そこにRLSポリシーとテストを手作業で同じ規律で追加する、というワークフローは有効です。\n\n最後に、展開中は安全策を残してください。ポリシーマイグレーションの前にスナップショットを取り、ロールバック手順を繰り返して慣れておき、RLSを全システムで無効にするような方法ではない、限定的なブレイクグラス経路を用意しておきましょう。