PostgreSQL Row Level Security 検証

## はじめに
実務にて、以下のリプレースがありました。
- MySQL ⇨ PostgreSQL へのデータベース変更
- PostgreSQL の Row Level Security を導入

Row Level Security の概要・効果について、PostgreSQL 操作を交えながら検証手順を残しておきます。

## 目次
1. [PostgreSQL とは？](#postgresql-とは)
2. [マルチテナントデータベース とは？](#マルチテナントデータベース-とは)
3. [Row Level Security とは？](#row-level-security-とは)
4. [Row Level Security を検証](#row-level-security-を検証)
5. [参考文献](#参考文献)

## PostgreSQL とは？
- オープンソースのリレーショナルデータベース管理システム(<b>RDBMS</b>)
オープンソース
  - ソースコードが公開されていて改変できる。
  - <b>ライセンス不要</b>かつ無償で使用可能

  RDBMS
  - データを保管する箱の集まり
  - 各箱は列と行で構成された表の形式でデータを扱う
  - 表同士を関連付けることができる(<b>リレーショナルデータベース＝RDB</b>)
- データベースの<b>テーブルデータを追加、削除、更新、検索(全文検索)できる。</b>
- <b>Row Level Security </b>、トランザクション、ストアドプロシージャも使える。
- <b>無料なのに他のデータベースに機能面で劣らない。</b>

## マルチテナントデータベース とは？
```マルチテナントデータベース```は、<b>複数の企業データを１つのデータベースで管理</b>します。

<b>データベースが１つのため、金銭的コストが少なくて済む</b>メリットがある一方、企業数が数千単位になってくると以下の懸念が挙げられる。
- <b>マイグレーション</b>
  - １テーブルで管理するデータ量が膨大になりがちなため、テーブルカラムの名称変更や追加といった小規模な SQL 実行でも時間がかかってしまう
  - 機能拡張や改修によって、既存データへの影響が大きくなる
- <b>安全性</b>
  - <b>企業跨ぎでのデータ操作</b>から情報漏洩等の問題発生に繋がる可能性が高い

今回紹介する Row Level Security はこの<b>「安全性」</b>を高めてくれる機能です。

## Row Level Security とは？
```Row Level Security```は、<b>テーブルの行へのアクセスを制御</b>することができます。

例えば、以下２つのテーブルがあり、双方のテーブルで、```company_id ： 企業ID```という企業を特定できるデータを持っているとします。
- company ： 企業情報を管理
- user ： 企業に所属するユーザー情報を管理

この時、```company_id ： １```に所属するデータだけを```user```テーブルから検索したい場合、通常は以下のような SQL をイメージするかと思います。
```sql
SELECT * 
FROM user
WHERE user.company_id = 1 -- company.company_id ： 1
```
ただ仮に、WHERE条件を記述漏れがある場合は全企業のユーザー情報が見れてしまいます。

<b>Row Level Security は上記のような実装漏れ防止は勿論、テーブルデータ操作における安全性を高めてくれます。</b>

## Row Level Security を検証
では、Row Level Security をどのように適用するのか、一緒に検証していきましょう。
長いので、ここからは Row Level Security ではなく<b> RLS </b>と略させてもらいます。

### psql とは？
インタラクティブ(<b>対話</b>)にユーザー入力を PostgreSQL へ渡して結果を表示する CUI ツール
以降、ターミナルにて```psql```を使って PostgreSQL を操作していきます。

ここからは以下の流れで、RLS の効果を見ていきます。
　[1. Docker × PostgreSQL 環境を用意](#1-docker--postgresql-環境を用意)
　[2. PostgreSQL コンテナを立ち上げる](#2-postgresql-コンテナを立ち上げる)
　[3. 立ち上げた PostgreSQL コンテナ内へ入る](#3-立ち上げた-postgresql-コンテナ内へ入る)
　[4. PostgreSQL へログイン](#4-postgresql-へログイン)
　[5. データベースの作成](#5-データベースの作成)
　[6. テーブルの作成](#6-テーブルの作成)
　[7. テーブルデータの追加](#7-テーブルデータの追加)
　[8. ROW LEVEL SECURITY の有効化](#8-row-level-security-の有効化)
　[9. ロールの作成 ・ 権限付与 ・ 切り替え](#9-ロールの作成--権限付与--切り替え)
　[10. RLS を効かせたテーブルデータ操作](#10-rls-を効かせたテーブルデータ操作)

### 1. Docker × PostgreSQL 環境を用意
RLS 検証用ということで、構成はかなりシンプルです。

-
    ```dockerfile:Dockerfile
    FROM postgres:11-alpine
    ENV LANG ja_JP.utf8
    ```
    ```compose.yaml
    version: '3'
    services:
      db:
        build: .
        ports:
          - 5433:5432
        environment:
          POSTGRES_USER: admin # PostgreSQL ログインユーザー
          POSTGRES_PASSWORD: admin # PostgreSQL ログインパスワード
        tty: true # true ： コンテナを継続起動、false ： コンテナ起動後、即停止
        volumes:
          - db_data:/var/lib/postgresql/data # PostgreSQL　の DB　データ保管場所
    volumes:
      db_data: {}
    ```

### 2. PostgreSQL コンテナを立ち上げる
- 
    ```
    docker compose up -d
    ```
### 3. 立ち上げた PostgreSQL コンテナ内へ入る
```psql```を使用する(PostgreSQL 操作を行う)には、<b>コンテナ内へ入る必要があります。</b>

- 
    ```
    docker compose exec db bash
    ```
### 4. PostgreSQL へログイン
コンテナ内へ入ったので、ここからは PostgreSQL を操作していきましょう。
まずは PostgreSQL へログインします。

- 
    ```:フォーマット
    psql -U ユーザー名
    ```
    ```
    psql -U admin
    ```
※ ```psql -U ユーザー名 -d データベース名```で一気にデータベースへのログインも可能です。

### 5. データベースの作成
- <b>データベースの作成</b>
RLS 検証用のデータベースを作成します。
    ```:フォーマット
    create database データベース名;
    ```
    ```
    create database rls_verification;
    ```
    ```:実行結果
    CREATE DATABASE
    ```

- <b>データベースの一覧表示</b>
データベースが作成されたかどうかは以下で確認できます。
    ```
    psql \l
    ```
    ```rls_verification```追加されていますね。
    ```:実行結果
                                 List of databases
           Name       | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
    ------------------+-------+----------+------------+------------+-------------------
     admin            | admin | UTF8     | ja_JP.utf8 | ja_JP.utf8 | 
     postgres         | admin | UTF8     | ja_JP.utf8 | ja_JP.utf8 | 
     rls_verification | admin | UTF8     | ja_JP.utf8 | ja_JP.utf8 | 
     template0        | admin | UTF8     | ja_JP.utf8 | ja_JP.utf8 | =c/admin         +
                      |       |          |            |            | admin=CTc/admin
     template1        | admin | UTF8     | ja_JP.utf8 | ja_JP.utf8 | =c/admin         +
                      |       |          |            |            | admin=CTc/admin
    ```

### 6. テーブルの作成

- <b>データベースへの接続</b>
    ```
    \connect rls_verification
    ```
    以下は、```admin```ユーザーで```rls_verification```へ接続できたことを示しています。
    ```:実行結果
    You are now connected to database "rls_verification" as user "admin".
    ```

- <b>テーブルの作成</b>
検証用のテーブルを2つ作成します。
- 
    ```:フォーマット
    CREATE TABLE テーブル名 (
        カラム名1 serial primary key,
        カラム名2 型([データ長]) NOT NULL UNIQUE,
        ・・・
    );
    ```

  - 企業情報を管理する```company```テーブルを作成
    ```sql
    CREATE TABLE company (
        company_id serial primary key,
        company_code varchar(20) NOT NULL UNIQUE,
        company_name varchar(255) NOT NULL
    );
    ```
    ```:実行結果
    CREATE TABLE
    ```

  - 企業内のユーザー情報を管理する```user```テーブルを作成
    ```sql
    CREATE TABLE "user" (
        user_id serial primary key,
        company_id integer NOT NULL, -- row level security で using 句に利用するカラム
        user_name varchar(255) NOT NULL,
        email varchar(255) NOT NULL,
        FOREIGN KEY (company_id) REFERENCES company(company_id)
    );
    ```
    ```:実行結果
    CREATE TABLE
    ```

- <b>テーブルの一覧表示</b>
テーブルが作成されたかどうかは以下で確認できます。
```\dt```は簡易表示、```\d```は詳細表示


  - 全テーブル内容の簡易表示
    ```
    \dt
    ```
    ```:実行結果
            List of relations
     Schema |  Name   | Type  | Owner 
    --------+---------+-------+-------
     public | company | table | admin
     public | user    | table | admin
    ```

  - ```user```テーブル内容の簡易表示
    ```
    \dt user
    ```
    ```
           List of relations
     Schema | Name | Type  | Owner 
    --------+------+-------+-------
     public | user | table | admin
    ```

  - 全テーブル内容の詳細表示
  詳細表示の場合、```sequence```の情報も参照できます。
    ```
    \d
    ```
    ```:実行結果
                     List of relations
     Schema |          Name          |   Type   | Owner 
    --------+------------------------+----------+-------
     public | company                | table    | admin
     public | company_company_id_seq | sequence | admin
     public | user                   | table    | admin
     public | user_user_id_seq       | sequence | admin
    ```
<br>

  - ```user```テーブル内容の詳細表示
  テーブルを指定した場合、```Null許容```、```Indexes```、```Foreign-key```の情報も参照できます。
    ```
    \d user
    ```
    ```：実行結果
                                            Table "public.user"
       Column   |          Type          | Collation | Nullable |                Default                
    ------------+------------------------+-----------+----------+---------------------------------------
     user_id    | integer                |           | not null | nextval('user_user_id_seq'::regclass)
     company_id | integer                |           | not null | 
     user_name  | character varying(255) |           | not null | 
     email      | character varying(255) |           | not null | 
    Indexes:
        "user_pkey" PRIMARY KEY, btree (user_id)
    Foreign-key constraints:
        "user_company_id_fkey" FOREIGN KEY (company_id) REFERENCES company(company_id)
    ```

### 7. テーブルデータの追加
作成した```company```、```user```テーブルへ RLS 検証用データを追加していきます。

-
    ```:フォーマット
    INSERT INTO テーブル名 (カラム名1, カラム名2) values
    (データ1, データ２,・　・・);
    ```
- 企業データを追加
    ```sql
    INSERT INTO company (company_code, company_name) values
    ('test_code1', 'テスト株式会社１'),
    ('test_code2', 'テスト株式会社２'),
    ('test_code3', 'テスト株式会社３');
    ```
    ```：実行結果
    INSERT 0 3
    ```
- ユーザーデータを追加
    ```sql
    INSERT INTO "user" (user_id, company_id, user_name, email) values
    (1,1,'user1','user1@ex.com'),
    (2,1,'user2','user2@ex.com'),
    (3,1,'user3','user3@ex.com'),
    (4,2,'user4','user4@ex.com'),
    (5,2,'user5','user5@ex.com'),
    (6,3,'user6','user6@ex.com'),
    (7,3,'user7','user7@ex.com'),
    (8,3,'user8','user8@ex.com'),
    (9,3,'user9','user9@ex.com');
    ```
    ```：実行結果
    INSERT 0 9
    ```

- テーブルデータの一覧表示
データが追加されたかどうかは以下で確認できます。
    ```:フォーマット
    select * from テーブル名;
    ```
    ```sql
    select * from "user";
    ```
    問題なく追加されていますね。
    ```:実行結果
     user_id | company_id | user_name |    email     
    ---------+------------+-----------+--------------
           1 |          1 | user1     | user1@ex.com
           2 |          1 | user2     | user2@ex.com
           3 |          1 | user3     | user3@ex.com
           4 |          2 | user4     | user4@ex.com
           5 |          2 | user5     | user5@ex.com
           6 |          3 | user6     | user6@ex.com
           7 |          3 | user7     | user7@ex.com
           8 |          3 | user8     | user8@ex.com
           9 |          3 | user9     | user9@ex.com
    (9 rows) 
    ```
    

### 8. ROW LEVEL SECURITY の有効化
次に```user```テーブルに対して<b> RLS を適用するための設定</b>を行い、テーブル行へのアクセスを制御します。</b>

- <b>RLS の有効化</b>
    ```:フォーマット
    ALTER TABLE テーブル名 ENABLE ROW LEVEL SECURITY;
    ```
    ```sql
    ALTER TABLE "user" ENABLE ROW LEVEL SECURITY;
    ```
    ```:実行結果
    ALTER TABLE
    ```

- <b>ポリシーの作成</b>

  - <b>RLS 検証用ポリシーの作成</b>
    ```:フォーマット
    CREATE POLICY rls_policy ON テーブル名
     USING(テーブルカラム名[::text] = current_setting('app.テーブルカラム名'[::text]));
    ```
    ```sql
    CREATE POLICY rls_policy ON "user"
     USING(company_id = current_setting('app.company_id'));
    ```
    エラーになりました。
    ```:実行結果
    ERROR:  operator does not exist: integer = text
    HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
    ```

  - <b>暗黙の型変換</b>
正確には上記フォーマットにある```::text```の通り、text 扱いになっていて```USING```で指定するデータカラムは```text型```である必要があります。
    ```company_id```は```integer```型のため、ポリシー使用の際に```text```型へキャストされるよう以下を実行します。
    ```sql
    CREATE CAST (integer AS text) WITH INOUT AS IMPLICIT;
    ```
    ```:実行結果
    CREATE CAST
    ```
    再度、ポリシー作成を行うと、問題なく作成できました。
    ```:実行結果
    CREATE POLICY
    ```

- <b>テーブル情報を参照</b>
    ```
    \d user
    ```
    ```user```テーブルの情報を見てみると、RLS 用ポリシーの情報が追加されています。
    ```：実行結果
                                                Table "public.user"
       Column   |          Type          | Collation | Nullable |                Default                
    ------------+------------------------+-----------+----------+---------------------------------------
     user_id    | integer                |           | not null | nextval('user_user_id_seq'::regclass)
     company_id | integer                |           | not null | 
     user_name  | character varying(255) |           | not null | 
     email      | character varying(255) |           | not null | 
    Indexes:
        "user_pkey" PRIMARY KEY, btree (user_id)
    Foreign-key constraints:
        "user_company_id_fkey" FOREIGN KEY (company_id) REFERENCES company(company_id)
    Policies:
        POLICY "rls_policy"
          USING (((company_id)::text = current_setting('app.company_id'::text)))
    ```
<b>※ RLS の有効化設定とポリシーによって、テーブルの行へのアクセス制御が可能となります。</b>


### 9. ロールの作成 ・ 権限付与 ・ 切り替え
次は、ロールの作成・権限付与・切り替えです。
```ロール```と言われて最初は混乱しましたが、要は```ユーザー```のことです。

- <b>ロールの一覧表示</b>
    ```
    \du
    ```
    現状は```admin```のみですね。
    ```:実行結果
                                           List of roles
     Role name |                         Attributes                         | Member of 
    -----------+------------------------------------------------------------+-----------
     admin     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    ```
    

- <b>RLS 検証用ロールの作成(ログイン権限付与)</b>
ロール作成手順も兼ねてということで、別途ロールを用意します。
<b>※ 管理者ロールのまま進めても問題ありません。</b>
    ```:フォーマット
    CREATE ROLE ロール名 WITH 与える操作権限(※WITH以降は記述なしでもOK);
    ```
    ```sql
    CREATE ROLE rls_test WITH LOGIN;
    ```
    ```:実行結果
    CREATE ROLE
    ```

- <b>ロールの一覧表示、操作権限の確認</b>
ロールが作成されたかどうかは以下で確認できます。
    ```
    \du
    ```
    ```rls_test```追加されていますね。
    ```:実行結果
                                       List of roles
     Role name |                         Attributes                         | Member of 
    -----------+------------------------------------------------------------+-----------
     admin     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
     rls_test  |                                                            | {}
    ```
    ちなみに、以下で確認することも可能です。
    ```
    select rolname, rolcreatedb from pg_roles;
    ```

-  <b>ロールへの操作権限付与</b>
```rls_test```ロールへ```user```テーブルの全ての操作権限を付与します。
    ```sql
    GRANT ALL PRIVILEGES ON "user" TO rls_test;
    ```
    ```:実行結果
    GRANT
    ```
    
- <b>ロールの切り替え</b>
```admin``` ⇨ ```rls_test```へロールの切り替えを行います。

    ```:フォーマット
    \connect - ロール名
    ```
    ```
    \connect - rls_test
    ```
    ```user```の部分が、```admin```から```rls_test```へ切り替わりました。
    ```:実行結果
    You are now connected to database "rls_verification" as user "rls_test".
    ```

### 10. RLS によるテーブルデータ操作
これで```user```テーブルへ RLS が適用されました。
この後、CRUD 動作を見ていきましょう！と言いたい所ですが、１つ準備が必要です。


- <b>テーブル行へのアクセスを制御するデータをセッション値としてセット</b>
  ```set_config```は、<b>SQL の```SET```コマンドと同じ</b>です。
    ```:フォーマット
    SELECT set_config('app.テーブルカラム名', カラムデータ, 「カラムデータ」のパラメータ設定);
     
    「カラムデータ」のパラメータ設定(true / false)
      ・ true ： カラムデータを現在のトランザクションに適用
      ・ false ： カラムデータを現在のセッションに適用
    ```
    ```sql
    SELECT set_config('app.company_id', 1, false);
    ```
    ```:実行結果
     set_config 
    ------------
     1
    (1 row)
    ```
<b>※以降、```user```テーブルについては```company_id ： 1```に紐づくデータのみにしかアクセスできません。```set_config```によって```user```テーブルの行へのアクセスを制御しました。</b>


では、実際に RLS による CRUD 動作を見ていきましょう。
<br>

-  <b>データ検索</b>
    ```:フォーマット
    SELECT * FROM テーブル名;
    ```
    ```sql
    SELECT * FROM "user";
    ```
　```WHERE```句なしで、```user```データの絞り込み検索がされました。
　検索されたのは、<b>```set_config```でセットした```company_id ： １```のデータのみ</b>です。

- 
    ```:実行結果
     user_id | company_id | user_name |    email     
    ---------+------------+-----------+--------------
           1 |          1 | user1     | user1@ex.com
           2 |          1 | user2     | user2@ex.com
           3 |          1 | user3     | user3@ex.com
    (3 rows)
    ```
<br>

-  <b>データ追加</b>
    ```:フォーマット
    INSERT INTO テーブル名 (カラム名1, カラム名2) values
    (データ1, データ２,・　・・);
    ```
    追加データの```company_id```と```set_config```によるセッション値が同じ場合、<b>登録成功</b>
    ```sql
    INSERT INTO "user" (user_id, company_id, user_name, email) values
    (10, 1,'user1','user10@ex.com');
    ```
    ```:実行結果
    INSERT 0 1
    ```
    追加データの```company_id```と```set_config```によるセッション値が異なる場合、<b>RLS エラー</b>
    
    ```sql
    INSERT INTO "user" (user_id, company_id, user_name, email) values
    (11, 3,'user11','user11@ex.com');
    ```
    ```:実行結果
    ERROR:  new row violates row-level security policy for table "user"
    ```
<br>

-  <b>データ更新</b>
    ```:フォーマット
    UPDATE テーブル名 SET カラム名 = カラムデータ WHERE 条件式;
    ```
    更新データの```company_id```と```set_config```によるセッション値が同じ場合、<b>更新成功</b>
    ```sql
    UPDATE "user" SET user_name = 'user10' WHERE user_id = 10;
    ```
    ```:実行結果
    UPDATE 1
    ```
    更新データの```company_id```と```set_config```によるセッション値が異なる場合、<b>更新無効</b>
    ```sql
    UPDATE "user" SET user_name = 'user9Up' WHERE user_id = 9;
    ```
    ```:実行結果
    UPDATE 0
    ```
<br>

-  <b>データ削除</b>
    ```:フォーマット
    DELETE　FROM テーブル名 WHERE 条件式
    ```
    削除データの```company_id```と```set_config```によるセッション値が同じ場合、<b>削除成功</b>
    ```sql
    DELETE FROM "user" WHERE user_id = 10;
    ```
    ```:実行結果
    DELETE 1
    ```
    削除データの```company_id```と```set_config```によるセッション値が異なる場合、<b>削除無効</b>
    
    ```sql
    DELETE FROM "user" WHERE user_id = 9;
    ```
    ```:実行結果
    DELETE 0
    ```

## 参考文献
<a href="https://products.sint.co.jp/siob/blog/postgresql">PostgreSQLとは？特徴や最新バージョンの機能を紹介！</a>
<a href="https://times.hrbrain.co.jp/entry/postgresql-row-level-security">PostgreSQLのRow Level Securityを使ってマルチテナントデータを安全に扱う</a>
<a href="https://masanyon.com/postgresql-db-cli-command-sql-list/#i-3">PostgreSQLの基本的な操作方法・コマンドまとめ</a>
<a href="https://qiita.com/6in/items/f23ead1314b9e6d2f2b7">PostgreSQLでの暗黙の型変換メモ（文字列<->数値）</a>
<a href="https://www.postgresql.jp/document/8.1/html/functions-admin.html">9.20. システム管理関数</a>
<a href="https://github.com/nao-United92/postgres-rls-verifycation" target="_blank"><img alt="Github" src="https://img.shields.io/badge/GitHub-%2312100E.svg?&style=for-the-badge&logo=Github&logoColor=white" /></a>