PostgreSQL Tenant

-- 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;

-- 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');

-- 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');

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'
  )
));