PostgreSQLのRow Level Securityを検証する
PostgreSQL の Row Level Security(RLS) について検証したので備忘録としてまとめておきます。
(本記事の検証は、PostgreSQL 15.4 で実施しています。)
また、今回検証に利用したコードは下記の repository にまとめています。
Row Level Securityとは
Row Level Security(RLS)は、テーブル内の行へのアクセスを制御する機能です。
利用場面としては、SaaS の開発を行う際に、複数の企業のデータを扱うマルチテナントのデータ分離方法として利用されるケースがあります。
RLS を利用すると、テナント間のデータが混ざらないように、SELECT 文などに暗黙的な WHERE 句 (where tenant_id = xxx) が追加されるような挙動が実現できます。
RLS を利用したマルチテナントのデータ分離の事例も複数あります。
Row Security Policiesの設定方法
RLS を利用するためには、作成した任意のテーブルにおいて RLS を有効化して Row Security Policies を作成する必要があります。
例えば、下記の SQL では、manager という column に対して行レベルでのデータアクセスの制御を行いたいので、current_userという組み込み関数を利用して現在実行しているユーザ名と一致する行のみにアクセスを許可するポリシーを作成しています。
PostgreSQL: Documentation: 15: 9.26. System Information Functions and Operators
CREATE TABLE accounts (manager text, company text, contact_email text);
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY account_managers ON accounts
USING (manager = current_user);
特定のロール (managers) のメンバーのみにアクセスを許可するポリシーを作成することも可能です。
CREATE POLICY account_managers ON accounts TO managers
USING (manager = current_user);
current_setting() を利用したポリシーの作成
先ほどの例では、実行時ユーザーを利用していましたが、テナント ID でアクセス制御するケースも考えてみましょう。
PostgreSQL の実行時ユーザーとテナント ID は 1:1 で紐づいていないので、current_userを利用することは難しいです。
代替案として、current_settingという組み込み関数を利用する方法があります。
PostgreSQL: Documentation: 15: 9.27. System Administration Functions
例えば、下記の例にある 2 番目の SQL では同様に Row Security Policies を作成していますが、 tenant_id の指定部分では app.tenant_id という名前のパラメータの値を読み込んで、テキスト値を UUID にキャストしています。
これは tenant_id という column に対するデータ型 (UUID) に対応させるためです。
CREATE TABLE tenant (
tenant_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
name VARCHAR(255) UNIQUE,
status VARCHAR(64) CHECK (status IN ('active', 'suspended', 'disabled')),
tier VARCHAR(64) CHECK (tier IN ('gold', 'silver', 'bronze'))
);
CREATE POLICY tenant_isolation_policy ON tenant
USING (tenant_id = current_setting('app.tenant_id')::UUID);
SQL 文実行前に、set_configという組み込み関数 (もしくは SET の SQL 文) を利用して、テナント ID をセッション内で有効なパラメータとして設定します。
これにより、SELECT などの SQL 文実行時に app.tenant_id で指定されているテナント ID と一致する行のみにアクセスを許可することが可能になります。
PostgreSQL: Documentation: 15: 9.27. System Administration Functions
select set_config('app.tenant_id', '29e3842a-3e8d-4e35-b7c9-4d60e3dc98e1', false);
検証
検証環境を用意したので、実際の RLS の挙動を見てみましょう。
動作検証用の SQL に関しては、下記の記事を参考にさせていただきました。
ユーザーの作成
まず最初に、動作検証に利用する PostgreSQL のユーザー (readwrite) を作成します。
スーパーユーザーやテーブルの所有者、 BYPASSRLS 付きで作成されたユーザーでは RLS の制限を受けない場合があるため、検証用に個別にユーザーを作成します。
Superusers and roles with the BYPASSRLS attribute always bypass the row security system when accessing a table.
Table owners normally bypass row security as well, though a table owner can choose to be subject to row security with ALTER TABLE ... FORCE ROW LEVEL SECURITY.
CREATE USER readwrite WITH LOGIN PASSWORD 'readwrite';
GRANT USAGE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO readwrite;
テーブルの作成
次に、tenant テーブルを作成して、検証用のサンプルデータを用意します。
uuid-ossp module を利用して UUID の生成を行うために、インストール用の SQL も実行しています。
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE tenant (
tenant_id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
name VARCHAR(255) UNIQUE,
status VARCHAR(64) CHECK (status IN ('active', 'suspended', 'disabled')),
tier VARCHAR(64) CHECK (tier IN ('gold', 'silver', 'bronze'))
);
-- insert sample data
INSERT INTO tenant (tenant_id, name, status, tier) VALUES
('29e3842a-3e8d-4e35-b7c9-4d60e3dc98e1', 'Tenant1', 'active', 'gold'),
('7a6f6a2c-5d18-4b6e-90c0-62e69509ef72', 'Tenant2', 'suspended', 'silver'),
('c0f67f9b-d6c8-471a-9d10-9fb9a3014c8a', 'Tenant3', 'active', 'bronze'),
('c3fd3b62-3010-4c6a-92f0-5f47d1953ea5', 'Tenant4', 'disabled', 'gold');
Row Security Policiesの作成
データの準備ができたので、tenant テーブルに対してポリシーを作成します。
今回はテナント ID を利用してアクセス制御を検証したいので、tenant_id の column を利用したポリシーを作成します。
-- Turn on RLS
ALTER TABLE tenant ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_policy ON tenant
USING (tenant_id = current_setting('app.tenant_id')::UUID);
select文の実行
検証環境の準備が完了したら、実際に RLS の挙動を確認していきましょう。
筆者が用意した検証環境の場合は、docker compose upで PostgreSQL コンテナが立ち上がる際に環境準備用の SQL が実行されます。
用意したサンプルデータの中には、29e3842a-3e8d-4e35-b7c9-4d60e3dc98e1という UUID を持つテナント (Tenant1) のデータがあります。
この UUID を app.tenant_id のパラメータとして指定して tenant テーブル内の全てのデータを取得する SQL を実行すると、Tenant1 の行のみ返ってきていることがわかります。
postgres=> select set_config('app.tenant_id', '29e3842a-3e8d-4e35-b7c9-4d60e3dc98e1', false);
set_config
--------------------------------------
29e3842a-3e8d-4e35-b7c9-4d60e3dc98e1
(1 row)
postgres=> select * from tenant;
tenant_id | name | status | tier
--------------------------------------+---------+--------+------
29e3842a-3e8d-4e35-b7c9-4d60e3dc98e1 | Tenant1 | active | gold
(1 row)
INSERT, DELETE文の実行
INSERT, DELETE 文に関しても同様に検証してみましょう。
新規に作成するテナントの UUID を app.tenant_id のパラメータとして指定することで、INSERT 文を実行できます
postgres=> select set_config('app.tenant_id', 'c3fd3b62-3010-4c6a-92f0-5f47d1953ea6', false);
set_config
--------------------------------------
c3fd3b62-3010-4c6a-92f0-5f47d1953ea6
(1 row)
postgres=> INSERT INTO tenant (tenant_id, name, status, tier) VALUES ('c3fd3b62-3010-4c6a-92f0-5f47d1953ea6', 'Tenant5', 'disabled', 'gold');
INSERT 0 1
先ほど作成した Tenant5 のデータに対して、DELETE 文を実行してみます。
正常に DELETE 文が実行されたことを確認した上で、他のテナントのデータが削除されていないことを確認します。
postgres=> select set_config('app.tenant_id', 'c3fd3b62-3010-4c6a-92f0-5f47d1953ea5', false);
set_config
--------------------------------------
c3fd3b62-3010-4c6a-92f0-5f47d1953ea5
(1 row)
postgres=> DELETE FROM tenant;
DELETE 1
postgres=> select set_config('app.tenant_id', '29e3842a-3e8d-4e35-b7c9-4d60e3dc98e1', false);
set_config
--------------------------------------
29e3842a-3e8d-4e35-b7c9-4d60e3dc98e1
(1 row)
postgres=> select * from tenant;
tenant_id | name | status | tier
--------------------------------------+---------+--------+------
29e3842a-3e8d-4e35-b7c9-4d60e3dc98e1 | Tenant1 | active | gold
(1 row)
Discussion