第10回｜権限・ロール・RLS・監査

「誰が接続でき、何をしてよく、どの行が見えるか」を分けて設計する。アプリが superuser で繋いでいる現場を卒業する回。

この回のねらい

多くの現場で、アプリケーションはデータベースに superuser（何でもできる最上位ロール）で接続している。 これは「全部の鍵を1本にまとめて玄関に挿しっぱなし」の状態で、SQL インジェクション（第11回）や 設定ミス1つが即座に全データの流出・破壊につながる。今回はこの状態を卒業する。PostgreSQL の 権限モデルは3層に分かれる――接続（誰が繋げるか）、権限（何をしてよいか）、 行レベルセキュリティ（どの行が見えるか）。この3層を分けて設計し、最小権限のロールを作り、 行レベルセキュリティで「自分のデータしか見えない」を実現し、監査ログで「誰が何をしたか」を 後から追える状態を作る。

到達目標

前提と準備

第1回で構築した PostgreSQL 16 系のデータベース（postshop）に共通スキーマとデータが投入済みで あることを前提にする。今回の操作の大半は superuser（例: postgres）で実行する――ロールを 作り権限を配る作業そのものが管理者の仕事だからである。作ったロールの見え方を確かめるときは、 実際に接続し直す代わりに SET ROLE を使う。SET ROLE は「そのロールでログインしたのと同じ 権限状態」にセッションを切り替えるコマンドで、非 superuser のロールに SET ROLE すると、 その間は superuser 権限も失う。これを使えば1つの psql セッションの中で権限差を検証できる。

psql -d postshop -U postgres

SELECT current_user, session_user;   -- いま誰として振る舞っているか

 current_user | session_user
--------------+--------------
 postgres     | postgres
(1 row)

pgaudit を試す小節では拡張のインストールと設定ファイルの編集が必要になるが、無い環境でも コア機能（log_statement 等）だけで監査の考え方は追えるように書く。

この回で新しく出てくる用語

権限の3層（認証・権限・行レベル）を分けて読むための語をここで揃えておく。 回をまたいで使う語は用語集にもまとめている。

用語一行での説明
ロール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（中に新しいオブジェクトを作ってよい）である。

読み取り専用ロールを最小権限で作る手順は次のとおり。グループロールに権限を束ね、そこへ ログインロールを所属させる。

