Hatena::ブログ(Diary)

日々の記録 別館

2015-08-16

PostgreSQL 9.5 Row Level Security(複数POLICY定義)

今日のエントリはRow Level Securityの小ネタ。
同一テーブル/同一ロール/同一操作に対して、複数のPOLICY定義を書いたらどうなるのっと。

元のテーブル

元のテーブルはこんな感じ。
(RLSをBYPASSする)superuserで検索するとこんなレコードが格納されている。

rls=# TABLE workflow;
 id | jobname |  status  
----+---------+----------
  1 | job_a   | checking
  2 | job_b   | working
  4 | job_d   | complete
  3 | job_c   | working
  5 | job_e   | new
(5 rows)

まあ、なんかタスクを管理するようなテーブルだと思ってください。

で、このテーブルに、empロールがSELECTするときのPOLICY定義を以下のように設定しておく。

CREATE POLICY emp_select_new ON workflow FOR SELECT TO emp USING  (status = 'new');
CREATE POLICY emp_select_working ON workflow FOR SELECT TO emp USING  (status = 'working');
CREATE POLICY emp_select_checking ON workflow FOR SELECT TO emp USING  (status = 'checking');

psqlから \d コマンドでテーブルに設定されたPOLICYが確認できる。

rls=# \d workflow
    Table "public.workflow"
 Column  |  Type   | Modifiers 
---------+---------+-----------
 id      | integer | not null
 jobname | text    | 
 status  | text    | 
Indexes:
    "workflow_pkey" PRIMARY KEY, btree (id)
Policies:
    POLICY "emp_select_checking" FOR SELECT
      TO emp
      USING ((status = 'checking'::text))
    POLICY "emp_select_new" FOR SELECT
      TO emp
      USING ((status = 'new'::text))
    POLICY "emp_select_working" FOR SELECT
      TO emp
      USING ((status = 'working'::text))

ちなみに、 \dp コマンドだと"Access privileges"も一緒に確認できる。参考までに。

rls=> \dp workflow 
                                           Access privileges
 Schema |   Name   | Type  | Access privileges | Column privileges |              Policies              
--------+----------+-------+-------------------+-------------------+------------------------------------
 public | workflow | table | nuko=arwdDxt/nuko+|                   | emp_select_new (r):               +
        |          |       | boss=arwdDxt/nuko+|                   |   (u): (status = 'new'::text)     +
        |          |       | emp=arwdDxt/nuko  |                   |   to: emp                         +
        |          |       |                   |                   | emp_select_working (r):           +
        |          |       |                   |                   |   (u): (status = 'working'::text) +
        |          |       |                   |                   |   to: emp                         +
        |          |       |                   |                   | emp_select_checking (r):          +
        |          |       |                   |                   |   (u): (status = 'checking'::text)+
        |          |       |                   |                   |   to: emp
(1 row)

empで検索すると・・・

で、empロールでrlsデータベースログインして、workflowテーブルを検索してみる。

[nuko@localhost 9.5-rls]$ psql rls -U emp -c "TABLE workflow"
 id | jobname |  status  
----+---------+----------
  1 | job_a   | checking
  2 | job_b   | working
  3 | job_c   | working
  5 | job_e   | new
(4 rows)

この結果から推測すると、3つ設定したPOLICY定義のUSING句に設定したconditionをOR接続しているのではないかと。
EXPLAINをとってみると

[nuko@localhost 9.5-rls]$ psql rls -U emp -c "EXPLAIN TABLE workflow"
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Seq Scan on workflow  (cost=0.00..24.88 rows=13 width=68)
   Filter: ((status = 'working'::text) OR (status = 'new'::text) OR (status = 'checking'::text))
(2 rows)

のように、やっぱりOR接続されている。
つまり、今回の例の3つのPOLICY定義は、以下のようなINを使った1つのPOLICY定義とだいたい同じ。

CREATE POLICY emp_select ON workflow FOR SELECT TO emp USING  (status IN ('new','working','checking'));

上記のPOLICY定義の結果を試してみる。

[nuko@localhost 9.5-rls]$ psql rls -U emp -c "\d workflow"
    Table "public.workflow"
 Column  |  Type   | Modifiers 
---------+---------+-----------
 id      | integer | not null
 jobname | text    | 
 status  | text    | 
Indexes:
    "workflow_pkey" PRIMARY KEY, btree (id)
Policies:
    POLICY "emp_select" FOR SELECT
      TO emp
      USING ((status = ANY (ARRAY['new'::text, 'working'::text, 'checking'::text])))

[nuko@localhost 9.5-rls]$ psql rls -U emp -c "TABLE workflow"
 id | jobname |  status  
----+---------+----------
  1 | job_a   | checking
  2 | job_b   | working
  3 | job_c   | working
  5 | job_e   | new
(4 rows)

[nuko@localhost 9.5-rls]$ psql rls -U emp -c "EXPLAIN TABLE workflow"
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on workflow  (cost=0.00..21.69 rows=13 width=68)
   Filter: (status = ANY ('{new,working,checking}'::text[]))
(2 rows)

[nuko@localhost 9.5-rls]$ 

