【PostgreSQL】 Row Level Securityを試す
Contents
Row Level Securityの概要
PostgreSQLのテーブル中の各行に対してSELECT文やUPDATE文による操作実行する際に、特定の条件(セキュリティポリシー)を満たしている場合のみ操作を許可するように設定することができる。
これをRow Level Securityという。PostgreSQLのバージョン9.5から使用可能となった。
ローカルマシン上のPostgreSQLでの検証
以下の手順でRow Level Securityの動作を検証する。
- テスト用DBとテーブルの作成
- 検証用ロールの作成
- Row Level Securityの有効化とポリシー作成
- SQL実行による動作検証
テスト用DBとテーブルの作成
ローカルマシンにPostgreSQLが実行しているものとする。そのPostgreSQLにスーパーユーザで接続する。
psql -h localhost -p 5432 -U postgres
テスト用のテーブルを作成する。マルチテナントで製品を格納するテーブルを想定して以下のようにテーブルを作成しデータを投入する。
-- テーブル作成
create table tenant_product (
id int primary key ,
name text,
price int ,
tenant_name text not null ,
version int
);
-- データ投入
insert into tenant_product
values
(1, 'product1', 100, 'test_user1', 0),
(2, 'product2', 101, 'test_user1', 0),
(3, 'product3', 102, 'test_user1', 0),
(4, 'product4', 103, 'test_user2', 0),
(5, 'product5', 104, 'test_user2', 0);
検証用ロールの作成
自分自身のデータのみアクセスできることを確認するためにロールを2つ作成し、テーブルへのアクセス権限を与える。
この時点ではRow Level Securityを有効化していないため、test_user1であってもtenant_nameがtest_user2のデータへアクセス可能である。
-- ロールの作成
create role test_user1;
create role test_user2;
-- テーブルとシーケンスに対する権限を付与
grant all on all tables in schema public to test_user1;
grant all on all sequences in schema public to test_user1;
Row Level Securityの有効化とポリシー作成
Row Level Securityを有効化しカレントロールとtenant_nameが一致しているデータのみアクセス可能とするポリシーを作成する。
カレントロールはcurrent_userで取得できる。
-- Row Level Securityの有効化
alter table tenant_product enable row level security;
-- ポリシーの作成
create policy tenant_policy on tenant_product for all using (tenant_name = current_user);
動作検証(1)
ロールを切り替えてアクセスできるデータの違いを確認する。検証している環境のpostgresユーザにはBypass RLS属性が付与されている。
このため、Row Level Securityを有効化しても全データへアクセス可能である。
¥du
-- postgresユーザにBypass RLS属性が付与されていることが確認できる。
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_user1 | | {}
test_user2 | | {}
-- postgresユーザでは全てのデータが取得できる
select * from tenant_product ;
-- test_user1へ切り替える
set role test_user1;
-- tenant_nameがtest_user1のデータのみ取得できる
select * from tenant_product ;
id | name | price | tenant_name | version
----+----------+-------+-------------+---------
1 | product1 | 100 | test_user1 | 0
2 | product2 | 101 | test_user1 | 0
3 | product3 | 102 | test_user1 | 0
-- test_user2へ切り替える
set role test_user2;
-- tenant_nameがtest_user2のデータのみ取得できる
select * from tenant_product ;
id | name | price | tenant_name | version
----+----------+-------+-------------+---------
4 | product4 | 103 | test_user2 | 0
5 | product5 | 105 | test_user2 | 0
-- current_userがtest_user2の状態でtest_user1のデータの主キーで検索してもデータは取得できない
select * from tenant_product where id = 1;
id | name | price | tenant_name | version
----+------+-------+-------------+---------
(0 rows)
動作検証(2) 2つのテーブルをinner joinする
Row Level Securityを有効化した2つのテーブルをjoinした際の動作を検証する。tenant_shopテーブルを追加し
データを投入する。このテーブルと先ほどのtenant_productテーブルをinner joinした際の動作を検証する。
-- テーブル作成
create table tenant_shop (
id int primary key ,
name text,
tenant_name text not null
);
-- データ投入
insert into tenant_shop
values
(1, 'shop1', 'test_user3'),
(2, 'shop2', 'test_user1');
-- Row Level Securityの有効化
alter table tenant_shop enable row level security ;
create policy tenant_policy on tenant_shop for all using (tenant_name = current_user);
-- スーパーユーザであるpostgresで実行するとRow Level Securityに関係なく結果が取得できる
select * from tenant_product p inner join tenant_shop s on p.id = s.id;
id | name | price | tenant_name | version | id | name | tenant_name
----+----------+-------+-------------+---------+----+-------+-------------
1 | product1 | 100 | test_user1 | 0 | 1 | shop1 | test_user3
2 | product2 | 101 | test_user1 | 0 | 2 | shop2 | test_user1
-- test_user1へ切り替える
set role test_user1;
select * from tenant_product p inner join tenant_shop s on p.id = s.id;
id | name | price | tenant_name | version | id | name | tenant_name
----+----------+-------+-------------+---------+----+-------+-------------
2 | product2 | 101 | test_user1 | 0 | 2 | shop2 | test_user1
-- test_user2へ切り替える
set role test_user2;
select * from tenant_product p inner join tenant_shop s on p.id = s.id;
id | name | price | tenant_name | version | id | name | tenant_name
----+------+-------+-------------+---------+----+------+-------------
(0 rows)
動作検証(3) 2つのテーブルをleft outer joinする
tenant_productテーブルとtenant_shopをleft outer joinした際の動作を検証する。
-- test_user1へ切り替える
set role test_user1;
select * from tenant_product p left outer join tenant_shop s on p.id = s.id;
id | name | price | tenant_name | version | id | name | tenant_name
----+----------+-------+-------------+---------+----+-------+-------------
1 | product1 | 100 | test_user1 | 0 | | |
2 | product2 | 101 | test_user1 | 0 | 2 | shop2 | test_user1
3 | product3 | 102 | test_user1 | 0 | | |
-- test_user2へ切り替える
set role test_user2;
select * from tenant_product p left outer join tenant_shop s on p.id = s.id;
id | name | price | tenant_name | version | id | name | tenant_name
----+----------+-------+-------------+---------+----+------+-------------
4 | product4 | 103 | test_user2 | 0 | | |
5 | product5 | 105 | test_user2 | 0 | | |