Roles

A role is an abstract set of subjects, scoped to one organization: (org_id, name) is unique. The physician role at Clinique Valmont Nord and the physician role at Clinique Valmont Sud are different rows with different rules.

INSERT INTO morbac.roles (org_id, name, description)
SELECT id, 'physician', 'Licensed medical doctor'
FROM morbac.orgs WHERE name = 'Clinique Valmont Nord';

Users are external UUIDs; pgmorbac stores no user table. Assignment binds user, role and org:

SELECT morbac.assign_role(
    '11111111-1111-4111-8111-111111111111',   -- Dr. Camille Rousseau
    (SELECT r.id FROM morbac.roles r
     JOIN morbac.orgs o ON o.id = r.org_id
     WHERE o.name = 'Clinique Valmont Nord' AND r.name = 'physician'),
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord')
);

assign_role / revoke_role are convenience wrappers that also enforce separation-of-duty and cardinality constraints; inserting into morbac.user_roles directly bypasses neither RLS nor the constraint triggers.

Role hierarchy

Senior roles inherit the permissions of junior roles via morbac.role_hierarchy:

INSERT INTO morbac.role_hierarchy (senior_role_id, junior_role_id)
VALUES (:chief_physician_id, :physician_id);

Grant a rule to staff (say, read on patients) and every physician and chief-physician holds it too. Rules attach at the most junior level that should have them; seniors accumulate.

Effective roles

At decision time the engine computes the user's comprehensive role set in the org:

1. Direct assignments from user_roles.
2. Delegated roles: active, unrevoked delegations whose delegator still holds the role.
3. Inherited roles: junior roles of everything collected so far, transitively.
4. Derived roles: condition-based membership evaluated on the fly.

Anything listed in negative_role_assignments is subtracted at every step: a negative assignment beats direct grants, delegations and derivations alike.

SELECT r.name, cr.source
FROM morbac.get_comprehensive_roles(
    '11111111-1111-4111-8111-111111111111',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord')) cr
JOIN morbac.roles r ON r.id = cr.role_id;