出展者の読み取りと書き込みを保護する

はじめに

Fern Studio と Cedar Works が、小さなクラフトフェアに向けて準備しています。各出展者は、共有の PostgreSQL テーブルで自分の搬入登録を管理する必要があります。フェアチームは引き続き全員を参照し、ブースを割り当てられなければなりません。クライアントコマンドのフィルターは閲覧に便利ですが、出展者は省略できます。データベースで境界を強制する必要があります。

行ポリシーを保存・適用し、別々の通常のパスワードログインで実際の登録を完成させます。PostgreSQL と対象範囲のアカウントは準備済みです。基本的なターミナル操作、PostgreSQL の SELECT/WHERE、INSERT、UPDATE、テーブルの概念が必要です。ロール、行レベルセキュリティ、ポリシー式、トランザクション、必要なシェル構文は使用前に説明します。Python プログラミングは不要です。全編を通して Terminal を使います。95 分は読解と通常のミスを含む作者の見積もりであり、初心者の実測完了時間ではありません。

/home/labex/project/exhibitor-desk で作業します。read-access.sql と write-access.sql を作り、fair.registrations の行ポリシーを変更し、後述の新規登録 2 件だけを作成・修正します。元の全レコード、テーブルの同一性と定義、準備済みの権限・アカウント、資格情報、ツール、サービスを保全してください。ポリシーファイルは自己完結した SQL とし、シェルコマンド、接続変更、外部 include を含めません。同等のポリシー名や正しい構成も受け入れます。あなたはコースの操作者として、準備済みの各テストアカウントを意図的に選べます。この実習はデータベースの各識別主体を互いから保護するもので、VM の sudo 操作者から資格情報を隠すものではありません。Web サイト認証や、あらゆる情報サイドチャネルへの保護は実装しません。

フェアチームの業務を保ちながら閲覧範囲を制限する

このステップでは、各出展者が自分の全登録を見られるようにし、主催者の出展者横断業務を保ちます。出展者の書き込みは、次のステップでポリシーを加えるまで使えません。

Terminal から始めます。cd はディレクトリを移動し、この絶対パスは現在の場所に関係なく使えます。

cd /home/labex/project/exhibitor-desk

./ は現在のディレクトリのプログラムを実行します。準備済みの db ランチャーは実際の PostgreSQL 接続を認証し、クライアントの psql を実行します。行を絞ったり結果を作ったりはしません。アカウントの別名を一覧表示します。

./db accounts

別名は、個別に準備された次のログインを選びます。

別名は操作者にとっての便宜であり、Fern に与えられたデータベース権限ではありません。cedar を選ぶと、意図的に Cedar の別の資格情報を使います。Fern の接続内で Cedar になることは、禁止されたままである必要があります。

-c は 1 つのコマンド文字列を渡します。二重引用符は空白をシェルでまとめます。session_user は認証されたログイン、current_user は現在の文が権限を使うロールです。この通常接続では一致するはずです。

./db fern -c "SELECT session_user, current_user;"

両列とも fern_studio を確認します。まず Fern の既知の通常レコードを読みます。箱数は整数で、ゼロは既知のゼロであり登録の欠落ではありません。

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations WHERE registration_id = 'R101';"

R101 は Fern に属し、連絡先は Fern desk、箱数 2、ブース A1、料金 1200 セントです。fair はテーブルをまとめるスキーマです。元の業務上の事実は変えてはいけません。

任意のクライアントフィルターを省きます。

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

初期状態では Fern は R101/R102 に加え、Cedar の R201/R202 も見えます。WHERE owner_role = 'fern_studio' ならそのクエリだけで隠せても、この無条件クエリは防げません。Cedar にも同じ問題があります。

PostgreSQL には関係する 2 層の権限があります。GRANT はテーブルや指定列への操作を許可します。準備済みの権限は、必要な操作へのアクセスをすでに許しています。行レベルセキュリティ（RLS）は、追加で読み書きできる行を決めます。適用可能なポリシーなしで RLS を有効にすると、既定では行へのアクセスを拒否します。所有ルールを自動的に作るわけではありません。

editor はこのコースのテーブル所有者で、ポリシーを変えられます。所有者は通常 RLS を迂回するため、editor のクエリ成功では出展者の制限を証明できません。定義確認は editor、結果テストは通常アカウントを使います。\d は SQL ではなく psql の説明コマンドです。

./db editor -c "\d fair.registrations"

