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

# RLS CI/CD Validation Guide

> > Version: 1.0.0 > Last Updated: 2026-01-11 > Status: Active

> **Version:** 1.0.0\
> **Last Updated:** 2026-01-11\
> **Status:** Active

## Overview

This guide explains the automated RLS (Row Level Security) validation integrated into our CI/CD pipeline. These checks prevent security regressions and ensure all database changes follow multi-tenant isolation best practices.

## Quick Reference

### Local Commands

```bash theme={null}
# Lint migration files (static analysis)
npx ts-node scripts/lint-rls-migration.ts

# Lint specific file
npx ts-node scripts/lint-rls-migration.ts supabase/migrations/20260111_my_migration.sql

# Lint staged files only
npx ts-node scripts/lint-rls-migration.ts --staged

# Full RLS validation (requires database connection)
npx ts-node scripts/validate-rls-policies.ts

# CI mode (JSON output, strict exit codes)
npx ts-node scripts/validate-rls-policies.ts --ci --fail-on-error
```

### What Gets Checked

| Check                  | Type    | Blocks CI | Description                                          |
| ---------------------- | ------- | --------- | ---------------------------------------------------- |
| Missing RLS            | Error   | ✅ Yes     | Tables without `ENABLE ROW LEVEL SECURITY`           |
| Missing WITH CHECK     | Error   | ✅ Yes     | UPDATE policies without `WITH CHECK` clause          |
| Recursion Risk         | Error   | ✅ Yes     | Policies querying `pf_user_roles` directly           |
| Missing search\_path   | Error   | ✅ Yes     | SECURITY DEFINER functions without `SET search_path` |
| Incomplete Policies    | Warning | ❌ No      | Tables with \< 4 policies (excludes audit tables)    |
| Missing org\_id Filter | Warning | ❌ No      | Policies not filtering by `organization_id`          |
| FOR ALL Policy         | Warning | ❌ No      | Using `FOR ALL` instead of specific CRUD policies    |

***

## CI/CD Pipeline

### Pipeline Stages

```text theme={null}
┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│  Migration Lint │ --> │  Unit Tests     │ --> │  RLS Validation │
│  (Static)       │     │  (Mocked)       │     │  (Live DB)      │
└─────────────────┘     └─────────────────┘     └─────────────────┘
```

### Workflow Jobs

1. **migration-lint** - Runs on every PR that modifies `supabase/migrations/*.sql`
2. **rls-policy-validation** - Runs after migrations are applied (push to main or internal PRs)

### Environment Variables Required

```yaml theme={null}
VITE_SUPABASE_URL: ${{ secrets.VITE_SUPABASE_URL }}
SUPABASE_SERVICE_ROLE_KEY: ${{ secrets.SUPABASE_SERVICE_ROLE_KEY }}
```

***

## Pre-Commit Hook

The pre-commit hook automatically lints staged migration files:

```bash theme={null}
# .husky/pre-commit (excerpt)
STAGED_SQL_FILES=$(git diff --cached --name-only --diff-filter=ACM | grep 'supabase/migrations/.*\.sql$' || true)
if [ -n "$STAGED_SQL_FILES" ]; then
  npx ts-node scripts/lint-rls-migration.ts $STAGED_SQL_FILES
fi
```

### Bypassing (Emergency Only)

```bash theme={null}
git commit --no-verify -m "Emergency fix"
```

⚠️ **Warning:** Bypassing pre-commit hooks will still fail CI. Only use for emergencies.

***

## Common Failures & Fixes

### Error: CREATE TABLE without RLS

**Failure:**

```text theme={null}
❌ Line 5: CREATE TABLE without ENABLE ROW LEVEL SECURITY: hr_new_table
```

**Fix:**

```sql theme={null}
CREATE TABLE hr_new_table (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES pf_organizations(id),
  -- ... columns
);

-- ADD THIS:
ALTER TABLE hr_new_table ENABLE ROW LEVEL SECURITY;

-- Then add policies...
```

### Error: UPDATE policy without WITH CHECK

**Failure:**

```text theme={null}
❌ Line 25: UPDATE policy without WITH CHECK clause: hr_new_table_update
```

**Fix:**

