編集する

PostgreSQL の RLS を試してみる

postgresql

RLS = Row Level Security

RLS = Row Level Security (行レベルのセキュリティ) の略です。RLS の機能を知って PostgreSQL に興味をもったという方も多いと思います。マルチテナントとかいろいろ小難しい解説よりも見て動かしてで、すぐ理解できたほうが良いと思いますので、プチはまりしたことなどをふまえて解説します。

ちなみに

PostgreSQL はほとんどいじったことはありませんが、DBまわりの基本はわかっていて RLS の部分を試したかったので、という感じです。マルチテナントとは?や create table みたいな序の序はすぱっとはしょります。

今回の環境まわり

ubuntu20 に PostreSQL を apt でインストールした
GUIツールの pgAdmin4 (高級アプリみたいな) を主に用いた
pgAdmin4 = apt でインストール→設定スクリプト→動作
phpPgAdmin = DL→解凍・設置→設定→動作

postgresql をいじるにあたって、日常的なものは phpPgAdmin で良さそうでしたが、pgAdmin でないと RLSまわりの設定・確認などでちょっと見れなかったなどありましたので pgAdmin で実験をしています。

DBユーザー

まずは以下のユーザーがいる状態にします

①テーブル作ったり操作するスーパーユーザー(postgre *はじめからいる)
②スーパーユーザーをはずした見るだけユーザー(test)

を用意しておきます。これらは GUIツールの pgAdmin でさくさくっと作れました。

早速 RLS をしてみた結果

実験結果ベースでおさらいしておくとまず以下のようなテーブル (t_test) とデータを WHERE なしだと通常は以下のようになります

SELECT * FROM t_test

それが RLS 状態(?) であれば WHERE なしでも WHERE member_id=2 がついたような状態になります。

SELECT * FROM t_test

感覚的には member_id=2 を環境変数のようにもっておき、いちいち WHERE で指定しなくても設定されるイメージです。

RLS を施す

t_test テーブル(の member_id を対象) に RLS を設定します。

CREATE POLICY test_member_policy ON t_test FOR ALL USING (member_id = current_setting('app.current_member_id')::bigint ) WITH CHECK (true)

t_test テーブルは member_id で RLS を設定

ALTER TABLE t_test FORCE ROW LEVEL SECURITY

上記はテーブル所有者もRLSの対象とするよという追加コマンド ( postgres などのスーパーユーザーは全データ見れます)

上記で RLS 状態? のテーブルができました。細かいパラメータをざっくり説明すると
FOR ALL は SELECT, UPDATE, INSERT, DELETE 全て
USING ~ は SELECT や UPDATE の時のレコードの条件
WITH CHECK ~ は INSERT の条件 *WITH CHECK がないと INSERT できない

データを確認する(RLS)

以下は上記とは別の test ユーザー (非スーパーユーザー + テーブル非所有者 + 非BypassRLS) で実施します。

*注スーパーユーザー、テーブル所有者、Bypass RLS が ON などの場合は全部見えてしまいますので、別のユーザー(ロール)を作っておく必要があります *ぷちはまりしました

// 環境変数のようなものに current_member_id=2 を設定する SET app.current_member_id=2 //SELECT set_config('app.current_member_id', '2', false); // 現在の設定値を見る場合 → 2 と表示されるはずです。 SHOW app.current_member_id;

上記をしたあとに testユーザーに切り替えて

SELECT * FROM t_test

をすると WHERE member_id=2 と同じデータが表示されます。

そして

SET app.current_member_id=1

をすれば、 WHERE member_id=1 と同じデータが表示されます。

上記以前に SELECT 文で権限エラーなどになったら以下のように
test ユーザーに t_test テーブルへ適宜権限を付与してください

GRANT SELECT, UPDATE, INSERT ON t_test To test;

INSERT時のデフォルト値

t_test の列 member_id のデフォルト値を
current_setting('app.current_member_id')::bigint
のように設定すれば、 app.current_member_id の値がデフォルトで設定されます

挙動確認(SELECT, UPDATE, DELETE, INSERT)

上記までの一連の流れで
SELECT, UPDATE, DELETE は app.current_member_id の値以外はできない。
INSERT したときの member_id の値は app.current_member_id になる。
ということが実現できます。

pgAdmin で RLS の設定確認

CREATE POLICY test_member_policy ON t_test ... と作ったオブジェクトは以下のようにして見たり変更したりすることができます。

インデックス

test_id が主キーですが、 RLSで設定した member_id と複合インデックスにする方が高速とのことです。*未確認

PostgreSQL + RLS + php のソース例

ということで、テーブルにRLS を設定すれば以下のようなプログラムでマルチテナントサービスが実現できます。

postgresql + RLS + php


$conn = pg_connect("host=localhost dbname=XXXXX user=test password=YYYYY");

// ↓↓↓↓ ここがキモ WHERE member_id=2 のようなもの
$rs = pg_query("SET app.current_member_id=2");
// $rs = pg_query("SELECT set_config('app.current_member_id', '2', false)");

$rs = pg_query('SELECT * FROM public.t_test');
if (!$rs) die( pg_last_error()); 

$num = pg_num_rows($rs) ;
$rows = pg_fetch_all($rs, PGSQL_ASSOC ) ;
print_r($rows) ;

pg_close($conn);

実行結果

(
    [0] => Array
        (
            [test_id] => 6
            [test_name] => aweg
            [test_body] => BBB
            [created_at] => 2024-10-22 11:25:03.298873
            [updated_at] => 2024-10-22 13:41:08.080873
            [member_id] => 2
        )

    [1] => Array
        (
            [test_id] => 7
            [test_name] => BBB
            [test_body] => aweg
            [created_at] => 2024-10-22 11:25:03.298873
            [updated_at] => 2024-10-22 13:41:08.080873
            [member_id] => 2
        )

    [2] => Array
        (
            [test_id] => 8
            [test_name] => ggg
            [test_body] => awegaweg
            [created_at] => 2024-10-22 11:58:37.715561
            [updated_at] => 2024-10-22 13:41:08.080873
            [member_id] => 2
        )

)

SET app.currect_member_id をしていない場合

上記プログラムで

pg_query("SET app.current_member_id=2");

をし忘れた場合は全件取得されちゃうんじゃないか? と心配になりましたのでその実験です。

$conn = pg_connect("host=localhost dbname=XXXXX user=test password=YYYYY");

// ↓↓↓↓ ここをはずすと
// $rs = pg_query("SET app.current_member_id=2");

$rs = pg_query('SELECT * FROM public.t_test');
if (!$rs) die( pg_last_error()); 

$num = pg_num_rows($rs) ;
$rows = pg_fetch_all($rs, PGSQL_ASSOC ) ;
print_r($rows) ;

pg_close($conn);

結果はエラーになります。以下は pg_last_error のエラーメッセージです。

pg_query(): Query failed: ERROR: unrecognized configuration parameter "app.current_member_id .... ERROR: unrecognized configuration parameter "app.current_member_id"