6 列は registration_id、owner_role、contact、crates、booth_label、fee_cents です。新登録の既定ブースは Pending、料金は 0 です。準備済みの列権限は、出展者に ID・所有者・連絡先・箱数の挿入と、所有者・連絡先・箱数の更新を許します。ID・ブース・料金の更新、行削除、テーブルの truncate はできません。所有者列への UPDATE 権限があっても、ポリシーで所有者の変更を拒否する必要があります。

PostgreSQL の pg_policies カタログビューで既存のポリシーを調べます。

./db editor -c "SELECT policyname, roles, cmd, qual, with_check FROM pg_policies WHERE schemaname = 'fair' AND tablename = 'registrations' ORDER BY policyname;"

legacy_open_access は全員を許します。通常のポリシーは許可型で、適用されるものが OR で結合されます。広いポリシーの横に狭いものを追加しても、広い結果は許されたままです。変更の一部として旧ポリシーを廃止します。

ポリシーの USING 式は、既存行が見えるかを決めます。owner_role = current_user は、行の所有者と実際のデータベース識別主体を比較します。FOR SELECT は読み取りだけに限定します。主催者には別の TO fair_team のポリシーで、全行に対する正当な操作を許します。true はその指定ロールについて全行を通すという意味です。

最初の移行を保存します。cat > read-access.sql は以下を書き、以前のコピーを置き換えます。<<'SQL' はヒアドキュメントを始め、引用符付きマーカーによって単独行の SQL まで SQL をそのまま保ちます。BEGIN と COMMIT はポリシー変更を 1 トランザクションにし、文が一緒にコミットするか、エラーで取り消されるようにします。DROP POLICY IF EXISTS により、修正後の再実行もできます。

cat > read-access.sql <<'SQL'
BEGIN;
ALTER TABLE fair.registrations ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS legacy_open_access ON fair.registrations;
DROP POLICY IF EXISTS own_read ON fair.registrations;
DROP POLICY IF EXISTS organizer_access ON fair.registrations;
CREATE POLICY own_read ON fair.registrations
  FOR SELECT TO fern_studio, cedar_works
  USING (owner_role = current_user);
CREATE POLICY organizer_access ON fair.registrations
  TO fair_team USING (true) WITH CHECK (true);
COMMIT;
SQL

終了マーカーを省いてシェルが待つなら、単独行の SQL を入力します。実行前に保存ファイルを読みます。

cat read-access.sql

-f は psql にファイルからのコマンド読み取りを指定します。対象テーブルの所有者として適用します。

./db editor -f read-access.sql

BEGIN、ALTER TABLE、DROP POLICY、CREATE POLICY、COMMIT のメッセージが出るはずです。新しいポリシー名がまだなかったという notice は問題ありません。ランチャーは ON_ERROR_STOP を有効にし、ファイルの構文エラーは psql ステータス 3 で実行を止めます。接続が閉じると未完了トランザクションはロールバックされます。VM をリセットせず、ファイルを直して再実行します。名前・パスのエラーでは、絶対パスの cd と ls を使い、実際のファイル名を読んで再試行します。元の行の削除や再作成は不要です。

各通常アカウントで、許された集合全体を確認します。

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

Fern は R101/R102、箱数は 2/0、Cedar は R201/R202、箱数は 3/1 です。主催者はまだ元の 4 行すべてを見られます。

./db organizer -c "SELECT registration_id, owner_role, booth_label FROM fair.registrations ORDER BY registration_id;"

これは意図的な中間状態です。出展者には SELECT ポリシーがありますが、INSERT/UPDATE ポリシーはまだありません。準備済みの GRANT は変えません。結果チェックを実行します。sudo は準備済みのチェックに保護状態の確認を許します。実際の業務と使い捨てデータ上のポリシー SQL を試し、不足作業を補うことはありません。

sudo python3 /opt/labex-exhibitors/verify.py visibility

両アカウントが自分のレコードを見られ、主催者も使えることの確認が期待されます。次に、提案される書き込み用の別ルールを加えます。

既存行と提案された値の両方を検査する

このステップでは、出展者が自分の登録を追加・更新でき、他の所有者のレコードを作成・譲渡できないようにします。Terminal の /home/labex/project/exhibitor-desk から始めます。

SELECT ポリシーだけでは INSERT は許可されません。書き込みポリシーは 2 つの時点を考える必要があります。UPDATE では USING が変更できる既存行を選び、WITH CHECK が提案された新しい行を検査します。INSERT に古い行はないので、WITH CHECK だけを使います。どちらも、提案された所有者は current_user と等しくなければなりません。

同じ引用符付きヒアドキュメントで、書き込み用移行を保存します。read-access.sql で保護済みのテーブルにポリシーを加えます。