```sql theme={null}
-- WRONG:
CREATE POLICY "hr_new_table_update" ON hr_new_table
  FOR UPDATE USING (hr_has_org_access(organization_id, auth.uid()));

-- CORRECT:
CREATE POLICY "hr_new_table_update" ON hr_new_table
  FOR UPDATE 
  USING (hr_has_org_access(organization_id, auth.uid()))
  WITH CHECK (hr_has_org_access(organization_id, auth.uid()));
```

### Error: Direct pf\_user\_roles query (Recursion Risk)

**Failure:**

```text theme={null}
❌ Line 30: Policy directly queries pf_user_roles (recursion risk)
```

**Fix:**

```sql theme={null}
-- WRONG: Direct query causes infinite recursion
CREATE POLICY "bad_policy" ON hr_employees
  FOR SELECT USING (
    organization_id IN (SELECT organization_id FROM pf_user_roles WHERE user_id = auth.uid())
  );

-- CORRECT: Use SECURITY DEFINER helper
CREATE POLICY "good_policy" ON hr_employees
  FOR SELECT USING (hr_has_org_access(organization_id, auth.uid()));
```

### Error: Missing search\_path on SECURITY DEFINER

**Failure:**

```text theme={null}
❌ Line 10: SECURITY DEFINER function without SET search_path: my_helper
```

**Fix:**

```sql theme={null}
-- WRONG:
CREATE FUNCTION my_helper(org_id uuid, user_id uuid)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER
AS $$ ... $$;

-- CORRECT:
CREATE FUNCTION my_helper(org_id uuid, user_id uuid)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = public  -- ADD THIS
AS $$ ... $$;
```

***

## Excluding Tables from Validation

Some tables intentionally have fewer than 4 policies:

### Audit/Log Tables (Append-Only)

* Only SELECT + INSERT policies (no UPDATE/DELETE)
* Examples: `pf_audit_logs`, `hr_payroll_audit_log`, `fw_approval_history`

### Version Tables (Immutable)

* Only SELECT + INSERT policies
* Examples: `fw_form_versions`, `pf_document_versions`

### System Tables (Deny-All)

* Only service\_role access
* Examples: `pf_integration_credentials`

To add a table to the exclusion list, update `scripts/validate-rls-policies.ts`:

```typescript theme={null}
const EXCLUDED_TABLES = [
  // ... existing tables
  'my_new_audit_table',
];
```

***

## Troubleshooting

### "Missing environment variables"

Ensure these are set:

```bash theme={null}
export VITE_SUPABASE_URL="https://your-project.supabase.co"
export SUPABASE_SERVICE_ROLE_KEY="your-service-role-key"
```

### "RPC function does not exist"

The validation script relies on helper functions. Run the migration that adds them:

```sql theme={null}
-- Creates: get_update_policies_without_with_check()
-- Creates: get_policies_with_recursion_risk()
-- Creates: get_policies_without_org_filter()
```

### CI passes but local fails

Local and CI may have different database states. Ensure:

1. You've applied all migrations locally
2. Your local Supabase matches the CI environment

***

## Adding New Validation Rules

1. Add rule definition in `scripts/lint-rls-migration.ts`:

   ```typescript theme={null}
   const RULES = {
     MY_NEW_RULE: {
       severity: 'error',
       message: 'Description of the issue',
       suggestion: 'How to fix it',
     },
   };
   ```

2. Add detection logic in `lintFile()` function

3. Add documentation in this guide

4. Test with existing migrations to avoid false positives

***

## Reports

CI generates a JSON report at `reports/rls-validation.json`:

```json theme={null}
{
  "timestamp": "2026-01-11T12:00:00.000Z",
  "passed": true,
  "results": [...],
  "summary": {
    "total_checks": 5,
    "passed_checks": 5,
    "errors": 0,
    "warnings": 0
  }
}
```

Reports are retained as CI artifacts for 30 days.

***

## See Also

* [RLS Patterns Reference](../database/RLS_PATTERNS.md)
* [Database Development Guide](DATABASE_DEVELOPMENT_GUIDE.md)
* [RLS Policy Audit](../database/RLS_POLICY_AUDIT.md)
* [Constitution §5.7](../../constitution.md) - RLS Requirements