Recipes

Multi-Tenant Isolation

Guaranteeing sibling organizations cannot see each other.

Situation. Clinique Valmont Nord and Clinique Valmont Sud are sibling hospitals in one database. A South nurse must never see North data, and you want that guaranteed by the database, not by application discipline.

Assumes the shared Valmont setup.

Isolation is the default

pgmorbac grants nothing implicitly. Julien Lefevre is a nurse at Clinique Valmont Nord:

INSERT INTO morbac.user_roles (user_id, role_id, org_id)
SELECT '22222222-2222-4222-8222-222222222222', r.id, r.org_id
FROM morbac.roles r JOIN morbac.orgs o ON o.id = r.org_id
WHERE o.name = 'Clinique Valmont Nord' AND r.name = 'nurse'
ON CONFLICT DO NOTHING;
 
INSERT INTO morbac.rules (org_id, role_id, activity, view, context_id, modality)
SELECT o.id, r.id, 'read', 'patients',
       (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 = 'nurse'
WHERE o.name = 'Clinique Valmont Nord'
ON CONFLICT DO NOTHING;

SELECT morbac.is_allowed('22222222-2222-4222-8222-222222222222',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord'),
    'read', 'patients');                                   -- true
SELECT morbac.is_allowed('22222222-2222-4222-8222-222222222222',
    (SELECT id FROM morbac.orgs WHERE name = 'Clinique Valmont Sud'),
    'read', 'patients');                                   -- false

South denies because nothing grants it: Julien holds no role there, no subtree rule from a common ancestor covers South, and the engine fails closed. Sibling isolation is not a feature you switch on; it is the absence of a grant.

Making the database enforce it per row

Authorization checks are only half the story; the other half is row-level security on your tables:

CREATE TABLE IF NOT EXISTS app.patients (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id UUID NOT NULL REFERENCES morbac.orgs(id),
    name TEXT NOT NULL
);
ALTER TABLE app.patients ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS patients_select ON app.patients;
CREATE POLICY patients_select ON app.patients FOR SELECT
USING (morbac.rls_check('read', 'patients', org_id));
 
INSERT INTO app.patients (org_id, name)
SELECT id, 'North patient' FROM morbac.orgs WHERE name = 'Clinique Valmont Nord';
INSERT INTO app.patients (org_id, name)
SELECT id, 'South patient' FROM morbac.orgs WHERE name = 'Clinique Valmont Sud';

Proof under a non-privileged role

The application role needs USAGE on morbac and EXECUTE on rls_check: policy expressions run with the privileges of the querying role, not the table owner. Grant those two and nothing else; the internals stay unreadable because every morbac function is SECURITY DEFINER.

CREATE ROLE valmont_app LOGIN;               -- no BYPASSRLS
GRANT USAGE ON SCHEMA app TO valmont_app;
GRANT SELECT ON app.patients TO valmont_app;
GRANT USAGE ON SCHEMA morbac TO valmont_app;
GRANT EXECUTE ON FUNCTION morbac.rls_check(TEXT, TEXT, UUID, UUID) TO valmont_app;

Resolve the org id first, then drop privileges: valmont_app cannot read morbac.orgs directly, and should not be able to.

SELECT id AS nord_id FROM morbac.orgs WHERE name = 'Clinique Valmont Nord' \gset
 
SET ROLE valmont_app;
SELECT set_config('morbac.user_id', '22222222-2222-4222-8222-222222222222', true);
SELECT set_config('morbac.org_id', :'nord_id', true);
 
SELECT name FROM app.patients;                -- North patient (1 row)
RESET ROLE;

In an application, the org id comes from the request context, so the \gset step is just psql plumbing for this demo.

Even if a buggy client asks for everything, the South row does not exist for Julien. Setting morbac.org_id to South instead yields zero rows, because rls_check then calls is_allowed for South and gets false.

When you do want to cross the wall

Deliberate exceptions are their own recipes: cross-org access for an HQ auditor, or a subtree rule from the common parent when the whole group should share a view.