使用Postgresql 设计一个多租户数据隔离模型

设计一个多租户模型

数据库层面(更好的隔离性与安全性)

• 支持一个租户一个db
• 支持一个租户一个schema

应用程序层面

• 共享连接池
  • 动态切换role(租户)和schema
• 独享连接池
  • 租户独享连接池(直连db或schema独享连接)

限制租户的连接数

避免某个租户占用大量数据库连接.而导致某些租户拿不到连接

• 数据库层面限制

alter database t1_db connection limit 300;

• 查询当前限制

select datname,
       datconnlimit
from pg_database;

• 用户层面限制

ALTER USER t1 WITH CONNECTION LIMIT 20;

• 查询当前用户的限制

select rolname,
       rolconnlimit
from pg_roles;

切换租户SQL

• 当执行数据库操作前每次都首先执行的SQL.切换执行的上下文环境.
• 可以在执行jdbc的 getConnection方法获取连接之前执行SQL

public class Ds extends AbstractDataSource {
    public Connection getConnection() {
        Connection connection = dataSource.getConnection();
        connection.setSchema($tenant);
        Statement statement = connection.createStatement();
        statement.executeUpdate("SET SESSION role $tenant");
        return connection;
    }
}

• 对于高并发的业务建议对SQL手动增加schema
• 会增加耗时. 这个延迟在毫秒级别
• 对分布式事务的兼容性
  • 需测试
• SET SESSION role $tenant
• SET SESSION search_path TO $tenant

实例

• 共享用户

CREATE ROLE share_user WITH
    LOGIN
    NOSUPERUSER
    NOINHERIT--设置角色为不继承
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION;

• 租户1

CREATE ROLE t1 WITH
    LOGIN
    NOSUPERUSER
    NOINHERIT--设置角色为不继承
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION;

• 租户2

CREATE ROLE t2 WITH
    LOGIN
    NOSUPERUSER
    NOINHERIT--设置角色为不继承
    NOCREATEDB
    NOCREATEROLE
    NOREPLICATION;

关于NOINHERIT

• 在PostgreSQL中，角色可以是遗传的（INHERIT或NOINHERIT选项）
• 如果它是继承的，那么GRANT意味着您授予授予角色对被授权者角色的所有访问权。
• 如果未继承，则GRANT授予使用SET ROLE切换到该角色的权限。换句话说，GRANT可以访问其他角色，INHERIT可以确定该访问是否需要SET ROLE命令才能生效

授权

• share_user的set role 权限

GRANT t1, t2 TO share_user;

• 数据库

CREATE DATABASE share_db
    OWNER = share_user
    ENCODING = 'UTF8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    IS_TEMPLATE = False;

• 租户1的schema

CREATE SCHEMA IF NOT EXISTS t1
    AUTHORIZATION t1;

• 租户2的schema

CREATE SCHEMA IF NOT EXISTS t2
    AUTHORIZATION t2;

测试

• 使用share_user查询租户1的数据

share_db=> select * from t1.t1;
ERROR:  permission denied for schema t1
LINE 1: select * from t1.t1;

• 使用share_user查询租户2的数据

share_db=> select * from t2.t2;
ERROR:  permission denied for schema t2
LINE 1: select * from t2.t2;

• 切换role为t1之后查询

share_db=> set role t1;
SET
share_db=> select * from t1.t1;
id 
----
(0 rows)
share_db=> select * from t2.t2;
ERROR:  permission denied for schema t2
LINE 1: select * from t2.t2;
^
share_db=> reset role;
RESET

• 查询当前连接的用户

share_db=> select session_user,current_user;
 session_user | current_user 
--------------+--------------
 share_user   | share_user
(1 row)

• 设置当前的查询的schema

SET search_path TO "$user", public,t1;

• 查询当前的search_path

share_db=> show search_path;
     search_path     
---------------------
 "$user", public, t1
(1 row)