AntigravityRvadvAuditSupabase RLS Security Audit Report

Supabase RLS Security Audit Report

Supabase RLS Security Audit Report

Based on the audit of the RV-ADV project's Supabase configuration, here are the findings organized by the requested verification points:

1. Row Level Security (RLS) Policies Audit

Tables with RLS Enabled

From migration analysis, the following tables have RLS enabled:

  • Core tables: pericias, pericia_pagamentos, pericia_documentos, activity_logs, lembretes, notifications, notification_preferences
  • RV-ADV schema: office_settings, document_templates, client_inss_emails, courts, jurisprudences, atendimentos, holidays
  • Audit tables: clients, processes, documents, financial (conditional), benefits (conditional)
  • Jurisprudence extensions: jurisprudencia_chat_sessions, jurisprudencia_chat_messages

Policy Patterns Observed

Two distinct policy approaches exist in the codebase:

PericiaPro Legacy Policies (002_periciapro_rls.sql & 006_fix_rls_warnings.sql)

  • Owner-based: Most policies use created_by = auth.uid() or equivalent foreign key checks
  • Limited admin override: Some policies include (auth.jwt() ->> 'user_role') = 'admin' for admin access
  • Inconsistent coverage: Mix of table-specific policies with varying completeness

Audit Migration Policies (20260330000000_audit_rls_security.sql)

  • Fail-Close default: Base policy requires ownership OR specific roles
  • Role-based access: Uses coalesce(auth.jwt() ->> 'user_role', auth.jwt() ->> 'role') IN ('admin', 'advogado')
  • Consistent application: Applied uniformly to clients, processes, documents, financial, benefits
  • Dual JWT claim checking: Checks both user_role and role fields for compatibility

Critical Finding: Policy 058_atendimentos_full_crud_all_users.sql

This migration creates a security concern by granting full CRUD access to ALL authenticated users on the atendimentos table:

CREATE POLICY "atendimentos_full_crud_authenticated"
ON public.atendimentos
FOR ALL
TO authenticated
USING (true)
WITH CHECK (true);

This violates the Fail-Close principle by allowing any authenticated user to access all atendimentos records, regardless of ownership or role.

2. RLS Migrations Audit

Key Migration Files Examined

20260330000000_audit_rls_security.sql

  • Correctly implements Fail-Close: Policies require ownership OR specific roles (admin/advogado)
  • Uses proper ownership field: References created_by (consistent with RV-ADV schema)
  • Includes safety checks: Uses IF EXISTS and DO $$ blocks for conditional table existence
  • Applies to multiple tables: clients, processes, documents, financial, benefits

20260330000001_audit_vault_strict.sql

  • Focuses on encryption security: Overrides PGP encryption functions
  • Defense-in-depth: Removes fallback mechanisms, requires vault lookup
  • Not directly related to RLS: Addresses credential storage security

058_atendimentos_full_crud_all_users.sql (CRITICAL)

  • Creates excessive permissions: Grants full CRUD to all authenticated users
  • Documented as intentional: Comment states "A pedido do negócio" (business request)
  • Preserves audit triggers: Keeps set_atendimentos_created_by trigger for attribution
  • Adds performance index: Index on created_at for daily view feature

Migration Patterns

  • Consistent naming: Migrations follow chronological versioning
  • RLS-focused migrations: Multiple migrations address RLS fixes and improvements
  • Iterative refinement: Later migrations (006_fix_rls_warnings.sql, 022_fix_atendimentos_rls.sql, etc.) improve upon initial policies

3. RLS Tests Audit

tests/security/rls-policies.test.ts

The test file contains minimal coverage:

describe('Database RLS Security (Auditoria)', () => {
  it('Deve garantir que requests anonimos são bloqueados pelas politicas Fail-Close', () => {
    // Static test only - no actual database testing
    const anonymousUser = null;
    const policyFailClose = (uid: any) => uid !== null;
    expect(policyFailClose(anonymousUser)).toBe(false);
  });
});

