トップ > Database > PostgreSQLのRow Level Securityを使ってマルチテナントデータを安全に扱う

PostgreSQLのRow Level Securityを使ってマルチテナントデータを安全に扱う

Database Security

こんにちは、サーバーサイドエンジニアーのユキチです。

SaaSの開発を行う上では複数の企業様のデータを扱うことになります。 そういったマルチテナントのデータ設計を行う上で、弊社ではPostgreSQLのRow Level Securityという機能を使って実装しました。

今回はPostgreSQLのRow Level Security（以下RLS）という機能を使ってマルチテナントのデータ操作を安全に扱う方法を紹介していきたいと思います。

f:id:uqichi:20190619212852j:plain

マルチテナントデータベース設計

主に3パターンの設計手法が考えられます。

  1. Multi Tenants in 1 Database：複数企業のデータを一つのデータベースで管理
  2. 1 Tenant in 1 Database：テナントごとにデータベースを分けて管理。物理的なインスタンスは同じ。
  3. 1 Tenant in 1 Instance：テナントごとにデータベースを分けて管理。物理的なインスタンスも分け企業ごとにデータベースインスタンスを作成。

それぞれのメリットデメリットは何でしょうか。

Pattern Multi Tenants in 1 Database 1 Tenant in 1 Database 1 Tenant in 1 Instance
金銭的コスト ○ ○ ×
マイグレーションコスト ○ × ×
安全性 × △ ◎
スケーラビリティ △ △ △

プチ解説

  • 金銭的コスト：どれくらいお金かかるか。リソースをどれだけ効率的に使えてるかとも言い換えられる。
  • マイグレーションコスト：データベースを分けるとスキーママイグレーションの適用を企業数分回す必要があります。少ないうちは耐えれそうですが、数千企業になってくると...。
  • 安全性：データベースを一つで複数の企業を管理するとData Violationが起こり得ます。こちらが今回の記事のテーマになりますね。また、DB障害が起きた時の影響範囲を最小限に留めるは企業ごとにインスタンスを分けるアーキテクチャが最強となります。
  • スケーラビリティ：企業がどんどん増えてった時。どのパターンも問題を抱えていそうです。スケーラビリティをあげるにはパターンの組み合わせが必要な気がします。

他に観点あるかもですが、全体的にみて、「Multi Tenants in 1 Database」のパターンが現実的な気がします。現に弊社もこちらを採用しました。

が、安全性に穴があります。企業を跨ぐ操作違反の可能性です。

これを防ぐには、アプリケーションコードで適切なWHERE句を書くということに頼りきりになっています。デベロッパーのミスが命取りですね。

そこで今回紹介するRow Level Securityの出番です。

Row Level Security とは

何が美味しいの？

データベーステーブル内の行へのアクセスを制御することができます。

Rowとは「行」のことです。直訳すると「行レベルセキュリティ」。「行」は「列」の対概念ですね。

ちなみにカラムレベルセキュリティというのがあるミドルウェアもあります（Asure SQL Data Warehouse etc)。

ピンとこないと思うので早速説明のためのサンプルテーブルを用意します。

CREATE TABLE tenants (
    id BIGSERIAL PRIMARY KEY,
    name character varying(100) NOT NULL
);

CREATE TABLE users (
    tenant_id BIGSERIAL PRIMARY KEY,
    name character varying(100) NOT NULL
);

ここで列というのは id , name とかのことですね。Columnとも言います。

そして行は、テーブルに入るデータそのもののことを指す言葉です。Recordとも言います。

「行レベルセキュリティ」とはつまりテーブルに入るデータそのもの（レコード）へのアクセスを制御する方法です。

もっと簡単にいうと、Aさんはこのデータ見れるけどBさんはこのデータ見れないというような制御を"行単位"で実現したい場合に有効な機能です。

参照コマンドに限らず作成（Aさんはデータを作れる/Bさんは作れない）などの他のコマンドにも対応しています。

実装方法

弊社ではすでに実装済みなので、結果だけばばんっと書いちゃってもいいのですが、それだとRLSのありがたみとか実装する上での注意点とかに気づいてもらえなそうな気がするので、検証した時の内容をご紹介しようと思います。

実際のプロダクトに適用した実装方法については最後にポイントごとに書きたいと思います。

検証する前に

コードを書く前に、まずはREPLでpostgresの動きとかRLSの使い方を確認していきます。

というのも僕自身PostgreSQL自体触ったことがなく、基本MySQLできた人間でして、割と同じ人多そうだなと思うからです。 おまけメモみたいなものなのでpostgres馴染みある読者の方はすっ飛ばしていただいて構いません。

Postgres

最新バージョン 11.x

documentation: https://www.postgresql.org/docs/11/index.html

とりあえずpostgreSQLを触る

Connect database

データベースはdocker-composeで作っています。

