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 is the policy helper. It comes in two forms, told apart by arity so that a NULL never carries two meanings:

morbac.rls_check(activity, view)                              -- table has no org column
morbac.rls_check(activity, view, row_org_id [, row_user_id])  -- row-scoped by org

The row-scoped form 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_org_id of NULL means the record is unattributed: it belongs to no organization. The two-argument form carries no org dimension at all and evaluates against the session org context.

Choosing which records come back

Authorization asks may this user act on this record. Selection asks which of the authorized records does this request want. The session variables answer the second question, and a JSON null element in morbac.org_ids names the unattributed bucket:

SET morbac.org_ids = '[null]';
SELECT * FROM app.patients;   -- exactly the records awaiting an organization

Because a single org focus is a filter over attributed records, it excludes unattributed ones; ask for them explicitly with the null element.

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) 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.