(´-`).oO(そういえば、INって、ANY (配列) に展開されちゃうんだっけ・・・)

まあ、ちょっとクエリの形は違うけど、同一の効果になる。

INSERT/UPDATEのWITH CHECK

POLICY定義には、INSERT/UPDATE操作専用の条件を指定する、WITH CHECKという句もある。
これは、挿入しようとする/更新しようとする行の値を条件を定義するもので、条件が偽の場合には、その挿入/更新は実行されない。

ということで、こんなUPDATE用のPOLICY定義を用意する。

CREATE POLICY emp_update_new2working ON workflow FOR UPDATE TO emp USING (status = 'new') WITH CHECK (status = 'working');
CREATE POLICY emp_update_new2checking ON workflow FOR UPDATE TO emp USING (status = 'new') WITH CHECK (status = 'checking');

これは、

  • statusが 'new' の行に対して statsusを 'working' に変更する操作を許容する定義
  • statusが 'new' の行に対して statsusを 'checking' に変更する操作を許容する定義

になる。

で、empで検索したときに、

[nuko@localhost 9.5-rls]$ psql rls -U emp -c "TABLE workflow"
 id | jobname |  status  
----+---------+----------
  1 | job_a   | checking
  2 | job_b   | working
  3 | job_c   | working
  5 | job_e   | new
(4 rows)

現状はこうなっているので、これに対してUPDATEをかけてみる。

ちなみにidが1,2,3の行はそもそもUPDATE POLICYのUSINGで対象になっていないので、更新操作自体が発生しない(ように見える)。

[nuko@localhost 9.5-rls]$ psql rls -U emp -c "UPDATE workflow SET status = 'complete' WHERE id = 1"
UPDATE 0

EXPLAIN ALANYZEをとると、いろんなことが見えてくる。

[nuko@localhost 9.5-rls]$ psql rls -U emp -c "EXPLAIN ANALYZE UPDATE workflow SET status = 'complete' WHERE id = 1"
                                                                      QUERY PLAN                                                                     
 
-----------------------------------------------------------------------------------------------------------------------------------------------------
-
 Update on workflow workflow_1  (cost=0.15..8.19 rows=1 width=42) (actual time=0.027..0.027 rows=0 loops=1)
   ->  Subquery Scan on workflow  (cost=0.15..8.19 rows=1 width=42) (actual time=0.027..0.027 rows=0 loops=1)
         ->  LockRows  (cost=0.15..8.18 rows=1 width=42) (actual time=0.027..0.027 rows=0 loops=1)
               ->  Index Scan using workflow_pkey on workflow workflow_2  (cost=0.15..8.17 rows=1 width=42) (actual time=0.025..0.025 rows=0 loops=1)
                     Index Cond: (id = 1)
                     Filter: (status = 'new'::text)
                     Rows Removed by Filter: 1
 Planning time: 0.412 ms
 Execution time: 0.102 ms
(9 rows)
  • primary keyによるIndexScan結果にFilterで'new'かどうかFilterをかけ、結果として適用対象から1件削除される(0件になる)。
  • なので、実行時のrowsも0になる。推定時には1だけど。

・・・ちょい脱線しちゃった。
ではid=5の行(statusは'new)のstatsusを'complete'に変更しようとする。
これは、2つのUPDATEののPOLICYに書いた条件に合致しないのでエラーになる。

rls=> BEGIN; UPDATE workflow SET status = 'complete' WHERE id = 5;ROLLBACK;
BEGIN
ERROR:  new row violates row level security policy for "workflow"
ROLLBACK

これが、'working'や'checking'になるとUPDATEは1件成功する。
(実際に値を変更すると後の復旧が面倒なので、今回はBEGIN〜ROLLBACKで囲んで、実際に更新しないようにしている)

rls=> BEGIN; UPDATE workflow SET status = 'working' WHERE id = 5;ROLLBACK;
BEGIN
UPDATE 1
ROLLBACK
rls=> BEGIN; UPDATE workflow SET status = 'checking' WHERE id = 5;ROLLBACK;
BEGIN
UPDATE 1
ROLLBACK
rls=> 


ただ、EXPLAIN結果には、USINGの条件は出てくるが、WITH CHECKの条件は出てこない・・・ので、挙動からそう推定しただけではある。

rls=> BEGIN; EXPLAIN ANALYZE UPDATE workflow SET status = 'checking' WHERE id = 5;ROLLBACK;
BEGIN
                                                                      QUERY PLAN                                                                     
 
-----------------------------------------------------------------------------------------------------------------------------------------------------
-
 Update on workflow workflow_1  (cost=0.15..8.19 rows=1 width=42) (actual time=0.039..0.039 rows=0 loops=1)
   ->  Subquery Scan on workflow  (cost=0.15..8.19 rows=1 width=42) (actual time=0.023..0.024 rows=1 loops=1)
         ->  LockRows  (cost=0.15..8.18 rows=1 width=42) (actual time=0.022..0.023 rows=1 loops=1)
               ->  Index Scan using workflow_pkey on workflow workflow_2  (cost=0.15..8.17 rows=1 width=42) (actual time=0.014..0.015 rows=1 loops=1)
                     Index Cond: (id = 5)
                     Filter: (status = 'new'::text)
 Planning time: 0.110 ms
 Execution time: 0.082 ms
(8 rows)

ROLLBACK

ということで・・・

実は、POLICY定義のINSERT規則やUPDATE規則を組み合わせて、DDLだけでワークフローっぽいことできないかなーと思っていたけど、
複数の(同一テーブル/同一ロール/同一操作)に対するPOLICY定義のUSINGやWITH CHECKがOR接続されちゃうと、ワークフローっぽいチェックは難しそうだなーと思って諦めることになったのであった。
本当は、'new'のときなら、このWITH CHECK、'working'のときならこのWITH CHECK、とかやりたかったけど、簡単にはできそうにないな・・・。