Zenn
👏

[PostgreSQL] CIでRLSポリシーの付け漏れを検知する

2022/12/04に公開
PostgreSQL
RLS
tech

マルチテナント系のシステムにおいてPostgreSQLのRow Level Securityを運用する上で、
DBレイヤーでRLSポリシーを付け漏れが発生しテナント間でのデータ混同することを防止する施策を行ったので備忘録として残します

対象読者

・マルチテナント型のサービスでRow Level Securityを使用している方
・特にDBマイグレーション機能を持ったORMツールを使用している方

技術スタック

あまり技術構成に関わらない話かと思いますが念の為
・TypeORM v2系
・PostgreSQL v13
・Github Actions

こんなことがありました

  1. ある新規機能を開発する際に、RLSを利用するべきテーブルを新規作成しました
CREATE TABLE product (id SERIAL NOT NULL, tenant_id UUID);
  1. ここで、作成したテーブルに以下のようなRLSを適用するsqlクエリを定義/実行/コミットすることを忘れる
ALTER TABLE product ENABLE ROW LEVEL SECURITY
CREATE POLICY tenant_policy ON product USING ("tenant_id" = current_setting('app.current_tenant_id', true)::UUID)

  1. RLSが有効でないため、SELECT * from productするとテナント関係なく全データが取得できる状態が出来上がる

  2. ローカル環境では1テナントで動作確認したため特に異常に気がつかないまま、検証/STG環境へデプロイ

  3. STG環境での動作確認中に、これテナント間でデータ混じってない?となり、ここでRLSポリシーの付け漏れに気がつく

幸いにも本番リリース前に発見することができましたが、もし気づかないまま本番まで上げてしまっていたら、、と思うとぞっとします

何をしたか

再発防止として、何かしらRLSポリシー設定の有無を自動で検知する仕組みは作れないかと考えました。

様々な方法が存在すると思いますが、一つの施策として、CI時に自動テスト環境用のDBに対して全テーブルのRLSポリシーの存在有無をチェックする機構を入れることにしました。

まず、対象となるDBスキーマからテナントカラムが存在するにも関わらず、RLSポリシーが付いていないテーブルを抽出するクエリを作成しました

rowsecurity=falseの(ENABLE ROW LEVEL SECURITYしていない)テーブル一覧取得するクエリ

checkRlsEnable.sql
select
    t.schemaname,
    t.tablename
from
    pg_tables as t
    INNER JOIN (
        select
            table_schema,
            table_name
        from
            information_schema.columns
        where
            column_name = 'tenant_id'  # 自システムのカラム命名に合わせる必要があります
    ) AS c ON t.schemaname = c.table_schema
    and t.tablename = c.table_name
WHERE
    t.rowsecurity = false;

policynameが付与されていないテーブル一覧取得するクエリ

checkRlsPolicy.sql
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 = 'tenant_id'  # 自システムのカラム命名に合わせる必要があります
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
      and p.policyname is null  # p.policyname not in ('tenant_policy', 'admin_policy')とかでもOK
order by
    t.table_schema;

これらのクエリをshell経由でCI実行させました

TypeORMとGithub Actionsの例

checkRlsNotActiveTable.sh
## (nodejsで書いてもいいですが)

set -e

rls_query_result=$(psql -t -h 127.0.0.1 -d hoge -p 5432 -U postgres -f ./checkRlsEnable.sql)

if [[ $rls_query_result ]]; then
  echo "RLSが有効になっていないテーブルがあります"
  echo "$rls_query_result"
  exit 1
fi


policy_query_result=$(psql -t -h 127.0.0.1 -d hoge -p 5432 -U postgres -f ./checkRlsPolicy.sql)

if [[ $policy_query_result ]]; then
  echo "RLSに関するポリシーが作成されていないテーブルがあります"
  echo "$policy_query_result"
  exit 1
fi
ci_test.yaml
~~ 略 ~~

jobs:
  run-test:
    timeout-minutes: 10
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - name: run db server
         run: docker compose up postgres -d

      ~~ 略 ~~

      - name: db migration
        run: npm run migrate

      ~~ 略 ~~

      - name: check RLS
        run: bash checkRlsNotActiveTable.sh

これにて、同様のRLSのポリシーの付け漏れが起きた場合に、CIで気がつくようにできました。

RLSポリシーの剥がれ落ちへの展開

同じ要領で、CD実行時や定期バッチなどで実運用環境のDBのRLSポリシーの有効状態のチェックも実装できます。DB作業での何かのヒューマンエラーでDROP POLICYされてしまった場合などでの即時検知などに有用かと考えられます。

最後に

RLSはマルチテナントシステムにおける銀の弾丸ではなく、せっかく導入/適用していても、テナント間でデータが混ざってしまうケースが幾つかあります。

周辺技術/環境によっては、RLSを導入することでかえってテナント間のデータ混在のリスクが上がるパターンもあるかと思われ、RLSの採用は慎重に行う必要があるなと実感しています。

Discussion