【PostgreSQL】Row Level Securityとリストパーティションを組み合わせを検証する
Contents
リストパーティションの作成
今までと同じようにRow Level Securityを有効化したテーブルに対してパーティションを設定します。今回はテナントごとにパーティションを作成するためにリストパーティションを設定します。パーテションの親テーブルを以下のSQLで作成します。
パーティションを使用する場合、主キーにパーティションキーを含めなくてはいけません。そのためid, tenant_nameの複合主キーとしています。
検証にはPostgreSQL11.6のDocker Imageを使用しています。
create table customer3 (
id serial,
tenant_name text, -- マルチテナントを想定したテナント名
customer_name text, -- 顧客名
customer_div int, -- 顧客区分
birthday timestamp with time zone, -- 顧客の生年月日
primary key (id, tenant_name) -- 主キーにtenant_nameを含める
) partition by list (tenant_name);
-- インデックス作成
create index index_customer3_birthday on customer3 (birthday);
-- Row Level Security有効化とポリシー設定
alter table customer3 enable row level security;
create policy tenant_policy on customer3 for all using (tenant_name = current_user);
tenant_nameが"tenant1″であるデータを格納するための子テーブルを以下のSQLで作成しデータをinsertします。
-- 子テーブル作成
create table tenant1 partition of customer3 for values in ('tenant1');
-- tenant1のデータをinsert
insert into customer3 (tenant_name, customer_name, customer_div, birthday) values ('tenant1', 'hoge1', 1, '1990-01-01');
パーティションキーに該当するテーブルが存在しない場合
次に、tenant_nameが"tenant2″であるデータをinsertしてみます。"tenant2″に対応する子テーブルが存在しないためエラーとなります。
-- tenant2のデータをinsert するとエラーとなる。
insert into customer3 (tenant_name, customer_name, customer_div, birthday) values ('tenant2', 'hoge2', 1, '1990-01-01');
ERROR: no partition of relation "customer3" found for row
DETAIL: Partition key of the failing row contains (tenant_name) = (tenant2).
対応する子テーブルが無いパーティションキーを指定された場合の保存先となる子テーブルを"default"を指定することで作成することもできます。このテーブル作成後にtenant_nameが"tenant2″であるデータをinsertしてみるとdefaultを指定したテーブルに保存されます。親テーブルであるcustomer3と子テーブルに対してselectすると以下のようになります。このselectはRow Level Securityをバイパスするpostgresロールで実行しています。
-- 該当する子テーブルが存在しない場合の保存先となるテーブル
create table tenant_default partition of customer3 default;
-- tenant2のデータをinsert
insert into customer3 (tenant_name, customer_name, customer_div, birthday) values ('tenant2', 'hoge2', 1, '1990-01-01');
-- 親テーブルを検索
select * from customer3;
id | tenant_name | customer_name | customer_div | birthday
----+-------------+---------------+--------------+------------------------
1 | tenant1 | hoge1 | 1 | 1990-01-01 00:00:00+00
4 | tenant2 | hoge2 | 1 | 1990-01-01 00:00:00+00
-- 子テーブルをそれぞれ検索
select * from tenant1;
id | tenant_name | customer_name | customer_div | birthday
----+-------------+---------------+--------------+------------------------
1 | tenant1 | hoge1 | 1 | 1990-01-01 00:00:00+00
select * from tenant_default ;
id | tenant_name | customer_name | customer_div | birthday
----+-------------+---------------+--------------+------------------------
4 | tenant2 | hoge2 | 1 | 1990-01-01 00:00:00+00
tenant2用の子テーブルを作成します。defaultのテーブルにtenant2のデータが入っている状態ではtenant2用の子テーブル作成時にエラーとなりました。そこで、テーブルを削除しtenant2用の子テーブルを作成しました。
create table tenant2 partition of customer3 for values in ('tenant2');
ERROR: updated partition constraint for default partition "tenant_default" would be violated by some row
drop table tenant_default;
create table tenant2 partition of customer3 for values in ('tenant2');
ロールを変更し実行計画を確認する
Row Level Securityをバイパスするpostgresロールとパイパスしないtenant1ロールのそれぞれで親テーブルであるcustomer3テーブルに対してselectを実行した際の実行計画を確認しました。
-- postgresロールの場合
explain analyze select * from customer3;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Append (cost=0.00..42.50 rows=1500 width=80) (actual time=1.089..2.229 rows=2 loops=1)
-> Seq Scan on tenant1 (cost=0.00..17.50 rows=750 width=80) (actual time=1.041..1.060 rows=1 loops=1)
-> Seq Scan on tenant2 (cost=0.00..17.50 rows=750 width=80) (actual time=1.061..1.080 rows=1 loops=1)
Planning Time: 11.196 ms
Execution Time: 2.500 ms
-- tenant1ロールに切り替えた場合
set role to tenant1;
select * from customer3;
id | tenant_name | customer_name | customer_div | birthday
----+-------------+---------------+--------------+------------------------
1 | tenant1 | hoge1 | 1 | 1990-01-01 00:00:00+00
explain analyze select * from customer3;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Append (cost=13.78..44.56 rows=8 width=80) (actual time=0.095..0.130 rows=1 loops=1)
Subplans Removed: 1
-> Bitmap Heap Scan on tenant1 (cost=13.78..22.26 rows=4 width=80) (actual time=0.073..0.103 rows=1 loops=1)
Recheck Cond: (tenant_name = (CURRENT_USER)::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on tenant1_pkey (cost=0.00..13.78 rows=4 width=0) (actual time=0.044..0.052 rows=1 loops=1)
Index Cond: (tenant_name = (CURRENT_USER)::text)
Planning Time: 7.316 ms
Execution Time: 2.084 ms
(9 rows)
データを追加し実行計画を確認する
テナント10個分の子テーブルを作成し各テナントに20000件ずつデータをinsertした後、実行計画を確認してみました。
create table tenant3 partition of customer3 for values in ('tenant3');
create table tenant4 partition of customer3 for values in ('tenant4');
create table tenant5 partition of customer3 for values in ('tenant5');
create table tenant6 partition of customer3 for values in ('tenant6');
create table tenant7 partition of customer3 for values in ('tenant7');
create table tenant8 partition of customer3 for values in ('tenant8');
create table tenant9 partition of customer3 for values in ('tenant9');
create table tenant10 partition of customer3 for values in ('tenant10');
insert into customer3 (
tenant_name,
customer_name,
customer_div,
birthday
)
select
case -- 10個のテナント名を生成
when mod(i, 10) = 0 then 'tenant1'
when mod(i, 10) = 1 then 'tenant2'
when mod(i, 10) = 2 then 'tenant3'
when mod(i, 10) = 3 then 'tenant4'
when mod(i, 10) = 4 then 'tenant5'
when mod(i, 10) = 5 then 'tenant6'
when mod(i, 10) = 6 then 'tenant7'
when mod(i, 10) = 7 then 'tenant8'
when mod(i, 10) = 8 then 'tenant9'
when mod(i, 10) = 9 then 'tenant10'
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, 200000, 1) as i;
explain analyze select * from customer3;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..4829.55 rows=20010 width=34) (actual time=0.743..594.764 rows=20000 loops=1)
Subplans Removed: 9
-> Seq Scan on tenant1 (cost=0.00..517.02 rows=20001 width=34) (actual time=0.725..230.365 rows=20000 loops=1)
Filter: (tenant_name = (CURRENT_USER)::text)
Planning Time: 57.375 ms
Execution Time: 777.260 ms
Row Level Security、パーティションキーとなる列を関数で設定する
insert時にはDB接続に使用するロール名がtenant_name列に自動で入るようにしたかったので、トリガーを
設定したかったのですが、親テーブルに対してBEFORE FOR EACH ROWを指定したトリガーは作成できないようです。子テーブルには作成できますが、SQLの実行対象はあくまで親テーブルとし子テーブルの存在は意識したくありません。
-- パーティションを設定した親テーブルにトリガーは作成できない
create trigger insert_tenant_name before insert on customer3 for each row execute procedure insert_tenant_name();
ERROR: "customer3" is a partitioned table
DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.
-- 子テーブルには作成できる。
create trigger insert_tenant_name before insert on tenant1 for each row execute procedure insert_tenant_name();
親テーブルのtenant_nameのデフォルト値を関数で設定するようにテーブル定義を変更すると、やりたいことが実現できました。
create table customer3 (
id serial,
tenant_name text default insert_tenant_name2() not null, -- マルチテナントを想定したテナント名, 関数で値を入れる
customer_name text, -- 顧客名
customer_div int, -- 顧客区分
birthday timestamp with time zone, -- 顧客の生年月日
primary key (id, tenant_name)
) partition by list (tenant_name);
-- current_user名を"_"で区切って前半部をtenant_nameにするための関数、"_"が含まれない場合はcurrent_user名をtenant_nameとする
create function insert_tenant_name2() returns text as $insert_tenant_name2$
begin
if position('_' in current_user) > 0 then
return plit_part(current_user, '_', 1);
end if;
return current_user;
end;
$insert_tenant_name2$ LANGUAGE plpgsql;
参考情報