Recipes

Cross-Org Access

Granting one organization's role access into another organization.

Situation. Bertrand Delacroix is an auditor at the Groupe Hospitalier Valmont level. For a compliance review he must read invoices at Clinique Valmont Sud, a subsidiary where he holds no role. Nothing else about Sud should open up, and the access should end when the review does.

Assumes the shared Valmont setup.

Why not a subtree rule

A rule at the Groupe with scope subtree would work, but it grants the auditor role access to every org under the Groupe forever. cross_org_rules is the precise instrument: it names the source org where the role is held and the single target org where it applies.

INSERT INTO morbac.user_roles (user_id, role_id, org_id)
SELECT '44444444-4444-4444-8444-444444444444', r.id, r.org_id
FROM morbac.roles r JOIN morbac.orgs o ON o.id = r.org_id
WHERE o.name = 'Groupe Hospitalier Valmont' AND r.name = 'auditor'
ON CONFLICT DO NOTHING;
 
INSERT INTO morbac.cross_org_rules
    (source_org_id, target_org_id, role_id, activity, view, context_id, modality,
     valid_from, valid_until)
SELECT src.id, tgt.id, r.id, 'read', 'invoices',
       (SELECT id FROM morbac.contexts WHERE name = 'always'), 'permission',
       now(), now() + interval '30 days'
FROM morbac.orgs src
JOIN morbac.roles r ON r.org_id = src.id AND r.name = 'auditor'
CROSS JOIN morbac.orgs tgt
WHERE src.name = 'Groupe Hospitalier Valmont'
  AND tgt.name = 'Clinique Valmont Sud'
ON CONFLICT DO NOTHING;

Proof

SELECT morbac.is_allowed('44444444-4444-4444-8444-444444444444',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Sud'),
    'read', 'invoices');                                    -- true
 
-- nothing else at Sud
SELECT morbac.is_allowed('44444444-4444-4444-8444-444444444444',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Sud'),
    'read', 'patients');                                    -- false
 
-- and Nord was never mentioned
SELECT morbac.is_allowed('44444444-4444-4444-8444-444444444444',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord'),
    'read', 'invoices');                                    -- false

Semantics worth remembering

The role is resolved in the source org: Bertrand must hold auditor at the Groupe, not at Sud.
source_org_id and target_org_id must differ (check constraint); use an ordinary rule for same-org access.
Cross-org rules take part in the normal priority resolution at steps 2 and 5, so a prohibition at Sud with equal or higher priority still wins.
valid_from / valid_until expire the access without a cleanup job. Drop the row early to end the review sooner.

Reviewing what crosses boundaries

SELECT s.name AS source, t.name AS target, r.name AS role,
       c.activity, c.view, c.modality, c.valid_until
FROM morbac.cross_org_rules c
JOIN morbac.orgs  s ON s.id = c.source_org_id
JOIN morbac.orgs  t ON t.id = c.target_org_id
JOIN morbac.roles r ON r.id = c.role_id
ORDER BY t.name;

Keep this list short and reviewed: every row is a deliberate hole in tenant isolation.

Why not a subtree rule

INSERT INTO morbac.user_roles (user_id, role_id, org_id)
SELECT '44444444-4444-4444-8444-444444444444', r.id, r.org_id
FROM morbac.roles r JOIN morbac.orgs o ON o.id = r.org_id
WHERE o.name = 'Groupe Hospitalier Valmont' AND r.name = 'auditor'
ON CONFLICT DO NOTHING;
 
INSERT INTO morbac.cross_org_rules
    (source_org_id, target_org_id, role_id, activity, view, context_id, modality,
     valid_from, valid_until)
SELECT src.id, tgt.id, r.id, 'read', 'invoices',
       (SELECT id FROM morbac.contexts WHERE name = 'always'), 'permission',
       now(), now() + interval '30 days'
FROM morbac.orgs src
JOIN morbac.roles r ON r.org_id = src.id AND r.name = 'auditor'
CROSS JOIN morbac.orgs tgt
WHERE src.name = 'Groupe Hospitalier Valmont'
  AND tgt.name = 'Clinique Valmont Sud'
ON CONFLICT DO NOTHING;

Proof

SELECT morbac.is_allowed('44444444-4444-4444-8444-444444444444',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Sud'),
    'read', 'invoices');                                    -- true
 
-- nothing else at Sud
SELECT morbac.is_allowed('44444444-4444-4444-8444-444444444444',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Sud'),
    'read', 'patients');                                    -- false
 
-- and Nord was never mentioned
SELECT morbac.is_allowed('44444444-4444-4444-8444-444444444444',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord'),
    'read', 'invoices');                                    -- false

Semantics worth remembering

The role is resolved in the source org: Bertrand must hold auditor at the Groupe, not at Sud.

source_org_id and target_org_id must differ (check constraint); use an ordinary rule for same-org access.

Cross-org rules take part in the normal priority resolution at steps 2 and 5, so a prohibition at Sud with equal or higher priority still wins.

valid_from / valid_until expire the access without a cleanup job. Drop the row early to end the review sooner.

Reviewing what crosses boundaries

SELECT s.name AS source, t.name AS target, r.name AS role,
       c.activity, c.view, c.modality, c.valid_until
FROM morbac.cross_org_rules c
JOIN morbac.orgs  s ON s.id = c.source_org_id
JOIN morbac.orgs  t ON t.id = c.target_org_id
JOIN morbac.roles r ON r.id = c.role_id
ORDER BY t.name;

Keep this list short and reviewed: every row is a deliberate hole in tenant isolation.