Row-Level Security in Clinical Systems: PostgreSQL Security-Invoker Views & Multi-Tenant Supabase Isolation

The High Stakes of Multi-Tenant Clinical Storage

In consumer healthcare applications like **VitalTrace**, patient blood panels, metabolic markers, and diagnostic histories reside within shared database infrastructure. Unlike traditional business software where an authorization bug might expose an invoice, leaking another patient's biomarker history constitutes a severe HIPAA/GDPR statutory breach.

Application-layer authorization filters (such as `WHERE user_id = req.user.id` in Express or FastAPI route handlers) are notoriously fragile. A single developer omitting the clause or an unauthenticated analytical query instantly leaks patient observations across accounts.

To build defensible healthcare systems, security must reside **inside the database engine itself** through PostgreSQL Row-Level Security (RLS).

Step 1: Base Table Row-Level Security Policies

In Supabase and PostgreSQL, enabling RLS on observation tables guarantees that no query can access rows without passing the evaluation predicate:

-- Enable Row-Level Security explicitly on base tables
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE lab_documents ENABLE ROW LEVEL SECURITY;

-- Deny all actions by default unless explicitly granted CREATE POLICY "Users can only read their own observations" ON observations FOR SELECT USING (auth.uid() = profile_id);

CREATE POLICY "Users can only insert observations for their profile" ON observations FOR INSERT WITH CHECK (auth.uid() = profile_id);

CREATE POLICY "Users can only update unconfirmed observations" ON observations FOR UPDATE USING (auth.uid() = profile_id AND status = 'pending') WITH CHECK (auth.uid() = profile_id); ```

Here, `auth.uid()` resolves to the authenticated JWT subject claim provided by Supabase Auth. Even if a malicious actor accesses the direct PostgREST endpoint with a valid token, the database engine enforces the constraint at the tuple level.

Step 2: The View Elevation Trap in PostgreSQL

When building dashboards, applications frequently query database views to aggregate longitudinal trends—calculating running averages, standard deviations, and min/max reference boundaries.

Prior to PostgreSQL 15, creating a view presented a massive security vulnerability:

**By default, PostgreSQL views execute with the permissions of the view creator (`security_definer`), completely bypassing the querying user's Row-Level Security policies!**

If an admin created:

-- VULNERABLE PATTERN (PostgreSQL <15 default behavior)
CREATE VIEW view_all_biomarkers AS
SELECT profile_id, loinc_code, canonical_name, value_numeric, observation_date
FROM observations;

Any authenticated user who queried `SELECT * FROM view_all_biomarkers` would receive **all patients' records across the entire database**, completely circumventing the table's RLS policies.

Step 3: Resolving Elevation with `security_invoker = true`

PostgreSQL 15 introduced `security_invoker`, allowing developers to mandate that views execute under the querying user's security context. In **VitalTrace**, all analytical and longitudinal views strictly enforce this setting:

-- SECURE PATTERN: Enforces caller RLS context
CREATE OR REPLACE VIEW view_patient_biomarker_trajectories
WITH (security_invoker = true) AS
SELECT 
  obs.profile_id,
  obs.loinc_code,
  obs.canonical_name,
  obs.observation_date,
  obs.value_numeric,
  obs.unit,
  obs.status
FROM observations obs
WHERE obs.status = 'confirmed';

When a patient queries `view_patient_biomarker_trajectories`, PostgreSQL checks the underlying `observations` table policies against that specific user's `auth.uid()`. If User A queries the view, they physically cannot see User B's rows, regardless of how the query is structured.

Step 4: Storage Bucket Security for Medical PDFs

Laboratory report PDFs uploaded by users contain unredacted names, clinic addresses, and raw biomarker figures. In VitalTrace, files are stored in a private Supabase Storage bucket governed by storage RLS policies:

-- Enforce path-based folder tenancy in Supabase storage.objects
CREATE POLICY "Patients access own uploaded lab reports"
ON storage.objects
FOR ALL
USING (
  bucket_id = 'patient-lab-reports' 
  AND (storage.foldername(name))[1] = auth.uid()::text
);

By prefixing each uploaded document path with `{user_id}/{document_uuid}.pdf`, file access is cryptographically bound to the authenticated session. Unauthenticated public URLs are completely disabled; all document downloads require time-limited signed URLs generated server-side.

Key Takeaways for Production Engineering

1. **Defense in Depth**: Application middleware should validate JWTs, but database RLS must enforce data isolation. 2. **Audit All Database Views**: Never create analytical views without verifying whether they inherit the creator's elevated rights or enforce caller RLS via `security_invoker = true`. 3. **Verify with Automated Negative Tests**: Write automated integration tests that intentionally attempt cross-tenant reads to assert that zero rows are returned.