こんにちは、清水（@_smzst）です。直近の業務でマルチテナントな DB でアプリケーションでのスイッチロール機能を実現するために Amazon Cognito や表題にある Row Level Security について技術検証や設計検討を行っていました。
今回はこの Row Level Security について PostgreSQL での例を挙げて挙動を確認しながら、実際に導入するにあたって必要な知識や注意点までを網羅します。本記事が安全で堅牢なアプリケーション構築の一助となれば幸いです。
1. マルチテナントとは
マルチテナントという単語が冒頭から登場していますが、これは一体なんでしょうか。Wikipedia では以下のような説明がされています。単一のインスタンスが複数の顧客にサービスを提供するアーキテクチャであり、テナントとは各顧客のことを指します。たとえば、単一の DB インスタンスの中に複数の顧客企業のレコードがテーブルごとに垣根なく存在するようなアーキテクチャです。
Software multitenancy is a software architecture in which a single instance of software runs on a server and serves multiple tenants. .... A tenant is a group of users who share a common access with specific privileges to the software instance.
https://en.wikipedia.org/wiki/Multitenancy
新しいアーキテクチャかと思いきや、ごくありふれたものですよね？このようなアーキテクチャの DB に対して SQL を書くとき WHERE 句に細心の注意を払うと思いますが、この WHERE 句の間違いに誰も気付かなかったら大変なことになってしまうでしょう…
2. Row Level Security とは
このような不安から仕組みとして守ってくれるのが Row Level Security (RLS) です。PostgreSQL のドキュメントの説明によると以下のようにあり、行レベルで DB のユーザーごとにクエリの結果や追加・削除・更新を保護してくれ、明示的に許可しなければその操作は拒否されます（行が表示されなかったり、変更されない）。なお、MySQL は RLS をサポートしていないのでご注意ください。
tables can have row security policies that restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands. This feature is also known as Row-Level Security.
いいですね！以降では業務で扱いそうなケースを例に RLS の挙動を確認していきましょう。
3. 実際に試してみる
以下のようなケースがあったとして、これを RLS を使って安全に実現する方法を考えていきましょう。先に完成形から示して、そのあとで適度に区切って詳しく見ていきます。
100 社ほどの「販売会社」と販売会社を束ねる「統括会社」があります。統括会社のユーザーは各販売会社に切り替えて代理ですべての閲覧と操作が行えますが、各販売会社は自社以外の会社の情報の閲覧や操作は一切行えないようにしたいです。また、販売会社は 001, 002, ... のような連番で追加することにします。
3-1. 実現するための SQL
結果から示すと以下のような SQL のようになります。
-- 例示のためのデータ準備 CREATE TABLE users ( id INT NOT NULL, name TEXT NOT NULL, email TEXT, company_id TEXT NOT NULL, PRIMARY KEY (company_id, id) ); INSERT INTO users VALUES (1, 'yamada', 'yamada@001.example.com', '001'); INSERT INTO users VALUES (2, 'murata', 'murata@001.example.com', '001'); INSERT INTO users VALUES (1, 'tanaka', 'tanaka@002.example.com', '002'); -- ロールおよびユーザーの作成と権限の許可 CREATE ROLE sales_company; GRANT SELECT,INSERT,UPDATE,DELETE ON users TO sales_company; CREATE USER sales_company_001; CREATE USER sales_company_002; GRANT sales_company TO sales_company_001; GRANT sales_company TO sales_company_002; CREATE USER presiding_company; GRANT SELECT,INSERT,UPDATE,DELETE ON users TO presiding_company; -- RLS の有効化とポリシーの作成 ALTER TABLE users ENABLE ROW LEVEL SECURITY; CREATE POLICY users_for_sales_company ON users TO sales_company USING (CONCAT('sales_company_', company_id) = CURRENT_USER); CREATE POLICY users_for_presiding_company ON users TO presiding_company USING (true) WITH CHECK (true);
3-2. RLS 有効化前
RLS 有効前後の差分が分かりやすいように、いったん「例示のためのデータ準備」と「ロールおよびユーザーの作成と権限の許可」までを適用した状態を見てみます。
ここで適用した SQL は、取得や追加・更新・削除のできる sales_company_001, sales_company_002, presiding_company ユーザーを作成したことだけ分かれば支障ありません。また、ユーザーが複数存在している理由は、次の節で分かりますが CURRENT_USER で取得できる実行コンテキストのユーザー名とテナントの ID（company_id）を 1:1 で対応させて、RLS によって特定の DB ユーザーの操作の影響範囲を特定のテナントのみに制限するためです。
次に、ロールの一覧や users テーブルとアクセス権を確認します。なお、以下については詳しく触れないので必要があればリンク先をご参照ください。
\dp,\duのようなコマンドについては psql の「Meta-Commands」の項目- Access privileges 列の
arwdDxtのような文字列については 5.7. Privileges の「Table 5.1. ACL Privilege Abbreviations」 - Superuser と Bypass RLS ロールを持つユーザーは RLS を貫通して閲覧や操作ができます。詳しくは 5.8. Row Security Policies の該当箇所
3-3. RLS 有効化後
では「RLS の有効化とポリシーの作成」までを適用してみます。これで何が変わったのかと言うと、以下の図のように users テーブルに対してポリシーが 2 つ追加されました。users_for_presiding_company は制限なく、users_for_sales_company は実行コンテキストのユーザー名と等しい場合に操作が許可されるポリシーとなります。
なお、実行コンテキストのユーザー名は、作成したユーザーと同じ sales_company_001 のような値で取得できるので CONCAT 関数で文字列結合してこのフォーマットに合わせています。もちろん company_id の 001 のようなフォーマットに合わせて CREATE USER "001" でユーザーは作れはしますが、分かりやすいネーミングを優先しました。
sales_company_001 ユーザーでログインしていろいろ操作をしてみます。company_id が 001 のレコード以外、取得や追加・更新・削除が行えないことが分かります。
-- company_id = 001 のレコードしか取得できない
postgres=> SELECT * FROM users;
id | name | email | company_id
----+--------+------------------------+------------
1 | yamada | yamada@001.example.com | 001
2 | murata | murata@001.example.com | 001
(2 rows)
-- company_id = 001 以外を含むレコードの追加は RLS に違反して行えない（両方追加されない）
postgres=> INSERT INTO users VALUES
(3, 'kotani', 'kotani@001.example.com', '001'),
(2, 'watabe', 'watabe@002.example.com', '002');
ERROR: new row violates row-level security policy for table "users"
-- id = 1 なレコードは company_id = 001, 002 とで 2 つあるが、更新されるのは 001 のだけ
postgres=> UPDATE users SET name = 'xxx' WHERE id = 1;
UPDATE 1
postgres=> SELECT * FROM users;
id | name | email | company_id
----+--------+------------------------+------------
2 | murata | murata@001.example.com | 001
1 | xxx | yamada@001.example.com | 001
(2 rows)
-- 削除されるのは company_id = 001 のだけ
postgres=> DELETE FROM users;
DELETE 2
postgres=> SELECT * FROM users;
id | name | email | company_id
----+------+-------+------------
(0 rows)
また、users_for_presiding_company ポリシーでは WITH CHECK 句があるのに、users_for_sales_company ポリシーにはないことに気付きましたでしょうか？WITH CHECK 句の制約がないので UPDATE で SET company_id = '002' にしてしまうイタズラもできそうに思えますが、これは RLS によって弾かれてしまいます。なぜなら以下の一文の通り USING 句の条件が暗黙的に制限されるためです。
The policy above implicitly provides a WITH CHECK clause identical to its USING clause,
5.8. Row Security Policies 該当箇所
このように、たとえ company_id = 001 以外のレコードがあったとしても sales_company_001 ユーザーからはマスクされて扱われるわけです。なお、presiding_company ユーザーに関しては users テーブルに許可した権限の範囲内であれば何でもできます。ログインし直すかロールを切り替えて先の操作で本当に company_id = 002 のレコードに影響がないか確認してみるとよいでしょう（ロール切り替えは SET ROLE presiding_company のようにする）。
これで「自社以外の会社の情報の閲覧や操作は一切行えないようにしたい」という要件を仕組みとして実現することができました。人間は誰しも間違いを犯すものなので仕組みで解決するのはよいことです。ここまでで RLS の基本的な説明と挙動については以上です。
4. 補足
ここからは、横道にそれるのを避けてあえて説明せずに進めたことを見ていきます。
4-1. クエリのパフォーマンスについて
一つ前の節で、RLS 有効化後に WHERE company_id = '001' とせずともそのような結果になっていました。クエリの結果だけで言えば company_id を指定しなくてもいいように見えますが、これだとパフォーマンスに問題が出てくる可能性が高いです。
どのような違いがあるかを見てみましょう。例のようなたった 3 レコードでは違いが分かりくいので company_id が 001 のレコード 6 万行と 002 のレコード 4 万行の計 10 万行用意して sales_company_001 ユーザーで EXPLAIN ANALYZE を取ります。
どちらも 60000 という結果が返りますが、実行計画や actual time に違いがあります。上のケースではインデックスが効くので高速な Index Only Scan が行われていますが、下のケースでは Seq Scan で 10 万行全部見てから company_id = 002 なレコード 4 万行を捨てています。単純なテーブルなので 8 ms ほどの差で収まっていますがカラム数やレコード数が増えれば増えるほど差は開くでしょう。
このため、RLS によって別テナントのレコードは結果的には弾かれますが、パフォーマンスの面で嬉しいことはないので素直に WHERE 句を書くことをおすすめします。なお、今回 PRIMARY KEY は (company_id, id) の複合キーとしていますが、これが (id, company_id) の場合はインデックスが効かずにどちらも Seq Scan になるので複合キーの順番には気をつけましょう。
4-2. ユーザーとロールの管理について
ユーザーとロールまわりで説明を省略していた内容を補足します。
本ブログで挙げた具体的な例ではユーザーとロールを区別していますが、PostgreSQL において両者の違いはほとんどありません。ユーザーで作成した場合はデフォルトでログイン権限を持つという違いしかないたいめCREATE USER myuser ではなく CREATE ROLE myuser WITH LOGIN としても同じことです。
また、許可する権限を付与した sales_company ロールを継承する形でユーザーに権限を付与していますが、こうすることで「販売会社」が増えて DB にユーザーを追加する際も出来合いの sales_company ロールを継承すればいいだけなので、付与する権限の過不足を防げるという点でメリットがあります。
さらに、今回の例では GRANT ... ON users TO sales_company; のように users テーブルに限定していましたが、実務の場では後から増えたテーブルに対しても同じ権限でアクセスできて欲しい場合が多いと思います。このようなときには以下のような SQL によって public スキーマの全テーブルに関する権限を付与することで解決できます（読み取り・書き込みロールの場合、通常 SEQUENCE も使用する必要がある）。
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO sales_company; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO sales_company;
なお、ここで補足した内容は以下の記事にもまとめられています。
4-3. コネクションの管理について
本ブログの内容で RLS を導入するにあたって最低限必要な知識や注意点は盛り込めたと自負していますが、一つ忘れてはいけないのがコネクションをどう管理するかという問題です。本ブログでは気にかけるポイントとして忘備録的な記載に留めることにします。
理想としては、すべてのテナントで 1 つのコネクションプールを使い回しながら、コネクションを使う前にそのテナントに対応した DB ユーザー・ロールに切り替えられることです。ここは使うライブラリが対応しているかどうかを確認して、必要があれば実装しなければならないかもしれません。
しかし、RLS を導入するにあたってすべての場面においてこの理想形を実現しなければならないのかと言うとそうではないと考えています。次善策として以下の 2 つが考えられるでしょう。
- コネクションプールをテナントごとに用意する
- コネクション数上限にひっかかる可能性がある
- コネクションのリソース効率が悪くなる可能性がある
- いちいちコネクションを取りに行く
- オーバーヘッドが大きい
- リソース効率はよい
基本的にはコネクションを張るオーバーヘッドは小さくしたいのでコネクションプーリングすると思います。しかし、コネクション数の上限をテナントの数だけ分かち合うので、割り当てられたコネクションプールではかつかつなテナントと余裕綽々なテナントが発生してリソース効率が悪くなる可能性も頭に入れておく必要があります。
とはいえ規模がある程度見込めていて、ここに挙げたような気をつけどころをクリアできる状況であれば比較的容易に RLS は導入できそうです。
4-4. BigQuery などでも RLS が使える
こちらは紹介だけに留めますが、エンジニア以外でも SQL を書いて DWH を活用したデータドリブンな意思決定やマーケティングを行っている企業もあると聞きます。多くの人が自由にデータを扱えるとメリットも大きいですが、人為的なミスを防ぐためにも誰に何をどこまで許すかのコントロールは必要になってくると思います。このような場面でも RLS を使えるのではないでしょうか。
5. さいごに
Row Level Security のよさを感じていただけたでしょうか？コネクションまわりで考えないといけないことはあるものの、従来のように WHERE 句を人の目で見て気をつけるのではなく仕組みとして保護できるところに魅力を感じていただければ幸いです。
