【Postgresql数据库行级锁】

租户ID无感化使用手册

使用背景
业务系统从单租架构向多租架构改造演进中，作为持久化存储层的数据库，需要存储多个租户的业务数据，各租户之间数据隔离，一般采用逻辑隔离和物理隔离两种策略：

1. 逻辑隔离是各租户的数据仍然存储在同一个数据库，增删查改操作通过数据表的 tenant_id 字段进行筛选隔离；
2. 物理隔离是各租户使用独立的数据库，增删查改的数据完全物理隔离，运行时可按租户ID路由到预期的数据源

这里，我们采用逻辑隔离策略，探讨对数据表的 tenant_id 字段的相关使用，一般有两种策略：
客户端重写SQL语句，增删查改（select, insert, update, delete 语句）需要拿当前 tenant_id 作为筛选条件或属性数据来使用，添加到重写的SQL语句中；
数据库服务侧行级安全策略 RLS，在数据库侧通过 RLS 对数据进行筛选过滤，客户端的SQL语句保持不变，执行SQL时租户无感；
前提条件
数据库须支持 RLS 行级安全策略
备注： Msql 不支持， postgres 支持
Postgresql 行级安全策略 RLS
RLS 即 Row-Level Security，数据侧 Security Policy 在数据行级别上控制用户的访问，粒度是数据行，控制用户只能访问数据表的特定数据行。

开启 RLS 行级安全策略：
ALTER TABLE <table_name> ENABLE ROW LEVEL SECURITY;
备注：RLS是针对数据库表级的，其中 <table_name> 代表需要按租户ID筛选数据库表

强制开启 RLS 策略（针对开启RLS策略失效时）
ALTER TABLE <table_name> force row level security;

创建 RLS 具体策略：
create policy tenant_isolation_policy on <table_name>
USING (tenant_id = current_setting(‘app.current_tenant’));
备注：配置访问 PG 数据库的用户，不能是 super user 类型，否则 RLS 行级策略会失效。

组件依赖
备注：0.0.1版本使用

<dependency>
  <groupId>XXX.XXX.XXX</groupId>
  <artifactId>XXX-orm-tenancy</artifactId>
  <version>0.0.1.SNAPSHOT</version>
</dependency>

配置说明
application.properties 配置文件
#租户无感化开启配置，默认值为 true
xxx.dao.tenant-unaware.enabled=true
#配置需要多租化的数据库表名，支持正则表达式
xxx.dao.tenant-unaware.tables=t_class,tfd_*
#配置需要多租化的数据库表的字段名，默认值为 “tenant_id”
xxx.dao.tenant-unaware.tenantid.column=tenant_id

datasource.configs.xml 数据源配置文件

数据源类把 dpcb2 的 BasicDataSource 替换为 租户ID无感数据源： JalorTenancyDataSource
该数据源类也继承了dpcb2 数据源类，扩展的主要功能是：在执行SQL语句之前，获取数据源 connection 连接对象之后，通过执行 SET 语句，把当前请求上下文中的 tenanId 作为参数传给到PG数据库，例如： SET app.current_tenant = ‘888’

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
 ... ...
  <property name="plugins">
    <list>
        <bean id="pagePlugin" class="com.xxx.core.orm.PageInterceptor"/>
        <bean id="jalorResultSetPlugin" class="com.xxx.core.orm.ResultSetInterceptor"/>
        <bean id="programPlugin" class="com.xxx.core.orm.ProgramInterceptor"/>
        <bean id="multiTenantInterceptor"                  
                class="com.xxx.multitenant.interceptor.TenantUnawareInterceptor"/>
    </list>
  </property>

在 “sqlSessionFactory” Bean XML 声明中， 往 plugins 属性添加租户无感的拦截器：TenantUnawareInterceptor
该拦截器主要针对 insert into 的SQL语句进行重写， 例如
重写之前的SQL语句为：
INSERT INTO orders (orderNumber, orderDate, requiredDate, shippedDate, status , comments, customerNumber)
VALUES (?, ?, ?, ?, ? , ?, ?)
重写之后的SQL语句为：
INSERT INTO orders (orderNumber, orderDate, requiredDate, shippedDate, status , comments, customerNumber, tenant_id)
VALUES (?, ?, ?, ?, ? , ?, ?, ‘888’)

