PostgreSQLのRow Level Securityを試す

概要

・マルチテナントのテナント分離の実現方法の１つとしてRDBMSのRow Level Securityを使う方法がある
・PostgreSQLで試してみる

環境

・macOS Catalina
・PostgreSQL 13.3

検証用データベース作成

・PostgreSQLのcreatedbコマンドを使う

$ createdb practice

・作成されたことを確認
(postgres, template0, template1はデフォルトで存在するDB)

$ psql -l
                                List of databases
   Name    |   Owner   | Encoding | Collate | Ctype |      Access privileges      
-----------+-----------+----------+---------+-------+-----------------------------
 postgres  | x.xxxxxxx | UTF8     | C       | C     | 
 practice  | x.xxxxxxx | UTF8     | C       | C     | 
 template0 | x.xxxxxxx | UTF8     | C       | C     | =c/"x.xxxxxxx"             +
           |           |          |         |       | "x.xxxxxxx"=CTc/"x.xxxxxxx"
 template1 | x.xxxxxxx | UTF8     | C       | C     | =c/"x.xxxxxxx"             +
           |           |          |         |       | "x.xxxxxxx"=CTc/"x.xxxxxxx"
(4 rows)

実行するSQLファイル作成

・データベースにログインしてから実行するSQL文を.sqlファイルに書いておく
・tenants, usersの２つのテーブルを作る
・自分が所属するテナントに属するユーザーのみ閲覧できるようにする

$ touch practice.sql

・以下を作成した practice.sql に書く

テーブル定義

-- テナントを管理するテーブルを作成
CREATE TABLE tenants (
    id serial primary key,
    tenant_id varchar(20) NOT NULL UNIQUE,
    name varchar(255) NOT NULL
);

-- 各テナント内のユーザーを管理するテーブルを作成
CREATE TABLE users (
    id serial primary key,
    -- row level securityでusing句に利用するカラム
    tenant_id varchar(20) NOT NULL,
    name varchar(255) NOT NULL,
    email varchar(255) NOT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id)
);

-- ROW LEVEL SECURITYをusersに設定
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- tenant_policyという名前のPOLICYをusersに設定
-- DBに接続する際に set_configで'app.tenant_id'を設定した上で接続する
-- 設定された'app.tenant_id'とusers.tenant_idが同じレコードのみ操作出来るようになる
CREATE POLICY tenant_policy ON users
 USING(tenant_id = current_setting('app.tenant_id'));

テストデータ挿入

INSERT INTO tenants (tenant_id,name) values
('ieo30', 'テスト①株式会社'),
('pe0b22', 'テスト②株式会社'),
('mm402pp', 'テスト③株式会社');

INSERT INTO users (company_id,identifier_name,name,email) values
(1,'ieo30','test1','[e-mail]'),
(1,'ieo30','test2','[e-mail]'),
(1,'ieo30','test3','[e-mail]'),
(2,'pe0b22','test4','[e-mail]'),
(2,'pe0b22','test5','[e-mail]'),
(3,'mm402pp','test6','[e-mail]'),
(3,'mm402pp','test7','[e-mail]'),
(3,'mm402pp','test8','[e-mail]'),
(3,'mm402pp','test9','[e-mail]');

SQL実行 (テーブルの作成とテストデータの挿入)

・該当のデータベースにログイン

$ psql practice
practice=>
(practiceという名前のデータベースにログインされている状態)

・作成したsqlファイル内のSQL文を実行

practice=> \i practice.sql
(\iで指定したsqlファイル内のSQLを実行できる)

・ちゃんと作成されたか確認

practice=> \dt
           List of relations
 Schema |   Name    | Type  |   Owner   
--------+-----------+-------+-----------
 public | tenants   | table | x.xxxxxxx
 public | users     | table | x.xxxxxxx
(2 rows)

practice=> \d users
                                                       Table "public.users"
     Column      |            Type             | Collation | Nullable |                          Default                          
-----------------+-----------------------------+-----------+----------+-----------------------------------------------------------
 id              | integer                     |           | not null | nextval('users_id_seq'::regclass)
 tenant_id       | character varying(20)       |           | not null | 
 name            | character varying(255)      |           | not null | 
 email           | character varying(255)      |           | not null | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "users_tenant_id_fkey" FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id)
Policies:
    POLICY "tenant_policy"
      USING (((tenant_id)::text = current_setting('app.tenant_id'::text)))

・tenant_policyがちゃんと設定されている

ユーザー作成、権限の付与

・デフォルトのユーザーはSUPERUSERやBYPASSRLSなど持っているため、ROW LEVEL SECURITYを無視して全レコードにアクセス出来てしまう
・実際アプリケーションからSQLを実行する場合を想定して権限を絞ったユーザー(ロール)を作成する
・作成したテーブルに対するアクセス権を、作成したロールに付与する
・ロールは、テーブル所有者でなく、SUPERUSERでなく、BYPASSRLSではない必要がある

practice=> create role test with LOGIN;
(DBにログインだけ出来るroleを作成)
practice=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 test      |                                                            | {}
 x.xxxxxxx | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
(新規作成したtestには、SuperuserやBypass RLSなどが無い)

・usersテーブルを操作する権限をtestに付与

practice=> grant insert,select,update,delete on users to test;

試す

・作成したtestロールでusersのRow Level Securityが効いているか確認

・ロールを切り替え

practice=> \connect - test
You are now connected to database "practice" as user "test".

・sqlを実行する前にset_configで'app.tenant_id'を設定(表示したいレコードのtenant_idを指定する)
・set_configの第3引数がtrueの場合は現在のトランザクションのみに適用され、falseの場合は現在のコネクションに適用される

practice=> select set_config('app.tenant_id', 'ieo30', false);
 set_config 
------------
 ieo30
(1 row)

・selectでusersを表示してみる

practice=> select * from users;
 id | tenant_id       |   name   |       email        | 
----+-----------------+----------+--------------------+
  1 | ieo30           | test1    | [e-mail]       |
  2 | ieo30           | test2    | [e-mail]       |
  3 | ieo30           | test3    | [e-mail]       |
(3 rows)

・where句で絞ってないのに、tenant_idが'app.tenant_id'に設定した値と同一のレコードのみ表示された！

まとめ

以下を行うことでRow Level Securityを使える
・テーブル作成時にRow Level Securityを設定
・current_settingの値で操作出来るレコードを絞るようPOLICYを設定しておく
・ロールをSUPERUSERでなく権限を絞ったロールに変更
・コネクション毎にcurrent_settingを設定する

RLSを使ってマルチテナントを実現するためには、
・コネクション毎にcurrent_settingを間違いなく設定する仕組み
・新規テーブル作成時に必ずRLSとPOLICYを設定する仕組み
が必要になる。
ただ、テナント毎にテーブルやDBを作成して運用するよりはだいぶ運用は楽になるかも。

参考記事

https://buildersbox.corp-sansan.com/entry/2021/05/10/110000
https://aws.amazon.com/jp/blogs/news/multi-tenant-data-isolation-with-postgresql-row-level-security/
https://dev.classmethod.jp/articles/postgresql-organize-command/
https://www.postgresql.jp/document/9.5/html/sql-createrole.html

注意点メモ

・viewへのアクセスについては、viewを作成したロールの権限で元のテーブルが参照される？みたいな話もあるので要調査。

以上。