Skip to main content

​
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:
  1. Test as different user roles to ensure appropriate access
  2. Verify clinic isolation by attempting cross-clinic access
  3. Check edge cases like new users or role changes
  4. 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

  1. Review all policies in the migration file before deployment
  2. Test in development environment first
  3. Deploy during low-traffic periods to minimize impact
  4. Monitor for access issues after deployment
  5. 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:
  1. audit_logs: Removed policy allowing all authenticated users full access
  2. medications: Removed global access policies, added clinic isolation
  3. pickup_schedules: Added missing policies for clinic-based access
  4. patient_app_requests: Strengthened INSERT validation
For details, see the migration file: 20240110_fix_critical_rls_policies.sql