Row-Level Security and staff access control at aescolab

The interesting engineering problem here was never "lock everyone out." It was giving each of three different parties exactly the scope they needed, enforced somewhere that doesn't depend on every future engineer remembering to check.

Assets: normalized biomarker records (blood_results), generated reports and scores (analyses), automated clinical alerts (clinical_alerts), action plans with supplement dosages (action_plans), uploaded documents, and aggregate B2B reporting data (meta_analyses).

The design below is built against that failure mode, for all three parties.

Every personal table carries a userId uuid column, and Postgres Row-Level Security enforces that an authenticated session can only see rows where that column matches its own identity:

-- prisma/migrations/20250807095714_rls_policies/migration.sql
CREATE POLICY "owner_read_blood_results" ON public.blood_results
  FOR SELECT USING ("userId" = auth.uid());

CREATE POLICY "owner_select_action_plans" ON public.action_plans
  FOR SELECT USING ("userId" = auth.uid());

This runs identically across every personal table: blood_results, analyses, documents, transactions, action_plans, clinical_alerts, user_profiles, user_facts. auth.uid() resolves from the Supabase session JWT, so the identity check happens below the query: even a query with no WHERE clause at all still only returns rows the session is allowed to see.

Tables meant for nobody but the backend get the same treatment with the inverse result: RLS enabled, zero policies, which is Postgres's default-deny.

-- clinical_patterns: the rule config behind automated alerts, admin-only
ALTER TABLE public.clinical_patterns ENABLE ROW LEVEL SECURITY;
-- no GRANT, no POLICY → blocked for every authenticated role

A user can read their own clinical_alerts (the alert, once generated) but not clinical_patterns (the logic that generates alerts). A later migration revokes privileges on that table again as defense-in-depth, in case Supabase's default grants on new tables ever reopened it.

This part holds regardless of what the application does. That's the property worth keeping for every other kind of access too.

The owner-isolation policy works because it never trusts the application to get the filter right. The database enforces it either way. Clinicians and admins can use the same mechanism with a wider scope. The fix is a second permissive policy per table, evaluated alongside the owner policy (Postgres ORs permissive policies together):

CREATE TABLE clinician_assignments (
  clinician_id uuid NOT NULL,
  patient_id   uuid NOT NULL,
  assigned_at  timestamptz NOT NULL DEFAULT now(),
  revoked_at   timestamptz,
  PRIMARY KEY (clinician_id, patient_id)
);

CREATE POLICY "clinician_read_assigned_patients"
  ON public.blood_results
  FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM clinician_assignments a
      WHERE a.patient_id = blood_results."userId"
        AND a.clinician_id = auth.uid()
        AND a.revoked_at IS NULL
    )
  );

For this to work, a clinician's session has to carry their real identity through auth.uid() and not a shared service credential. Staff authenticate the same way users do, through Supabase auth with a role claim on the JWT, rather than through a separate JWT scheme that only an admin API understands. The admin API becomes a thinner layer: it still checks the role before serving a request, but the database is the thing that actually enforces which patients, and it enforces it even if a new endpoint ships tomorrow without that check wired in correctly.

Revocation is a single UPDATE to clinician_assignments, effective on the clinician's very next query, with no cache to invalidate and no session to kill.

Admin aggregate reporting gets a separate anonymized table. A scheduled job is the one thing allowed a service-role connection, because nobody is sitting behind a login form waiting on it. It writes into a table with no userId column at all, cohort buckets only (age band, biomarker code, value range, count), and suppresses any bucket under a fixed minimum size. The interactive admin role gets grants on that table and nothing else, so there's no query an admin could write, correct or malicious, that reaches an individual's row:

CREATE TABLE cohort_stats (
  client_id    int NOT NULL,
  period_start date NOT NULL,
  age_band     text NOT NULL,
  biomarker_id int NOT NULL,
  value_bucket text NOT NULL,
  n            int NOT NULL CHECK (n >= 5)  -- k-anonymity floor
);
-- admin role: GRANT SELECT ON cohort_stats TO admin_reporting;
-- no grant on blood_results, analyses, or any raw table

Reads get logged in the same transaction as the read. A SECURITY DEFINER function wraps the clinician-scoped read and writes an access-log row before returning data, so "who looked at this and when" survives even if whoever builds the next admin endpoint doesn't remember to call a logging helper:

CREATE FUNCTION read_patient_blood_results(p_patient_id uuid)
RETURNS SETOF blood_results
SECURITY DEFINER
AS $$
  INSERT INTO access_log (actor_id, patient_id, resource, accessed_at)
  VALUES (auth.uid(), p_patient_id, 'blood_results', now());

  SELECT * FROM blood_results WHERE "userId" = p_patient_id;
$$ LANGUAGE sql;

1. The assignment table is only as good as whoever maintains it: clinician_assignments replaces "any Medical-role JWT" with "an explicit row," which is strictly better, but someone still has to create and revoke those rows correctly. It moves the trust boundary, it doesn't remove it.
2. SECURITY DEFINER functions are a sharp tool: Written carelessly, they can become exactly the kind of blanket bypass they're meant to prevent, since they run with the privileges of whoever defined them and not whoever calls them. Every one of them needs the same scrutiny as a production migration.
3. The k-anonymity floor is a judgment call: Set it too low and small cohorts leak; too high and the reporting a client is paying for stops being useful. There's no default that's right for every table.

The owner-isolation policy and the staff-access policy should be the same kind of thing: a rule the database enforces regardless of what the application remembers to do, scoped to exactly who needs exactly what. The actual lesson from aescolab isn't the SQL. It's that the same pressure that justified the original RLS design (don't trust application code with data this sensitive) applies just as hard to staff access, and it's precisely the access that feels like "just for now, just for us" that's easiest to ship as an exception and hardest to go back and fix later.