Recipes

Derived Roles

Condition-based role membership evaluated at decision time.

Situation. Each department at Valmont has a head, recorded in the application's own app.departments table. Whoever is head must automatically hold the department-head role, and lose it the moment the table says someone else took over. Nobody should have to remember to revoke anything.

Assumes the shared Valmont setup.

The application table

CREATE TABLE IF NOT EXISTS app.departments (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id        UUID NOT NULL REFERENCES morbac.orgs(id),
    name          TEXT NOT NULL,
    head_user_id  UUID
);
 
INSERT INTO app.departments (org_id, name, head_user_id)
SELECT id, 'Cardiologie', '11111111-1111-4111-8111-111111111111'
FROM morbac.orgs WHERE name = 'Clinique Valmont Nord';

The predicate

A derived role's evaluator takes (user_id, org_id) and 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
    )
$$;

Wire it up

The role is an ordinary role and carries ordinary rules; only its membership is computed:

INSERT INTO morbac.roles (org_id, name)
SELECT id, 'department-head' FROM morbac.orgs WHERE name = 'Clinique Valmont Nord'
ON CONFLICT (org_id, name) DO NOTHING;
 
INSERT INTO morbac.derived_roles (role_id, condition_evaluator, description)
SELECT r.id, 'app.is_department_head'::regproc, 'Computed from app.departments'
FROM morbac.roles r JOIN morbac.orgs o ON o.id = r.org_id
WHERE o.name = 'Clinique Valmont Nord' AND r.name = 'department-head'
ON CONFLICT (role_id) DO NOTHING;
 
INSERT INTO morbac.rules (org_id, role_id, activity, view, context_id, modality)
SELECT o.id, r.id, 'read', 'invoices',
       (SELECT id FROM morbac.contexts WHERE name = 'always'), 'permission'
FROM morbac.orgs o JOIN morbac.roles r ON r.org_id = o.id AND r.name = 'department-head'
WHERE o.name = 'Clinique Valmont Nord'
ON CONFLICT DO NOTHING;

Proof: membership follows the data

SELECT morbac.is_allowed('11111111-1111-4111-8111-111111111111',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord'),
    'read', 'invoices');                                    -- true (Camille is head)
 
UPDATE app.departments SET head_user_id = '22222222-2222-4222-8222-222222222222'
WHERE name = 'Cardiologie';
SELECT morbac.invalidate_cache();   -- app tables have no morbac trigger
 
SELECT morbac.is_allowed('11111111-1111-4111-8111-111111111111',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord'),
    'read', 'invoices');                                    -- false
SELECT morbac.is_allowed('22222222-2222-4222-8222-222222222222',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord'),
    'read', 'invoices');                                    -- true

Cache invalidation is your job here

morbac triggers cannot watch your tables. Either flush after changing the source data, as above, or attach a trigger of your own:

CREATE OR REPLACE FUNCTION app.departments_invalidate() RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
    PERFORM morbac.invalidate_cache(NULL, COALESCE(NEW.org_id, OLD.org_id));
    RETURN NULL;
END $$;
 
CREATE TRIGGER services_invalidate
AFTER INSERT OR UPDATE OR DELETE ON app.departments
FOR EACH ROW EXECUTE FUNCTION app.departments_invalidate();

Guidance

Keep the evaluator STABLE and indexed on the columns it reads: it runs inside uncached decisions.
Derived membership is still filtered by negative role assignments, so you can exclude one person from an otherwise automatic role.
If the condition is about when rather than who, use a context instead.