Skip to main content

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.

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

# 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

CheckTypeBlocks CIDescription
Missing RLSError✅ YesTables without ENABLE ROW LEVEL SECURITY
Missing WITH CHECKError✅ YesUPDATE policies without WITH CHECK clause
Recursion RiskError✅ YesPolicies querying pf_user_roles directly
Missing search_pathError✅ YesSECURITY DEFINER functions without SET search_path
Incomplete PoliciesWarning❌ NoTables with < 4 policies (excludes audit tables)
Missing org_id FilterWarning❌ NoPolicies not filtering by organization_id
FOR ALL PolicyWarning❌ NoUsing FOR ALL instead of specific CRUD policies

​
CI/CD Pipeline

​
Pipeline Stages

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│  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

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

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: 
❌ Line 5: CREATE TABLE without ENABLE ROW LEVEL SECURITY: hr_new_table
Fix: 
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: 
❌ Line 25: UPDATE policy without WITH CHECK clause: hr_new_table_update
Fix: 
-- 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: 
❌ Line 30: Policy directly queries pf_user_roles (recursion risk)
Fix: 
-- 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: 
❌ Line 10: SECURITY DEFINER function without SET search_path: my_helper
Fix: 
-- 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: 
const EXCLUDED_TABLES = [
  // ... existing tables
  'my_new_audit_table',
];

​
Troubleshooting

​
”Missing environment variables”

Ensure these are set: 
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: 
-- 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: 
    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: 
{
  "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