cat > write-access.sql <<'SQL'
BEGIN;
DROP POLICY IF EXISTS own_insert ON fair.registrations;
DROP POLICY IF EXISTS own_update ON fair.registrations;
CREATE POLICY own_insert ON fair.registrations
  FOR INSERT TO fern_studio, cedar_works
  WITH CHECK (owner_role = current_user);
CREATE POLICY own_update ON fair.registrations
  FOR UPDATE TO fern_studio, cedar_works
  USING (owner_role = current_user)
  WITH CHECK (owner_role = current_user);
COMMIT;
SQL

読んでから editor として適用します。

cat write-access.sql

./db editor -f write-access.sql

2 つの CREATE POLICY がコミットするはずです。修正して再実行しても、指定した書き込みポリシーだけを置き換え、登録は初期化しません。保存したポリシーファイルから始め直すなら、read-access.sql、次に write-access.sql の順で適用します。

同等の構成でも動作します。USING だけを持つ ALL または UPDATE ポリシーは、省略された WITH CHECK にその式を暗黙的に使います。節がないことだけで欠陥とは限りません。ここでは、コマンド別ポリシーで役割を明示します。大切なのは名前や記述の完全一致ではなく、許可・禁止される全操作の動作です。

まずテストデータを残さず、正当な書き込みを試します。BEGIN はトランザクションを開始し、RETURNING は INSERT/UPDATE が実際に保存した行を表示します。ROLLBACK は変更を破棄し、元の 4 行を保ちます。

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FERN', 'fern_studio', 'Loading test', 0); UPDATE fair.registrations SET crates = 2 WHERE registration_id = 'TRY-FERN' RETURNING registration_id, owner_role, crates; ROLLBACK;"

TRY-FERN、所有者 fern_studio、箱数 2 と、続く ROLLBACK が期待されます。通常の挿入は実際のゼロを受け入れ、更新も動作します。全書き込みを拒否するルールでは、この要件を満たせません。

次に、見えない既存行を試します。この文はコマンドとして成功しつつ、更新可能な行を見つけない必要があります。

./db fern -c "UPDATE fair.registrations SET contact = 'Wrong contact' WHERE registration_id = 'R201' RETURNING registration_id;"

返る行はなく、UPDATE 0 になるはずです。R201 は Cedar のものです。ゼロ行は行の絞り込みであり、接続失敗やデータベースエラーではありません。

次の 2 コマンドは想定された SQL 失敗です。両方ともトランザクションで試すので、誤ったポリシーで受け入れられても、最後の ROLLBACK が破棄します。エラーで ROLLBACK 前に止まっても、接続終了で未完了トランザクションを破棄します。

Fern として認証しながら、Cedar 所有のレコード作成を試します。意図的に RETURNING を省きます。返却行への SELECT ポリシー検査を、提案行の書き込みルールの代わりにしてはいけません。

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FOREIGN', 'cedar_works', 'Wrong owner', 1); ROLLBACK;"

new row violates row-level security policy を確認します。$? は直前のシェルコマンドのステータスです。他のコマンドを実行する前に表示します。

echo $?

失敗した -c SQL コマンドのステータスは 1 のはずです。接続・認証エラーのステータス 2 は別の失敗で、行ポリシーが機能した証拠にはなりません。ランチャーは呼び出しごとに準備済み資格情報を選びます。資格情報ファイルを編集せず、./db accounts で説明された別名を確認して再試行します。

次に、Fern の見える行の所有者を Cedar に変えようとします。この定数を使う UPDATE は、テーブル列を読む WHERE や RETURNING を持ちません。提案行の検査に直接到達し、周囲のトランザクションは、誤って受け入れられても取り消せるようにします。

./db fern -c "BEGIN; UPDATE fair.registrations SET owner_role = 'cedar_works'; ROLLBACK;"

echo $?

再び行セキュリティエラーとステータス 1 を確認します。RETURNING を使う更新だけを保護すればよいと考えないでください。許可された SQL はそれを省けます。

列権限は別の境界です。出展者は自分の連絡先・箱数を変えられますが、ブース・料金はフェアチームが割り当てます。このブース変更は Fern 自身の行でもテーブル権限エラーになる必要があります。

./db fern -c "BEGIN; UPDATE fair.registrations SET booth_label = 'WRONG'; ROLLBACK;"

提案行ポリシーエラーではなく permission denied for table registrations が期待されます。同様に、Fern 接続はロールを指定するだけで Cedar にはなれません。

./db fern -c "SET ROLE cedar_works;"

