--- note: 最小権限・行レベルセキュリティ(RLS)・監査。アプリのsuperuser接続からの卒業 created: 2026-08-14T10:00:00+09:00 --- # 第10回｜権限・ロール・RLS・監査 > 「誰が接続でき、何をしてよく、どの行が見えるか」を分けて設計する。アプリが superuser で繋いでいる現場を卒業する回。 ## この回のねらい 多くの現場で、アプリケーションはデータベースに superuser（何でもできる最上位ロール）で接続している。 これは「全部の鍵を1本にまとめて玄関に挿しっぱなし」の状態で、SQL インジェクション（第11回）や 設定ミス1つが即座に全データの流出・破壊につながる。今回はこの状態を卒業する。PostgreSQL の 権限モデルは3層に分かれる――**接続（誰が繋げるか）**、**権限（何をしてよいか）**、 **行レベルセキュリティ（どの行が見えるか）**。この3層を分けて設計し、最小権限のロールを作り、 行レベルセキュリティで「自分のデータしか見えない」を実現し、監査ログで「誰が何をしたか」を 後から追える状態を作る。 ## 到達目標 - 「PostgreSQL にユーザーという独立概念はなく、すべてロールである」ことを説明できる。 - グループロールとログインロールを分け、`GRANT` / `REVOKE` で読み取り専用ロールを作れる。 - `GRANT SELECT ON ALL TABLES` と `ALTER DEFAULT PRIVILEGES` の役割の違いを説明でき、 **スキーマの `USAGE` を忘れると `SELECT` すら通らない**理由を説明できる。 - `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` と `CREATE POLICY` で「自ロールの顧客の注文だけ見える」を実現できる。 - ポリシーの `USING`（読取・更新前のフィルタ）と `WITH CHECK`（書込後の検証）の違いを説明できる。 - **テーブル所有者・superuser・`BYPASSRLS` 属性は RLS を素通りする**ことと、`FORCE ROW LEVEL SECURITY` の効果を説明できる。 - 監査を `log_statement` / `log_connections` と pgaudit で有効化し、`pg_hba.conf` の認証方式を概観できる。 ## 前提と準備 第1回で構築した PostgreSQL 16 系のデータベース（`postshop`）に共通スキーマとデータが投入済みで あることを前提にする。今回の操作の大半は **superuser（例: `postgres`）で実行する**――ロールを 作り権限を配る作業そのものが管理者の仕事だからである。作ったロールの見え方を確かめるときは、 実際に接続し直す代わりに `SET ROLE` を使う。`SET ROLE` は「そのロールでログインしたのと同じ 権限状態」にセッションを切り替えるコマンドで、**非 superuser のロールに `SET ROLE` すると、 その間は superuser 権限も失う**。これを使えば1つの psql セッションの中で権限差を検証できる。 ```bash psql -d postshop -U postgres ``` ```sql SELECT current_user, session_user; -- いま誰として振る舞っているか ``` ```text current_user | session_user --------------+-------------- postgres | postgres (1 row) ``` pgaudit を試す小節では拡張のインストールと設定ファイルの編集が必要になるが、無い環境でも コア機能（`log_statement` 等）だけで監査の考え方は追えるように書く。 ## この回で新しく出てくる用語 権限の3層（認証・権限・行レベル）を分けて読むための語をここで揃えておく。 回をまたいで使う語は[用語集](glossary.md)にもまとめている。 | 用語 | 一行での説明 | |---|---| | ロール | PostgreSQLにおける「ユーザー」と「グループ」を兼ねる唯一の主体オブジェクト | | 権限（privilege） | どのオブジェクトに何の操作をしてよいかを表す、オブジェクト単位の許可 | | 認証 / 認可 | 誰が接続できるかの判定と、接続後に何をしてよいかの判定。別の層である | | DDL（Data Definition Language） | テーブルや制約など、データの入れ物の定義を変更するSQLの総称 | | DML（Data Manipulation Language） | 行そのものを読み書きするSQLの総称（`SELECT`/`INSERT`/`UPDATE`/`DELETE`） | | RLS（行レベルセキュリティ） | 同じ表・同じSQLでも、ロールによって見える行を変える仕組み | | ポリシー | RLSで「どの行が見えるか」を定める条件。`CREATE POLICY` で作る | | マルチテナント | 1つの表に複数の顧客のデータが同居する構成 | | 監査 | 誰がいつどの操作をしたかを、後から追跡できる形で記録し残すこと | | `SET ROLE` | そのロールでログインしたのと同じ権限状態にセッションを切り替えるコマンド | ## 本編 ### ロールとユーザー ―― 区別は存在しない PostgreSQL には「ユーザー」という独立した概念はない。あるのは**ロール（role）**だけである。 `CREATE USER foo` は `CREATE ROLE foo LOGIN` の別名にすぎない。ロールに **`LOGIN` 属性**が 付いていれば接続に使える（=いわゆるユーザー）、付いていなければ接続できない（=いわゆる グループ）。両者は同じ「ロール」というオブジェクトの属性違いである。 ロールは他のロールの**メンバー**になれる。メンバーは、既定の `INHERIT` 属性により、所属先の ロールが持つ権限を自動的に使える。これを使って「権限の束（グループロール）」と「接続する主体 （ログインロール）」を分離するのが定石である。 主なロール属性を挙げる。 | 属性 | 意味 | 危険度 | |---|---|---| | `LOGIN` | 接続に使える（=ユーザー相当） | 低 | | `SUPERUSER` | 全権限。権限チェックも RLS もすべて無視 | **最高** | | `CREATEDB` | データベースを作れる | 中 | | `CREATEROLE` | ロールを作れる／権限を配れる | 高 | | `BYPASSRLS` | 行レベルセキュリティを常に素通りする | 高 | | `REPLICATION` | レプリケーション接続ができる | 高 | | `INHERIT` | 所属ロールの権限を自動継承する（既定 ON） | ― | アプリが接続するロールに `SUPERUSER` を与えてはならない。これが今回の一番の主張である。 superuser は後述する `GRANT` も RLS もすべて無視するため、どれだけ丁寧に権限設計をしても 接続ロールが superuser なら全部が無意味になる。 ### GRANT / REVOKE と最小権限 ―― 読み取り専用ロールを作る 権限（privilege）はオブジェクトごとに与える。テーブルに対する権限は `SELECT` / `INSERT` / `UPDATE` / `DELETE` / `TRUNCATE` / `REFERENCES` / `TRIGGER`、スキーマに対する権限は `USAGE` （中のオブジェクトにアクセスしてよい）と `CREATE`（中に新しいオブジェクトを作ってよい）である。 読み取り専用ロールを最小権限で作る手順は次のとおり。**グループロール**に権限を束ね、そこへ **ログインロール**を所属させる。 ```sql -- 管理者(postgres)として実行する -- 1) 権限の器となるグループロール（接続はできない） CREATE ROLE readonly NOLOGIN; -- 2) スキーマそのものへのアクセス権（忘れやすい。これが無いと表に触れない） GRANT USAGE ON SCHEMA public TO readonly; -- 3) いま public にある全テーブルへの SELECT GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; -- 4) 今後 public に作られるテーブルにも自動で SELECT を付ける ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; -- 5) 実際に接続するログインロール（readonly の権限を継承する） CREATE ROLE app_read LOGIN PASSWORD 'change_me'; GRANT readonly TO app_read; ``` 3 と 4 の違いが要点である。`GRANT SELECT ON ALL TABLES` は**いま存在する**テーブルにしか効か ない。マイグレーションで後からテーブルを足すと、そのテーブルには権限が付かず「新しい表だけ 読めない」という事故になる。`ALTER DEFAULT PRIVILEGES` は「**これから作られる**テーブルには 自動でこの権限を付けておけ」という予約で、これを併用して初めて将来にわたる読み取り専用が完成する。 権限差を確かめる。`SET ROLE` で `app_read` になり、読めるが書けないことを見る。 ```sql SET ROLE app_read; SELECT count(*) FROM orders; -- 読み取りは通る ``` ```text count --------- 1000000 (1 row) ``` ```sql -- 書き込みは拒否される INSERT INTO orders (customer_id, status, ordered_at) VALUES (1, 'pending', now()); ``` ```text ERROR: permission denied for table orders ``` ```sql RESET ROLE; -- 管理者に戻る ``` `app_read` は `readonly`（=`SELECT` のみ）を継承しているだけなので、`INSERT` は `permission denied for table orders` で弾かれる。これが最小権限である。実務では役割ごとに ロールを分ける――**マイグレーション用ロール**（DDL を打つ管理者的ロール）と、**アプリ実行時 ロール**（該当テーブルへの `SELECT`/`INSERT`/`UPDATE`/`DELETE` だけを持つ非特権ロール）を 別にする。アプリの通常運転で `DROP TABLE` や `CREATE ROLE` ができる必要はない。 ### つまずきの実演 ―― スキーマの USAGE を忘れる 手順 2 の `GRANT USAGE ON SCHEMA public` を省くとどうなるか。ここで PostgreSQL 15 以降の既定を 押さえておく必要がある。`public` スキーマは既定で**全ロール（`PUBLIC`）に `USAGE` を与えている** （一方 `CREATE` は 15 以降は既定で剥がされている）。つまり素の状態では `USAGE` を付け忘れても たまたま通ってしまい、落とし穴が見えない。最小権限の第一歩として、まずこの緩い既定を締める。 ```sql -- public の既定権限を剥がし「原則拒否」にする（CREATE も USAGE も PUBLIC から外す） REVOKE ALL ON SCHEMA public FROM PUBLIC; ``` この状態で「`SELECT` 権だけ渡して `USAGE` を渡し忘れた」ロールを作り、実際に触ってみる。 ```sql CREATE ROLE probe LOGIN; GRANT SELECT ON ALL TABLES IN SCHEMA public TO probe; -- USAGE はわざと付けない SET ROLE probe; SELECT count(*) FROM orders; RESET ROLE; ``` ```text ERROR: permission denied for schema public ``` 表への `SELECT` は持っているのに、**スキーマに入る権利（`USAGE`）が無い**ため、そもそも表に 到達できない。エラーが `permission denied for table`（表の権限不足）ではなく `permission denied for schema`（スキーマの権限不足）である点を読み分けられると、原因の切り分けが 速くなる。`USAGE` を足せば通る。 ```sql GRANT USAGE ON SCHEMA public TO probe; -- これで SELECT が通るようになる ``` もう1つの伝播の落とし穴が `ALTER DEFAULT PRIVILEGES` の**適用対象**である。デフォルト権限は 「**それを実行したロール（または `FOR ROLE` で指定したロール）が作成する**オブジェクト」にしか 効かない。テーブルを作るのが `migrator` ロールなのに、`postgres` として `ALTER DEFAULT PRIVILEGES` を実行しても、`migrator` が作る新テーブルには権限が付かない。作成者を 明示するのが正解である。 ```sql -- migrator が今後作るテーブルに、自動で readonly の SELECT を付ける ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public GRANT SELECT ON TABLES TO readonly; ``` ### 3層の権限モデル ここまでを一枚に整理する。クライアントの1回のアクセスは、次の3つの関門を順に通る。 ```text クライアント接続 ↓ [1] 認証（pg_hba.conf）: 誰が / どこから / どう繋げるか ↓ [2] 権限（ロール + GRANT）: どの表に何をしてよいか ↓ [3] 行レベル（RLS ポリシー）: どの行が見えるか ↓ データ ``` | 層 | 決めること | 設定する場所 | 今回の該当節 | |---|---|---|---| | 認証（authentication） | 誰が、どこから、どの方式で接続できるか | `pg_hba.conf` | 認証の概観 | | 権限（authorization） | どのテーブルに対し何の操作をしてよいか | ロール属性・`GRANT`/`REVOKE` | GRANT/REVOKE | | 行レベル（row security） | 通った操作のうち、どの**行**が見えるか | `CREATE POLICY` | 行レベルセキュリティ | `GRANT SELECT ON orders` は「orders 表を読んでよい」という**表単位**の許可であり、 「どの行が見えるか」は決めない。行を絞るのは次の RLS の仕事である。両者は別レイヤーで、 **両方**が必要になる。 ### 行レベルセキュリティ（RLS） RLS は「同じ表・同じ `SELECT` でも、接続しているロールによって見える行を変える」仕組みである。 マルチテナント（1つの表に複数顧客のデータが同居する）で「自分のデータしか見えない」を DB 側で 強制するのに使う。 仕組みは2段階。まず表で RLS を**有効化**し、次に見える条件を**ポリシー**として書く。 ```sql -- テナント(顧客)とロールの対応表を用意する CREATE TABLE customer_role_map ( role_name text NOT NULL PRIMARY KEY, customer_id bigint NOT NULL REFERENCES customers(id) ); INSERT INTO customer_role_map (role_name, customer_id) VALUES ('cust_100', 100), ('cust_200', 200); -- 各テナントのログインロールと、必要な表アクセス権 CREATE ROLE cust_100 LOGIN; CREATE ROLE cust_200 LOGIN; GRANT USAGE ON SCHEMA public TO cust_100, cust_200; GRANT SELECT ON orders TO cust_100, cust_200; GRANT SELECT ON customer_role_map TO cust_100, cust_200; -- ポリシー内の副問い合わせが読む ``` RLS を有効化してポリシーを張る。ここでは「自ロールに対応する顧客の注文だけ」を条件にする。 条件には現在のロール名を返す `current_user` を使い、対応表で顧客 ID に引き当てる。 ```sql ALTER TABLE orders ENABLE ROW LEVEL SECURITY; CREATE POLICY orders_own_customer ON orders FOR ALL USING ( customer_id = (SELECT customer_id FROM customer_role_map WHERE role_name = current_user) ) WITH CHECK ( customer_id = (SELECT customer_id FROM customer_role_map WHERE role_name = current_user) ); ``` 有効化した瞬間の既定は「**ポリシーに一致しない行は見えない（原則拒否）**」である。ポリシーを 1つも書かずに `ENABLE` すると、対象ロールにはその表が空に見える。 別ロールで検証する。 ```sql SET ROLE cust_100; SELECT id, customer_id, status FROM orders ORDER BY id LIMIT 3; RESET ROLE; ``` ```text id | customer_id | status -------+-------------+--------- 512 | 100 | paid 1893 | 100 | shipped 4021 | 100 | delivered (3 rows) ``` ```sql SET ROLE cust_200; SELECT id, customer_id, status FROM orders ORDER BY id LIMIT 3; RESET ROLE; ``` ```text id | customer_id | status -------+-------------+--------- 233 | 200 | pending 977 | 200 | paid 3110 | 200 | cancelled (3 rows) ``` 同じ `SELECT ... FROM orders` が、ロールによって別の行集合を返している。アプリ側のクエリに `WHERE customer_id = ?` を書き忘れても、DB がテナントを跨いだ閲覧を止める。これが RLS の価値である。 ### USING と WITH CHECK の違い ポリシーには2つの条件を書ける。役割がはっきり異なる。 - **`USING`** … **既存の行**に適用する可視性フィルタ。`SELECT` で見えるか、`UPDATE`/`DELETE` の 対象にできるか（=読取・更新前の判定）を決める。 - **`WITH CHECK`** … **これから書き込む／更新後の行**に適用する検証。`INSERT` する行や `UPDATE` 後の行がこの条件を満たさなければ拒否する（=書込後の判定）。 対応関係を表にする。 | コマンド | `USING`（対象行の判定） | `WITH CHECK`（書く行の判定） | |---|---|---| | `SELECT` | 適用（見える行を絞る） | ― | | `DELETE` | 適用（消せる行を絞る） | ― | | `INSERT` | ― | 適用（挿す行を検証） | | `UPDATE` | 適用（更新できる行を絞る） | 適用（更新後の行を検証） | `UPDATE` で `WITH CHECK` を省略すると、`USING` の式が `WITH CHECK` にも流用される。書き込みを 実際に試す。`cust_100` に `INSERT` 権を与え、自分の顧客なら通り、他人の顧客は弾かれることを見る。 ```sql GRANT INSERT ON orders TO cust_100; SET ROLE cust_100; -- 自分の顧客(100)なら WITH CHECK を満たすので通る INSERT INTO orders (customer_id, status, ordered_at) VALUES (100, 'pending', now()); -- 他人の顧客(200)は WITH CHECK に反するので拒否される INSERT INTO orders (customer_id, status, ordered_at) VALUES (200, 'pending', now()); RESET ROLE; ``` ```text INSERT 0 1 ERROR: new row violates row-level security policy for table "orders" ``` 1件目は成功し、2件目は `new row violates row-level security policy` で拒否された。`WITH CHECK` が 無ければ「他人になりすました行」を挿入できてしまう。読取（`USING`）だけでなく書込 （`WITH CHECK`）も塞いで、初めて「自分のデータの箱から出られない」設計になる。 なお、接続ごとにロールを分けられず1つの DB ロールで多数のテナントを捌く（コネクションプール などの）構成では、`current_user` の代わりに**セッション変数**を使う。アプリが接続直後に 「今このセッションは顧客 100 として動く」を注入し、ポリシーはそれを読む。 ```sql CREATE POLICY orders_by_setting ON orders USING (customer_id = current_setting('app.current_customer', true)::bigint); -- アプリが接続直後に実行する（第2引数 true は「未設定なら NULL を返す」の意味） SELECT set_config('app.current_customer', '100', false); ``` `current_setting('app.current_customer', true)` の第2引数（`missing_ok`）を `true` にすると、 未設定時にエラーではなく `NULL` を返す。`customer_id = NULL` は必ず0行になる（第2回の三値論理） ため、**設定し忘れたら「全件」ではなく「0件」に倒れる**。これは安全側（fail-closed）の挙動で、 RLS の条件はこのように「未設定なら見えない」に倒すのが鉄則である。 ### 所有者・superuser・BYPASSRLS は素通りする RLS を張っても、次の3者は**ポリシーを無視して全行にアクセスできる**。これを知らないと 「RLS を張ったのに素通りする」と混乱する。 1. **superuser** … 常に RLS を無視する。 2. **`BYPASSRLS` 属性を持つロール** … 常に RLS を無視する。 3. **テーブルの所有者** … 既定では RLS を無視する（`FORCE` で変えられる。後述）。 まず `BYPASSRLS` を実演する。解析用に全件見たいロールを作ってしまうと、それは RLS を貫通する。 ```sql CREATE ROLE analyst LOGIN BYPASSRLS; GRANT USAGE ON SCHEMA public TO analyst; GRANT SELECT ON orders TO analyst; SET ROLE analyst; SELECT count(*) FROM orders; -- ポリシーを無視して全件が見える RESET ROLE; ``` ```text count --------- 1000001 (1 row) ``` ここで冒頭の主張に戻る。**アプリが superuser で接続していると、この 1 番により RLS も GRANT も すべて素通りになる**。RLS 設計が丸ごと無意味になるので、アプリ接続ロールは superuser でも `BYPASSRLS` でもあってはならない。 3 番の所有者バイパスと、それを塞ぐ `FORCE ROW LEVEL SECURITY` を、専用の小さな表で確かめる。 `SET ROLE` で非 superuser の所有者になりきると、所有者としての挙動を観察できる。 ```sql -- 非 superuser の所有者ロールを用意し、public に作る権利を与える CREATE ROLE demo_owner NOLOGIN; GRANT USAGE, CREATE ON SCHEMA public TO demo_owner; SET ROLE demo_owner; -- 以降このセッションは superuser 権限を失う CREATE TABLE demo_rows (tag text, val int); INSERT INTO demo_rows VALUES ('a', 1), ('b', 2); ALTER TABLE demo_rows ENABLE ROW LEVEL SECURITY; CREATE POLICY deny_all ON demo_rows USING (false); -- 誰にも見せないポリシー -- 所有者自身は RLS を素通りするので、まだ2行見える SELECT * FROM demo_rows; ``` ```text tag | val -----+----- a | 1 b | 2 (2 rows) ``` `USING (false)` は「1行も見せない」条件なのに、**所有者には2行見えている**。これが所有者バイパス である。`FORCE` を付けると所有者もポリシーの対象になる。 ```sql ALTER TABLE demo_rows FORCE ROW LEVEL SECURITY; SELECT * FROM demo_rows; -- 所有者にも適用され、0行になる RESET ROLE; ``` ```text tag | val -----+----- (0 rows) ``` ただし `FORCE` が効くのは**所有者まで**である。superuser（`RESET ROLE` で戻った `postgres`）は `FORCE` があっても依然として全行を見る。だから「テーブルを superuser が所有し、アプリも superuser で繋ぐ」構成では `FORCE` を付けても守れない。根本の対策は、**アプリを、所有者でも superuser でも `BYPASSRLS` でもない専用ロールで接続させる**ことである。 ### 監査 ―― 誰が何をしたか 権限で入口を絞っても、「実際に誰が何をしたか」を後から追える記録が無ければ調査も説明もできない。 **監査**とは、誰がいつどの操作をしたかを、後から追跡できる形で記録し残すことである。PostgreSQL の 監査には、コア機能のログ設定と、拡張の pgaudit の2段階がある。 まずコア機能。サーバ設定（`postgresql.conf` か `ALTER SYSTEM`）で有効化する。多くは再起動不要で、 `pg_reload_conf()` の再読み込みで反映される。 ```sql -- 接続・切断を記録し、データ変更文(DML)と DDL を記録する ALTER SYSTEM SET log_connections = on; ALTER SYSTEM SET log_disconnections = on; ALTER SYSTEM SET log_statement = 'mod'; -- none / ddl / mod / all -- 行頭に「時刻 [PID] ロール@DB」を出す ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d '; SELECT pg_reload_conf(); ``` `log_statement` の値が監査の粒度を決める。`ddl` は定義変更のみ、`mod` は `ddl` に加え `INSERT`/`UPDATE`/`DELETE`/`TRUNCATE` などの変更文、`all` は `SELECT` を含む全文である。`all` は 情報量が最大だが本番では膨大になりやすい。`log_line_prefix` の `%u`（ロール）`%d`（DB） `%m`（時刻）`%p`（PID）で「誰が」を各行に刻む。出力される行のイメージは次のようになる。 ```text 2026-08-14 10:12:03.451 JST [21899] app_write@postshop LOG: statement: UPDATE orders SET status = 'paid' WHERE id = 42; ``` コア機能は手軽だが、「どのテーブル・どの列を触ったか」の構造化までは面倒を見ない。より本格的な 監査は拡張 **pgaudit** を使う。pgaudit はコアに含まれないため、共有ライブラリに事前読み込みして （要再起動）から拡張を作成する。 ```text # postgresql.conf （shared_preload_libraries の変更は再起動が必要） shared_preload_libraries = 'pgaudit' pgaudit.log = 'write, ddl, role' # read/write/function/role/ddl/misc/all から選ぶ pgaudit.log_relation = on # 触れた各テーブルを個別に記録する ``` ```sql CREATE EXTENSION pgaudit; ``` pgaudit はログ行を `AUDIT:` 接頭辞つきの構造化された1行として出す。操作の種別・コマンド・対象 オブジェクト・文本体が CSV 風に並ぶため、後から機械的に集計・検索しやすい。 ```text ... [21899] app_write@postshop LOG: AUDIT: SESSION,1,1,WRITE,UPDATE,TABLE,public.orders,"UPDATE orders SET status='paid' WHERE id=42;", ``` `log_statement`（生の文をクラス単位で記録）と pgaudit（種別・対象オブジェクト単位で構造化、 特定ロール・特定オブジェクトだけを狙って監査も可能）は補完関係にある。要件が「変更文をとにかく 残す」ならコア機能で足り、「機密テーブルへの参照も含め監査要件として構造化して残す」なら pgaudit を足す、と考えればよい。 ### 認証の概観 ―― pg_hba.conf ここまでは「接続できた後」の話だった。**そもそも誰が接続できるか**を決めるのが `pg_hba.conf` （host-based authentication）である。これは3層モデルの一番外側、認証の層に当たる。上から順に 評価し、**最初に一致した行**で認証方式が決まる。 ```text # TYPE DATABASE USER ADDRESS METHOD local all all peer host postshop app_read 10.0.0.0/24 scram-sha-256 host postshop app_write 10.0.0.0/24 scram-sha-256 hostssl postshop +readonly 0.0.0.0/0 scram-sha-256 host all all 0.0.0.0/0 reject ``` 各列の意味は次のとおり。 | 列 | 意味 | |---|---| | `TYPE` | `local`=Unix ソケット、`host`=TCP、`hostssl`=SSL 必須の TCP | | `DATABASE` | 対象データベース（`all` は全部） | | `USER` | 対象ロール。`+readonly` は「`readonly` ロールのメンバー全員」を表す | | `ADDRESS` | 接続元 IP 範囲（`host` 系のみ） | | `METHOD` | 認証方式 | `METHOD` の主なものは、`trust`（無条件で許可。テスト以外で使わない）、`reject`（拒否）、 `scram-sha-256`（パスワードを SCRAM でチャレンジ認証。**現在の推奨**）、`md5`（旧式のパスワード。 非推奨）、`peer`（OS ユーザー名と同名の DB ロールを許可。ローカル運用で使う）、`cert` （クライアント証明書）である。PostgreSQL 14 以降はパスワード保存の既定が `scram-sha-256` で、 新規パスワードは自動的にこの方式で保存される。 ```sql SHOW password_encryption; -- scram-sha-256 （PG14 以降の既定） ``` ```text password_encryption --------------------- scram-sha-256 (1 row) ``` `pg_hba.conf` を編集したら `SELECT pg_reload_conf();` で反映する（再起動は不要）。ここで確認して おきたいのは、**認証（pg_hba）と権限（GRANT/RLS）は別の層**だということである。pg_hba は 「入館証を持っているか」を見るだけで、入館後に「どの部屋の何を触ってよいか」は一切決めない。 逆に、どれだけ GRANT を絞っても pg_hba が `trust` で誰でも入れるなら、入口が開けっ放しになる。 ### 最小権限とインジェクション ―― 第11回への布石 最小権限は、それ自体が防御であると同時に、他の攻撃の**被害を縮小する保険**でもある。アプリの 接続ロールが「特定テーブルへの `SELECT`/`INSERT`/`UPDATE`/`DELETE` だけ」を持つ非特権ロールで あれば、仮に SQL インジェクションで任意の SQL を流し込まれても、`DROP TABLE` も `CREATE ROLE` も `pg_authid`（パスワードハッシュが入るシステムカタログ）の閲覧もできない。 被害は「そのロールが触れる範囲」に閉じ込められる。逆にアプリが superuser で繋いでいれば、 1つのインジェクションで全データの破壊・流出まで一直線である。権限設計は、境界を破られた後の 被害範囲を決める最後の壁になる。インジェクションそのものの防ぎ方は[第11回](11-app-boundary-injection.md)で扱う。 ## ハンズオン ### 課題1: 読み取り専用ロールを作り、権限差を確認する **やること** 1. グループロール `readonly` を作り、`USAGE`・`SELECT`・デフォルト権限を付ける。 2. ログインロール `app_read` を作って `readonly` に所属させる。 3. `SET ROLE app_read` で、`SELECT` は通り `INSERT`/`UPDATE`/`DELETE` は拒否されることを確認する。 4. `USAGE` を付け忘れたロールでは `permission denied for schema` になることを再現する。 **想定解答** 本編「GRANT / REVOKE と最小権限」の 1〜5 をそのまま実行してロールを作る。権限差の確認は次のとおり。 ```sql SET ROLE app_read; SELECT count(*) FROM customers; -- 通る UPDATE orders SET status = 'paid' WHERE id = 1; -- 拒否される RESET ROLE; ``` ```text count ------- 50000 (1 row) ERROR: permission denied for table orders ``` `SELECT` は `readonly` 経由で許可され、`UPDATE` は許可されていないため `permission denied for table orders` になる。`USAGE` 忘れの再現は本編「つまずきの実演」の `probe` ロールの手順で、`permission denied for schema public` が出ることを確認する。表への権限 （`SELECT`）とスキーマへの権限（`USAGE`）が別物であること、エラーメッセージの `for table` と `for schema` を読み分けることが確認できれば正解である。 ### 課題2: orders に RLS を張り、別ロールで「自分の顧客の注文だけ」を検証する **やること** 1. `customer_role_map` と `cust_100` / `cust_200` を作り、必要な `GRANT` を与える。 2. `orders` で RLS を有効化し、`current_user` で顧客を引き当てるポリシーを張る。 3. `SET ROLE cust_100` と `SET ROLE cust_200` で、見える行が変わることを確認する。 4. `cust_100` から他人の顧客(200)の注文を `INSERT` しようとし、`WITH CHECK` で弾かれることを確認する。 5. `BYPASSRLS` を持つ `analyst` では全件見えてしまうことを確認し、RLS が素通りする条件を説明する。 **想定解答** 本編「行レベルセキュリティ」「USING と WITH CHECK の違い」「所有者・superuser・BYPASSRLS は 素通りする」の各手順をそのまま実行する。到達点は次の3つを自分の目で確認できること。 - `SET ROLE cust_100` と `cust_200` で `SELECT ... FROM orders` の結果集合が別になる （同じ SQL・同じ表なのにロールで変わる）。 - `cust_100` から `customer_id = 200` の行を `INSERT` すると `new row violates row-level security policy for table "orders"` で拒否される。 - `analyst`（`BYPASSRLS`）で `SELECT count(*) FROM orders` すると全件が返り、RLS が無効化される。 最後の点から「アプリ接続ロールに `BYPASSRLS`／`SUPERUSER` を与えてはならず、テーブル所有者でも 接続させない」という設計上の結論を言葉で説明できれば、この課題の狙いは達成である。 ### 課題3: 監査ログを有効化し、操作を追跡する **やること** 1. `log_connections` / `log_statement = 'mod'` / `log_line_prefix` を設定し `pg_reload_conf()` で反映する。 2. 別ロールで接続し、`UPDATE` を1本流す。 3. サーバログに、いつ・どのロール・どの DB から・どの文が実行されたかが残っていることを確認する。 **想定解答** 設定は本編「監査」の `ALTER SYSTEM SET ...` をそのまま実行する。ログの出力先は `SHOW log_directory;` と `SHOW log_filename;`（`logging_collector = on` の場合）で確認できる。 その後、監査対象の操作を流す。 ```sql -- 書き込み用ロールで一手動かす（事前に app_write に UPDATE 権を付けておく） SET ROLE app_write; UPDATE orders SET status = 'refunded' WHERE id = 42; RESET ROLE; ``` サーバログに次のような行が出れば追跡できている。`app_write@postshop`（誰が・どこへ）と `statement: UPDATE ...`（何を）が1行に揃う。 ```text 2026-08-14 10:31:20.882 JST [22713] app_write@postshop LOG: statement: UPDATE orders SET status = 'refunded' WHERE id = 42; ``` さらに構造化した監査が要るなら pgaudit を `shared_preload_libraries` に加えて再起動し、 `pgaudit.log = 'write'` で同じ `UPDATE` が `AUDIT: SESSION,...,WRITE,UPDATE,TABLE,public.orders,...` として記録されることを確認する。「どのロールが・いつ・どの表に・何をしたか」が後から一意に 辿れれば正解である。 ## よくあるつまずきと対処 | 症状 | 原因 | 対処 | |---|---|---| | RLS を張ったのに全行見えてしまう | アプリが superuser で接続している | アプリ接続ロールから `SUPERUSER` を外す。専用の非特権ロールで繋ぐ | | RLS を張ったのに全行見えてしまう（superuser ではない） | 接続ロールがテーブル所有者、または `BYPASSRLS` 属性を持つ | 所有者・`BYPASSRLS` ロールで接続しない。所有者にも適用したいなら `FORCE ROW LEVEL SECURITY` | | `FORCE` を付けても素通りする | superuser は `FORCE` があっても常に RLS を無視する | superuser で接続しない。表の所有も superuser 以外にする | | `SELECT` 権を付けたのに `permission denied for schema public` | スキーマの `USAGE` が無く、表に到達できない | `GRANT USAGE ON SCHEMA public TO ロール` を付ける | | 後から追加した表だけ読めない | `GRANT ... ON ALL TABLES` は実行時点の表にしか効かない | `ALTER DEFAULT PRIVILEGES ... GRANT SELECT ON TABLES` を併用する | | `ALTER DEFAULT PRIVILEGES` を設定したのに新表に権限が付かない | デフォルト権限は「実行したロールが作る表」にしか効かない。作成者が別ロール | `ALTER DEFAULT PRIVILEGES FOR ROLE 作成者 ...` と作成者を明示する | | セッション変数版ポリシーで全件見える／エラーになる | `current_setting(name)` が未設定で例外、または設定漏れで意図せぬ比較 | `current_setting(name, true)` で未設定時 `NULL`。`NULL` 比較は0行=安全側に倒す | | `RLS` 有効化直後に対象ロールで表が空に見える | ポリシー未作成の `ENABLE` は既定で原則拒否 | 目的のポリシーを `CREATE POLICY` する | ## 到達度チェックリスト - [ ] 「PostgreSQL にユーザーはなくロールだけ」「`LOGIN` の有無でユーザー／グループが分かれる」を説明できる。 - [ ] グループロールに `USAGE`＋`SELECT`＋デフォルト権限を束ね、ログインロールを所属させて読み取り専用を作れる。 - [ ] `GRANT ON ALL TABLES`（既存）と `ALTER DEFAULT PRIVILEGES`（将来）の違いを説明できる。 - [ ] スキーマ `USAGE` を忘れると `permission denied for schema` になる理由を、表権限との違いとして説明できる。 - [ ] `ENABLE ROW LEVEL SECURITY` ＋ `CREATE POLICY` で「自ロールの顧客の行だけ」を実現し、別ロールで検証できる。 - [ ] `USING`（既存行のフィルタ）と `WITH CHECK`（書く行の検証）の違いを、コマンド別に説明できる。 - [ ] superuser・`BYPASSRLS`・所有者が RLS を素通りすること、`FORCE` が所有者までしか効かないことを説明できる。 - [ ] `log_statement`／`log_connections`／pgaudit の役割分担と、`pg_hba.conf` の認証方式（`scram-sha-256` 推奨）を概観できる。 ## 宿題（次回までの自習） 1. アプリ実行時ロール `app_write` を最小権限で作る。`orders`・`order_items`・`customers` への `SELECT`/`INSERT`/`UPDATE`/`DELETE` だけを持ち、`DROP` も `CREATE ROLE` もできないことを `SET ROLE app_write` で確かめる。将来テーブルにも権限が伝播するよう `ALTER DEFAULT PRIVILEGES` を設定する。 2. `orders` の RLS を、セッション変数 `app.current_customer` を使う版（`current_setting(..., true)`） に書き換える。設定せずに `SELECT` して0行になること（安全側に倒れること）を確認する。 3. `log_statement = 'mod'` を有効にした状態で `app_write` から数本の更新を流し、サーバログから 「どのロールが・いつ・どの表を更新したか」を実際に読み取ってみる。次回のインジェクション対策で、 最小権限がどこまで被害を抑えるかを考える材料にする。 ## 次回への接続 今回は「入口を絞る（認証）」「できることを絞る（GRANT）」「見える行を絞る（RLS）」「記録を残す （監査）」の4点で、DB 側の防御を組んだ。[第11回](11-app-boundary-injection.md)では、アプリと DB の 境界に視点を移し、SQL インジェクションがどう起きるか、プレースホルダで防ぐ正攻法、そして今回作った 最小権限ロールが「境界を破られた後」の被害をどこまで小さくするかを扱う。権限設計は、攻撃を受けた ときに初めて価値が分かる最後の壁である。 ## 参考 - PostgreSQL 公式ドキュメント: "Server Administration" > "Database Roles" （ロール・属性・メンバーシップ・`GRANT`/`REVOKE`） - PostgreSQL 公式ドキュメント: "The SQL Language" > "Data Definition" > "Privileges" （権限の種類・デフォルト権限） - PostgreSQL 公式ドキュメント: "The SQL Language" > "Data Definition" > "Row Security Policies" （RLS・`CREATE POLICY`・`USING`/`WITH CHECK`・`FORCE ROW LEVEL SECURITY`） - PostgreSQL 公式ドキュメント: "SQL Commands" > `CREATE POLICY` / `ALTER TABLE` / `ALTER DEFAULT PRIVILEGES` - PostgreSQL 公式ドキュメント: "Server Administration" > "Client Authentication"（`pg_hba.conf`・認証方式） - PostgreSQL 公式ドキュメント: "Server Administration" > "Error Reporting and Logging" （`log_statement`・`log_connections`・`log_line_prefix`） - pgaudit 公式ドキュメント（PostgreSQL Audit Extension, https://github.com/pgaudit/pgaudit ）