PostgreSQLの行レベルセキュリティ(RLS)が実行計画に与える影響を確認してみた

2022.02.06

MAD事業部@大阪の岩田です。最近PostgreSQLの行レベルセキュリティ(Row Level Security以後RLSと表記します)を利用している案件に関わる機会がありました。個人的にRLSを利用するのが初めてだったので、RLSがクエリの実行計画やアクセスパスにどのように影響するかを確認してみました。

RLSとは?

RLSはDBユーザー等の属性を元にCRUD操作の影響を受ける対象レコードを制限できる機能です。例えばtab1というテーブルに対してRLSを有効化すると、SELECT * FROM tab1というWHERE句の指定が無いSQLを発行した場合でもDBサーバー側で自動的にRLSによるフィルタを行ってからクライアントにレコードを返却する といった動作が可能になります。RLSを利用すると、アプリケーション側でうっかりWHERE句の指定ミスがあった場合に権限の無いクライアントに対して本来取得できてはいけないレコードを返却してしまう といった事態が未然に防止できます。

この動作はマルチテナントなシステムにおけるデータの分離に有用で、AWSのブログでもSaaSプロバイダー向けのアーキテクチャとしてRLSを利用するパターンが紹介されています。

RLSはスキーマやDBごと分割するパターンに比べてメリット・デメリットがありますが、本ブログではそこには言及しません。RLSが実行計画にどのような影響を与えるか?という観点から検証していきます。

RLSが実行計画に与える影響を確認してみる

ここから実際にRLSがクエリの実行計画にどのような影響を与えるか検証していきます。まずテストには以下のような構造のテーブルを利用します。

Id tenant_name product_code order_date
1 cm Product01 2022-02-05 12:00:00
2 an Product02 2022-02-05 13:00:00
3 cm Product03 2022-02-05 14:00:00
4 an Product04 2022-02-05 15:00:00

マルチテナントなECサイトの注文データをイメージしています。tenant_nameのカラムにテナント名が入り、このカラムとDBユーザーの一致を行セキュリティポリシーよってチェックするようなイメージです。

テストデータ等の準備

それでは検証環境を準備していきましょう。今回の検証にはPostgreSQLバージョン11.11を利用しています。

まずはテーブルを作成

CREATE TABLE orders (
	id SERIAL PRIMARY KEY,
	tenant_name  text,
	product_code text,
	order_date timestamp
);
CREATE TABLE

続いてマルチテナント用のDBユーザーを作成

CREATE ROLE user01 LOGIN;
CREATE ROLE

CREATE SCHEMA "saas";
CREATE SCHEMA

GRANT USAGE ON SCHEMA "saas" TO "user01";
GRANT

GRANT ALL ON ALL TABLES IN SCHEMA "saas" TO "user01";
GRANT

GRANT ALL ON ALL SEQUENCES IN SCHEMA "saas" TO "user01";
GRANT

RLSの有効化

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE

行セキュリティポリシーの作成

CREATE POLICY orders_policy_user01 ON orders TO "user01"
    USING (tenant_name = current_user::text);
CREATE POLICY

ここまでできたらテスト用のデータを適当に投入しておきましょう

INSERT INTO orders(
    tenant_name,
    product_code,
    order_date
)
SELECT 
    'user' || LPAD(CEIL(RANDOM() * 100)::varchar, 2, '0'),
    SUBSTRING(md5(clock_timestamp()::text), 1, 5)::varchar,
    s
FROM
    generate_series(
        '2020-01-01',
        '2021-12-31', INTERVAL '1 minute'
    ) AS s;
INSERT 0 1051201

約105万件のデータが投入されました。

インデックスが無い状態で100件をSELECT

ここからは先程作成したuser01というDBユーザーに切り替えて操作を行います。まずは単純にordersテーブルの中身を100件取得してみます。

SET search_path = "saas";
SET

EXPLAIN ANALYZE
SELECT
    *
FROM
    orders
LIMIT 100;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..242.87 rows=100 width=25) (actual time=0.112..13.234 rows=100 loops=1)
   ->  Seq Scan on orders  (cost=0.00..26126.02 rows=10757 width=25) (actual time=0.082..9.955 rows=100 loops=1)
         Filter: (tenant_name = (CURRENT_USER)::text)
         Rows Removed by Filter: 10817
 Planning Time: 0.455 ms
 Execution Time: 14.960 ms
(6 rows)

インデックスが何もないのでシーケンシャルスキャンになっています。とはいえORDER BY句の指定はなく、かつLIMIT100を付けているため、100レコードだけ読み取ってさっさと終了してくれそうなクエリです。しかし、RLSによってtenant_name = CURRENT_USERのフィルタが暗黙的に実行されるため、1万件以上のレコードがスキャンされています。Rows Removed by Filter: 10817の出力を見れば分かりますね。

テナント名にインデックスを貼った状態で100件SELECT

RLSによって暗黙的に付与される条件tenant_name = CURRENT_USERで利用できるようにテナント名のカラムにインデックスを追加し、再度SQLを実行してみます。

CREATE INDEX idx1 ON orders (tenant_name);
CREATE INDEX
EXPLAIN ANALYZE
SELECT
    *
FROM
    orders
LIMIT 100;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..239.19 rows=100 width=25) (actual time=0.186..5.517 rows=100 loops=1)
   ->  Index Scan using idx1 on orders  (cost=0.43..25683.82 rows=10757 width=25) (actual time=0.150..1.741 rows=100 loops=1)
         Index Cond: (tenant_name = (CURRENT_USER)::text)
 Planning Time: 1.082 ms
 Execution Time: 7.296 ms
