Clé étrangère de la relation un-à-tous

Question

Je souhaite créer une solution mutualisée avec des rôles globaux et personnalisés.
L'autorisation de l'application sera créée en fonction des autorisations.
Ensuite, il peut être défini un rôle qui combine plusieurs autorisations.

Je souhaite fournir plusieurs rôles globaux prédéfinis pouvant être utilisés par tout le monde.
En outre, je souhaite offrir aux locataires la possibilité de définir leurs propres rôles personnalisés.

Pour le moment, j'ai le brouillon suivant de la conception du schéma (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)
);

Dans ce schéma, je ne suis pas en mesure de référencer correctement les rôles à partir de user_roles.

Est-il possible d'implémenter une telle contrainte dans postgres?

Answer 1

Je suppose que les références au customertableau et en customer_idcommençant par la définition de votre userstableau signifient vraiment faire référence à tenantet tenant_id.

À un moment donné, vous devez faire confiance à votre code pour être correct. Si cela ne vous suffit pas et que vous devez avoir des contraintes, voici ce que je ferais:

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

Sinon, vous êtes coincé tenant_iddans la duplication user_roles.