RLSポリシーに基づいてSaaSテナントのデータを分離する - ApsaraDB RDS

このトピックでは、行レベルのセキュリティ (RLS) ポリシーに基づいて、データベースレベルでデータ権限を分離する方法について説明します。これにより、SaaS (software as a service) シナリオで小規模および小規模のテナントを管理できます。

背景情報

PostgreSQLのRLSポリシーは、データ権限を分離するために使用されます。管理者は、テーブルの行レベルのアクセスポリシーを作成して、表示および変更できる行を制御できます。 SaaSシナリオでは、テナント固有のフィールドとRLSポリシーを使用して、小規模なテナントの多数のデータベースとテーブルの困難なO&Mを解決します。

Introduction to RLS policies

RLSポリシーはPostgreSQL 9.5でサポートされています。

RLSポリシーでは、異なるユーザーがテーブル内の異なるデータ行を表示できます。データクエリや更新を含むデータに対するすべての操作は、ポリシーの対象となります。

RLSポリシーの適用範囲

RLSポリシーは、SELECT、INSERT、UPDATE、DELETEの各ステートメントに対して作成できます。これらすべてのステートメントに対してRLSポリシーを作成することもできます。
RLSポリシーは、複数のロールにアタッチできます。
RLSポリシーは、ステートメントに対して作成し、ロールにアタッチできます。

RLSポリシーの構文

