Constraints

Four constraint tables shape who can hold which roles, upstream of any rule evaluation.

Negative role assignments

An explicit "never this role for this user", stronger than every positive path: direct assignment, delegation and derivation are all filtered against it.

INSERT INTO morbac.negative_role_assignments (user_id, role_id, org_id, reason)
VALUES (:olivier, :billing-clerk_id, :nord_id, 'Under investigation');

Even if someone re-assigns Olivier the role tomorrow, or a colleague delegates it to him, his effective role set never contains it.

Separation of duty (SoD)

morbac.sod_conflicts declares mutually exclusive role pairs per org. At Valmont, the person who writes a prescription must not be the person who fills it:

INSERT INTO morbac.sod_conflicts (role_a_id, role_b_id, org_id, description)
VALUES (:physician_id, :pharmacist_id, :nord_id,
        'Prescriber and dispenser must differ');

assign_role() checks the table and raises on violation. The SoD recipe shows the full flow.

Role cardinality

Bounds on how many users may hold a role:

INSERT INTO morbac.role_cardinality (role_id, min_users, max_users, description)
VALUES (:auditor_id, NULL, 2, 'At most two auditors at Group level');

assign_role() rejects the third auditor. min_users is informational (reported by check_cardinality_violation); the engine cannot conjure a missing user.

Derived roles

Membership computed at decision time instead of stored. The role exists normally and carries rules normally; morbac.derived_roles attaches a predicate function(user_id, org_id) returns boolean:

CREATE OR REPLACE FUNCTION app.is_department_head(p_user UUID, p_org UUID)
RETURNS BOOLEAN LANGUAGE sql STABLE AS $$
    SELECT EXISTS (SELECT 1 FROM app.departments s
                   WHERE s.head_user_id = p_user AND s.org_id = p_org)
$$;
 
INSERT INTO morbac.derived_roles (role_id, condition_evaluator, description)
VALUES (:department_head_role_id, 'app.is_department_head'::regproc,
        'Computed from the services table');

Whoever the services table says is head is department-head, with no assignment to forget to revoke. Negative assignments still win over derivation. Full example: derived roles recipe.