PostgreSQL の RLS を試してみる

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

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

###title2
今回の環境まわり
###

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

###

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

###title2
DBユーザー
###

まずは以下のユーザーがいる状態にします
###list
- ①テーブル作ったり操作するスーパーユーザー(postgre *はじめからいる)
- ②スーパーユーザーをはずした 見るだけユーザー(test)
###
を用意しておきます。これらは GUIツールの pgAdmin でさくさくっと作れました。

###title
早速 RLS をしてみた結果

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

###command
SELECT * FROM t_test

###center
(img://18/498m)
###
それが RLS 状態(?) であれば WHERE なしでも WHERE member_id=2 がついたような状態になります。

###command
SELECT * FROM t_test

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

###title
RLS を 施す

###

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

###command

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 を設定

###command
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 できない

###title2
データを確認する(RLS)

###

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

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

###center
(img://18/497s)

###command

// 環境変数のようなものに 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ユーザーに切り替えて

###center
(img://18/504s)

###command
SELECT * FROM t_test 
###
をすると WHERE member_id=2 と同じデータが表示されます。

###center
(img://18/499m)
###

###
そして
###command
SET app.current_member_id=1

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

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

###command
GRANT SELECT, UPDATE, INSERT ON t_test To test;

###title2
INSERT時の デフォルト値
###

t_test の列 member_id のデフォルト値を
#+*#"current_setting('app.current_member_id')::bigint"
のように設定すれば、 app.current_member_id の値がデフォルトで 設定されます
###center
(img://18/506s)

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

###title2
pgAdmin で RLS の設定確認
###

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

###center
(img://18/502s)
(img://18/501s)

###title2
インデックス
###

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

###title
PostgreSQL + RLS + php のソース例
###

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

###source
(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);

###
実行結果
###source ret
(
    [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
        )

)

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

上記プログラムで

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

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

###source
$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 のエラーメッセージです。
###error
pg_query(): Query failed: ERROR:  unrecognized configuration parameter "app.current_member_id ....
ERROR:  unrecognized configuration parameter "app.current_member_id"

wakatta 記法

このコンテンツを見る