文档

基于行级安全策略实现SAAS租户数据隔离解决方案

更新时间:

本文介绍如何通过数据库层面行级安全策略做数据权限隔离,方便SAAS场景小微租户的管理。

背景信息

本文介绍PostgreSQL数据库中的行级安全策略,可用于实现数据权限隔离,允许管理员对表格的行创建访问规则,以控制用户可以查看或修改的行。在SAAS场景中,通过采用字段分租户和行级安全策略,可以有效缓解小租户库表隔离下库表多,维护困难的问题。

行级安全策略简介

行安全策略是PostgreSQL 9.5版本新增的特性,该特性是在数据库授权体系下提供更细粒度的控制。

通俗的说,就是不同用户可以看到表中不同的数据,这种控制是行级别的。所有对数据的操作,包括数据查询和更新,都受策略的限制。

行级安全策略应用范围

  • 行级安全策略可以加在命令上,命令可以是ALLSELECTINSERTUPDATEDELETE

  • 行级安全策略也可以赋予多个角色。

  • 行级安全策略可以同时加在命令上和授权给角色。

行级安全策略语法

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:针对已经存在的记录的校验,可实施在SELECTINSERTUPDATEDELETE,或者ALL上。

  • WITH CHECK:针对将要新增的记录的校验,可实施在SELECTINSERTUPDATEDELETE,或者ALL上。

  • 如果只写了USING,但是没有提供WITH CHECK的话,USING同时会当成WITH CHECK来进行检查。如果针对同样的命令创建了多个策略,所有策略中任意一个为TRUE都通过,例如ALLSELECT各创建了一个策略对于指定用户, 执行SELECT时任意一个为TRUE都通过。

注意事项

  • ALTER TABLE可以用于行级安全性的启用/禁用。如果应用想忽略行级安全性机制的限制,可以将row_security设置为off

    ALTER TABLE table_name SET (row_security = off);
    重要

    执行上述命令后,所有对该表的查询和操作将不再受到行级安全机制的限制。为了确保数据库的安全性和数据完整性,应该谨慎地使用此功能,并限制可执行此操作的用户和角色。在某些情况下,关闭行级安全机制可能会导致数据泄露或损坏的风险,因此应该在必要时才使用。

  • 您可以使用CREATE POLICYALTER POLICYDROP POLICY命令来管理策略(创建、修改和删除)。

  • 每个策略都有一个名字,每个表可以定义多个策略,因为策略是针对表的,所以表内的多个策略名字必须唯一,但是不同的表可以有同名的策略,当表有多个策略时,多个策略之间是OR的关系。

  • 全表操作命令(如DELETEUPDATE等可以操作表中所有行的命令)和TRUNCATE、REFERENCES命令由于其特殊的操作方式,不受行级安全机制的影响。因此在授权租户时,需要注意剔除这些可能导致全表操作的权限,以确保数据的安全性和完整性。

方案介绍

方案架构

每一个租户分配一个租户ID(tenant_id),每一个租户部署一套应用(或者租户每次获取数据库连接时,都将app.tenant_id参数设置到连接对象),数据库中提前配置启用表的行安全策略,租户增删改查tenant_id等于app.tenant_id的数据,可以大幅度降低对应用的侵入,实现租户数据隔离。

image

方案示例

步骤一:环境准备及账号权限配置

  1. 环境准备。

    软件

    版本

    数量

    规格

    RDS PostgreSQL

    15.0

    1

    4核16GBESSD云盘版

    ECS

    CentOS 7.8 64位

    1

    4核16GB

    说明

    RDS PostgreSQL与ECS在同一地域同一VPC下,将ECS私有IP加入到RDS PostgreSQL的白名单中。

  2. RDS PostgreSQL数据库账号规划。

    说明

    您只需要通过RDS PostgreSQL管理控制台创建高权限账号即可,其他账号在后续步骤中通过SQL创建,无需提前创建。创建高权限账号的方法,请参见创建账号

    账号

    权限类型

    用途

    su_user

    高权限账号

    数据库管理员账号

    bypassrls_user

    BYPASSRLS

    执行批量更新数据的账号

    t1

    普通账号

    部署租户应用账号

    t2

    普通账号

    部署租户应用账号

  1. 在ECS上安装PostgreSQL客户端。具体操作,请参见官方文档

  1. 通过PostgreSQL客户端命令行工具远程连接RDS PostgreSQL实例。具体操作,请参见连接PostgreSQL实例

  1. 使用高权限账号创建测试数据。

    1. 创建数据库。

      CREATE DATABASE rls_vip;
    2. 在数据库rls_vip下创建schema。

      CREATE SCHEMA sh_vip;
    3. 创建数据表。

      CREATE TABLE sh_vip.t_user(
        tenant_id bigint,
        user_id bigint, 
        tenant_name varchar(50), 
        PRIMARY KEY(tenant_id, user_id)
      ); 
    4. 插入测试数据。

      INSERT INTO sh_vip.t_user values(1, 10, '张三'); 
      INSERT INTO sh_vip.t_user values(2, 20, '李四'); 
  1. 使用高权限账号创建测试账号和行安全策略。

    1. 创建普通用户并授权。

      - 创建普通用户
      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; 
    2. 为表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)
  1. 查看行安全策略是否生效。

    \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))
  1. 使行安全策略生效。

    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,通过行级权限管控,只允许读取和变更自己的数据。

  1. 使用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 指定连接参数。

  2. 查询测试。

    SELECT * FROM sh_vip.t_user;
    说明

    使用租户普通账号登录后,只能查到tanant_id等于1(t1的租户ID)的数据。

    返回结果示例:

     tenant_id | user_id | tenant_name
    -----------+---------+-------------
             1 |      10 | 张三
    (1 row)
  3. 插入数据测试。

    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'); 
  4. 修改数据测试。

    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;
    • 您可以使用高权限账号连接实例后查看是否修改成功。

  5. 删除数据测试。

    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应用程序为例。

  1. 在pom.xml中配置依赖。

    <dependency>
    	<groupId>org.postgresql</groupId>
    	<artifactId>postgresql</artifactId>
    	<version>42.3.1</version>
    </dependency>
  1. 创建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)