扩展篇
场景一：
上下文中无法获取 tenantId 时，可以通过 IDefaultTenantIdProvider.getTenantId() 接口的自定义spring bean 扩展实现兜底获取。
例如定时任务触发执行SQL语句时，当前上下文租户ID获取不到时（即 requestContext.getTenantId() == null 时）
使用 IDefaultTenantIdProvider.getTenantId() 扩展方法作为获取租户ID的兜底实现逻辑。

场景二：
针对一些特殊场景，不同的数据库表所使用的租户ID字段名称不一样，例如数据库表 table_A 使用的租户ID是 tenantId_a, 数据库表 table_B 使用的租户ID是 tenantId_b
可以通过 IDefaultTenantIdProvider.resolveTenantIdColumnName(String tableName) 接口的自定义spring bean 来扩展实现， 方法示例如下：
@Override
public String resolveTenantIdColumnName(String tableName) {
if(“table_A”.equals(tableName)){
return “tenantId_a”;
}
if(“table_B”.equals(tableName)){
return “tenantId_b”;
}
return null;
}
1
2
3
4
5
6
7
8
9
10
注意： 此方法 return null 时，会兜底使用配置 xxx.dao.tenant-unaware.tenantid.column的值； 如果没有配置，默认值为 tenant_id；
此扩展特性，限于 7.0.2.0-SP1.RELEASE 以上版本使用。

FAQ

1. 数据库 RLS 策略不生效，检查步骤：
   检查当前用户的类型，usersuper 应为 ‘f’
   select * from pg_user where usename = current_user ; (备注：示例SQL适用于postgres数据库)

检查开启 RLS 策略的表名是否包含在内
select relname,relrowsecurity from pg_class where relrowsecurity=‘t’; (备注：示例SQL适用于postgres数据库)

检查已经创建的 RLS 策略详情
select * from pg_policies
(备注：示例SQL适用于postgres数据库)

强制开启 RLS 策略
alter table [表名t_table_xxx] force row level security;

2. 设置PG数据库，用户级不启用 RLS 策略
   alter user <user_name> BYPASSRLS; （针对用户级 RLS 策略失效）
   alter user <user_name> NOBYPASSRLS; （针对用户级 RLS 策略生效）
   备注：其中 <user_name> 为用户名， RLS失效策略的用户一般用SQL客户端工具，不可用于部署应用中。

常见异常

1. SQL异常信息：
   Cause: org.postgresql.util.PSQLException: ERROR: unrecognized configuration parameter “app.current_tenant”;

问题分析：当前执行SQL语句中数据库表开启了RLS策略，但当前数据库连接 connection 没有设置 app.current_tenant 值， 根因是当前请求上下文 requestContext 中没有获取到 tenantId 所导致。
通常用户使用异步多线程场景，没有透传当前请求上下文，异步子线程执行DAO层操作时，因请求上下文 requestContext 为 null 导致出现该异常。

解决方案：
把主线程的请求上下文对象requestContext，透传到异步子线程中，在子线程执行SQL语句时，能从 requestContext 获取到 tenantId 信息。
例如: RequestContextManager.setCurrent(requestContext);

2.SQL异常信息：
ERROR: new row violates row-level security policy for table “tpl_personalized_setting_t22”
问题分析：当前执行 insert 语句中数据库表开启了RLS策略，RLS策略的cmd未包含 insert, 通过 select * from pg_policies; 语句查看RLS策略详情：

解决方案：
重新创建RLS策略，确保策略信息中的 cmd 值为 all
备注：执行的 insert 语句中也需要有字段 tenant_id ，否则也会报此异常。如果上述都检查了，再检查一下 租户无感的拦截器 TenantUnawareInterceptor 是否生效了

3. Insert into 场景的SQL异常：
   Error updating database. Cause: com.alibaba.druid.sql.parser.ParserException: syntax error. pos 989, line 1, column 989, token ERROR
   问题分析：对 insert 语句的租户无感处理逻辑，是通过mybatis拦截器会对insert 语句补充 tenantId 字段以及数值，其中把SQL语句解析成语法树的处理过程，使用的是 druid 开源组件。此问题发生在 insert into 语句的value数值中含有连续的单引号"'"时，druid解析SQL语句时会报以上的异常。

解决方案：
目前 druid 对数值中含有特色字符的处理不是特别好，不支持连续的单引号"\‘\’“，仅支持单引号”\'", 或者不连续的单引号。
由于使用单引号容易引发SQL注入安全问题，需要用户对连续的单引号的SQL进行限制或转换（例如单引号字符半角转全角）