> ## Documentation Index
> Fetch the complete documentation index at: https://docs.adhdsimple.co.uk/llms.txt
> Use this file to discover all available pages before exploring further.

# Row Level Security (RLS) Policies

> Understanding and managing database security through Row Level Security policies

# 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.

```sql theme={null}
-- ❌ 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:

```sql theme={null}
-- 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:

```sql theme={null}
-- 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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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:

```sql theme={null}
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

```sql theme={null}
-- 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`