时间戳

首页 > 解决方案 > 一对多关系的外键

postgresql - 一对多关系的外键

问题描述

我想构建一个具有全局和自定义角色的多租户解决方案。
应用程序的授权将基于权限构建。
然后可能会定义一个组合多个权限的角色。

我想提供几个预定义的全局角色，每个人都可以使用。
此外，我想为租户提供定义自己的自定义角色的能力。

目前，我有以下架构设计草案（postgres）：

create table tenants (
  id uuid primary key,
  ...
);

create table permissions (
  code character varying(30) primary key
);

create table roles (
  id  uuid primary key,
  tenant_id  uuid null references tenants,
  ...
);

create unique index on roles (id, tenant_id) where tenant_id is not null;

create table role_permissions (
  role_id uuid not null references roles,
  permission_id character varying(30) not null references permissions,
  unique (role_id, permission_id)
);

create table users (
  id uuid not null,
  tenant_id uuid not null references tenants,
  ...
  primary key (id, tenant_id)
);

create table user_roles (
  tenant_id uuid not null,
  user_id uuid not null,
  role_tenant_id uuid null,
  role_id uuid not null references roles,
  foreign key (user_id, tenant_id) references users (id, tenant_id),
  check (customer_id = role_tenant_id or role_tenant_id is null)
);

在此架构中，我无法正确引用 user_roles 中的角色。

是否可以在 postgres 中实现这样的约束？

标签： postgresqlforeign-keysnullable

解决方案

我假设对customer表的引用并customer_id从表的定义开始users确实意味着引用tenantand tenant_id。

在某些时候，您需要相信您的代码是正确的。如果这对您来说还不够好，并且您必须有限制，那么这就是我要做的：


create or replace function user_role_check(_user_id uuid, _role_id uuid)
  returns boolean as $$
  select count(*) = 1 
    from roles r
         join users u
           on u.tenant_id = r.tenant_id
   where u.id = _user_id
     and r.id = _role_id;
$$ language sql;

create table user_roles (
  id uuid not null primary key,
  user_id uuid references users(id),
  role_id uuid references roles(id),
  check (user_role_check(user_id, role_id)),
  unique (user_id, role_id)
);

否则你会被困在复制tenant_id到user_roles.

推荐阅读