Findings Ledger RLS Migration Guide

Audience: DevOps operators applying the Findings Ledger Row-Level Security migration in release pipelines and air-gapped environments. Contract: CONTRACT-FINDINGS-LEDGER-RLS-011 Applies to: PostgreSQL 16+ with the Findings Ledger schema.

Overview

Migration 007_enable_rls.sql enables Row-Level Security (RLS) on all Findings Ledger tables, enforcing tenant isolation at the database level. This guide covers the deployment, validation, and rollback procedures for both standard release pipelines and air-gapped environments.

Prerequisites

PostgreSQL 16 or later
All prior migrations applied (001–006)
Service accounts configured with appropriate roles

Migration Files

File	Purpose	SHA256
`007_enable_rls.sql`	Apply RLS policies	(generated at build time)
`007_enable_rls_rollback.sql`	Revert RLS policies	manual script on disk; excluded from embedded startup migrations
`007_enable_rls.manifest.json`	Metadata for offline-kit	(generated at build time)

Protected Tables

The migration enables RLS and creates tenant isolation policies on:

ledger_events
ledger_merkle_roots
findings_projection
finding_history
triage_actions
ledger_attestations
orchestrator_exports
airgap_imports

Deployment Procedures

Standard Pipeline Deployment

The CI workflow at .gitea/workflows/findings-ledger-ci.yml handles migration validation automatically:

Applies prerequisites (001–006)
Applies RLS migration (007)
Validates RLS configuration
Tests rollback capability
Verifies idempotency

Manual Deployment

# 1. Connect to database
psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE

# 2. Apply migration
\i migrations/007_enable_rls.sql

# 3. Validate
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename IN (
    'ledger_events', 'ledger_merkle_roots', 'findings_projection',
    'finding_history', 'triage_actions', 'ledger_attestations',
    'orchestrator_exports', 'airgap_imports'
  );
-- All should show rowsecurity = true

Air-Gapped Deployment

Export migration bundle

# After CI passes, download the migration artifact
gh run download -n findings-ledger-migrations

Transfer to air-gapped environment
- Copy out/findings-ledger/offline-kit/ to target host
- Verify SHA256 checksums match manifest

Apply migration

cd /path/to/offline-kit/migrations
# Verify checksums
sha256sum -c 007_enable_rls.manifest.json

# Apply
psql -f 007_enable_rls.sql

Validate using RlsValidationService

dotnet run --project tools/LedgerReplayHarness \
  -- --connection "$LEDGER_DB" --validate-rls-only

Rollback Procedure

If issues are encountered, rollback is safe and non-destructive:

psql -f migrations/007_enable_rls_rollback.sql

The rollback:

Disables RLS on all 8 tables
Drops tenant isolation policies
Removes the findings_ledger_app schema and tenant function
Does NOT drop the findings_ledger_admin role (preserves other grants)

007_enable_rls_rollback.sql is intentionally kept out of the service’s embedded startup migrations so normal boot does not treat rollback as pending forward work.

Validation Checklist

After applying the migration, verify:

[ ] All 8 tables have relrowsecurity = true in pg_class
[ ] All 8 tenant isolation policies exist in pg_policies
[ ] Function findings_ledger_app.require_current_tenant() exists
[ ] Application can connect and query with tenant context
[ ] RlsValidationService.ValidateAsync() returns IsCompliant = true

Tenant Context Requirements

After RLS is enabled, all queries must set tenant context:

-- Set tenant before querying
SELECT set_config('app.current_tenant', 'tenant-123', false);

-- Now queries are tenant-scoped
SELECT * FROM ledger_events;  -- Only returns tenant-123 data

The LedgerDataSource.OpenConnectionAsync(tenantId, ...) handles this automatically for application code.

Admin Bypass

For migrations and cross-tenant admin operations, use the findings_ledger_admin role:

SET ROLE findings_ledger_admin;
-- Queries now bypass RLS

Metrics & Observability

After migration, monitor:

ledger_connection_opened_total{role="tenant"} - Connection count with tenant context
ledger_connection_opened_total{role="system"} - Admin/migration connections
RLS violation errors in application logs

CI Workflow Integration

The migration is validated in every CI run via:

# .gitea/workflows/findings-ledger-ci.yml
jobs:
  migration-validation:
    # Tests apply → validate → rollback → re-apply cycle