psql -U xuser -d xdb

インタラクティブにパスワード入力させられます。

Show tables

\dt

Table schema

\d table

show access policy of table

\z table

Get current user

select current_user;

Show user's role

\du

xuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

super userで実行すると上みたいな感じ。

ちなみにスーパーユーザには全てのRLSを許可される権限がついています Bypass RLS

All users

select * from pg_user;

Add role to user

grant all on table to user;

Switch user

\connect - <USER_NAME>

Table access policy

\z products

Policy table

select * from pg_policy;

検証する

検証内容と目的

  • PostgreSQLのRow Level Security機能をアプリケーションで使う検証
  • 用途としてはマルチテナントの管理に使いたい
  • コードサンプルとして Managerを内包するAccountsテーブルを用意
  • Managerごとにデータベースユーザーを作成, RLSを設定
  • 各Managerが自身に関係するAccountsレコードのみしか検索/更新/削除できないことがRLSによって保証されるかを確認
  • 上記をやる上で、DSNをユーザーの数だけ作って接続する必要があるが、その辺の実装どうするかも考えたい

実際にRLSを使ってみる

About RLS: https://www.postgresql.org/docs/current/ddl-rowsecurity.html

テーブルのRLSを有効にすると、アクセスするには行セキュリティポリシーによって許可される必要があります。 以下例です。

CREATE POLICY user_policy ON users
    USING (user_name = yukichi);

user_nameがyukichiの場合のみusersテーブル内の行データにアクセスできるぜ！って意味になります。

行セキュリティポリシーは特定のコマンド（SELECTとかCREATEとか）、特定のロール、あるいはその両方に対して定義できます。

マルチテナントのアクセス制御を考える場合、基本的にはコマンドは ALL、ロールは会社単位になりそうです。

CREATE POLICY user_policy ON users
    USING (user_name = current_user);

上のような書き方をすると、一つのポリシーで全てのユーザーに適用させることができます。 current_user という組み込み関数を使っているので動的に比較値が変わるからですね。

しかし、マルチテナント管理において、Primary KeyおよびForeign Keyが数値の場合 tenant_id = current_user とする必要があるため、dbユーザー名もそれに合わせる必要がありますね。（それに合わせてdbユーザー名が1とかちょっと嫌よねって話。） このへんの対応については後述することにします。

兎にも角にもトライ。

テーブル作成してRLSを有効化します。

今回は accountsというテーブルをサンプルに作りました。 セキュリティポリシーの条件には manager カラムを使って、現在ログインしているユーザー名と一致した場合のみにアクセスを許可されることを確認していきます。