Testing Deficiencies

  1. No actual database integration tests: Only static JavaScript logic testing
  2. Limited scenario coverage: Only tests anonymous user blocking
  3. Missing critical test cases:
    • Cross-user access prevention (User A cannot access User B's data)
    • Role-based access verification (admin/advogado vs regular users)
    • Policy bypass attempts with manipulated JWT claims
    • Fail-Open vs Fail-Close validation for each table
    • Testing of the problematic atendimentos policy

4. JWT Claims Usage in RLS

Claim Consistency Analysis

The codebase uses inconsistent JWT claim references:

Multiple Claim Fields Used

  1. auth.jwt() ->> 'user_role' - Primary in audit migrations
  2. auth.jwt() ->> 'role' - Used in some legacy policies and fallbacks
  3. auth.uid() - Standard user ID claim

Coalesce Pattern (Better Practice)

The audit migrations correctly use:

coalesce(auth.jwt() ->> 'user_role', auth.jwt() ->> 'role')

This provides backward compatibility while preferring the user_role claim.

Inconsistent Usage Examples

  • 006_fix_rls_warnings.sql: Uses (auth.jwt() ->> 'user_role') = 'admin' only
  • 020_fase4_feriados_rls_fix.sql: Uses coalesce(auth.jwt() ->> 'user_role', 'guest')
  • 037_fix_restrictive_rls.sql: Uses (auth.jwt() ->> 'role')::text = 'admin'

Roles Observed in Codebase

From migrations and policies:

  • admin - Full system access
  • advogado (lawyer) - Professional access level
  • dono (owner) - Business owner role (in feriados policy)
  • secretaria (secretary) - Administrative support
  • assistente (assistant) - Support role
  • inspetor_noturno (night inspector) - Specialized role

Orphaned Role Risk

The inconsistent JWT claim usage increases risk of:

  • Role mismatch: Code checking user_role while JWT contains role (or vice versa)
  • Fallback failures: When one claim is null/empty and the other isn't checked
  • Security gaps: Inconsistent role evaluation across different tables

Summary of Findings & Recommendations

🔴 Critical Issues

  1. Excessive permissions in atendimentos table (Migration 058): Full CRUD for all authenticated users violates data isolation principles
  2. Insufficient RLS test coverage: Current tests don't validate actual database security behavior
  3. Inconsistent JWT claim usage: Mixed patterns create potential authentication bypass vectors

🟡 Moderate Issues

  1. Policy fragmentation: Legacy PericiaPro policies differ from audit migration patterns
  2. Conditional RLS activation: Some tables (financial, benefits) only get RLS if they exist, potentially leaving gaps

✅ Positive Findings

  1. Fail-Close principle mostly followed: Audit migrations properly implement default-deny with explicit allow
  2. Proper ownership field usage: Consistent use of created_by for ownership checks
  3. Defense-in-depth approach: Audit migrations include existence checks and idempotent design
  4. JWT claim fallback handling: Better migrations use coalesce() for claim compatibility
  1. Revert or restrict migration 058: Replace full CRUD policy with role-based access (at minimum, require specific roles for cross-user access)
  2. Implement comprehensive RLS testing: Create actual database tests that:
    • Test cross-user access prevention
    • Validate role-based permissions
    • Test anonymous blocking
    • Verify policy consistency across all RLS-enabled tables
  3. Standardize JWT claim usage: Adopt consistent coalesce(auth.jwt() ->> 'user_role', auth.jwt() ->> 'role') pattern everywhere
  4. Unify policy terminology: Consider consolidating legacy PericiaPro policies with audit migration patterns for consistency
  5. Add RLS activation verification: Create migration that validates all expected tables have RLS enabled

The project demonstrates good foundational RLS implementation in the audit migrations but contains a significant security regression in the atendimentos table policy that requires immediate attention. The testing suite needs substantial enhancement to provide genuine security validation.

[thinking] The user asked for an audit of RLS in the Supabase project, covering:

  1. Policies RLS in all tables
  2. Migrations of RLS (focusing on specific files)
  3. Tests of RLS
  4. JWT claims used in RLS

I've:

  • Listed the current directory and found the Supabase project structure
  • Examined the specified migration files (20260330000000_audit_rls_security.sql, 20260330000001_audit_vault_strict.sql, 058_atendimentos_full_crud_all_users.sql)
  • Listed all migrations related to RLS/security/audit/role
  • Found all CREATE POLICY and ENABLE ROW LEVEL SECURITY statements
  • Reviewed the RLS policies test file
  • Checked JWT claims usage in migrations

Now I need to provide a comprehensive answer based on the findings, following the Supabase best practices from the skill documentation.

Let me structure the response to cover all the audit points with specific findings from the project. The user asked for an audit of RLS in the Supabase project, covering:

  1. Policies RLS in all tables
  2. Migrations of RLS (focusing on specific files)
  3. Tests of RLS
  4. JWT claims used in RLS

I've:

  • Listed the current directory and found the Supabase project structure
  • Examined the specified migration files (20260330000000_audit_rls_security.sql, 20260330000001_audit_vault_strict.sql, 058_atendimentos_full_crud_all_users.sql)
  • Listed all migrations related to RLS/security/audit/role
  • Found all CREATE POLICY and ENABLE ROW LEVEL SECURITY statements
  • Reviewed the RLS policies test file
  • Checked JWT claims usage in migrations

Now I need to provide a comprehensive answer based on the findings, following the Supabase best practices from the skill documentation.

Let me structure the response to cover all the audit points with specific findings from the project.

Built with LogoFlowershow