RLS Test Patterns

Comprehensive testing framework for Row Level Security (RLS) policies in Supabase. Catch security vulnerabilities before production with automated tests for user isolation, multi-tenant security, role-based access, and anonymous user restrictions.

Instructions

1. Test User Isolation

Verify users can only access their own data:

Test user isolation on specific tables

bash scripts/test-user-isolation.sh conversations messages profiles

Test with specific user IDs

bash scripts/test-user-isolation.sh documents --user1 "uuid1" --user2 "uuid2"

Generate detailed report

bash scripts/test-user-isolation.sh --all --report isolation-report.md

What it tests:

• User A cannot read User B's data

• User A cannot modify/delete User B's data

• User A can only insert data owned by themselves

• Null user_id values are properly rejected

2. Test Multi-Tenant Isolation

Verify organization/team data separation:

Test organization isolation

bash scripts/test-multi-tenant-isolation.sh organizations projects documents

Test with specific org IDs

bash scripts/test-multi-tenant-isolation.sh --org1 "org-uuid-1" --org2 "org-uuid-2"

Test member access patterns

bash scripts/test-multi-tenant-isolation.sh --test-members

What it tests:

• Org A members cannot access Org B's data

• Users not in an org cannot access org data

• Removing user from org revokes access immediately

• Shared resources respect org boundaries

3. Test Role-Based Permissions

Verify role-based access control:

Test RBAC policies

bash scripts/test-role-permissions.sh admin_panel sensitive_data

Test specific role hierarchy

bash scripts/test-role-permissions.sh --roles "admin,editor,viewer"

Test permission escalation prevention

bash scripts/test-role-permissions.sh --test-escalation

What it tests:

• Admin role has full access

• Editor role can read/write but not delete

• Viewer role has read-only access

• Users cannot escalate their own permissions

• Role changes take effect immediately

4. Test Anonymous Access Restrictions

Verify anonymous users are properly restricted:

Test anonymous access on all public tables

bash scripts/test-anonymous-access.sh

Test specific tables

bash scripts/test-anonymous-access.sh public_posts comments

Test auth.uid() null handling

bash scripts/test-anonymous-access.sh --test-null-uid

What it tests:

• Anonymous users cannot access protected data

• Anonymous users can only access designated public data

• auth.uid() returns null correctly for anon users

• Policies handle null uid safely

5. Audit RLS Coverage

Check all tables have proper RLS policies:

Audit entire database

bash scripts/audit-rls-coverage.sh

Audit specific schema

bash scripts/audit-rls-coverage.sh --schema public

Generate compliance report

bash scripts/audit-rls-coverage.sh --report compliance-report.md --format markdown

What it checks:

• All public schema tables have RLS enabled

• Each table has policies for all DML operations (SELECT, INSERT, UPDATE, DELETE)

• Policies target appropriate roles (authenticated, anon)

• No tables are accidentally exposed without policies

• Policy naming follows best practices

6. Run Complete Test Suite

Execute all RLS tests:

Run all tests with default settings

bash scripts/run-all-rls-tests.sh

Run with custom database URL

bash scripts/run-all-rls-tests.sh --db-url "postgresql://..."

Run and generate comprehensive report

bash scripts/run-all-rls-tests.sh --report rls-test-results.json --verbose

Run in CI/CD mode (exit 1 on any failure)

bash scripts/run-all-rls-tests.sh --ci --fail-fast

Test sequence:

• Audit RLS coverage

• Test user isolation

• Test multi-tenant isolation

• Test role permissions

• Test anonymous access

• Generate summary report

Examples

Example 1: Testing Chat Application Security

Test conversation isolation

bash scripts/test-user-isolation.sh conversations messages participants

Output:

✓ User cannot read other user's conversations

✓ User cannot send messages to other user's conversations

✓ User cannot add participants to other user's conversations

✓ All isolation tests passed (12/12)

Example 2: Multi-Tenant SaaS Security Audit

Full audit of multi-tenant application

bash scripts/test-multi-tenant-isolation.sh organizations projects documents embeddings

Output:

