文脈、背景や問題点の説明

マルチテナントを実装するうえで企業情報（以下company）単位で最小限の情報を扱うようにしたいがcompany単位にTableを作ったりDatabaseを作るのはALTERなどの運用が大変。そこでRLSを採用するために実際の技術検証をした上での注意点と実際の運用について必要な情報をまとめる。

PostgreSQL 14を前提としている

公式ドキュメント

CREATE POLICY

必ず一読はすること。困ったとき、わからないときはまずは公式ドキュメントを都度見ること。

このドキュメントのゴール

RLSの概要をつかめる
RLSの最低限の注意点を理解し、実装時に罠を踏まない
自分たちでRLSのポリシー自体をメンテナンスすることができ、デバッグできる

テーブル構成

create table if not exists company
(
    id         uuid                     default gen_random_uuid() not null
        primary key,
    created_at timestamp with time zone default now()             not null,
    name       text                                               not null
);

create table if not exists organization
(
    id         uuid                     default gen_random_uuid() not null
        primary key,
    created_at timestamp with time zone default now()             not null,
    name       text
);

create index if not exists organization_created_at_index
    on organization (created_at);


create table if not exists organization_chart
(
    id                     bigint generated by default as identity (maxvalue 2147483647)
        constraint id
            primary key,
    company_id             uuid not null
        constraint organization_chart_company_id_fk
            references company,
    parent_organization_id uuid not null
        constraint organization_chart_organization_id_fk_2
            references organization,
    organization_id        uuid not null
        constraint organization_chart_organization_id_fk
            references organization
);

実装方法

-- GROUPの作成
CREATE ROLE company;

-- companyごとの接続アカウントの作成
CREATE ROLE '{company.id::uuid}' LOGIN;
ALTER ROLE '{company.id::uuid}' WITH PASSWORD '{ランダムな文字列}';
-- sample
CREATE ROLE "ce2825e1-5f11-4ab9-b552-f85d76897e61" LOGIN;
ALTER ROLE "ce2825e1-5f11-4ab9-b552-f85d76897e61" WITH PASSWORD 'nbZRy$>UlaF)>MN&x^P5UMAG2RB"{X0W';

-- company グループに権限の設定
GRANT USAGE ON SCHEMA "public" TO "company";
GRANT ALL ON ALL TABLES IN SCHEMA "public" TO "company";
GRANT ALL ON ALL SEQUENCES IN SCHEMA "public" TO "company";

-- company グループにRLSのポリシーを設定
-- ポリシーを作成せず、RLSを有効化すると全てFALSEになるため、全件見えなくなる
-- USINGにフィルタしたい条件を書く
-- 接続名で絞り込みを行う
-- 指定するcommandはALL
-- アカウントは指定する
CREATE POLICY company_policy_company_user ON company TO "company"
    USING (id = current_user::uuid);

-- company グループに接続アカウントを追加
grant company to '{company.id::uuid}';
-- sample
grant company to "ce2825e1-5f11-4ab9-b552-f85d76897e61";

-- company テーブルのRLSを有効（ポリシーの反映は即時）
ALTER TABLE company ENABLE ROW LEVEL SECURITY;

-- organization_chartに紐づいているorganization.idで絞り込む
-- organization_chartとcompanyをJOINすることでcompanyに適用したポリシーが反映され、companyは接続アカウントのcompanyしか取得できない
-- companyとorganization_chartはINNER JOINなので自分の所属の組織しか取得できない
CREATE POLICY organization_policy_test ON organization TO "company"
    USING (id IN (SELECT organization_id FROM company  INNER JOIN organization_chart AS oc ON company.id = oc.company_id));

-- organization テーブルのRLSにを有効化
ALTER TABLE organization ENABLE ROW LEVEL SECURITY;

実際のSQLの実行結果

-- SQL sample
-- 100013件された organization Tableに対してSQLを実行し、25ms程度で結果を返す

demo=> explain analyze select * from organization;
                                                                QUERY PLAN                                             >
