Go to Qiita Advent Calendar 2024 Top
LoginSignup
29
12

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

PostgreSQL 10全部ぬこAdvent Calendar 2017Day 23
@nuko_yokohama(nuko yokohama)

Row Level Securityの変更

Last updated at Posted at 2017-12-22

はじめに

にゃーん
この記事は、PostgreSQL 10全部ぬこ Advent Calendar 2017 の23日目のエントリです。

Row Level Security(RLS)とは

Row Level Security(RLS)というのは、名前のとおり行レベルでの権限制御を行うもの。

RLS_01.png

RLSを使うことで、上の図にあるように、ユーザ別にアクセス可能な行を指定することが可能になった。

この機能はPostgreSQL 9.5から使えるようになった。
通常のデータベースオブジェクトに対する権限制御は、GRANT/REVOKEコマンドを使うが、RLSはCREATE/ALTER/DROP POLICYコマンドを使う。

「別にRLSを使わなくても、ユーザ別にVIEWを定義すれば同じようなことはできるんじゃあないか？」という話もあるが、RLSではポリシー定義という考え方で、操作別/ロール別に細かく行単位の権限制御を分かりやすく設定できる。
(実際には細かいレベルでの可視性の話もあるのだが、ここでは省略)

RLSの例(おさらい)

おさらいの意味で、上の図でしめしたような行レベルの権限制御を行う簡単な例を示す。

RLSの設定例

まず、以下のようなテーブルを作成して、データを4件挿入しておく。(今回、この操作は特権ユーザで実施している。

rls# CREATE TABLE test (id int primary key, data text, acl text[]);
CREATE TABLE
rls# INSERT INTO test VALUES
  (1, 'aaa', ARRAY['user_a']),
  (2, 'bbb', ARRAY['user_a','user_b']),
  (3, 'ccc', ARRAY[]::text[]),
  (4, 'ddd', ARRAY['user_b'])
;

aclというTEXT配列を使って、今回は行レベルの制御を行ってみる。

ポリシーの定義

さらに以下のようなポリシーをtestテーブルに対して定義する。

rls=# CREATE POLICY manager_select_test ON test
  FOR SELECT
  TO PUBLIC
  USING (current_user = 'manager');
CREATE POLICY

上記のCREATE POLICYで生成したポリシー manager_select_test は、

  • testテーブルに対して
  • SELECT操作を
  • 全てのユーザについて、
  • 以下の条件を満たす行のみを検索対象とする
    • 自分のユーザ名が、'manager'である

というポリシーを定義している。
(実際には、TO句でmanagerロールを指定するほうが普通だと思うが、複数ポリシーの適用例を示すために、こんな例にしてみた)

さらに以下のようなポリシーをtestテーブルに対して定義する。

rls=# CREATE POLICY public_select_test ON test
  FOR SELECT
  TO PUBLIC
  USING (current_user = ANY(acl));
CREATE POLICY

上記のCREATE POLICYで生成したポリシー public_select_test は、

  • testテーブルに対して
  • SELECT操作を
  • 全てのユーザについて、
  • 以下の条件を満たす行のみを検索対象とする
    acl列(配列)のどれかに一致する

ポリシーの確認

作成したポリシーは、psql の \d メタコマンドや、 \dp メタコマンドで確認できる。

rls=# \d test
                Table "public.test"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 data   | text    |           |          | 
 acl    | text[]  |           |          | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
Policies (row security disabled):
    POLICY "manager_select_test" FOR SELECT
      USING ((CURRENT_USER = 'manager'::name))
    POLICY "public_select_test" FOR SELECT
      USING (((CURRENT_USER)::text = ANY (acl)))

rls=# \dp
                                             Access privileges
 Schema | Name | Type  | Access privileges | Column privileges |                 Policies                  
--------+------+-------+-------------------+-------------------+-------------------------------------------
 public | test | table |                   |                   | manager_select_test (r):                 +
        |      |       |                   |                   |   (u): (CURRENT_USER = 'manager'::name)  +
        |      |       |                   |                   | public_select_test (r):                  +
        |      |       |                   |                   |   (u): ((CURRENT_USER)::text = ANY (acl))
(1 row)

ポリシーの有効化

ポリシーは定義しただけでは有効でない。ポリシーを有効にするためには、ALTER TABLEコマンドで、そのテーブルに対する ROW LEVEL SECURITYを有効にする必要がある。

rls=# ALTER TABLE test ENABLE ROW LEVEL SECURITY ;
ALTER TABLE

また、テーブルレベルでは、全てのロールに対して、全ての操作を許容するという漢設定をしておく。

rls=# GRANT ALL on test TO PUBLIC ;
GRANT

これで準備完了。

実行例

上記の設定を行ったテストテーブルに対して、一般ユーザ manager, user_a, user_b の3ユーザでログインして、全レコードを検索するTABLE testを実行してみる。

  • manager
[nuko@localhost pg10]$ psql rls -U manager -c "TABLE test"
 id | data |       acl       
----+------+-----------------
  1 | aaa  | {user_a}
  2 | bbb  | {user_a,user_b}
  3 | ccc  | {}
  4 | ddd  | {user_b}
(4 rows)

managerは全ての行を検索できる。

  • user_a
[nuko@localhost pg10]$ psql rls -U user_a -c "TABLE test"
 id | data |       acl       
----+------+-----------------
  1 | aaa  | {user_a}
  2 | bbb  | {user_a,user_b}
(2 rows)

user_aはaclに’user_a'を含む行のみ検索対象となる。

  • user_b
[nuko@localhost pg10]$ psql rls -U user_b -c "TABLE test"
 id | data |       acl       
----+------+-----------------
  2 | bbb  | {user_a,user_b}
  4 | ddd  | {user_b}
(2 rows)

user_bはaclに’user_b'を含む行のみ検索対象となる。

今度は、EXPLAINをつけて、どんなRLS設定が有効な場合の挙動を確認してみる。

[nuko@localhost pg10]$ psql rls -U user_a -c "EXPLAIN TABLE test"
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..37.62 rows=8 width=68)
   Filter: (((CURRENT_USER)::text = ANY (acl)) OR (CURRENT_USER = 'manager'::name))