table_nameでポリシー名を作成する
[FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[{role_name | パブリック | CURRENT_USER | SESSION_USER } [, ...] ]
[USING ( using_expression ) ]
[チェック付き (check_expression ) ]

次のリストでは、上記のステートメントのパラメーターについて説明します。

USING句を使用すると、既存のステートメントが作成されたRLSポリシーに準拠しているかどうかが確認されます。チェックは、SELECT、INSERT、UPDATE、またはDELETEステートメント、またはすべてのステートメントで実行できます。
WITH CHECK CLAUSEを使用すると、新しいステートメントが作成されたRLSポリシーに準拠しているかどうかがチェックされます。チェックは、SELECT、INSERT、UPDATE、またはDELETEステートメント、またはすべてのステートメントで実行できます。
USING句が使用されているがWITH CHECK句が使用されていない場合、USING句はWITH CHECK句としても機能します。同じステートメントに対して複数のRLSポリシーを作成し、いずれかのRLSポリシーに対してTRUEが返された場合、ステートメントはすべてのポリシーに準拠しています。たとえば、ALLを指定してRLSポリシーを作成し、ユーザーのSELECTステートメントに対して別のRLSポリシーを作成します。ユーザーがSELECTステートメントを実行するときに、RLSポリシーの1つに対してTRUEが返された場合、SELECTステートメントはすべてのポリシーに準拠します。

使用上の注意

ALTER TABLEステートメントを使用して、RLSポリシーを有効または無効にできます。アプリケーションのRLSポリシーを無効にする場合は、row_securityをoffに設定します。
```
ALTER TABLE table_name SET (row_security = off);
```
重要
上記のステートメントを実行すると、テーブルに対するすべてのクエリと操作はRLSポリシーの対象になりません。データベースのセキュリティとデータの整合性を確保するには、RLSを慎重に使用し、RLSポリシーを無効にできるユーザーとロールを管理する必要があります。場合によっては、RLSを無効にすると、データリークやデータ破損が発生する可能性があります。必要に応じてRLSポリシーを無効にすることを推奨します。
CREATE POLICY、ALTER POLICY、およびDROP POLICYステートメントを使用して、RLSポリシーを作成、変更、および削除できます。
各RLSポリシーには名前があり、テーブルに対して複数のポリシーを定義できます。テーブルのRLSポリシーの名前が一意であることを確認します。ただし、異なるテーブルのRLSポリシーは重複する名前を持つことができます。テーブルに対して複数のRLSポリシーが作成されている場合、RLSポリシーは論理ORを使用して評価されます。
DELETEやUPDATEなど、テーブル内のすべての行を管理するために実行されるステートメント、およびTRUNCATEステートメントとREFERENCESステートメントは、その動作特性のためにRLSポリシーの対象にはなりません。データのセキュリティと整合性を確保するために、テナントに権限を付与するときに、これらのステートメントに対する権限を取り消すことをお勧めします。

解決策

アーキテクチャ

各テナントには、tenant_idで指定されたテナントIDが割り当てられます。各テナントは、テナントがデータベースに接続するたびに、アプリケーションをデプロイするか、データベースのapp.tenant_idをテナントIDに設定します。接続の前に、テナントはテーブルのRLSポリシーを有効にする必要があります。このようにして、テナントはapp.tenant_idがtenant_idの値に設定されているデータベースのデータを管理できます。これにより、アプリケーションへの侵入の脅威が大幅に軽減され、データが分離されます。

例

手順1: 環境の準備とアカウントのアクセス許可の設定

環境を準備します。

インスタンス	データベースエンジンのバージョン	数量	仕様
ApsaraDB RDS for PostgreSQL インスタンス	15.0	1	4コア、16 GB、および拡張SSD (ESSD)
ECS インスタンス	CentOS 7.8 (64ビット)	1	4 コア 16 GB

説明

RDSインスタンスとECSインスタンスが同じリージョンと仮想プライベートクラウド (VPC) にある場合、ECSインスタンスのプライベートIPアドレスをRDSインスタンスのホワイトリストに追加する必要があります。

RDSインスタンスのアカウントを作成します。

説明

ApsaraDB RDSコンソールで特権アカウントを作成するだけで済みます。 SQL文を使用して、後続の手順で他のアカウントを作成できます。特権アカウントの作成方法の詳細については、「アカウントの作成」をご参照ください。

ユーザー名	アカウントタイプ	説明
su_user	特権アカウント	データベース管理者アカウント。
bypassrls_user	BYPASSRLS	一度に複数のデータレコードを更新するために使用されるアカウント。
t1	標準アカウント	テナントアプリケーションのデプロイに使用されるアカウント。
t2	標準アカウント	テナントアプリケーションのデプロイに使用されるアカウント。

ECSインスタンスにPostgreSQLクライアントをインストールします。詳細については、公式ドキュメントをご参照ください。

PostgreSQLクライアントのCLIを使用して、RDSインスタンスに接続します。詳細については、「ApsaraDB RDS For PostgreSQLインスタンスへの接続」をご参照ください。

特権アカウントを使用してテストデータを作成します。

データベースを作成します。
```
データベースの作成rls_vip;
```
rls_vipデータベースにスキーマを作成します。
```
スキーマを作成sh_vip;
```

テーブルを作成します。

テーブルの作成sh_vip_t_user (
  tenant_id bigint、
  user_id bigint、 
  tenant_name varchar(50) 、 
  PRIMARYキー (tenant_id, user_id)
);

テーブルにテストデータを挿入します。

INSERT INTO sh_vip_t_user値 (1、10、'Zhang San');
INSERT INTO sh_vip_t_user値 (2,20、'Li Si');

特権アカウントを使用して、テストアカウントとRLSポリシーを作成します。

標準ユーザーを作成し、必要な権限をユーザーに付与します。

-標準アカウントを作成します。
パスワード「TestPW123! 」でユーザーt1を作成します。パスワード「TestPW123! 」でユーザーt2を作成します。-アカウントに必要な権限を付与します。
SCHEMA sh_vipですべてをt1に承認します。テーブルsh_vip_t_userをすべてt1に承認します。SCHEMA sh_vipですべてをt2まで許可します。テーブルsh_vip_t_userをすべてt2に承認します。

RLSポリシーを作成して、sh_vip_t_userテーブルにデータを追加します。

ポリシーの作成sh_vip_t_user
ON sh_vip.t_user FOR ALL TO PUBLIC USING (
  tenant_id = (current_setting('app.tenant_id '::text))::bigint
);

次のSQL文を実行して、RLSポリシーの詳細を照会できます。

SELECT * からpg_policies;

サンプル出力：

schemaname | tablename | policyname | permissive | roles | cmd | qual | with_ch
eck
------------------------------------------------------------------------------ ---------------------------------------------------------------- -------------
----
 sh_vip | t_user | sh_vip_t_user | PERMISSIVE | {public} | ALL | (tenant_id = (current_setting('app.tenant_id '::text))::bigint) |
(1行)

RLSポリシーが有効かどうかを確認します。

\d sh_vipt_user;

説明

rls_vipデータベースで上記のコマンドを実行する必要があります。
出力では、行セキュリティ無効が返されます。これは、RLSポリシーが有効にならないことを示します。

サンプル出力：

テーブル "sh_vip_t_user"
   列 | タイプ | 照合 | Nullable | デフォルト
------------ ----------------------- --------------------------------------------
 tenant_id | bigint | | nullではない |
 user_id | bigint | | nullではない |
 tenant_name | character variing (50) | | |
インデックス:
    "t_user_pkey" PRIMARY KEY, btree (tenant_id, user_id)
ポリシー (行セキュリティ無効):
    ポリシー "sh_vip_t_user"
      USING ((tenant_id = (current_setting('app.tenant_id '::text))::bigint))

RLSポリシーを有効にします。

ALTER TABLE sh_vip__userは行レベルのセキュリティを有効にします。

RLSポリシーが再度有効になるかどうかを確認します。 行セキュリティ無効は返されません。

テーブル "sh_vip_t_user"
   列 | タイプ | 照合 | Nullable | デフォルト
------------ ----------------------- --------------------------------------------
 tenant_id | bigint | | nullではない |
 user_id | bigint | | nullではない |
 tenant_name | character variing (50) | | |
インデックス:
    "t_user_pkey" PRIMARY KEY, btree (tenant_id, user_id)
ポリシー:
    ポリシー "sh_vip_t_user"
      USING ((tenant_id = (current_setting('app.tenant_id '::text))::bigint))

手順2: 標準アカウントを使用してデータを追加、削除、変更、および照会できるかどうかを確認します

テーブルに対してRLSポリシーを有効にすると、各テナントにはtenant_idで指定されたIDが割り当てられます。このように、テナントは、テナントのデータのみを読み取り、変更することができる。

t1アカウントを使用してRDSインスタンスに接続します。
```
env PGOPTIONS="-c app.tenant_id=1" psql -h pgm-**** .pg.rds.aliyuncs.com -d rls_vip -U t1 -p 5432 -W
```
説明
t1アカウントに割り当てられたIDは1で、app.tenant_idは1に設定されます。データベース接続中に、envコマンドを実行して、PGOPTIONS環境変数を -c app.tenant_id=1に設定します。値で、-cは接続パラメーターを指定します。
テストデータを照会します。
```
SELECT * からsh_vip_t_user;
```
説明
標準テナントアカウントを使用してRDSインスタンスに接続すると、tenant_idが1のテナントのデータのみを表示できます。
サンプル出力：
```
tenant_id | user_id | tenant_name
---------- -------------------------------
         1 | 10 | 張サン
(1行) 
```
テストデータを挿入します。
```
INSERT INTO sh_vip.t_user値 (1、11、'Leo');
```
説明
テストデータの挿入に使用するテナントのtenant_idが1でない場合、新しい行が行レベルのセキュリティポリシーに違反しているテーブル "t_user" エラーが報告されます。
```
INSERT INTO sh_vip.t_user値 (2、21、'Darren');
```
テストデータを変更します。
```
UPDATE sh_vip_t_user SET tenant_name='Leo1' WHERE user_id = 11;
```
説明
- tenant_idが1でないテナントのデータを変更すると、変更は失敗します。
```
UPDATE sh_vip_t_user SET tenant_name='Leo1' WHERE user_id = 20;
```
- 特権アカウントを使用してRDSインスタンスに接続し、変更が成功したかどうかを確認できます。
テストデータを削除します。
```
DELETE FROM sh_vip_t_user WHERE user_id = 11;
```
説明
- tenant_idが1でないテナントのデータを削除すると、削除に失敗します。
```
DELETE FROM sh_vip_t_user WHERE user_id = 20;
```
- 特権アカウントを使用してRDSインスタンスに接続し、削除が成功したかどうかを確認できます。

手順3: グローバルデータの管理

標準テナントは、独自のデータのみを管理できます。 BYPASSRLS権限を持つ管理者とロールは、RLSポリシーの対象にはなりません。一度に複数のデータレコードを更新したり、完全なデータを照会したりする場合は、BYPASSRLS権限を持つロールまたはユーザーを作成する必要があります。特権アカウントを使用して次のSQL文を実行し、ロールまたはユーザーを作成できます。

ユーザーを作成するbypassrls_user BYPASSRLS PASSWORD 'TestPW123!';
SCHEMA sh_vipをすべてbypassrls_userに承認します。テーブルsh_vip_t_userをすべてbypassrls_userに承認します。

Javaの例

この例では、Mavenを使用して構築されたJavaアプリケーションを例として使用します。

pom.xmlファイルで依存関係を設定します。

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.3.1</version>
</dependency>

SaaSrlsControllerファイルを作成し、Javaで次のサンプルコードをファイルにコピーします。

java.sql.Connectionをインポートします。java.sql.DriverManagerをインポートします。java.sql.ResultSetをインポートします。java.sql.SQLExceptionをインポートします。java.sql.Statementをインポートします。java.util.Propertiesをインポートします。パブリッククラスSaaSrlsController {
public static void main( String[] args ){
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}

// RDSインスタンスのエンドポイント
文字列ホスト名="pgm-**** .pg.rds.aliyuncs.com";
// RDSインスタンスのポート番号
int port = 5432;
// データベース名
文字列dbname = "rls_vip";
// ユーザー名。
		文字列username = "t1";
// パスワード。
		文字列パスワード="*****";

String dbUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname;
接続dbConnection;
try {
Properties props = new Properties();
props.setProperty("user" 、username);
props.setProperty("password" 、password);
                       // 注: 割り当てられたテナントIDを使用します。
			props.setProperty("options","-c app.tenant_id=1");

dbConnection = DriverManager.getConnection(dbUrl、props);
ステートメントステートメント=dbConnection.createStatement();

// 実行するSQL文を入力します。 
			String selectSql = "select * from rls_schema.t_user;";
ResultSet resultSet = statement.exe cuteQuery(selectSql);
while (resultSet.next()) {
System.out.println(resultSet.getString("tenant_id") + "," + resultSet.getString("tenant_name"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

結論

このソリューションでは、テナント固有のフィールドとRLSポリシーを組み合わせることで、データのクエリ、変更、削除中にテナントIDを自動的に識別できます。これにより、アプリケーションへの侵入の脅威が大幅に減少します。