permission denied to set role を確認します。準備済み出展者は、他アカウントへの所属、テーブル所有権、BYPASSRLS を持たない通常ログインです。自由に選べるアプリ設定を変えても、別のログインとして認証されるわけではありません。ポリシーは実際のデータベースロールを比較します。

取り消せるテストで元の業務値が変わっていないことを確認します。

./db organizer -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

元の 4 行、連絡先、箱数、ブース、料金は初期値に一致する必要があります。書き込み境界のチェックを実行します。

sudo python3 /opt/labex-exhibitors/verify.py writes

両方の通常アカウント、正当な書き込み、異なるデータ集合、所有者を越える書き込み、主催者アクセスを調べます。全項目が通れば、保護されたテーブルを通常業務に使えます。

保護されたアカウントで登録を完成させる

このステップでは、機能する権限を使い、実際の搬入登録を 2 件準備します。Terminal の /home/labex/project/exhibitor-desk から始めます。追加・修正してよい業務行はこの新規 2 件だけで、元の全行を保ちます。

Fern は既知の箱数ゼロで新登録を始めます。準備済み既定値により、主催者の割り当てまでブース Pending、料金 0 です。

./db fern -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R301', 'fern_studio', 'Fern loading', 0) RETURNING registration_id, owner_role, contact, crates, booth_label, fee_cents;"

R301、fern_studio、Fern loading、0、Pending、0 を確認します。先のトランザクション内のテストと違い、実際にコミットされた INSERT です。繰り返すと重複キーエラーになり、冪等な作成コマンドではありません。

対象を限定した通常更新で、最終箱数を 4 にします。

./db fern -c "UPDATE fair.registrations SET crates = 4 WHERE registration_id = 'R301' RETURNING registration_id, owner_role, crates;"

Cedar は自分の新登録を箱数 1 で作ります。

./db cedar -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R302', 'cedar_works', 'Cedar freight', 1) RETURNING registration_id, owner_role, crates;"

R302、cedar_works、1 が期待されます。各出展者は、元の 2 件を含む 3 件を見られる状態です。

主催者自身の通常アカウントで、新しい各行に合意したブースと料金を割り当てます。料金は整数セントです。提示された金額はシナリオの事実で、税務・会計計算ではありません。

./db organizer -c "UPDATE fair.registrations SET booth_label = 'C3', fee_cents = 1800 WHERE registration_id = 'R301' RETURNING registration_id, booth_label, fee_cents;"

./db organizer -c "UPDATE fair.registrations SET booth_label = 'D2', fee_cents = 600 WHERE registration_id = 'R302' RETURNING registration_id, booth_label, fee_cents;"

R301/C3/1800 と R302/D2/600 を確認します。これはテーブル所有者ではなく fair_team を使います。所有者の迂回では、主催者のポリシーが使える証明になりません。

各出展者で、最終的な完全なレコードを読みます。

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

Fern は R101/R102/R301 を見て、新 R301 は箱数 4、ブース C3、料金 1800 です。Cedar は R201/R202/R302 を見て、新 R302 は箱数 1、ブース D2、料金 600 です。元のレコードは不変である必要があります。

INSERT を誤って繰り返したら、対応するアカウントで、その指定された新レコードを確認します。存在すれば、削除やデータベースのリセットではなく、対象を限定して連絡先・箱数を UPDATE します。たとえば Fern の新行は UPDATE fair.registrations SET contact = 'Fern loading', crates = 4 WHERE registration_id = 'R301'; で直せます。指定された新レコードだけを修正し、主催者のブース・料金を間違えた場合も、その行の割り当てを再実行します。保存済みの移行も、データを再作成せず修正・再適用できます。

最終結果のチェックを実行します。

sudo python3 /opt/labex-exhibitors/verify.py business

両新規登録の所有者、箱数、ブース、料金が正しく準備されたことの確認を期待します。再利用可能なポリシー SQL と、通常の出展者・主催者が実際に使える共有登録簿ができました。独立した Challenge では、別の作家の登録簿に同じ境界を適用します。

まとめ

テーブル・列の権限と行の所有を分け、広い許可ポリシーを廃止し、実際の通常ログインで既存行と提案された書き込みの両方を検査しました。新しい 2 件の登録と主催者が割り当てたブースは、他の出展者のレコードを公開・変更せずに動作します。

境界となるのは認証済みのデータベース識別主体です。テーブル所有者と管理者は信頼する前提のままで、この実習は Web サイトのログインや、すべてのデータベースのサイドチャネルに対する保証を提供しません。