【PostgreSQL】Row Level Securityを有効化したテーブルでSQLの実行計画を検証する
Contents
本記事の目的
Row Level Securityを有効化したテーブルに対して、Bypass RLS属性を持つロールと持たないロールでSQLの実行計画がどのように変わるかを確認する。
検証用のテーブルとロールの準備
検証にはPostgreSQL11.6のDocker Imageを使用する。
適当なデータベースを作成した後、以下のテーブルを作成しデータを投入する。また、Row Levele Security検証用のロールとしてtenant1, tenant2を作成する。
-- 顧客データ用テーブル
create table customer (
id int primary key, -- 主キー
tenant_name text, -- マルチテナントを想定したテナント名
customer_name text, -- 顧客名
customer_div int, -- 顧客区分
birthday timestamp with time zone -- 顧客の生年月日
);
-- birthday にインデックス追加
create index index_customer_birthday on customer (birthday);
-- Row Level Security有効化
alter table customer enable row level security;
-- ポリシー作成
create policy tenant_policy on customer for all using (tenant_name = current_user);
-- データ投入
insert into customer (
id,
tenant_name,
customer_name,
customer_div,
birthday
)
select
i,
case -- 2つのテナント名を生成
when mod(i, 2) = 0 then 'tenant1'
when mod(i, 2) = 1 then 'tenant2'
end,
left(md5(i::text), 10),
mod(i, 5), -- 顧客区分値として0~4 を生成
timestamp '1970-01-01 00:00:00' + random() * (timestamp '2000-12-31 23:59:59' - timestamp '1970-01-01 00:00:00')
from
generate_series(1, 20000, 1) as i;
-- ロール作成
create role tenant1;
create role tenant2;
-- テーブルへのアクセス権限を付与
grant all on all tables in schema public to tenant1;
grant all on all tables in schema public to tenant2;
インデックスをはった列を検索条件にしてSQLの実行計画を確認する
まず、作成したテーブルに対するいくつかのSQLの実行計画を確認する。テーブル情報は以下の通りである。
¥d customer
-- テーブル情報
Table "public.customer"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
id | integer | | not null |
tenant_name | text | | |
customer_name | text | | |
customer_div | integer | | |
birthday | timestamp with time zone | | |
Indexes:
"customer_pkey" PRIMARY KEY, btree (id)
"index_customer_birthday" btree (birthday)
Policies:
POLICY "tenant_policy"
USING ((tenant_name = (CURRENT_USER)::text))
SQLの実行計画をロールを変更しながら確認する。件数取得のためのcount(*)とデータ取得のためのSELECT文の実行計画を確認する。
-- postgresロールで実行する
set role to postgres;
-- テーブルの全件数を取得する
explain analyze select count(*) from customer;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=417.00..417.01 rows=1 width=8) (actual time=430.217..430.259 rows=1 loops=1)
-> Seq Scan on customer (cost=0.00..367.00 rows=20000 width=0) (actual time=1.325..216.269 rows=20000 loops=1)
Planning Time: 3.361 ms
Execution Time: 430.357 ms
-- テーブル中のid < 1000であるデータ数を取得する
explain analyze select count(*) from customer where id < 1000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=44.27..44.28 rows=1 width=8) (actual time=28.947..28.979 rows=1 loops=1)
-> Index Only Scan using customer_pkey on customer (cost=0.29..41.77 rows=999 width=0) (actual time=3.215..16.359 rows=999 loops=1)
Index Cond: (id < 1000)
Heap Fetches: 999
Planning Time: 1.984 ms
Execution Time: 29.238 ms
-- tenant1ロールで実行する
set role to tenant1;
-- テーブルの全件数を取得する
explain analyze select count(*) from customer;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=542.00..542.01 rows=1 width=8) (actual time=242.763..242.781 rows=1 loops=1)
-> Seq Scan on customer (cost=0.00..517.00 rows=10000 width=0) (actual time=0.880..126.436 rows=10000 loops=1)
Filter: (tenant_name = (CURRENT_USER)::text)
Rows Removed by Filter: 10000
Planning Time: 38.436 ms
Execution Time: 242.900 ms
-- tenant1ロールで実行する
-- テーブル中のid < 1000であるデータ数を取得する
explain analyze select count(*) from customer where id < 1000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=50.51..50.52 rows=1 width=8) (actual time=13.082..13.100 rows=1 loops=1)
-> Index Scan using customer_pkey on customer (cost=0.29..49.26 rows=500 width=0) (actual time=0.051..7.688 rows=499 loops=1)
Index Cond: (id < 1000)
Filter: (tenant_name = (CURRENT_USER)::text)
Rows Removed by Filter: 500
Planning Time: 9.376 ms
Execution Time: 13.458 ms
(7 rows)
tenant1ロールで実行した場合はRow Level Securityによりフィルタされていることがわかる。次に、where句の条件にbirthdayを入れてみる。
-- postgresロールで実行する
set role to postgres;
-- birthdayの期間を指定して件数取得
explain analyze select count(*) from customer where '1990-01-01' <= birthday and birthday < '1991-01-01';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=194.71..194.72 rows=1 width=8) (actual time=28.020..28.038 rows=1 loops=1)
-> Bitmap Heap Scan on customer (cost=15.46..192.96 rows=700 width=0) (actual time=9.240..19.556 rows=705 loops=1)
Recheck Cond: (('1990-01-01 00:00:00+00'::timestamp with time zone <= birthday) AND (birthday < '1991-01-01 00:00:00+00'::timestamp with time zone))
Heap Blocks: exact=162
-> Bitmap Index Scan on index_customer_birthday (cost=0.00..15.29 rows=700 width=0) (actual time=6.187..6.196 rows=705 loops=1)
Index Cond: (('1990-01-01 00:00:00+00'::timestamp with time zone <= birthday) AND (birthday < '1991-01-01 00:00:00+00'::timestamp with time zone))
Planning Time: 9.195 ms
Execution Time: 29.591 ms
-- 検索条件は同じでデータ取得
explain analyze select * from customer where '1990-01-01' <= birthday and birthday < '1991-01-01';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on customer (cost=15.46..192.96 rows=700 width=35) (actual time=0.514..6.761 rows=705 loops=1)
Recheck Cond: (('1990-01-01 00:00:00+00'::timestamp with time zone <= birthday) AND (birthday < '1991-01-01 00:00:00+00'::timestamp with time zone))
Heap Blocks: exact=162
-> Bitmap Index Scan on index_customer_birthday (cost=0.00..15.29 rows=700 width=0) (actual time=0.467..0.474 rows=705 loops=1)
Index Cond: (('1990-01-01 00:00:00+00'::timestamp with time zone <= birthday) AND (birthday < '1991-01-01 00:00:00+00'::timestamp with time zone))
Planning Time: 4.083 ms
Execution Time: 15.421 ms
-- tenant1ロールで実行する
set role to tenant1;
-- birthdayの期間を指定して件数取得
TestDB=> explain analyze select count(*) from customer where '1990-01-01' <= birthday and birthday < '1991-01-01';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=199.00..199.01 rows=1 width=8) (actual time=17.614..17.637 rows=1 loops=1)
-> Bitmap Heap Scan on customer (cost=15.38..198.12 rows=350 width=0) (actual time=0.604..11.542 rows=364 loops=1)
Recheck Cond: (('1990-01-01 00:00:00+00'::timestamp with time zone <= birthday) AND (birthday < '1991-01-01 00:00:00+00'::timestamp with time zone))
Filter: (tenant_name = (CURRENT_USER)::text)
Rows Removed by Filter: 341
Heap Blocks: exact=162
-> Bitmap Index Scan on index_customer_birthday (cost=0.00..15.29 rows=700 width=0) (actual time=0.339..0.352 rows=705 loops=1)
Index Cond: (('1990-01-01 00:00:00+00'::timestamp with time zone <= birthday) AND (birthday < '1991-01-01 00:00:00+00'::timestamp with time zone))
Planning Time: 8.467 ms
Execution Time: 20.555 ms
-- 件削除条件は同じでデータ取得
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on customer (cost=15.38..198.12 rows=350 width=35) (actual time=0.161..3.906 rows=364 loops=1)
Recheck Cond: (('1990-01-01 00:00:00+00'::timestamp with time zone <= birthday) AND (birthday < '1991-01-01 00:00:00+00'::timestamp with time zone))
Filter: (tenant_name = (CURRENT_USER)::text)
Rows Removed by Filter: 341
Heap Blocks: exact=162
-> Bitmap Index Scan on index_customer_birthday (cost=0.00..15.29 rows=700 width=0) (actual time=0.104..0.111 rows=705 loops=1)
Index Cond: (('1990-01-01 00:00:00+00'::timestamp with time zone <= birthday) AND (birthday < '1991-01-01 00:00:00+00'::timestamp with time zone))
Planning Time: 46.065 ms
Execution Time: 7.737 ms
ここでもインデックスによる絞り込みが行われた後に、Row Level Securityによるフィルタが行われていることが確認できる。
インデックスの無い列を検索条件にしてSQLの実行計画を確認する
-- postgresロールで実行する
set role to postgres;
-- customer_divを条件にして検索
explain analyze select * from customer where customer_div = 1 or customer_div = 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on customer (cost=0.00..467.00 rows=7200 width=35) (actual time=0.791..109.671 rows=8000 loops=1)
Filter: ((customer_div = 1) OR (customer_div = 2))
Rows Removed by Filter: 12000
Planning Time: 13.492 ms
Execution Time: 199.784 ms
-- tenant1ロールで実行する
set role to tenant1;
-- customer_divを条件にして検索
explain analyze select * from customer where customer_div = 1 or customer_div = 2;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on customer (cost=0.00..617.00 rows=3600 width=35) (actual time=3.495..76.216 rows=4000 loops=1)
Filter: (((customer_div = 1) OR (customer_div = 2)) AND (tenant_name = (CURRENT_USER)::text))
Rows Removed by Filter: 16000
Planning Time: 4.240 ms
Execution Time: 120.831 ms
シーケンシャルスキャンの条件にRow Level SecurityがAND条件で追加されていることが確認できる。