(2 rows)

この実行計画を見ると、以下のことが分かる。

  • POLICY定義のUSINGで設定した条件が、Filterとして設定されている。
  • 複数のPOLICY定義の条件はORで接続される。

RLS設定の改善

少し前置きが長くなってしまったが、PostgreSQL 10では、このRLSの設定をより柔軟に行えるような改善が行われた。
PostgreSQL 9.6とPostgreSQL 10ではRLSの設定方法に違いがある。

  • PostgreSQL 9.6までのCREATE POLICY構文
CREATE POLICY name ON table_name
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]
  • PostgreSQL 10のCREATE POLICY構文
CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

PostgreSQL 10では、**[ AS { PERMISSIVE | RESTRICTIVE } ]**という指定が追加された。
ざっくりいうと、

  • PERMISSIVEは「アクセスを許可する設定の追加」、
  • RESTRICTIVEは「アクセスを制限する設定の追加」

ということになる。
ASで始まるこの句は省略も可能であり、そのときの挙動は、PERMISSIVEと同じになる。

さて、複数のPERMISSIVEのポリシーとRESTRICTIVEのポリシーが、同じテーブル/操作/ユーザに対して設定されたらどうなるのか。
あるテーブル/操作/ユーザが同じで、かつ複数のポリシー(a, b, c, d)が以下のように設定されたとする。

  • ポリシーa は PERMISSIVE
  • ポリシーb は RESTRICTIVE
  • ポリシーc は PERMISSIVE
  • ポリシーd は RESTRICTIVE

この場合、ポリシー全体としては、以下のように評価される。

(ポリシーa OR ポリシーb) AND ポリシーb AND ポリシーd

RESTRICTIVEの例

突然だが、皆さんは、チャイナ・ミエヴィルの小説「都市と都市」をご存知だろうか。
（最初はちょっととっつきにくいけど、それさえ我慢すれば面白くなる。おすすめ）

ふたつの都市国家〈ベジェル〉と〈ウル・コーマ〉は、欧州において地理的にほぼ同じ位置を占めるモザイク状に組み合わさった特殊な領土を有していた。

さらに面白いのは、〈ベジェル〉市民は〈ウル・コーマ〉の出来事が見えない（見えても見えないふりをしなければならない、逆も同様）、というルールの元に生活をしているという設定だ。

「なんじゃ、その荒唐無稽な設定は？」と思うかもしれないが、日本にも似たような都市がある。そう、町田市だ。

s-machida.jpg

みっつの都市〈東京都町田市〉と〈神奈川県町田市〉と〈神奈川県相模原市町田区〉は、東京都・神奈川県境において地理的に同じ位置を占める特殊な領土を有していた。

〈東京都町田市〉の市民は、〈神奈川県町田市〉と〈神奈川県相模原市町田区〉で起きていることは見えない。（見えているとしても「見えない」）

まあ、そういう設定だとする。
この町田市を例にRESTRICTIVE句の効果を説明してみる。

ポリシーの定義

まず、こんなテーブルを定義して、以下のようなデータを投入する。

rls=# CREATE table machida (id int primary key, pref text, city text, area text, data text);
CREATE TABLE
rls=# GRANT ALL ON public.machida TO PUBLIC ;
GRANT
rls=# INSERT INTO machida VALUES
(1, '東京都', '町田市', NULL, '町田駅'),
(2, '神奈川県', '町田市', NULL, '町田駅'),
(3, '神奈川県', '相模原市', '町田区', '町田駅'),
(4, '東京都', '町田市', NULL, 'リス園'),
(5, '神奈川県', '町田市', NULL, 'Ｙカメラ'),
(6, '神奈川県', '相模原市', NULL, 'スパ銭 町田店')
;
INSERT 0 6

このデータベースクラスタには、tokyo, machida, sagamihara の3種類のロールが存在する。
各ロールのmachidaテーブルに対するポリシーを以下のように設定する。

  • 〈東京都町田市〉の市民(tokyo)はpref列が「東京都」の行しか見えない。
  • 〈神奈川県町田市〉の市民(kanagawa)はpref列が「神奈川県」の行しか見えない。
  • 〈神奈川県相模原市町田区〉の区民(sagamihara)はpref列が「神奈川県」かつ、city列が「相模原市」の行しか見えない。

