カテゴリー
コンピューター

マルチテナントアーキテクチャのアプリをより安全にするために PostgreSQL の行レベルセキュリティ (行セキュリティポリシー、 Row Level Security) をすぐに体験できるようにした記録

5.8. 行セキュリティポリシー を利用することで単一データベースかつ単一スキーマ方式を採用したマルチテナントアーキテクチャをより安全にすることができそうです。

実際にシステムやアプリに組み込む前に、 PostgreSQL の Row Level Security とはそもそもどのような物なのか、手を動かすことで体験いたしました。

本投稿では、その時の動きをまとめます。また、時間が経ってもう一度 PostgreSQL の Row Level Security をおさらいしたくなったり、新たに挙動を確かめたくなったりした時のために、すぐにお試しの環境を作れるように記録を残します。

操作コマンドまとめ

Docker で PostgreSQL そのものを操作するコマンド

docker container run --name sample_rls -e POSTGRES_PASSWORD=postgres -d postgres:13.0
docker container ps
docker container exec -it sample_rls bash
docker container stop sample_rls
docker container start sample_rls
docker container rm --force sample_rls

psql でのデータベース操作

docker container exec -it sample_rls bash 後の操作となります。

# PostgreSQL ターミナル起動
psql -U postgres
# データベースバックアップ
pg_dump -U postgres sampledb > sampledb.sql

データベースの状態確認、接続操作

psql -U postgres 後の操作となります。

-- データベース一覧
\l
-- データベースへユーザ指定して接続
\c dbname username
-- 現在のデータベースへ接続するユーザを変更
\c - username
-- テーブル一覧
\dt

データベース、テーブル、レコード操作

-- データベース、ユーザ作成、権限付与
CREATE USER sampleuser PASSWORD 'password';
CREATE DATABASE sampledb;
GRANT ALL PRIVILEGES ON DATABASE sampledb TO sampleuser;
-- データベースへ作成ユーザで接続
\c sampledb sampleuser
-- テーブル作成
CREATE TABLE users (tenant_id text, name text, email text);
-- RLS ポリシー作成
CREATE POLICY tenant_isolation_policy ON users FOR ALL TO PUBLIC USING (tenant_id=current_setting('app.tenant_id')::text);
-- RLS 有効化
ALTER TABLE users FORCE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- データベースへスーパーユーザで接続
\c - postgres
-- レコード挿入
INSERT INTO users VALUES ('footenant', 'hanako', 'hanako@example.com');
INSERT INTO users VALUES ('bartenant', 'taro', 'taro@example.com');
-- データベースへ作成ユーザで接続
\c - sampleuser
-- RLS を試す。レコード挿入
SET app.tenant_id TO footenant;
-- RLS を試す。レコード選択
-- RLS を試す。レコード更新
-- RLS を試す。レコード削除

PostgreSQL で Row Level Security を実際に体験

docker container exec -it sample_rls bash し、さらに、 psql -U postgres した後からスタートとなります。

postgres=# -- データベース、ユーザ作成、権限付与
postgres=# CREATE USER sampleuser PASSWORD 'password';
CREATE ROLE
postgres=# CREATE DATABASE sampledb;
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE sampledb TO sampleuser;
GRANT
postgres=# -- 確認
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |    Access privileges
-----------+----------+----------+------------+------------+-------------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 sampledb  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres           +
           |          |          |            |            | postgres=CTc/postgres  +
           |          |          |            |            | sampleuser=CTc/postgres
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres            +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres            +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

postgres=#
postgres=# -- データベースへ作成ユーザで接続
postgres=# \c sampledb sampleuser
You are now connected to database "sampledb" as user "sampleuser".
sampledb=> -- テーブル作成 (RLS ポリシー付与)
sampledb=> CREATE TABLE users (tenant_id text, name text, email text);
CREATE TABLE
sampledb=> -- RLS ポリシー作成
sampledb=> CREATE POLICY tenant_isolation_policy ON users FOR ALL TO PUBLIC USING (tenant_id=current_setting('app.tenant_id')::text);
CREATE POLICY
sampledb=> -- RLS 有効化
sampledb=> -- テーブルを作成したユーザには通常 RLS は適用されないが、 RLS を有効にできるようにするために FORCE を指定
sampledb=> ALTER TABLE users FORCE ROW LEVEL SECURITY;
ALTER TABLE
sampledb=> ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE
sampledb=>
sampledb=> -- データベースへスーパーユーザで接続
sampledb=> \c - postgres
You are now connected to database "sampledb" as user "postgres".
sampledb=#
sampledb=# -- スーパーユーザには RLS は効かず、どのような操作でも可能
sampledb=# INSERT INTO users VALUES ('footenant', 'hanako', 'hanako@example.com');
INSERT 0 1
sampledb=# INSERT INTO users VALUES ('bartenant', 'taro', 'taro@example.com');
INSERT 0 1
sampledb=# INSERT INTO users VALUES ('hogetenant', 'hoge', 'hoge@example.com');
INSERT 0 1
sampledb=#
sampledb=# UPDATE users SET name = 'jiro', email = 'jiro@example.com' WHERE tenant_id = 'hogetenant';
UPDATE 1
sampledb=#
sampledb=# DELETE FROM users WHERE tenant_id = 'hogetenant';
DELETE 1
sampledb=#
sampledb=# SELECT * FROM users;
 tenant_id |  name  |       email