✓ Org A users cannot access Org B projects

✓ Removed users lose access immediately

✓ Cross-org document access blocked

✓ Embeddings respect org boundaries

✓ All multi-tenant tests passed (24/24)

Example 3: CI/CD Integration

In .github/workflows/security-tests.yml

• name: Run RLS Tests run: | bash scripts/run-all-rls-tests.sh
  --ci
  --fail-fast
  --report rls-results.json

• name: Upload Test Report uses: actions/upload-artifact@v3 with: name: rls-test-results path: rls-results.json

Example 4: Pre-Production Security Check

Complete security validation before deploy

bash scripts/audit-rls-coverage.sh --report audit.md bash scripts/run-all-rls-tests.sh --verbose --report tests.json

Review both reports before deploying

cat audit.md cat tests.json

Requirements

Prerequisites

• Supabase project with database access

• PostgreSQL client (psql ) installed

• Node.js 18+ (for TypeScript test suite)

• Supabase CLI v1.11.4+ (for pgTAP tests)

Environment Variables

Required in .env file:

Database connection

SUPABASE_DB_URL="postgresql://postgres:[password]@[host]:5432/postgres"

API keys for client testing

SUPABASE_URL="https://[project-ref].supabase.co" SUPABASE_ANON_KEY="eyJ..." SUPABASE_SERVICE_KEY="eyJ..."

Test user credentials (optional, for client tests)

TEST_USER_1_EMAIL="test1@example.com" TEST_USER_1_PASSWORD="testpass123" TEST_USER_2_EMAIL="test2@example.com" TEST_USER_2_PASSWORD="testpass456"

Test Data Setup

Tests create and clean up their own data, but you can provide:

Optional: Use existing test users

TEST_USER_1_ID="uuid-for-test-user-1" TEST_USER_2_ID="uuid-for-test-user-2"

Optional: Use existing test orgs

TEST_ORG_1_ID="uuid-for-test-org-1" TEST_ORG_2_ID="uuid-for-test-org-2"

Security Best Practices Tested

• ✓ RLS enabled on all public schema tables

• ✓ User isolation prevents cross-user data access

• ✓ Multi-tenant isolation prevents cross-org access

• ✓ Anonymous users properly restricted

• ✓ Role hierarchy enforced correctly

• ✓ auth.uid() null values handled safely

• ✓ Policies use indexed columns for performance

• ✓ WITH CHECK clauses prevent policy bypass

• ✓ Service key never exposed to clients

Common Vulnerabilities Detected

• Missing RLS on public tables (data leak)

• Missing WITH CHECK clauses (INSERT/UPDATE bypass)

• Improper null handling (anonymous access leak)

• Missing role checks (privilege escalation)

• Unindexed policy columns (DoS via slow queries)

• Cross-tenant joins (org data leak)

• Missing policies for specific operations

• Overly permissive anonymous access

Test Output Format

Success Output

🔒 RLS Test Suite v1.0.0 ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

📊 Test Summary Tables tested: 5 Total tests: 48 Passed: 48 ✓ Failed: 0 Duration: 12.3s

✅ All RLS policies working correctly!

Failure Output

🔒 RLS Test Suite v1.0.0 ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

❌ SECURITY ISSUE DETECTED

Table: conversations Test: User isolation - SELECT Issue: User B could read User A's conversations Expected: 0 rows Actual: 5 rows

Recommendation: Add USING clause to SELECT policy: USING (auth.uid() = user_id)

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 📊 Test Summary Total tests: 48 Passed: 43 ✓ Failed: 5 ❌

❌ Critical security issues found. Do not deploy.

Integration Points:

• Used by: /supabase:test-rls command

• Used by: supabase-security-auditor agent

• Complements: rls-templates skill (creates policies, this tests them)

• CI/CD: Run in GitHub Actions before deployment

Best Practices:

• Run tests after every RLS policy change

• Include in pre-commit hooks for critical tables

• Run full suite weekly in CI/CD

• Test with real user data patterns (anonymized)

• Keep test users separate from production users

• Document expected behavior for each policy

• Update tests when adding new tables/policies