Zenn
Open5

PostgreSQLでRLS扱う時の知見

PostgreSQL
#
rls
XknzwXknzw

RLSの設定が抜けているテーブル（テナントidカラムがあるテーブルかつ、policyが存在しないテーブル）を抜き出すクエリ

select t.table_schema,
       t.table_name
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name and c.table_schema = t.table_schema
left join pg_policies p on p.tablename = t.table_name and p.schemaname = t.table_schema
where c.column_name = '{YOUR_TENANT_COLUMN_NAME}'
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
      and p.policyname is null
order by t.table_schema;

ci/cdに組み込むことでRLS付け忘れテーブルを事前に検知できそう

CI環境にマイグレーション
↓
CI環境でRLS有効になっていないテーブルないかチェック
↓
STG環境へマイグレーション
↓
STG環境へRLS有効になっていないテーブルないかチェック
↓
STG環境へソースコードデプロイ

のようなフローにする

XknzwXknzw

insert時にもtenant_idを暗黙的に指定するにはWITH CHECKを使う

CREATE POLICY tenant_policy ON "product"."category" USING ("tenant_id" = current_tenant_id) WITH CHECK ("tenant_id" = current_tenant_id);
XknzwXknzw

RLSの設定漏れ防止する策

  1. 複数テナントでのテスト書く
  2. ci/cdで抜け漏れチェックツール入れる
  3. RLSを信用しないで、where句にtenantIdをアプリケーション側でも明示的に入れる。あくまでRLSはセーフティネット扱いにする。
  4. insert時にもRLSは有効にしてアプリケーション側ではwhere句にtenantIdを指定しないようにする。これでRLSが無効になった状態でinsertするとtenantIdがnull不可でエラーになりinsertされなくなる
  5. . クエリ実行する前に、RLSが有効かどうかのチェック機構を入れる
XknzwXknzw

RESETするとデフォルト値である空文字が入る。
なので、RESETしたコネクションのSELECTクエリは、where句に空文字が入ってinvalid input syntax for type uuid: ""が発生する

# in new connection
$ SHOW app.current_tenant_id;
 -> Error: unrecognized configuration parameter

# in new connection
$ SET app.current_tenant_id = '53tt3g-1289-519b-a76e-5571672a0027';
$ SHOW app.current_tenant_id;
 -> '53tt3g-1289-519b-a76e-5571672a0027'

# in new connection
$ SET app.current_tenant_id = '53tt3g-1289-519b-a76e-5571672a0027';
$ RESET app.current_tenant_id;
$ SHOW app.current_tenant_id;
 -> ''

# in new connection
$ RESET app.current_tenant_id;
$ SHOW app.current_tenant_id;
 -> ''
XknzwXknzw

connection poolを使っている場合、コネクションをプールに戻す際にRESETするのを忘れない。

RESETを忘れると他のリクエストが有効なtenant_idを保持しているコネクションを掴んでしまい、テナント間でデータが混じることにつながる