本文介紹如何通過資料庫層面行級安全性原則做資料許可權隔離，方便SAAS情境小微租戶的管理。
背景資訊
本文介紹PostgreSQL資料庫中的行級安全性原則，可用於實現資料許可權隔離，允許管理員對錶格的行建立訪問規則，以控制使用者可以查看或修改的行。在SAAS情境中，通過採用欄位分租戶和行級安全性原則，可以有效緩解小租戶庫表隔離下庫表多，維護困難的問題。
行級安全性原則簡介
行安全性原則是PostgreSQL 9.5版本新增的特性，該特性是在資料庫授權體系下提供更細粒度的控制。
通俗的說，就是不同使用者可以看到表中不同的資料，這種控制是行層級的。所有對資料的操作，包括資料查詢和更新，都受策略的限制。
行級安全性原則應用範圍
行級安全性原則可以加在命令上，命令可以是ALL、SELECT、INSERT、UPDATE和DELETE。
行級安全性原則也可以賦予多個角色。
行級安全性原則可以同時加在命令上和授權給角色。
行級安全性原則文法
CREATE POLICY name ON table_name
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
[ USING ( using_expression ) ]
[ WITH CHECK ( check_expression ) ] 各欄位解釋：
USING：針對已經存在的記錄的校正，可實施在SELECT，INSERT，UPDATE，DELETE，或者ALL上。
WITH CHECK：針對將要新增的記錄的校正，可實施在SELECT，INSERT，UPDATE，DELETE，或者ALL上。
如果唯寫了USING，但是沒有提供WITH CHECK的話，USING同時會當成WITH CHECK來進行檢查。如果針對同樣的命令建立了多個策略，所有策略中任意一個為TRUE都通過，例如ALL和SELECT各建立了一個策略對於指定使用者, 執行SELECT時任意一個為TRUE都通過。
注意事項
ALTER TABLE可以用於行級安全性的啟用/禁用。如果應用想忽略行級安全性機制的限制，可以將row_security設定為off。
ALTER TABLE table_name SET (row_security = off);重要執行上述命令後，所有對該表的查詢和操作將不再受到行級安全機制的限制。為了確保資料庫的安全性和資料完整性，應該謹慎地使用此功能，並限制可執行此操作的使用者和角色。在某些情況下，關閉行級安全機制可能會導致資料泄露或損壞的風險，因此應該在必要時才使用。
您可以使用CREATE POLICY、ALTER POLICY和DROP POLICY命令來管理原則（建立、修改和刪除）。
每個策略都有一個名字，每個表可以定義多個策略，因為策略是針對錶的，所以表內的多個策略名稱字必須唯一，但是不同的表可以有同名的策略，當表有多個策略時，多個策略之間是OR的關係。
全表操作命令（如DELETE和UPDATE等可以動作表中所有行的命令）和TRUNCATE、REFERENCES命令由於其特殊的操作方式，不受行級安全機制的影響。因此在授權租戶時，需要注意剔除這些可能導致全表操作的許可權，以確保資料的安全性和完整性。
方案介紹
方案架構
每一個租戶分配一個租戶ID（tenant_id），每一個租戶部署一套應用（或者租戶每次擷取資料庫連接時，都將app.tenant_id參數設定到連線物件），資料庫中提前配置啟用表的行安全性原則，租戶增刪改查tenant_id等於app.tenant_id的資料，可以大幅度降低對應用的侵入，實現租戶資料隔離。
方案樣本
步驟一：環境準備及帳號許可權配置
環境準備。
軟體
版本
數量
規格
RDS PostgreSQL
15.0
1
4核16GBESSD雲端硬碟版
ECS
CentOS 7.8 64位
1
4核16GB
說明RDS PostgreSQL與ECS在同一地區同一VPC下，將ECS私人IP加入到RDS PostgreSQL的白名單中。
RDS PostgreSQL資料庫帳號規劃。
說明您只需要通過RDS PostgreSQL管理主控台建立高許可權帳號即可，其他帳號在後續步驟中通過SQL建立，無需提前建立。建立高許可權帳號的方法，請參見建立帳號。
帳號
權限類別型
用途
su_user
高許可權帳號
資料庫管理員帳號
bypassrls_user
BYPASSRLS
執行批次更新資料的帳號
t1
普通帳號
部署租戶應用帳號
t2
普通帳號
部署租戶應用帳號
在ECS上安裝PostgreSQL用戶端。具體操作，請參見官方文檔。
通過PostgreSQL用戶端命令列工具遠端連線RDS PostgreSQL執行個體。具體操作，請參見串連PostgreSQL執行個體。
使用高許可權帳號建立測試資料。
建立資料庫。
CREATE DATABASE rls_vip;在資料庫rls_vip下建立schema。
CREATE SCHEMA sh_vip;建立資料表。
CREATE TABLE sh_vip.t_user( tenant_id bigint, user_id bigint, tenant_name varchar(50), PRIMARY KEY(tenant_id, user_id) );插入測試資料。
INSERT INTO sh_vip.t_user values(1, 10, '張三'); INSERT INTO sh_vip.t_user values(2, 20, '李四');
使用高許可權帳號建立測試帳號和行安全性原則。
建立普通使用者並授權。
- 建立普通使用者 CREATE USER t1 WITH PASSWORD 'TestPW123!'; CREATE USER t2 WITH PASSWORD 'TestPW123!'; - 為普通使用者授權 GRANT ALL ON SCHEMA sh_vip TO t1; GRANT ALL ON TABLE sh_vip.t_user TO t1; GRANT ALL ON SCHEMA sh_vip TO t2; GRANT ALL ON TABLE sh_vip.t_user TO t2;為表sh_vip.t_user建立一個新增資料的策略。
CREATE POLICY sh_vip_t_user ON sh_vip.t_user FOR ALL TO PUBLIC USING( tenant_id = (current_setting('app.tenant_id'::text))::bigint );您可以使用如下SQL查詢策略詳情：
SELECT * FROM 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 row)
查看行安全性原則是否生效。
\d sh_vip.t_user;說明此命令需要切換至
rls_vip資料庫下執行。返回結果中可以看到
row security disabled的描述，及行安全性原則未生效。
返回結果樣本：
Table "sh_vip.t_user" Column | Type | Collation | Nullable | Default -------------+-----------------------+-----------+----------+--------- tenant_id | bigint | | not null | user_id | bigint | | not null | tenant_name | character varying(50) | | | Indexes: "t_user_pkey" PRIMARY KEY, btree (tenant_id, user_id) Policies (row security disabled): POLICY "sh_vip_t_user" USING ((tenant_id = (current_setting('app.tenant_id'::text))::bigint))
使行安全性原則生效。
ALTER TABLE sh_vip.t_user enable row level security;再次查看行安全性原則的生效情況，發現
row security disabled的描述已消失。Table "sh_vip.t_user" Column | Type | Collation | Nullable | Default -------------+-----------------------+-----------+----------+--------- tenant_id | bigint | | not null | user_id | bigint | | not null | tenant_name | character varying(50) | | | Indexes: "t_user_pkey" PRIMARY KEY, btree (tenant_id, user_id) Policies: POLICY "sh_vip_t_user" USING ((tenant_id = (current_setting('app.tenant_id'::text))::bigint))
步驟二：測試普通帳號增刪改查
表啟用行安全性原則後，每個租戶會提前分配一個tanant_id，通過行級許可權管控，只允許讀取和變更自己的資料。
使用t1帳號串連RDS PostgreSQL執行個體。
env PGOPTIONS="-c app.tenant_id=1" psql -h pgm-****.pg.rds.aliyuncs.com -d rls_vip -U t1 -p 5432 -W說明分配給租戶t1的數位識別碼為1，即app.tenant_id=1，在串連資料庫時，使用
env命令來設定環境變數PGOPTIONS，它的值為-c app.tenant_id=1，其中-c指定串連參數。查詢測試。
SELECT * FROM sh_vip.t_user;說明使用租戶普通帳號登入後，只能查到tanant_id等於1（t1的租戶ID）的資料。
返回結果樣本：
tenant_id | user_id | tenant_name -----------+---------+------------- 1 | 10 | 張三 (1 row)插入資料測試。
INSERT INTO sh_vip.t_user VALUES(1, 11, 'Leo');說明如果插入的租戶ID不是1，則會在插入時報錯
new row violates row-level security policy for table "t_user"。INSERT INTO sh_vip.t_user VALUES(2, 21, 'Darren');修改資料測試。
UPDATE sh_vip.t_user SET tenant_name='Leo1' WHERE user_id = 11;說明如果修改其他租戶的資料，則會修改不生效。
UPDATE sh_vip.t_user SET tenant_name='Leo1' WHERE user_id = 20;您可以使用高許可權帳號串連執行個體後查看是否修改成功。
刪除資料測試。
DELETE FROM sh_vip.t_user WHERE user_id = 11;說明如果刪除其他租戶的資料，則刪除不生效。
DELETE FROM sh_vip.t_user WHERE user_id = 20;您可以使用高許可權帳號串連執行個體後查看是否刪除成功。
步驟三：全域資料管理
普通使用者只能操作自己的資料，而管理使用者、具備BYPASSRLS屬性的角色不受行安全性的限制，如果需要批次更新資料或者查詢全量資料時，則需要建立具備BYPASSRLS屬性的角色或者使用者。您可以使用高許可權帳號執行如下SQL進行建立：
CREATE USER bypassrls_user BYPASSRLS PASSWORD 'TestPW123!';
GRANT ALL ON SCHEMA sh_vip TO bypassrls_user;
GRANT ALL ON TABLE sh_vip.t_user TO bypassrls_user;Java應用程式樣本
本文以Maven構建的Java應用程式為例。
在pom.xml中配置依賴。
<dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.3.1</version> </dependency>
建立
SaaSrlsController檔案並複製如下Java範例程式碼。import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class SaaSrlsController { public static void main( String[] args ){ try { Class.forName("org.postgresql.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } //執行個體串連地址 String hostname = "pgm-****.pg.rds.aliyuncs.com"; //執行個體串連連接埠 int port = 5432; //資料庫名稱 String dbname = "rls_vip"; //使用者名稱 String username = "t1"; //密碼 String password = "*****"; String dbUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname; Connection 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); Statement statement = dbConnection.createStatement(); //輸入需要執行的SQL語句。 String selectSql = "select * from rls_schema.t_user;"; ResultSet resultSet = statement.executeQuery(selectSql); while (resultSet.next()) { System.out.println(resultSet.getString("tenant_id") + "," +resultSet.getString("tenant_name")); } } catch (SQLException e) { e.printStackTrace(); } } }
方案總結
本文介紹的解決方案中，按欄位分租戶結合行安全性原則，在查改刪時可以自動帶入租戶ID，可以大幅度降低對應用的侵入。