PostgREST & RLS Context

PostgREST turns a schema into an HTTP API. Combined with pgmorbac RLS, the API is authorized by the database itself: no per-endpoint permission code, no way for a forgotten check to leak rows.

The pre-request hook

PostgREST calls one function before every request. Make it translate the JWT and headers into the morbac session variables:

CREATE OR REPLACE FUNCTION app.set_morbac_context()
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_user_id TEXT := current_setting('request.jwt.claims', true)::jsonb ->> 'sub';
    v_org_id  TEXT := current_setting('request.headers', true)::jsonb ->> 'x-org-id';
BEGIN
    PERFORM set_config('morbac.user_id', COALESCE(v_user_id, ''), true);
    PERFORM set_config('morbac.org_id',  COALESCE(v_org_id,  ''), true);
END $$;

Wire it in:

PGRST_DB_PRE_REQUEST=app.set_morbac_context
PGRST_DB_SCHEMAS=app
PGRST_DB_ANON_ROLE=anon

With no morbac.user_id set, rls_check denies everything: an unauthenticated request sees an empty database rather than an error, which is the correct fail-closed behaviour.

Policies do the authorizing

ALTER TABLE app.prescriptions ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY prescriptions_select ON app.prescriptions FOR SELECT TO app_user
USING (morbac.rls_check('read', 'prescriptions', org_id));
 
CREATE POLICY prescriptions_insert ON app.prescriptions FOR INSERT TO app_user
WITH CHECK (morbac.rls_check('prescribe', 'prescriptions', org_id));
 
CREATE POLICY prescriptions_update ON app.prescriptions FOR UPDATE TO app_user
USING (morbac.rls_check('update', 'prescriptions', org_id))
WITH CHECK (morbac.rls_check('update', 'prescriptions', org_id));

GET /prescriptions now returns exactly the rows Camille Rousseau is allowed to see at the org in her X-Org-Id header, and nothing at Clinique Valmont Sud.

Multi-org requests

Set morbac.org_ids instead of morbac.org_id when a request legitimately spans organizations, for example a dashboard over a subtree. This goes inside the pre-request function body, where PERFORM is valid:

PERFORM set_config('morbac.org_ids',
    (SELECT string_agg(s.org_id::text, ',')
     FROM morbac.get_org_scope(v_root_org, 'subtree') s),
    true);

rls_check then admits rows whose org_id is in the list, still subject to is_allowed per org.

Per-user row filtering

morbac.target_user_id narrows rows to one user, which is how "my own records" endpoints avoid a separate policy:

CREATE POLICY shifts_select ON app.shifts FOR SELECT TO app_user
USING (morbac.rls_check('read', 'shifts', org_id, user_id));

Deployment guidance

• Do not expose PostgREST directly to the internet in a multi-tenant deployment. Proxy it so the X-Org-Id header is validated against the caller's real memberships before forwarding.
• The PostgREST role (app_user above) must not have BYPASSRLS.
• Grant it table privileges normally; RLS narrows what those privileges reach.
• Policy expressions run as the querying role, so it also needs:

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

• Keep the pre-request function SECURITY INVOKER and side-effect free apart from set_config.