SQLコマンドで書くと、こんな感じになる。

rls=# CREATE POLICY select_tokyo_machida ON machida FOR SELECT TO tokyo USING (pref = '東京都');
CREATE POLICY
rls=# CREATE POLICY select_kanagawa_machida ON machida FOR SELECT TO kanagawa USING (pref = '神奈川県');
CREATE POLICY
rls=# CREATE POLICY select_sagamihara_machida_1 ON machida AS PERMISSIVE FOR SELECT TO sagamihara USING (pref = '神奈川県');
CREATE POLICY
rls=# CREATE POLICY select_sagamihara_machida_2 ON machida AS RESTRICTIVE FOR SELECT TO sagamihara USING (city = '相模原市');
CREATE POLICY

machidaユーザには2つのポリシーが設定されている。

  • select_sagamihara_machida_1 ポリシーはPERMISSIVEで設定する。
  • select_sagamihara_machida_2 ポリシーはRESTRICTIVEで設定する。

machidaテーブルの定義を \dp メタコマンドで確認する。

rls=# \dp machida
                                                     Access privileges
 Schema |  Name   | Type  |     Access privileges     | Column privileges |                    Policies
--------+---------+-------+---------------------------+-------------------+------------------------------------------------
 public | machida | table | postgres=arwdDxt/postgres+|                   | select_tokyo_machida (r):                     +
        |         |       | =arwdDxt/postgres         |                   |   (u): (pref = '東京都'::text)                +
        |         |       |                           |                   |   to: tokyo                                   +
        |         |       |                           |                   | select_kanagawa_machida (r):                  +
        |         |       |                           |                   |   (u): (pref = '神奈川県'::text)              +
        |         |       |                           |                   |   to: kanagawa                                +
        |         |       |                           |                   | select_sagamihara_machida_1 (r):              +
        |         |       |                           |                   |   (u): (pref = '神奈川県'::text)              +
        |         |       |                           |                   |   to: sagamihara                              +
        |         |       |                           |                   | select_sagamihara_machida_2 (RESTRICTIVE) (r):+
        |         |       |                           |                   |   (u): (city = '相模原市'::text)              +
        |         |       |                           |                   |   to: sagamihara
(1 row)

各ユーザの実行結果

tokyo, kanagawa, sagamiharaの各ロールでログインして、machidaテーブルを参照する。

[nuko@localhost ~]$ psql -U tokyo rls -c "TABLE machida"
 id |  pref  |  city  | area |  data
----+--------+--------+------+--------
  1 | 東京都 | 町田市 |      | 町田駅
  4 | 東京都 | 町田市 |      | リス園
(2 rows)

[nuko@localhost ~]$ psql -U kanagawa rls -c "TABLE machida"
 id |   pref   |   city   |  area  |     data
----+----------+----------+--------+---------------
  2 | 神奈川県 | 町田市   |        | 町田駅
  3 | 神奈川県 | 相模原市 | 町田区 | 町田駅
  5 | 神奈川県 | 町田市   |        | Ｙカメラ
  6 | 神奈川県 | 相模原市 |        | スパ銭 町田店
(4 rows)

[nuko@localhost ~]$ psql -U sagamihara rls -c "TABLE machida"
 id |   pref   |   city   |  area  |     data
----+----------+----------+--------+---------------
  3 | 神奈川県 | 相模原市 | 町田区 | 町田駅
  6 | 神奈川県 | 相模原市 |        | スパ銭 町田店
(2 rows)

ポリシーに従った行のみが参照されたことが分かる。

sagamiharaロールでmachidaを検索するときの実行計画を見てみる。

[nuko@localhost ~]$ psql -U sagamihara rls -c "EXPLAIN TABLE machida"
                             QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on machida  (cost=0.00..17.65 rows=1 width=132)
   Filter: ((city = '相模原市'::text) AND (pref = '神奈川県'::text))
(2 rows)

machidaテーブル、かつ、sagamiahraロールに設定した2つのポリシー(select_sagamihara_machida_1, select_sagamihara_machida_2)で指定された条件をAND接続で評価していることがわかる。

RLSの影響を受けるクエリ最適化の改善

PostgreSQL 10では、POLICY定義によって追加されたconditionを含むクエリを、より賢く処理できるようになった（らしい）。
本当は、PostgreSQL 9.6とPostgreSQL 10を比較して、より適切な実行計画を生成する例も出したかったが、良い例を考える時間が足りなかった。無念。

おわりに

  • PostgreSQL 10からは、RESTRICTIVE句により、複数のポリシーの評価をAND接続で評価することが可能になった。
  • 町田は神奈川。

参考：該当するリリースノート

本エントリに関連するPostgreSQL 10リリースノートの記載です。

E.2.3.1.4. Optimizer

  • Improve performance of queries affected by row-level security restrictions (Tom Lane)

E.2.3.4. Utility Commands

  • Allow restrictive row-level security policies (Stephen Frost)
29
12
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
29
12

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?