CREATE TABLE accounts (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

確認

xdb=# \d accounts
                Table "public.accounts"
    Column     | Type | Collation | Nullable | Default
---------------+------+-----------+----------+---------
 manager       | text |           |          |
 company       | text |           |          |
 contact_email | text |           |          |
Policies (row security enabled): (none)

ポリシーを作ります。

create policy account_managers on accounts to manager using (manager = current_user);

これがまさにRLSによる制御を適用してるステートメントになります。 accountsテーブルのmanager フィールドが現在ログインしているユーザー名（current_user）と一致した場合にのみアクセスを許可しますよってことです。

xdb=# \d accounts
                Table "public.accounts"
    Column     | Type | Collation | Nullable | Default
---------------+------+-----------+----------+---------
 manager       | text |           |          |
 company       | text |           |          |
 contact_email | text |           |          |
Policies:
    POLICY "account_managers"
      TO manager
      USING ((manager = (CURRENT_USER)::text))

確認してみるとちゃんとポリシーがついています。

アクセスが適切に制御されるかどうかを確認していきます。 つまりこのポリシーの条件をパスしたユーザーしか見れないことを確認します。

まだユーザーがいないのでOK, NG用に二人作りますね。

REPL一旦出ます。

\q

ユーザー作成

# createuser --interactive shacho
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
createuser: could not connect to database postgres: FATAL:  role "root" does not exist

上のはNG用のユーザーですね。(manager != current_user)

ちなみに、PostgreSQLではロールとユーザーはほぼ同じ意味と理解してもらって大丈夫です。

なので以下のような形でも作れます。

CREATE ROLE manager

こちらはOK用のmanagerになります。 (manager = current_user)

以下で確認してみるとユーザーもロールとして扱われていることがわかります。ややこしい。

xdb=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 manager   | Cannot login                                               | {}
 xuser     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

とはいえ上記で作成した manager には何も権限がありません。ログインすら許されていません。試しにユーザー切り替えようとすると

xdb=# \connect - manager
FATAL:  role "manager" is not permitted to log in
Previous connection kept

ログイン権限がないぞと怒られます。与えます。

xdb=# create role manager with login;
ERROR:  role "manager" already exists

すでに存在するので作ろうとすると期待通りエラーになります。権限だけ与える。 ALTER ROLE をつかいます。

alter role manager with login

okです。これでRLS制御OK/NG用の二人のユーザーができました。

OKがmanager, NGがshachoです。

ここで、彼らにはセキュリティポリシーで許可されたアクセスが可能となるはずなんですが、そもそもデフォルトだとなんのアクセス権限も与えられていません。かなしい

accountsテーブルにおける利用可能な全ての権限を、manager および shacho ユーザに与えます。

GRANT ALL PRIVILEGES ON accounts TO manager;
GRANT ALL PRIVILEGES ON accounts TO shacho;

ようやく、アクセス制御の動作を確認します。

とりあえず、 accounts テーブルにレコードが何もないので適当に入れましょう。

テーブル構造（再掲）

xdb=# \d accounts
                Table "public.accounts"
    Column     | Type | Collation | Nullable | Default
---------------+------+-----------+----------+---------
 manager       | text |           |          |
 company       | text |           |          |
 contact_email | text |           |          |
Policies (row security enabled): (none)

insert

insert into accounts values ('hogeman', 'hogeな会社', 'hoge@hoge.com');
insert into accounts values ('fugaman', 'fugaな会社', 'fuga@fuga');
insert into accounts values ('manager', '真面目な会社', 'manager@majime.com');

xdb=# select * from accounts;
 manager |   company    |   contact_email
---------+--------------+--------------------
 hogeman | hogeな会社      | hoge@hoge.com
 fugaman | fugaな会社      | fuga@fuga
 manager | 真面目な会社 | manager@majime.com
(3 rows)

3件登録しました。

ここで一旦この後期待される挙動をまとめておきます。

用意したデータに対する二人のユーザーそれぞれの期待するアクセス制御は以下のようになります。

  • manager は3番目のレコード（真面目な会社）にだけアクセスできる (フィールド:manager = current_user:manager)
  • shacho はどのレコードにもアクセスできない

実際に、 manager にユーザースイッチしてデータを取得してみます。

xdb=# \connect - manager;
You are now connected to database "xdb" as user "manager".

xdb=> select * from accounts;
 manager |   company    |   contact_email
---------+--------------+--------------------
 manager | 真面目な会社 | manager@majime.com
(1 row)

成功しました。自分がmanagerである行の情報しか取得できていません。

where句も指定してみます。

xdb=> select * from accounts where manager = 'manager';
 manager |   company    |   contact_email
---------+--------------+--------------------
 manager | 真面目な会社 | manager@majime.com
(1 row)

いいですね。where条件句を指定しようがしまいがどちらでも構わないから、アプリケーションコードでメソッド単位でいちいち、 where tenant_id = ? する必要はなくなります。

ここがRLSの便利なところです。

逆にRLS使ってないケースで、一つ問題提起をしてみましょう。

マルチテナント管理しているデータに対して、アプリケーション側で万が一コーダーが where tenant_id = ? を書くのを忘れたらどうなるでしょうか。コードレビューも通過してしまいプロダクトにリリースされてしまいました。

結果は、他の企業のデータにアクセスできてしまいます。A社がB社の情報を見れる事故が起こるということです。おそろしい

RLSを使うとこれを制御することができます。アプリケーションレベルではなくデータベースミドルウェアレベルで企業を跨いだ操作違反を防ぐことが可能になります。

戻りまして。

では、他のmanagerの列をselectしてみるとどうなるでしょうか。

xdb=> select * from accounts where manager = 'hogeman';
 manager | company | contact_email
---------+---------+---------------
(0 rows)

hogeman は確かに存在するけども、RLSによって切り捨てられていますね。エラーにはならずにただ結果が返らないだけです。

一応、更新系も見ておきます。

成功パターン

xdb=> insert into accounts values ('manager', '真面目な会社2', 'manager@majime2.com');
INSERT 0 1

xdb=> select  * from accounts;
 manager |    company    |    contact_email
---------+---------------+---------------------
 manager | 真面目な会社  | manager@majime.com
 manager | 真面目な会社2 | manager@majime2.com
(2 rows)

xdb=> update accounts set company = 'upd majime 2' where contact_email = 'manager@majime2.com';;
UPDATE 1

xdb=> delete from accounts where company = 'upd majime 2';
DELETE 1

失敗パターン

xdb=> insert into accounts values ('hogeman', '真面目な会社2', 'manager@majime2.com');
ERROR:  new row violates row-level security policy for table "accounts"

OK。insert処理の時はエラーになり、エラーメッセージにRLSが原因ということが書いています。

xdb=> update accounts set company = 'upd majime 2' where contact_email = 'hoge@hoge.com';
UPDATE 0

xdb=> delete from accounts where company = 'hogeinc';
DELETE 0

OK. update,deleteの時はNO ERROR. セレクト時と同じ挙動となります。

最後に shacho ロールは挙動どうなるか？ 検証過程は省略しますが、結果だけいうとなんの結果もかえりません。上で見たようにINSERTコマンドだけエラーになります。

RLSの設定手順まとめ

// 1. Create table
CREATE TABLE accounts (manager text, company text, contact_email text);

// 2. Enable RLS
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

// 3. Create role(user)
CREATE ROLE manager WITH LOGIN;

// 4. Create policy
create policy account_managers on accounts to manager using (manager = current_user);

// 5. Grant privileges to role(user)
GRANT ALL PRIVILEGES ON accounts TO manager;

こんな感じです。

RLSでマルチテナント設計を実装するに当たっての注意点

テナントテーブルの外部キーが存在する全てのテーブルにRLSを適用する

例えば、マスターテーブルが tenants として、 users テーブルはカラムに tenant_id という外部キーを持ちます。

この外部キーがある関連テーブル全てにRLSを有効化する必要があります。 なのでそういった新しい関連テーブルを追加する際には以下のように毎度設定します。

ALTER TABLE new_table ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_new_table ON new_table TO new_table USING('tenantuser' = current_user);

ただ上の例だと、USING句のところは企業ごとにポリシー作るのか？という疑問が生まれるかと思いますがこれは回避できます。以下に続きます。

ロール（ユーザー）名のベストプラクティス

ロール名には制約があります。

https://www.postgresql.org/docs/current/sql-syntax-lexical.html

ちなみにこのsyntaxルールはロール名に限らず。

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($).

前述で「しかし、マルチテナント管理において、Primary KeyおよびForeign Keyが数値の場合 tenant_id = current_user とする必要があるため、dbユーザー名もそれに合わせる必要がありますね。（それに合わせてdbユーザー名が1とかちょっと嫌よねって話。）」

と書きましたが、そもそも数字のみの名前はルール違反となってます。

検証過程では、 current_user を manager カラムの値と文字列比較しましたが、しかし、大抵のDB設計では、PKやForeign KeyはAuto incrementな数字とかランダムな文字列とかで抽象化されているケースがほとんどだと思います。

今回マルチテナントでのRLS実装においては、外部キーを数字で扱いたく、ポリシーを

CREATE POLICY {policy} ON tenants USING(tenant_id = current_user)

な感じにしたら、テナント単位でポリシー作らなくて良くなるので、 tenant_1 とか tenant$1 みたいにして、最後のtenant_idを抽出、ポリシーのルールに使用というかんじにしたいです。

そして、これは組み込み関数concatを併用することで実現できます。

CREATE POLICY {policy} ON tenants USING(concat('tenant$', tenant_id) = current_user);

このようにポリシーを作っておくと、ロール名は tenant$100 のような命名規約で対応できるようになります。新しい企業が追加されるときには、この命名でロールを作成します。

注意

Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable. The SQL standard will not define a key word that contains digits or starts or ends with an underscore, so identifiers of this form are safe against possible conflict with future extensions of the standard.

「$」はpostgres特有の識別子なので、他のSQLでは動作が保証されません。

ロールの親子関係を応用する

企業ユーザーを毎度追加する必要がありますが、その度に権限を与える処理をするのはめんどくさいですよね。

postgresではグループロール機能を使うことで、ロールの継承ができるので、あらかじめ親ロールを作っておいて、新しい企業を作成する場合にはその用意しておいたロールを継承させるだけで済みます。

継承対象となるロールをあらかじめ用意

-- create tenant group role
CREATE ROLE origin_tenant INHERIT;

-- grant access to the new table in the future automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO origin_tenant;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO origin_tenant;

新しく作成したロールに権限を継承させる

GRANT origin_tenant TO new_tenant;

コネクションプーリング

上でみてきたように、セキュリティポリシーには current_user を使いたい。

となると、 テナントごとにユーザーを用意する必要があります。

するとアプリケーション側では大抵コネクションプーリングをやったりしているわけですが、これを最適化した方がいいよねってことです。

例えば、O社はデイリー300アクセス、P社はデイリー30000アクセスとか、そういうのですね。

数についてはプロダクトのアクセス状況などに応じて考える必要があります。

パフォーマンス

RLSを使うことで多少パフォーマンスに影響がでる可能性があるようです。

この問題に関しては、弊社で確認できたパフォーマンスの劣化が特別困るレベルではないため、まだ調査しきれておらず原因の特定ができていない次第です。

まとめ

いかがでしたでしょうか。

データベースレベルでセキュアなアクセス制御ができるPostgreSQLのRow Level Securityでした。

開発チームの安心感もありますが、何よりクライアントの企業のデータを適切に扱うという責任が我々SaaS開発チームには要求されます。

これを機に設計・セキュリティを見直してみようとかいう機運がどこかで生まれますと幸いです。