(5 rows)

今度はインデックスを使う実行計画に変わっています。先程よりも効率的に100レコードが取得できることが分かります。

※この例ではインデックスが利用できていますが、色々なパターンを検証する中で行セキュリティポリシーにtenant_name = current_userを指定した場合はDBサーバー側が暗黙的に付与するフィルタが(tenant_name::name = CURRENT_USER)となるケースがありました。行セキュリティポリシーの指定は明示的にtenant_name = current_user::textとtext型にキャストしておくのが無難でしょう

order_dateにインデックスを貼った状態でorder_dateの範囲を指定して上位100件SELECT

今度はもう少し実務で使いそうなクエリを試してみましょう。指定した期間の受注データ上位100件を抽出するようなクエリです。

SELECT
    *
FROM
    orders
WHERE
    order_date BETWEEN '2021-12-01' AND '2021-12-31'
ORDER BY
		order_date,
		id
LIMIT 100;

order_dateで対象データを絞り込みたいので、order_dateにインデックスを貼ってみます

CREATE INDEX idx2 ON orders (order_date);
CREATE INDEX

この状態で先ほどのクエリを実行してみます

EXPLAIN ANALYZE
SELECT
    *
FROM
    orders
WHERE
    order_date BETWEEN '2021-12-01' AND '2021-12-31'
ORDER BY
		order_date,
		id
LIMIT 100;
                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1902.63..1902.88 rows=100 width=25) (actual time=34.170..36.304 rows=100 loops=1)
   ->  Sort  (cost=1902.63..1903.68 rows=420 width=25) (actual time=34.149..34.851 rows=100 loops=1)
         Sort Key: order_date, id
         Sort Method: top-N heapsort  Memory: 32kB
         ->  Index Scan using idx2 on orders  (cost=0.43..1886.58 rows=420 width=25) (actual time=0.274..29.389 rows=433 loops=1)
               Index Cond: ((order_date >= '2021-12-01 00:00:00'::timestamp without time zone) AND (order_date <= '2021-12-31 00:00:00'::timestamp without time zone))
               Filter: (tenant_name = (CURRENT_USER)::text)
               Rows Removed by Filter: 42768
 Planning Time: 0.301 ms
 Execution Time: 37.207 ms
(10 rows)

こちらもorder_dateのインデックスは使ってるいるものの、RLSによる暗黙的なフィルタがインデックスだけでは評価できないため、無駄に多くのレコードにアクセスしていることが分かります。Rows Removed by Filter: 42768の出力から判断できますね。

tenant_name,order_dateにインデックスを貼った状態でorder_dateの範囲を指定して上位100件SELECT

今度はtenant_name, order_dateの複合インデックスを作成してからクエリを実行してみます。

まずインデックス作成

CREATE INDEX idx3 ON orders (tenant_name, order_date);
CREATE INDEX

続いてクエリの実行

]
EXPLAIN ANALYZE
SELECT
    *
FROM
    orders
WHERE
    order_date BETWEEN '2021-12-01' AND '2021-12-31'
ORDER BY
    order_date,
    id
LIMIT 100;
                                                                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1393.05..1393.30 rows=100 width=25) (actual time=14.664..18.058 rows=100 loops=1)
   ->  Sort  (cost=1393.05..1394.10 rows=420 width=25) (actual time=14.635..15.698 rows=100 loops=1)
         Sort Key: order_date, id
         Sort Method: top-N heapsort  Memory: 32kB
         ->  Bitmap Heap Scan on orders  (cost=13.79..1377.00 rows=420 width=25) (actual time=0.536..7.729 rows=433 loops=1)
               Recheck Cond: ((tenant_name = (CURRENT_USER)::text) AND (order_date >= '2021-12-01 00:00:00'::timestamp without time zone) AND (order_date <= '2021-12-31 00:00:00'::timestamp without time zone))
               Heap Blocks: exact=236
               ->  Bitmap Index Scan on idx3  (cost=0.00..13.68 rows=420 width=0) (actual time=0.436..0.446 rows=433 loops=1)
                     Index Cond: ((tenant_name = (CURRENT_USER)::text) AND (order_date >= '2021-12-01 00:00:00'::timestamp without time zone) AND (order_date <= '2021-12-31 00:00:00'::timestamp without time zone))
 Planning Time: 0.326 ms
 Execution Time: 19.441 ms
(11 rows)

今度はIndex Cond: ((tenant_name = (CURRENT_USER)::text)...の表示から分かるように、インデックスを使って効率的に対象レコードを絞り込めているのが分かります。

まとめ

ここまで見てきたように、RLSによって暗黙的に実行されるフィルタがインデックスを利用できない場合は、効率よく対象レコードを絞り込めないことが分かりました。RLSを利用する際は

  • ポリシーの評価に利用するカラムに対してインデックスを貼る必要はないか?
  • ポリシーの評価に利用しないカラムに対して設定するインデックスは、ポリシーの評価に利用するカラムとの複合インデックスにする必要はないか?

といったことを検討すると良いでしょう。

アプリケーションを実装するメンバーは、RLSによる暗黙的なフィルタの影響まで考慮した上で最適なSQLを書くように心がけ、もし必要なインデックスが不足しているようであればDB設計担当者にフィードバックしましょう。また、単体テストの段階では、実装したコードが単に仕様通りに動作することを確認するだけでなく、SQLの実行計画が意図通りになっているかまでしっかり確認しておきましょう。

参考