本文介绍如何通过数据库层面行级安全策略做数据权限隔离，方便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，可以大幅度降低对应用的侵入。
- 本页导读 （1）