-- 管理者(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 になり、読めるが書けないことを見る。

SET ROLE app_read;

SELECT count(*) FROM orders;   -- 読み取りは通る

  count
---------
 1000000
(1 row)

-- 書き込みは拒否される
INSERT INTO orders (customer_id, status, ordered_at) VALUES (1, 'pending', now());

ERROR:  permission denied for table orders

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 を付け忘れても たまたま通ってしまい、落とし穴が見えない。最小権限の第一歩として、まずこの緩い既定を締める。

-- public の既定権限を剥がし「原則拒否」にする（CREATE も USAGE も PUBLIC から外す）
REVOKE ALL ON SCHEMA public FROM PUBLIC;

この状態で「SELECT 権だけ渡して USAGE を渡し忘れた」ロールを作り、実際に触ってみる。

CREATE ROLE probe LOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO probe;   -- USAGE はわざと付けない

SET ROLE probe;
SELECT count(*) FROM orders;
RESET ROLE;

ERROR:  permission denied for schema public

表への SELECT は持っているのに、スキーマに入る権利（USAGE）が無いため、そもそも表に 到達できない。エラーが permission denied for table（表の権限不足）ではなく permission denied for schema（スキーマの権限不足）である点を読み分けられると、原因の切り分けが 速くなる。USAGE を足せば通る。

GRANT USAGE ON SCHEMA public TO probe;   -- これで SELECT が通るようになる

もう1つの伝播の落とし穴が ALTER DEFAULT PRIVILEGES の適用対象である。デフォルト権限は 「それを実行したロール（または FOR ROLE で指定したロール）が作成するオブジェクト」にしか 効かない。テーブルを作るのが migrator ロールなのに、postgres として ALTER DEFAULT PRIVILEGES を実行しても、migrator が作る新テーブルには権限が付かない。作成者を 明示するのが正解である。

-- migrator が今後作るテーブルに、自動で readonly の SELECT を付ける
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

3層の権限モデル

ここまでを一枚に整理する。クライアントの1回のアクセスは、次の3つの関門を順に通る。

クライアント接続
   ↓
[1] 認証（pg_hba.conf）: 誰が / どこから / どう繋げるか
   ↓
[2] 権限（ロール + GRANT）: どの表に何をしてよいか
   ↓
[3] 行レベル（RLS ポリシー）: どの行が見えるか
   ↓
データ
層決めること設定する場所今回の該当節
認証（authentication）誰が、どこから、どの方式で接続できるかpg_hba.conf認証の概観
権限（authorization）どのテーブルに対し何の操作をしてよいかロール属性・GRANT/REVOKEGRANT/REVOKE
行レベル（row security）通った操作のうち、どの行が見えるかCREATE POLICY行レベルセキュリティ

GRANT SELECT ON orders は「orders 表を読んでよい」という表単位の許可であり、 「どの行が見えるか」は決めない。行を絞るのは次の RLS の仕事である。両者は別レイヤーで、 両方が必要になる。

行レベルセキュリティ（RLS）

RLS は「同じ表・同じ SELECT でも、接続しているロールによって見える行を変える」仕組みである。 マルチテナント（1つの表に複数顧客のデータが同居する）で「自分のデータしか見えない」を DB 側で 強制するのに使う。

仕組みは2段階。まず表で RLS を有効化し、次に見える条件をポリシーとして書く。

-- テナント(顧客)とロールの対応表を用意する
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 に引き当てる。

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 すると、対象ロールにはその表が空に見える。

別ロールで検証する。

SET ROLE cust_100;
SELECT id, customer_id, status FROM orders ORDER BY id LIMIT 3;
RESET ROLE;

  id   | customer_id | status
-------+-------------+---------
 512   |         100 | paid
 1893  |         100 | shipped
 4021  |         100 | delivered
(3 rows)

SET ROLE cust_200;
SELECT id, customer_id, status FROM orders ORDER BY id LIMIT 3;
RESET ROLE;

  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（対象行の判定）WITH CHECK（書く行の判定）
SELECT適用（見える行を絞る）―
DELETE適用（消せる行を絞る）―
INSERT―適用（挿す行を検証）
UPDATE適用（更新できる行を絞る）適用（更新後の行を検証）

UPDATE で WITH CHECK を省略すると、USING の式が WITH CHECK にも流用される。書き込みを 実際に試す。cust_100 に INSERT 権を与え、自分の顧客なら通り、他人の顧客は弾かれることを見る。

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;

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 として動く」を注入し、ポリシーはそれを読む。

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 を貫通する。

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;

  count
---------
 1000001
(1 row)

ここで冒頭の主張に戻る。アプリが superuser で接続していると、この 1 番により RLS も GRANT も すべて素通りになる。RLS 設計が丸ごと無意味になるので、アプリ接続ロールは superuser でも BYPASSRLS でもあってはならない。

3 番の所有者バイパスと、それを塞ぐ FORCE ROW LEVEL SECURITY を、専用の小さな表で確かめる。 SET ROLE で非 superuser の所有者になりきると、所有者としての挙動を観察できる。

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

 tag | val
-----+-----
 a   |   1
 b   |   2
(2 rows)

USING (false) は「1行も見せない」条件なのに、所有者には2行見えている。これが所有者バイパス である。FORCE を付けると所有者もポリシーの対象になる。

ALTER TABLE demo_rows FORCE ROW LEVEL SECURITY;
SELECT * FROM demo_rows;                     -- 所有者にも適用され、0行になる
RESET ROLE;

 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() の再読み込みで反映される。

-- 接続・切断を記録し、データ変更文(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）で「誰が」を各行に刻む。出力される行のイメージは次のようになる。

2026-08-14 10:12:03.451 JST [21899] app_write@postshop LOG:  statement: UPDATE orders SET status = 'paid' WHERE id = 42;

コア機能は手軽だが、「どのテーブル・どの列を触ったか」の構造化までは面倒を見ない。より本格的な 監査は拡張 pgaudit を使う。pgaudit はコアに含まれないため、共有ライブラリに事前読み込みして （要再起動）から拡張を作成する。

# 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          # 触れた各テーブルを個別に記録する

CREATE EXTENSION pgaudit;

pgaudit はログ行を AUDIT: 接頭辞つきの構造化された1行として出す。操作の種別・コマンド・対象 オブジェクト・文本体が CSV 風に並ぶため、後から機械的に集計・検索しやすい。

... [21899] app_write@postshop LOG:  AUDIT: SESSION,1,1,WRITE,UPDATE,TABLE,public.orders,"UPDATE orders SET status='paid' WHERE id=42;",<none>

log_statement（生の文をクラス単位で記録）と pgaudit（種別・対象オブジェクト単位で構造化、 特定ロール・特定オブジェクトだけを狙って監査も可能）は補完関係にある。要件が「変更文をとにかく 残す」ならコア機能で足り、「機密テーブルへの参照も含め監査要件として構造化して残す」なら pgaudit を足す、と考えればよい。

認証の概観 ―― pg_hba.conf

ここまでは「接続できた後」の話だった。そもそも誰が接続できるかを決めるのが pg_hba.conf （host-based authentication）である。これは3層モデルの一番外側、認証の層に当たる。上から順に 評価し、最初に一致した行で認証方式が決まる。

# 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

各列の意味は次のとおり。

列意味
TYPElocal=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 で、 新規パスワードは自動的にこの方式で保存される。

SHOW password_encryption;   -- scram-sha-256 （PG14 以降の既定）

 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回で扱う。

ハンズオン

課題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 をそのまま実行してロールを作る。権限差の確認は次のとおり。

SET ROLE app_read;
SELECT count(*) FROM customers;                 -- 通る
UPDATE orders SET status = 'paid' WHERE id = 1; -- 拒否される
RESET ROLE;

 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つを自分の目で確認できること。

最後の点から「アプリ接続ロールに 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 の場合）で確認できる。 その後、監査対象の操作を流す。

-- 書き込み用ロールで一手動かす（事前に app_write に UPDATE 権を付けておく）
SET ROLE app_write;
UPDATE orders SET status = 'refunded' WHERE id = 42;
RESET ROLE;

サーバログに次のような行が出れば追跡できている。app_write@postshop（誰が・どこへ）と statement: UPDATE ...（何を）が1行に揃う。

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 する

到達度チェックリスト

宿題（次回までの自習）

  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回では、アプリと DB の 境界に視点を移し、SQL インジェクションがどう起きるか、プレースホルダで防ぐ正攻法、そして今回作った 最小権限ロールが「境界を破られた後」の被害をどこまで小さくするかを扱う。権限設計は、攻撃を受けた ときに初めて価値が分かる最後の壁である。

参考