🧙‍♂️

RLSを手書きする時代は終わりました 🧙‍♂️

に公開
PostgreSQL
SQL
Productivity
RLS
tech

みなさん、PostgreSQLのRLS（Row Level Security）、使ってますか？
便利ですよね、RLS。アプリケーションコードでガチャガチャやるより、DBレベルでビシッとアクセス制御できる安心感。たまりません。

でも、書くの面倒くさくないですか？

CREATE POLICY ... って打ってる時、ふと「あれ、私これ前も書かなかったっけ？」とか「開発環境のRLSを本番に適用したいけど、DDLどこいった？」ってなること、ありますよね。
挙句の果てに、既存のテーブルからRLS定義を引っこ抜こうとして pg_dump して grep して... ああ、日が暮れる。

そんなあなたに朗報です。
既存のテーブルから CREATE POLICY 文を錬成する魔法（SQL） を作りました。

そもそもRLSって何？

RLS（Row Level Security）は、「お前はこの行を見ていいけど、あの行はダメ」 というのをDBが勝手にやってくれる機能です。

例えるなら、「会員制クラブの屈強なバウンサー（用心棒）」 です。
アプリ側で WHERE user_id = ... とかチマチマ書くのは、客が自分で「あ、僕ここ入っちゃダメですね」って遠慮するようなもの。
RLSなら、バウンサー（DB）が「おっと、そこから先は会員様限定だ」と無言で通せんぼしてくれます。安心感が違いますね。

魔法の呪文

これを実行するだけで、あなたの目の前に美しい CREATE POLICY 文が現れます。

SELECT
    'CREATE POLICY ' || quote_ident(p.polname) || ' ON ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || E'\n\t' ||
    CASE p.polpermissive WHEN true THEN 'AS PERMISSIVE' ELSE 'AS RESTRICTIVE' END || E'\n\t' ||
    'FOR ' ||
    CASE p.polcmd
        WHEN 'r' THEN 'SELECT'
        WHEN 'a' THEN 'INSERT'
        WHEN 'w' THEN 'UPDATE'
        WHEN 'd' THEN 'DELETE'
        WHEN '*' THEN 'ALL'
        ELSE p.polcmd::text
    END || E'\n\t' ||
    'USING (' || pg_get_expr(p.polqual, p.polrelid) || ')' ||
    CASE WHEN p.polwithcheck IS NOT NULL THEN E'\n\tWITH CHECK (' || pg_get_expr(p.polwithcheck, p.polrelid) || ')' ELSE '' END || ';'
FROM
    pg_catalog.pg_policy p
JOIN
    pg_catalog.pg_class c ON p.polrelid = c.oid
JOIN
    pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE
    c.relname = 'your_table_name'  -- <--- ここに対象のテーブル名を入れる
    AND n.nspname = 'public'; -- <--- スキーマが違う場合はここも変えてね

何が起きているのか

PostgreSQLのシステムカタログ pg_policy には、ポリシーの定義が格納されています。
これを pg_class (テーブル情報) や pg_namespace (スキーマ情報) と結合し、文字列連結で CREATE POLICY 文を再構築しているわけです。
pg_get_expr 関数がいい仕事をしています。これが内部形式の式をSQLテキストに戻してくれるのです。

使い方

  1. 上記のSQLの your_table_name を、RLSを抽出したいテーブル名に書き換えます。
  2. 実行します。
  3. 結果行に CREATE POLICY 文がズラッと並びます。
  4. コピーして、好きなところで実行します。
  5. 定時で帰ります。

おわりに

これで、RLSの移行もバックアップも怖くありません。
手書きの温かみもいいですが、自動生成の冷徹な正確さもまた一興。
浮いた時間で、美味しいコーヒーでも飲みましょう ☕

Discussion