Row-Level Security

is_allowed answers "may this user do this activity on this view in this org". Row-level security turns that into per-row enforcement on your own tables, inside the database, for every client that connects.

Session context

Three (optionally four) session variables tell the engine who is asking:

Set them at the start of a request (or transaction):

SELECT set_config('morbac.user_id', '11111111-1111-4111-8111-111111111111', true);
SELECT set_config('morbac.org_id',
    (SELECT id::text FROM morbac.orgs WHERE name = 'Clinique Valmont Nord'), true);

The third argument true scopes the setting to the transaction, which is the safe default behind connection pools.

rls_check

morbac.rls_check(activity, view, row_org_id, row_user_id) is the policy helper. It reads the session variables and:

1. denies outright when no morbac.user_id is set;
2. rejects rows whose org_id is outside the session org (or org list);
3. rejects rows whose user_id differs from morbac.target_user_id when that variable is set;
4. finally delegates to is_allowed(user, org, activity, view).

A row with NULL org_id is treated as global: only global rules can grant it.

Attaching policies

CREATE TABLE app.prescriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id UUID NOT NULL REFERENCES morbac.orgs(id),
    patient TEXT NOT NULL,
    drug TEXT NOT NULL
);
 
ALTER TABLE app.prescriptions ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY prescriptions_select ON app.prescriptions FOR SELECT
USING (morbac.rls_check('read', 'prescriptions', org_id));
 
CREATE POLICY prescriptions_insert ON app.prescriptions FOR INSERT
WITH CHECK (morbac.rls_check('prescribe', 'prescriptions', org_id));

Now a nurse at Clinique Valmont Sud selecting from app.prescriptions sees zero North rows, no matter what the application layer forgets.

Rules of thumb

• Write one policy per command type; SELECT policies use USING, INSERT uses WITH CHECK, UPDATE typically needs both.
• The role your application connects as must not have BYPASSRLS if you want RLS to be the enforcement layer.
• Policy expressions run with the privileges of the querying role, so that role needs USAGE on the morbac schema and EXECUTE on rls_check:

GRANT USAGE ON SCHEMA morbac TO valmont_app;
GRANT EXECUTE ON FUNCTION morbac.rls_check(TEXT, TEXT, UUID, UUID) TO valmont_app;

That is the whole grant. Every morbac function is SECURITY DEFINER, so the role still cannot read morbac tables directly.

• Proof queries for tenant isolation live in the multi-tenant recipe.