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、とかやりたかったけど、簡単にはできそうにないな・・・。