-----------+--------+--------------------
 footenant | hanako | hanako@example.com
 bartenant | taro   | taro@example.com
(2 rows)

sampledb=#
sampledb=#
sampledb=#
sampledb=# -- データベースへ作成ユーザで接続
sampledb=# \c - sampleuser
You are now connected to database "sampledb" as user "sampleuser".
sampledb=>
sampledb=> -- RLS を一般ユーザで試す
sampledb=> -- `tenant_id=current_setting('app.tenant_id')::text` というポリシーにしたため 'app.tenant_id' がない状態ではエラー
sampledb=> SELECT * FROM users;
ERROR:  unrecognized configuration parameter "app.tenant_id"
sampledb=>
sampledb=> SET app.tenant_id TO footenant;
SET
sampledb=>
sampledb=> -- tenant_id = 'footenant' 以外のレコードは無視される
sampledb=> SELECT * FROM users;
 tenant_id |  name  |       email
-----------+--------+--------------------
 footenant | hanako | hanako@example.com
(1 row)

sampledb=> SELECT * FROM users where tenant_id = 'bartenant';
 tenant_id | name | email
-----------+------+-------
(0 rows)

sampledb=>
sampledb=> -- tenant_id = 'footenant' 以外のレコードは挿入できない
sampledb=> INSERT INTO users VALUES ('hogetenant', 'hoge', 'hoge@example.com');
ERROR:  new row violates row-level security policy for table "users"
sampledb=> INSERT INTO users VALUES ('footenant', 'hoge', 'hoge@example.com');
INSERT 0 1
sampledb=> SELECT * FROM users;
 tenant_id |  name  |       email
-----------+--------+--------------------
 footenant | hanako | hanako@example.com
 footenant | hoge   | hoge@example.com
(2 rows)

sampledb=>
sampledb=> -- tenant_id = 'footenant' 以外のレコードは更新できない
sampledb=> UPDATE users SET name = 'saburo' where tenant_id = 'bartenant';
UPDATE 0
sampledb=> UPDATE users SET name = 'saburo' where tenant_id = 'footenant' and email = 'hoge@example.com';
UPDATE 1
sampledb=> SELECT * FROM users;
 tenant_id |  name  |       email
-----------+--------+--------------------
 footenant | hanako | hanako@example.com
 footenant | saburo | hoge@example.com
(2 rows)

sampledb=>
sampledb=> -- tenant_id = 'footenant' 以外のレコードは削除できない
sampledb=> DELETE FROM users WHERE tenant_id = 'bartenant';
DELETE 0
sampledb=>
sampledb=> DELETE FROM users WHERE name = 'saburo';
DELETE 1
sampledb=> SELECT * FROM users;
 tenant_id |  name  |       email
-----------+--------+--------------------
 footenant | hanako | hanako@example.com
(1 row)

sampledb=>
sampledb=> \q

バックアップ時の RLS の影響

# スーパーユーザでは RLS の定義等も含め全てバックアップできた
# pg_dump -U postgres sampledb > sampledb.sql
#
#
#
# # テーブル作成ユーザではバックアップできなかった。 FORCE で RLS を掛けたためのようだ。
# pg_dump -U sampleuser sampledb > sampledb.sql
pg_dump: error: query failed: ERROR:  query would be affected by row-level security policy for table "users"
HINT:  To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
pg_dump: error: query was: COPY public.users (tenant_id, name, email) TO stdout;
#

おわりに

RLS の何が嬉しいのか?

マルチテナントアーキテクチャで単一データベース共通スキーマ方式をとるときに嬉しいと思います。

実際に PostgreSQL の Row Level Security を採用している事例としては、こちらがありました。

またより踏み込んで、システムでどうやって PostgreSQL の Row Level Security を使ったら良いのか、については次のページが参考になりました。本ページの基となっております♪

マイグレーションに時間がかかるようになってしまったらどうするのか?

ALTER TABLE の結果がいつまで経っても帰ってこない、、、ということは起こり得ると思います。 PostgreSQL ですので、 Citus を導入すれば解決でキルのはないかと思えます。

これは、次のページを見てこの考えに至りました。

RLS も Citus も PostgreSQL ですので、ちょうど良いですね♪

以上です。

コメントを残す