Row Level Security (RLS) Policies

Row Level Security (RLS) is a critical security feature in our Supabase database that ensures data isolation between clinics and proper access control based on user roles.

Overview

RLS policies control which rows in a database table each user can access. When properly configured, they provide:

Multi-tenant isolation: Each clinic can only access their own data
Role-based access: Different user roles have appropriate permissions
Data privacy: Patient information is protected from unauthorized access

Key Security Principles

1. Never Use `USING (true)`

Policies with USING (true) bypass all security checks and should never be used in production unless absolutely necessary for system operations.

-- ❌ BAD: Allows access to all rows
CREATE POLICY "bad_policy" ON table_name
FOR SELECT TO authenticated
USING (true);

-- ✅ GOOD: Restricts to user's clinic
CREATE POLICY "good_policy" ON table_name
FOR SELECT TO authenticated
USING (
  clinic_id IN (
    SELECT clinic_id FROM user_profiles 
    WHERE user_id = auth.uid()
  )
);

2. Always Enforce Clinic Isolation

Every policy should verify that the user belongs to the same clinic as the data they’re accessing:

-- Standard clinic isolation pattern
USING (
  clinic_id IN (
    SELECT clinic_id 
    FROM user_profiles 
    WHERE user_id = auth.uid()
  )
)

3. Validate User Roles

In addition to clinic isolation, verify that users have the appropriate role for the action:

-- Role validation pattern
AND EXISTS (
  SELECT 1 
  FROM user_profiles 
  WHERE user_id = auth.uid() 
  AND role IN ('admin', 'doctor', 'clinician')
)

Common RLS Patterns

Read Access Pattern

For tables that need to be read by all clinic members:

CREATE POLICY "clinic_members_can_read"
ON table_name FOR SELECT
TO authenticated
USING (
  clinic_id IN (
    SELECT clinic_id 
    FROM user_profiles 
    WHERE user_id = auth.uid()
  )
);

Write Access Pattern

For tables that should only be modified by specific roles:

CREATE POLICY "authorized_users_can_write"
ON table_name FOR ALL
TO authenticated
USING (
  clinic_id IN (
    SELECT clinic_id 
    FROM user_profiles 
    WHERE user_id = auth.uid()
  )
  AND EXISTS (
    SELECT 1 
    FROM user_profiles 
    WHERE user_id = auth.uid() 
    AND role IN ('admin', 'doctor', 'clinician', 'manager')
  )
)
WITH CHECK (
  -- Same conditions for INSERT/UPDATE
  clinic_id IN (
    SELECT clinic_id 
    FROM user_profiles 
    WHERE user_id = auth.uid()
  )
);

Service Role Pattern

System operations often need unrestricted access:

CREATE POLICY "service_role_bypass"
ON table_name FOR ALL
TO service_role
USING (true)
WITH CHECK (true);

Critical Tables and Their Policies

audit_logs

Audit logs should only be accessible to administrators:

CREATE POLICY "owners_and_admins_can_view_audit_logs"
ON audit_logs FOR SELECT
TO authenticated
USING (
  auth.uid() IN (
    SELECT user_id FROM user_profiles 
    WHERE role IN ('owner', 'admin')
  )
);

medications

Medications need clinic isolation for multi-tenant security:

CREATE POLICY "users_can_read_medications_from_their_clinic"
ON medications FOR SELECT
TO authenticated
USING (
  clinic_id IN (
    SELECT clinic_id 
    FROM user_profiles 
    WHERE user_id = auth.uid()
  )
);

patients

Patient data is highly sensitive and requires strict access control:

CREATE POLICY "clinic_users_can_access_their_patients"
ON patients FOR ALL
TO authenticated
USING (
  clinic_id IN (
    SELECT clinic_id 
    FROM user_profiles 
    WHERE user_id = auth.uid()
  )
);

Testing RLS Policies

Always test RLS policies before deploying to production:

Test as different user roles to ensure appropriate access
Verify clinic isolation by attempting cross-clinic access
Check edge cases like new users or role changes
Monitor query performance as complex policies can impact speed

Testing Example

-- Test as a specific user
SET LOCAL role TO authenticated;
SET LOCAL request.jwt.claims.sub TO 'user-uuid-here';

-- Attempt to query data
SELECT * FROM patients;
-- Should only return patients from the user's clinic

Common Security Issues

1. Overly Permissive Policies

Issue: Policies that use USING (true) or don’t check clinic membership Solution: Always include clinic isolation checks

2. Missing Policies

Issue: Tables with RLS enabled but no policies defined Solution: Define appropriate policies or disable RLS if not needed

3. Weak Validation

Issue: Policies that only check authentication without role validation Solution: Add role checks for operations that require specific permissions

4. Policy Conflicts

Issue: Multiple policies that contradict each other Solution: Review all policies on a table to ensure consistency

Deployment Best Practices

Review all policies in the migration file before deployment
Test in development environment first
Deploy during low-traffic periods to minimize impact
Monitor for access issues after deployment
Keep audit logs of policy changes

Security Audit Checklist

Regular audits should verify:

No policies use USING (true) except for service role
All tables with sensitive data have RLS enabled
All policies enforce clinic isolation
Role-based access is properly implemented
No tables have RLS enabled without policies
Policy performance is acceptable

Recent Security Fixes

The following critical vulnerabilities were recently addressed:

audit_logs: Removed policy allowing all authenticated users full access
medications: Removed global access policies, added clinic isolation
pickup_schedules: Added missing policies for clinic-based access
patient_app_requests: Strengthened INSERT validation

For details, see the migration file: 20240110_fix_critical_rls_policies.sql

Getting Started

User Guides - Clinical

User Guides - Pharmacy

User Guides - Mobile

Technical Documentation - General

Technical Documentation - Mobile

Documentation Standards

Security

Architecture

Row Level Security (RLS) Policies

Row Level Security (RLS) Policies

Overview

Key Security Principles

1. Never Use `USING (true)`

2. Always Enforce Clinic Isolation

3. Validate User Roles

Common RLS Patterns

Read Access Pattern

Write Access Pattern

Service Role Pattern

Critical Tables and Their Policies

audit_logs

medications

patients

Testing RLS Policies

Testing Example

Common Security Issues

1. Overly Permissive Policies

2. Missing Policies

3. Weak Validation

4. Policy Conflicts

Deployment Best Practices

Security Audit Checklist

Recent Security Fixes

​Row Level Security (RLS) Policies

​Overview

​Key Security Principles

​1. Never Use USING (true)

​2. Always Enforce Clinic Isolation

​3. Validate User Roles

​Common RLS Patterns

​Read Access Pattern

​Write Access Pattern

​Service Role Pattern

​Critical Tables and Their Policies

​audit_logs

​medications

​patients

​Testing RLS Policies

​Testing Example

​Common Security Issues

​1. Overly Permissive Policies

​2. Missing Policies

​3. Weak Validation

​4. Policy Conflicts

​Deployment Best Practices

​Security Audit Checklist

​Recent Security Fixes

Row Level Security (RLS) Policies

Overview

Key Security Principles

1. Never Use `USING (true)`

2. Always Enforce Clinic Isolation

3. Validate User Roles

Common RLS Patterns

Read Access Pattern

Write Access Pattern

Service Role Pattern

Critical Tables and Their Policies

audit_logs

medications

patients

Testing RLS Policies

Testing Example

Common Security Issues

1. Overly Permissive Policies

2. Missing Policies

3. Weak Validation

4. Policy Conflicts

Deployment Best Practices

Security Audit Checklist

Recent Security Fixes