PostgreSQL 9.5 Row Level Security(基本編)
先週の金曜日にPostgreSQL 9.5-alpha2のリリースアナウンスーンが出ましたね。
さて今回のエントリもPostgreSQL 9.5新機能ネタ。"Row Level Security"
これもPostgreSQL 9.5の目玉機能の一つっぽい。
Row Level Securityってなんぞ?
これは字面のとおり、行レベルでセキュリティコントロールを行える機能。
GRANT文だと表、あるいは列に対する権限の制御を行うのだが、これは表中の行単位でSELECT/INSERT/UPDATE/DELETEの制御を行うというもの。
え?以前からPostgreSQL機能を駆使すれば似たようなことは出来たんじゃないの?というツッコミもあると思う。
例えば、行単位でフィルタリングするというのはVIEWを使えば似たようなことは出来たんじゃないかとか、トリガでも似たようなこと出来たんじゃないかとか。
今回のRow Level Securityは、こうした制御をPOLOCY定義でスマートにしようということっぽい(ちょっと自信なし)。
自分なりに使ってみて、Row Level Securityってだいたいこんな感じなのかな、というのを簡単に図示してみた。
Row Level Securityを使うためには、CREATE POLICY文でポリシーを定義する。
CREATE POLICY文では、おおまかに
- 対象となる表
- 対象となるユーザ
- 対象となる操作(ALL/SELECT/INSERT/UPDATE/DELETE)
- アクセス対象となる行のフィルタリング
を指定する。
ただ、それだけだとRow Level Securityは有効にならない。Row Level Securityを有効にするためには、対象とするテーブルに対してALTER TABLE文でRow Level Securityを有効にする必要がある(デフォルトでは無効になっている)。
使ってみた
まあ、こーゆーのは、とりあえず実際に使ってみるのが一番早い。
モデル
ユーザとして、以下の3種類のユーザ(非Superuser)を作成する。
- user_a
- user_b
- user_c
上記3ユーザがアクセスするテーブルを1つ作成する。
CREATE TABLE test (id int primary key, name text, body text, acl text[]);
aclというカラムが各ユーザがアクセス可能な行を識別するための条件となる。
(この配列に'A','B','M'という文字列が含まれているかどうかで、user_a, user_bがどうかで判別する)。
TEXT配列にしたのは特に深い意味はないw
user_mはManagerという設定なので、全テーブルアクセス可能とする。
このテーブル自体への(全ての)アクセス権限をuser_a, user_b, user_mにも付与しておく。
このテーブルにデータを挿入する。
INSERT INTO test VALUES (1, 'Project-A document', 'dummy', ARRAY['A','M']), (2, 'Project-B document', 'dummy', ARRAY['B','M']), (3, 'Project-A black-history', 'dummy', ARRAY['A']), (4, 'Project list', 'dummy', ARRAY['M']) ;
検索するとこんな感じになる。
id | name | body | acl ----+-------------------------+-------+------- 1 | Project-A document | dummy | {A,M} 2 | Project-B document | dummy | {B,M} 3 | Project-A black-history | dummy | {A} 4 | Project list | dummy | {M}
POLICY定義
以下の様なPOLICYを定義する。
- user_a はaclに'A'が含まれる行へのSELECTを許可する。
- user_b はaclに'B'が含まれる行へのSELECTを許可する。
- user_m は(aclの値に関係なく)全ての行へのSELECTを許可する。
上記のPOLICYをCREATE POLICY文で定義すると以下の様な感じになる。
CREATE POLICY a_select_test ON test FOR SELECT TO user_a USING ('A' = ANY(acl)); CREATE POLICY b_select_test ON test FOR SELECT TO user_b USING ('B' = ANY(acl)); CREATE POLICY m_select_test ON test FOR SELECT;
ALTER TABLEでPOLICYを有効化する
さて、POLICY定義を作成しただけだと、POLICYによる行レベルのアクセス制御は動作しない。
上記のPOLICY定義によって、本来はuser_aはidが1と4の行のみSELECT可能になるはずだが・・・
[nuko@localhost 9.5-rls]$ psql rls -U user_a -c "TABLE test" id | name | body | acl ----+-------------------------+-------+------- 1 | Project-A document | dummy | {A,M} 2 | Project-B document | dummy | {B,M} 3 | Project-A black-history | dummy | {A} 4 | Project list | dummy | {M} (4 rows)
アイエエエ!行レベル制御無視!ナンデ行レベル制御無視!
そりゃ、テーブルのRow Level Security機能が有効になっていないもの・・・
Row Level Securityを動作させるためには、ALTER TABLE文のactionに"ENABLE ROW LEVEL SECURITY"を指定する必要がある。
例えばこんな感じ。
ALTER TABLE test ENABLE ROW LEVEL SECURITY;
こうしてtestのRow Level Securityを有効にしておくと、以下のようにuser_a,user_bで検索対象となる行が制限される。
[nuko@localhost 9.5-rls]$ psql rls -U user_a -c "TABLE test" id | name | body | acl ----+-------------------------+-------+------- 1 | Project-A document | dummy | {A,M} 3 | Project-A black-history | dummy | {A} (2 rows)
user_b, user_mもそれぞれPOLICYに従った結果を返却する。
[nuko@localhost 9.5-rls]$ psql rls -U user_b -c "TABLE test" id | name | body | acl ----+--------------------+-------+------- 2 | Project-B document | dummy | {B,M} (1 row) [nuko@localhost 9.5-rls]$ psql rls -U user_m -c "TABLE test" id | name | body | acl ----+-------------------------+-------+------- 1 | Project-A document | dummy | {A,M} 2 | Project-B document | dummy | {B,M} 3 | Project-A black-history | dummy | {A} 4 | Project list | dummy | {M} (4 rows)
実行計画はどう見える?
で、ちょっと気になるのは実行計画。POLICY定義に記述した条件は実行計画上はどう見えるのか。
TABLE文(条件なしSELECTと同じ)をEXPLAINつきで動かしてみた。
[nuko@localhost 9.5-rls]$ psql rls -U user_a -c "EXPLAIN TABLE test" QUERY PLAN ------------------------------------------------------- Seq Scan on test (cost=0.00..24.17 rows=3 width=100) Filter: ('A'::text = ANY (acl)) (2 rows)
EXPLAINによって出力される実行計画に、CREATE POLICYで指定した条件式が展開されているのが分かる。
次の疑問は、じゃあ、POLICY定義によって記述された条件式はWHERE句より先に評価されちゃうのか?ということ。
もちろんそんなことはない。
上記のテーブルにとりあえず約10万件ほどダミーのデータを突っ込んでみる。
INSERT INTO test VALUES (generate_series(5,100000), 'dummy','dummy',ARRAY['B','M']); INSERT 0 99996
この状態で、user_aが単純にアクセスするとこんな実行計画になる。
[nuko@localhost 9.5-rls]$ psql rls -U user_a -c "EXPLAIN ANALYZE TABLE test" QUERY PLAN ----------------------------------------------------------------------------------------------------- Seq Scan on test (cost=0.00..3281.00 rows=500 width=52) (actual time=0.014..20.088 rows=2 loops=1) Filter: ('A'::text = ANY (acl)) Rows Removed by Filter: 99998 Planning time: 0.454 ms Execution time: 20.138 ms (5 rows)
グワーッ!フツーにフルスキャンしちゃいますね。
10万件くらいならそれほど気にはならないけど、件数が多いテーブルにPOLICYを設定する場合、CREATE POLICYで記述する条件式でインデックが使われるように設計するなどの考慮が必要かもしれない。
それはさておき、例えば user_a がtestにアクセスする場合、インデックスを設定された列を検索条件として設定すると、プランナはきちんとその辺を判断して、先にインデックスを使ってくれるっぽい。
[nuko@localhost 9.5-rls]$ psql rls -U user_a -c "EXPLAIN ANALYZE SELECT * FROM test WHERE id < 10" QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Index Scan using test_pkey on test (cost=0.29..8.56 rows=1 width=52) (actual time=0.012..0.015 rows=2 loops=1) Index Cond: (id < 10) Filter: ('A'::text = ANY (acl)) Rows Removed by Filter: 7 Planning time: 0.461 ms Execution time: 0.048 ms (6 rows)
特権ユーザのアクセスはどうなる?
さて、POLOCYでは、user_a, user_b, user_mのみ設定し、特権ユーザ(今の環境だとビルドしたときのOSユーザ名 nuko と後で作成した postgres)に対しては何も設定していない。
じゃあ、もしかするとPOLICYが有効になっているテーブルに対しては、特権ユーザでのアクセスも制限できるのか!?
[nuko@localhost 9.5-rls]$ psql rls -U nuko -c "\du" List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- nuko | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres | Superuser, Create role, Create DB | {} user_a | | {} user_b | | {} user_m | | {} [nuko@localhost 9.5-rls]$ psql rls -U postgres -c "TABLE test" id | name | body | acl ----+-------------------------+-------+------- 1 | Project-A document | dummy | {A,M} 2 | Project-B document | dummy | {B,M} 3 | Project-A black-history | dummy | {A} 4 | Project list | dummy | {M} (4 rows)
アバーッ!そんなことはなかったぜ・・・
psqlのメタコマンド "\du"を見ると、こんな記述がRoleのattributeに入っているようだ。
[nuko@localhost 9.5-rls]$ psql rls -U nuko -c "\du" List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- nuko | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres | Superuser, Create role, Create DB | {} user_a | | {} user_b | | {} user_m | | {}
nukoのattributeに "Bypass RLS" って書いてあるよ・・・
でも、後で追加した特権ユーザ postgres のattributeには書かれてないなあ・・・これってバグ?なのかしらん。
次回予告(なのか?)
今回は基本ということで、まずはSELECTの例を示したけど、POLOCY定義にはINSERT/UPDATE/DELETEのバリエーションもあるので、次回はそのへんを調べて書こうかと。