使用非SQL连接用户名的tenant_id实现PostgreSQL行级安全

可行性确认 #

完全可以实现，无需将数据库用户名设置为UUID格式的tenant_id，通过用户-租户映射表或角色属性绑定两种方式，就能关联友好用户名与对应的tenant_id，进而实现行级隔离。

具体操作方法 #

方法一：用户-租户映射表（兼容性强，适配所有PostgreSQL版本） #

通过专门的映射表存储友好用户名和tenant_id的对应关系，再用函数动态获取当前用户的tenant_id：

1. 创建映射表

CREATE TABLE user_tenant_mapping (
    username TEXT PRIMARY KEY,
    tenant_id UUID NOT NULL UNIQUE
);

2. 插入用户与租户的绑定关系

INSERT INTO user_tenant_mapping (username, tenant_id)
VALUES ('my_friendly_client_name', '12345678-1234-1234-1234-abcdef012345');

3. 创建获取当前租户ID的函数

CREATE OR REPLACE FUNCTION get_current_tenant_id()
RETURNS UUID AS $$
SELECT tenant_id FROM user_tenant_mapping WHERE username = current_user;
$$ LANGUAGE sql STABLE;

标记为STABLE可让PostgreSQL缓存函数结果，减少重复查询开销

4. 启用行级安全并创建隔离策略

ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy ON my_table
USING (tenant_id = get_current_tenant_id());

5. 授予用户映射表的查询权限

GRANT SELECT ON user_tenant_mapping TO my_friendly_client_name;

方法二：角色属性绑定（PostgreSQL 12+推荐） #

利用PostgreSQL 12及以上版本支持的自定义角色配置参数，直接将tenant_id绑定到友好用户角色上，无需额外映射表：

1. 创建友好用户

CREATE USER "my_friendly_client_name" WITH PASSWORD 'your_secure_password';

2. 给用户绑定tenant_id（自定义配置参数）

ALTER ROLE "my_friendly_client_name" SET "tenant_id" TO '12345678-1234-1234-1234-abcdef012345';

也可用角色注释存储tenant_id：COMMENT ON ROLE "my_friendly_client_name" IS '12345678-1234-1234-1234-abcdef012345';

3. 创建获取当前租户ID的函数
   如果用自定义配置参数：

CREATE OR REPLACE FUNCTION get_current_tenant_id()
RETURNS UUID AS $$
SELECT current_setting('tenant_id')::UUID;
$$ LANGUAGE sql STABLE;

如果用角色注释：

CREATE OR REPLACE FUNCTION get_current_tenant_id()
RETURNS UUID AS $$
SELECT obj_description(('role:' || current_user)::regrole)::UUID;
$$ LANGUAGE sql STABLE;

4. 启用行级安全并创建策略（同方法一）

ALTER TABLE my_table ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy ON my_table
USING (tenant_id = get_current_tenant_id());

性能损耗分析 #

两种方案的性能损耗都可以忽略不计：

• 映射表方案：通过主键查询映射表，速度极快；函数标记STABLE后，PostgreSQL会缓存结果，不会重复执行查询。
• 角色属性方案：直接读取角色内置属性/配置，开销比映射表更小。
• 行级安全本身的策略评估是简单的相等匹配，只要给my_table.tenant_id创建索引，查询性能和普通无隔离的查询几乎一致：

CREATE INDEX idx_my_table_tenant_id ON my_table(tenant_id);

内容的提问来源于stack exchange，提问作者dstj