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` に書く


### テーブル定義

```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'));
```

### テストデータ挿入

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

INSERT INTO users (company_id,identifier_name,name,email) values
(1,'ieo30','test1','test1@ex.com'),
(1,'ieo30','test2','test2@ex.com'),
(1,'ieo30','test3','test3@ex.com'),
(2,'pe0b22','test4','test4@ex.com'),
(2,'pe0b22','test5','test5@ex.com'),
(3,'mm402pp','test6','test6@ex.com'),
(3,'mm402pp','test7','test7@ex.com'),
(3,'mm402pp','test8','test8@ex.com'),
(3,'mm402pp','test9','test9@ex.com');
```

## 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    | test1@ex.com       |
  2 | ieo30           | test2    | test2@ex.com       |
  3 | ieo30           | test3    | test3@ex.com       |
(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を作成したロールの権限で元のテーブルが参照される？みたいな話もあるので要調査。

以上。