この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
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";
GRANTRLSの有効化
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 INDEXEXPLAIN 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の実行計画が意図通りになっているかまでしっかり確認しておきましょう。
2