----------------------------------------------------------------------------------------------------------------------->
 Seq Scan on organization  (cost=2125.08..4013.27 rows=50008 width=24) (actual time=16.010..24.329 rows=2 loops=1)
   Filter: (hashed SubPlan 1)
   Rows Removed by Filter: 100013
   SubPlan 1
     ->  Hash Join  (cost=26.99..2124.82 rows=103 width=16) (actual time=15.998..16.000 rows=2 loops=1)
           Hash Cond: (oc.company_id = company.id)
           ->  Seq Scan on organization_chart oc  (cost=0.00..1834.15 rows=100015 width=32) (actual time=0.003..5.951 r>
           ->  Hash  (cost=26.98..26.98 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1)
                 Buckets: 1024  Batches: 1  Memory Usage: 9kB
                 ->  Seq Scan on company  (cost=0.00..26.98 rows=1 width=16) (actual time=0.003..0.005 rows=1 loops=1)
                       Filter: (short_id = (CURRENT_USER)::text)
                       Rows Removed by Filter: 7
 Planning Time: 0.113 ms
 Execution Time: 24.514 ms


-- 1000万件超えた状態でも1秒以内に実行結果を返す
-- organizationやcompanyは更新が少ないのでindex only scanも選ばれやすい
demo=> explain analyze select * from organization;
                                                                                           QUERY PLAN                  >
----------------------------------------------------------------------------------------------------------------------->
 Seq Scan on organization  (cost=14.52..190598.07 rows=5050062 width=24) (actual time=608.108..881.846 rows=1 loops=1)
   Filter: (hashed SubPlan 1)
   Rows Removed by Filter: 10100014
   SubPlan 1
     ->  Nested Loop  (cost=0.72..14.52 rows=1 width=16) (actual time=0.024..0.027 rows=1 loops=1)
           ->  Index Only Scan using company_pkey on company  (cost=0.16..8.18 rows=1 width=16) (actual time=0.015..0.0>
                 Index Cond: (id = (CURRENT_USER)::uuid)
                 Heap Fetches: 1
           ->  Index Only Scan using organization_chart_company_id_organization_id_uindex on organization_chart oc  (co>
                 Index Cond: (company_id = (CURRENT_USER)::uuid)
                 Heap Fetches: 1
 Planning Time: 0.110 ms
 JIT:
   Functions: 17
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.126 ms, Inlining 0.000 ms, Optimization 0.262 ms, Emission 4.627 ms, Total 6.014 ms
 Execution Time: 882.958 ms
(17 rows)

-- 更新

demo=> UPDATE organization SET id = 'ce2825e1-5f11-4ab9-b552-f85d76897e61' WHERE id = '39aaf4f8-ad7e-48a7-ad7b-75545c143619';
UPDATE 0

demo=> EXPLAIN ANALYZE  UPDATE organization SET id = 'ce2825e1-5f11-4ab9-b552-f85d76897e61' WHERE id = '39aaf4f8-ad7e-48a7-ad7b-75545c143619';

                                                                                               QUERY PLAN              >
----------------------------------------------------------------------------------------------------------------------->
 Update on organization  (cost=14.96..22.98 rows=0 width=0) (actual time=0.045..0.046 rows=0 loops=1)
   ->  Index Scan using organization_pkey on organization  (cost=14.96..22.98 rows=1 width=22) (actual time=0.044..0.04>
         Index Cond: (id = '39aaf4f8-ad7e-48a7-ad7b-75545c143619'::uuid)
         Filter: (hashed SubPlan 2)
         Rows Removed by Filter: 1
         SubPlan 2
           ->  Nested Loop  (cost=0.72..14.52 rows=1 width=16) (actual time=0.011..0.013 rows=1 loops=1)
                 ->  Index Only Scan using company_pkey on company company_1  (cost=0.16..8.18 rows=1 width=16) (actual>
                       Index Cond: (id = (CURRENT_USER)::uuid)
                       Heap Fetches: 1
                 ->  Index Only Scan using organization_chart_company_id_organization_id_uindex on organization_chart o>
                       Index Cond: (company_id = (CURRENT_USER)::uuid)
                       Heap Fetches: 1
   SubPlan 1
     ->  Nested Loop  (cost=0.72..14.52 rows=1 width=16) (never executed)
           ->  Index Only Scan using company_pkey on company  (cost=0.16..8.18 rows=1 width=16) (never executed)
                 Index Cond: (id = (CURRENT_USER)::uuid)
                 Heap Fetches: 0
           ->  Index Only Scan using organization_chart_company_id_organization_id_uindex on organization_chart oc  (co>
                 Index Cond: (company_id = (CURRENT_USER)::uuid)
                 Heap Fetches: 0
 Planning Time: 0.137 ms
 Execution Time: 0.291 ms
(23 rows)

-- 削除
demo=> DELETE FROM organization WHERE id = '39aaf4f8-ad7e-48a7-ad7b-75545c143619';
DELETE 0
demo=> EXPLAIN ANALYZE DELETE FROM organization WHERE id = '39aaf4f8-ad7e-48a7-ad7b-75545c143619';
                                                                                              QUERY PLAN               >
----------------------------------------------------------------------------------------------------------------------->
 Delete on organization  (cost=14.96..22.98 rows=0 width=0) (actual time=0.032..0.033 rows=0 loops=1)
   ->  Index Scan using organization_pkey on organization  (cost=14.96..22.98 rows=1 width=6) (actual time=0.031..0.032>
         Index Cond: (id = '39aaf4f8-ad7e-48a7-ad7b-75545c143619'::uuid)
         Filter: (hashed SubPlan 1)
         Rows Removed by Filter: 1
         SubPlan 1
           ->  Nested Loop  (cost=0.72..14.52 rows=1 width=16) (actual time=0.011..0.013 rows=1 loops=1)
                 ->  Index Only Scan using company_pkey on company  (cost=0.16..8.18 rows=1 width=16) (actual time=0.00>
                       Index Cond: (id = (CURRENT_USER)::uuid)
                       Heap Fetches: 1
                 ->  Index Only Scan using organization_chart_company_id_organization_id_uindex on organization_chart o>
                       Index Cond: (company_id = (CURRENT_USER)::uuid)
                       Heap Fetches: 1
 Planning Time: 0.104 ms
 Execution Time: 0.201 ms
(15 rows)

-- 追加
-- ポリシーのcheckが実行され、自分のID以外のINSERTが実行できない
demo=> insert into company default values;
ERROR:  new row violates row-level security policy for table "company"

注意点

RLSに設定したPolicyは該当のユーザがテーブルにアクセスするたびに実行される。
- これはトリガーや外部キー制約なども例外ではない
- なのでパフォーマンスに直撃するのでクエリ自体が高速であることは重要
- WHEREの条件がシンプルでもPolicyを適用することでINDEXが活用されない可能性もある
- そのため、Policyを意識したSQLが書けると良い
RLSが有効だがPolicyが無い場合は全てのデータが見えない
- 開発環境などでユーザを作ったりデバッグの際などは注意
companyアカウントを作成したら接続用のアカウントを作成する必要がある
今、どのアカウントで接続しているかを意識する必要はある
- Bypassの権限を持っている接続アカウントはRLSが設定されないため注意が必要
- super userはRLSが設定されていないため注意が必要

Special Thanks

高塚さん、あの時助けていただいた鶴です。

日本語メインの意見交換の場所としてSlackにPostgreSQL部屋作りました。初心から玄人まで意見交換出来る場にできればと思います。ご活用ください。 https://t.co/kFeiiKZW2d
— そーだい@初代ALF (@soudai1025) 2019年10月31日

やっぱPostgreSQLユーザ会は最高だぜ！

そーだいなるらくがき帳

そーだいが自由気侭に更新します。

マルチテナントにおけるRow Level Securityの具体的な実装と注意点