Skip to main content

PostgreSQL Tenant

context variable

  • set
    • session - 链接维度
    • local - tx 结束重置
  • 自定义变量最好 前缀+.
-- name, value, local
-- local 如果为 true 则优先返回 tx 范围 - 不存在不会异常
-- local 如果为 false 则返回 session 范围 - 不存在会异常
select set_config('tenant.id', 1000, true);
set tenant.id = 1000;

-- name, optional
select current_setting('tenant.id',true);
show tenant.id;

reset tenant.id;

-- 特殊
select current_user;

generate_res_id

针对租户+资源生成唯一 ID

-- TENANT-RES-ULID
create or replace function generate_res_id(name text) returns text
as
$$
begin
return concat(current_setting('tenant.id'), '-', name, '-', generate_ulid());
end
$$
language plpgsql
volatile;

select generate_res_id('user');

generate_res_sid

针对租户+资源生成唯一 序列 ID

  • 如果直接预先插入记录,则可只用 update 会更好
-- track sequence
create table system_resource_sequences
(
id serial,
tenant_id bigint,
resource text,
value bigint,
unique (tenant_id, resource)
);

-- generate_res_sid - upsert version
create or replace function generate_res_sid(res text)
returns bigint
as
$$
insert into system_resource_sequences(tenant_id, resource, value)
values (1000, res, 1)
on conflict(tenant_id,resource) do update set value=system_resource_sequences.value + 1
returning value
$$
language sql
volatile;

-- generate_res_sid - update and insert version
create or replace function generate_res_sid(res text)
returns bigint
as
$$
declare
id system_resource_sequences.value%TYPE;
tid system_resource_sequences.tenant_id%TYPE := current_setting('tenant.id')::bigint;
begin
-- trigger less default computing
update system_resource_sequences
set value=value + 1
where tenant_id = tid
and resource = res
returning value into id;
if id is null
then
insert into system_resource_sequences(tenant_id, resource, value)
values (tid, res, 1)
on conflict(tenant_id,resource) do update set value=system_resource_sequences.value + 1
returning value into id;
end if;

return id;
end;
$$
language plpgsql
volatile;
-- test insert
insert into system_resource_sequences(tenant_id, resource, value)
values (current_setting('tenant.id'), 'User', 1)
on conflict(tenant_id,resource) do update set value=system_resource_sequences.value + 1
returning value;

-- test function
select generate_res_sid('User');

RLS

大大简化 SaaS 数据隔离

ALTER TABLE tenant ENABLE ROW LEVEL SECURITY;

-- 用户维度
CREATE POLICY tenant_isolation_policy ON tenant
USING (tenant_id::TEXT = current_user);

-- 上下文信息
CREATE POLICY tenant_isolation_policy ON tenant
USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- 复杂 级联
CREATE POLICY my_fancy_policy
ON t_company
USING (manager IN ( WITH RECURSIVE t AS
(
SELECT current_user AS person, NULL::text AS manager
FROM t_manager
WHERE manager = CURRENT_USER
UNION ALL
SELECT m.person, m.manager
FROM t_manager m
INNER JOIN t ON t.person = m.manager
)
SELECT person FROM t
)
)
;

-- ACL
create policy item_owner
on items
as permissive
for all
to application_user
using (
items.acl_read && regexp_split_to_array(current_setting('jwt.claims.roles'), ',')::uuid[]
or items.acl_write && regexp_split_to_array(current_setting('jwt.claims.roles'), ',')::uuid[]
)
with check (
items.acl_write && regexp_split_to_array(current_setting('jwt.claims.roles'), ',')::uuid[]
);

create policy item_owner
on items
as permissive
for all
to application_user
using (
items.public = true
or exists(
select item_id
from permissions
where (
permissions.user_or_group_id =
any(regexp_split_to_array(current_setting('jwt.claims.role'), ',')::uuid[])
and permissions.item_id = items.id
)
)
)
with check (exists(
select item_id
from permissions
where (
permissions.user_or_group_id =
any(regexp_split_to_array(current_setting('jwt.claims.role'), ',')::uuid[])
and permissions.item_id = items.id
and permissions.role = 'write'
)
));