技術本部 Bill One Engineering Unit 共通認証基盤チームの古石です。
Bill One 開発 Unit ブログリレー2024の記念すべき第一弾なのですが、少々ニッチな話になります。ごめんなさい。
共通認証基盤では、DBにPostgreSQLを使用しています。
そして、テーブルにRow Level Security（以降RLS）を適用し、テナントIDのチェックを行うことでテナント間のデータ分離を堅牢にしています。
ところで皆さんは、
RLSを適用している環境で、クエリのパフォーマンスが期待外れに低下した経験はありませんか？
特に LIKE / ILIKE で検索を行う際、INDEXが適切に使われないことがありませんか？
私はあります。先月それで悩まされていました。
検索すれば原因は特定できるような内容だったのですが、まとめているページなどが少なかったので、ここで備忘録として残しておこうと思います。
前提として
PostgreSQLのバージョンは15.4を使用しています。
説明するにあたり、以下のような検証用スキーマ、テーブル、ユーザーを用意します。
テナント数件はかつemailにASCII文字しか入らない想定でemailカラムにGIN(gin_trgm_ops)INDEXを使います。
他にもGiSTやB-Treeでも再現するはずです。
-- 検証用にスキーマ作成 CREATE SCHEMA example; GRANT ALL ON SCHEMA example TO postgres; SET search_path TO example; -- 検証用にテーブル作成 CREATE TABLE user_account ( user_account_id UUID PRIMARY KEY, tenant_id VARCHAR(20) NOT NULL, email VARCHAR(254) UNIQUE NOT NULL ); CREATE INDEX idx_user_account_tenant_id_email_btree ON user_account USING btree(tenant_id, email); CREATE INDEX idx_user_account_email_gin ON user_account USING gin (email gin_trgm_ops); -- 検証用テーブルにRLSポリシーを設定 ALTER TABLE user_account ENABLE ROW LEVEL SECURITY; CREATE POLICY user_account_rls_policy_tenant_id ON user_account USING (tenant_id = current_setting('app.tenant_id')::VARCHAR); -- 検証用にユーザーを追加 CREATE USER example_app WITH LOGIN PASSWORD 'example_app'; GRANT USAGE ON SCHEMA example to example_app; GRANT SELECT ON user_account TO example_app;
データは多めに100万件ぐらい入れておきます。
私のローカルではINSERTに10秒強かかりました。
-- ダミーデータを大量に投入 INSERT INTO user_account (user_account_id, tenant_id, email) SELECT gen_random_uuid(), 'dummy-tenant', 'user-' || i || '@example.com' FROM generate_series(1, 1000000) AS s(i); -- 念の為統計情報更新 VACUUM ANALYSE user_account;
これで準備は完了です。
わたしのクエリ、遅すぎ...？
クエリはemailの部分一致検索を行うとてもシンプルなものとします。
イメージとしては、システム管理者がユーザー検索を行うようなものでしょうか。
条件は部分一致ですが、前方一致や後方一致でも再現します。
SELECT * FROM user_account WHERE email ILIKE '%3333%';
EXPLAIN ANALYZEで実行計画を見てみましょう。
Bitmap Heap Scan on user_account (cost=24.77..396.48 rows=100 width=51) (actual time=0.362..3.693 rows=280 loops=1)
Recheck Cond: ((email)::text ~~* '%3333%'::text)
Rows Removed by Index Recheck: 3420
Heap Blocks: exact=1028
-> Bitmap Index Scan on idx_user_account_email_gin (cost=0.00..24.75 rows=100 width=0) (actual time=0.248..0.248 rows=3700 loops=1)
Index Cond: ((email)::text ~~* '%3333%'::text)
Planning Time: 0.127 ms
Execution Time: 3.720 ms
Execution Time: 3.720 ms 速いですね。
...と思ってしまいますが、実はRLSがバイパスされるpostgresなどの特権ユーザーで実行しています。
検証時に忘れがちなので気をつけましょう。
というわけで先ほど作った検証用のexample_appユーザーで実行してみます。
SELECT set_config('app.tenant_id', 'dummy-tenant', FALSE); EXPLAIN ANALYZE SELECT * FROM user_account WHERE email ILIKE '%3333%';
実行計画を見ると...
-- 検証用example_appユーザーでの実行計画
Gather (cost=1000.00..19401.67 rows=8000 width=51) (actual time=4.623..283.478 rows=280 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on user_account (cost=0.00..17601.67 rows=3333 width=51) (actual time=4.030..276.665 rows=93 loops=3)
Filter: (((tenant_id)::text = current_setting('app.tenant_id'::text)) AND ((email)::text ~~* '%3333%'::text))
Rows Removed by Filter: 333240
Planning Time: 0.112 ms
Execution Time: 283.513 ms
Execution Time: 283.513 ms 見事にINDEXが使われなくなっていますね。
Parallel Seq Scanになっていて、本番環境であれば負荷的にも危険です。
そういえば特権ユーザーで実行した際にtenant_idを条件に入れていなかったので、tenant_idを入れて再度試してみましたが、INDEXは適切に使われました。
EXPLAIN ANALYZE SELECT * FROM user_account WHERE tenant_id = current_setting('app.tenant_id') and email ILIKE '%3333%';
-- 特権ユーザーでの実行計画
Bitmap Heap Scan on user_account (cost=24.77..396.98 rows=100 width=51) (actual time=0.378..3.669 rows=280 loops=1)
Recheck Cond: ((email)::text ~~* '%3333%'::text)
Rows Removed by Index Recheck: 3420
Filter: ((tenant_id)::text = current_setting('app.tenant_id'::text))
Heap Blocks: exact=1028
-> Bitmap Index Scan on idx_user_account_email_gin (cost=0.00..24.75 rows=100 width=0) (actual time=0.262..0.262 rows=3700 loops=1)
Index Cond: ((email)::text ~~* '%3333%'::text)
Planning Time: 0.151 ms
Execution Time: 3.697 ms
また、pg_hint_planを入れてヒント句でINDEXを明示したうえで検証用exmaple_appユーザーでクエリを実行しても、実行計画は変わりませんでした。
-- これは効果がない /*+ BitmapScan(user_account idx_user_account_email_gin) */
RLSの影響が疑わしい
特権ユーザーと検証用ユーザーのクエリの条件の違いはRLSの適用有無ぐらい、さらにヒント句が効かないとなると、これはもうRLSが影響しているだろう...
と考え検索していたら同じ事象の人がいました。
どうやらLIKE / ILIKE検索で用意したINDEXが使用されない理由は、LIKE / ILIKEで使用される演算子がLEAKPROOFではないからだそうです。
LEAKPROOFとは
公式ドキュメントのCREATE FUNCTIONの項目に詳しく記載がありました。
LEAKPROOFは、関数が副作用を持たないことを示します。 その引数に関する情報を戻り値以外で漏らしません。 例えば、一部の引数値に対してのみエラーメッセージを返す関数や何らかのエラーメッセージの中に引数の値を含める関数は漏洩防止（leakproof）とはいえません。 これはsecurity_barrierオプション付きで作成されたビュー、あるいは行単位セキュリティが有効にされたテーブルに対して、システムが問い合わせを実行する方法に影響します。 データが偶然に露見することを防ぐため、システムは、漏洩防止でない関数を含む問い合わせのユーザが提供した条件より前に、セキュリティポリシーおよびセキュリティバリアビューの条件を強制します。 漏洩防止であるとされた関数および演算子は信頼できると見なされ、セキュリティポリシーおよびセキュリティバリアビューによる条件より先に実行されることがあります。
引用元: https://www.postgresql.jp/docs/15/sql-createfunction.html
ざっくりと要約すると、演算子のLEAKPROOFがtrueの場合はRLSのフィルタリングより先に実行できるが、falseの場合はRLSのフィルタリングが先に適用される、ということのようです。
何らかのエラーメッセージの中に引数の値を含める関数という部分は、サイドチャネル攻撃などのリスクを考慮しているようです。
ではLIKE/ILIKEがLEAKPROOFがfalseなのか確認してみましょう。
-- 実行計画に出てくる演算子からコードを特定（~~がLIKE、~~*がILIKE） SELECT oprname, oprcode FROM pg_operator WHERE oprname in ('~~', '~~*') ORDER BY oprname, oprcode;
| oprname | oprcode |
|---|---|
| ~~ | textlike |
| ~~ | namelike |
| ~~ | bpcharlike |
| ~~ | bytealike |
| ~~* | texticlike |
| ~~* | nameiclike |
| ~~* | bpchariclike |
-- 今回はtextの部分一致なのでtextlike, texticlikeで調べる SELECT proname, proleakproof FROM pg_proc WHERE proname IN ('textlike', 'texticlike');
| proname | proleakproof |
|---|---|
| textlike | false |
| texticlike | false |
結果、確かにproleakproofはfalseになっていました。
ということは、RLSのフィルタリングが先に実行されてしまい、用意したINDEXが使用されない可能性が高そうです。
ちなみに、pg_procを眺めてLEAKPROOFなオペレータを調べたところ、概ね= / != / < / <= / > / >= のような単純な演算子が LEAKPROOFに設定されているようでした。
それ以外は LEAKPROOFではないというわけです。
PostgreSQL標準のLEAKPROOFの選定方針については詳細を調べきれなかったのですが、直近のPostgreSQLメーリングリストで、関数インデックスとLEAKPROOFに関する相談があり、その中で言及されていました。
内容としては、upper、lower、textlikeといった文字列処理系の関数を標準でLEAKPROOFに指定できないか、という議論でした。
しかし、これらの関数は入力データによってエラーメッセージが変わる可能性があり、その情報が攻撃者に利用されるリスクを懸念していました。（上述した公式ドキュメントのLEAKPROOFの説明と一致しています。）
PostgreSQLの開発者としてはわずかなリスクでも考慮して、標準でLEAKPROOFに指定することには慎重であるべきという方針のようです。
LEAKPROOFではないことが原因だった
では、本当にLEAKPROOFが影響しているか確認しましょう。
Stack Overflowのスレッドによると、LEAKPROOF属性はALTER FUNCTIONで更新できるようなので、trueにした上で再度実行計画を取得してみます。
ALTER FUNCTION textlike LEAKPROOF; ALTER FUNCTION texticlike LEAKPROOF; -- 戻す時は -- ALTER FUNCTION textlike NOT LEAKPROOF; -- ALTER FUNCTION texticlike NOT LEAKPROOF;
そして検証用のexample_appユーザーでクエリを実行します。
SELECT set_config('app.tenant_id', 'dummy-tenant', FALSE); SELECT * FROM user_account WHERE email ILIKE '%3333%';
-- texticlikeをLEAKPROOFにした状態で、検証用example_appユーザーでの実行計画
Bitmap Heap Scan on user_account (cost=24.77..396.98 rows=100 width=51) (actual time=0.387..3.089 rows=280 loops=1)
Recheck Cond: ((email)::text ~~* '%3333%'::text)
Rows Removed by Index Recheck: 3420
Filter: ((tenant_id)::text = current_setting('app.tenant_id'::text))
Heap Blocks: exact=1028
-> Bitmap Index Scan on idx_user_account_email_gin (cost=0.00..24.75 rows=100 width=0) (actual time=0.287..0.287 rows=3700 loops=1)
Index Cond: ((email)::text ~~* '%3333%'::text)
Planning Time: 0.181 ms
Execution Time: 3.144 ms
Execution Time: 3.144 ms ...改善しました。INDEXが使われなかった原因はLEAKPROOFで間違いないようです。
ちなみに、部分一致ではts_vector型でts_queryを条件に使うこともあると思いますが、演算子的にはLEAKPROOFではないため、LEAKPROOFにしない限りは同様にパフォーマンス劣化の懸念があるかと思われます。
対策はどうすればいい？
先ほど挙げたようにALTER FUNCTIONでLEAKPROOFにすれば、適切なINDEXが使われるようになりパフォーマンスは改善します。
しかし、サイドチャネル攻撃などのリスクを許容することになるため、組織や開発チームとして正しく検討する必要があるかと思われます。
私の場合は、今回のようなクエリで実現している検索機能が必要かどうかを見直し、機能自体を削除しました。
他にも（根本的な解決ではないので要注意ですが）以下のような対策も考えられます。
- RLSが適用されないMaterializedViewを作る
- SECURITY DEFINER を適用したFunctionを作成し、特権ユーザーで実行する
- テーブルにRLSポリシーを適用しない
- テナントのデータ分離の仕組みを考え直す
要件とコストが見合うのであれば外部検索エンジンを使うという選択もあるかと思います。
まとめ
LIKE/ILIKEの内部的な処理がLEAKPROOFではないため、RLSのフィルタが優先的に適用され、狙ったINDEXが効かないことがある。LEAKPROOFではない処理は、エラーメッセージの中に入力情報が入る可能性がある。ALTER FUNCTIONでLEAKPROOFにすれば解消するが、根本的な対策にはならない。他の対策には一工夫必要。
スッキリと解決するには至らなかったのですが、本記事が同じ事象で悩める方の足がかりにでもなれば幸いです。
最後まで読んでいただきありがとうございました。
次回、Bill One 開発 Unit ブログリレー2024 vol.2は組織として、定期的に品質向上と改善に向けた取り組みをどのように推進しているかです。
ブログリレーの最新の投稿についてはXアカウント @SansanTech でお知らせしますので、フォローよろしくお願い致します。
