Findings Ledger RLS Migration Guide

Withdrawn chain (2026-09-30, SPRINT_20260928_004 PCD-149). The 007_enable_rls.sql and 007_enable_rls_rollback.sql files this guide describes belonged to the StellaOps.Findings.Ledger migration chain, which no host ran and which is now frozen under src/__Obsoleted/Findings/StellaOps.Findings.Ledger/PCD-149/. On a current install RLS is enabled, forced and policed by the consolidated Findings baseline (src/Findings/__Libraries/StellaOps.Findings.Persistence/Migrations/001_findings_consolidated_baseline.sql) on startup, with no separate operator step. The migration files, eight-table checklist, deployment and rollback sections below describe the withdrawn chain. For the current connection boundary, use Current owner and tenant boundary and the current RLS contract.

Validator withdrawn (2026-10-06, PCD-330). The unregistered RlsValidationService cited in the historical steps below is frozen. Current Findings hosts expose LedgerRowLevelSecurityPostureCheck (doctor.findings.ledger.rls-posture) through their service Doctor checks.

Audience: operators reviewing the historical migration and current tenant-scoped connection requirements. 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

Migration Files

FilePurposeSHA256
007_enable_rls.sqlApply RLS policies(generated at build time)
007_enable_rls_rollback.sqlRevert RLS policiesmanual script on disk; excluded from embedded startup migrations
007_enable_rls.manifest.jsonMetadata for offline-kit(generated at build time)

Protected Tables

The migration enables RLS and creates tenant isolation policies on:

  1. ledger_events
  2. ledger_merkle_roots
  3. findings_projection
  4. finding_history
  5. triage_actions
  6. ledger_attestations
  7. orchestrator_exports
  8. airgap_imports

Deployment Procedures

Standard Pipeline Deployment

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

  1. Applies prerequisites (001–006)
  2. Applies RLS migration (007)
  3. Validates RLS configuration
  4. Tests rollback capability
  5. 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

  1. Export migration bundle

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

  2. Transfer to air-gapped environment

    • Copy out/findings-ledger/offline-kit/ to target host
    • Verify SHA256 checksums match manifest

  3. Apply migration

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

# Apply
psql -f 007_enable_rls.sql

  4. 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:

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:

Tenant Context Requirements

Reads of protected Findings tables require a tenant on each connection. For an authorized tenant, the session binding is:

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

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

LedgerDataSource.OpenConnectionAsync(tenantId, ...) binds this setting for application code and rejects a blank tenant before opening the connection. System connections have a separate, restricted purpose described below.

Current owner and tenant boundary

Verified against 54d487c9b9 on 2026-10-04 (PCD-268). The supported service owner remains NOSUPERUSER NOBYPASSRLS under ADR-039. The install path creates findings_ledger_admin as a separate NOLOGIN BYPASSRLS role for the startup prerequisite; it grants neither role membership to the Findings owner nor schema/table access to that role. Its existence does not provide a cross-tenant query procedure.

BYPASSRLS is a role attribute and is not inherited through membership. Even if membership were separately granted, switching to that role replaces the owner’s privileges: the retained baseline analysis and FindingsRoleGrantTests show 42501 on the findings schema. That historical measurement explains why the former SET ROLE instruction was unusable; it is not a request to grant membership, schema access or bypass attributes.

Use the current tenant-scoped connection path for protected parent tables. Their forced RLS policy also applies to the owner and rejects an unset tenant with P0001. OpenSystemConnectionAsync(purpose, ...) uses the same owner pool and clears the tenant; it is limited to relations without RLS, such as the tenant registry and checkpoints. It does not log in as the admin role and cannot read protected ledger rows across tenants.

The earlier tenantless projection-reader failure belongs to PCD-241, completed on 2026-10-03. Its current implementation enumerates findings.ledger_tenants (forward migration 018), then reads and checkpoints each tenant separately. This documentation correction preserves that runtime disposition and does not repeat its database acceptance tests. See the owner posture and settled deployment guidance.

Source re-verification (read-only; no database connection):

rg -n "NOBYPASSRLS|findings_ledger_admin" devops/compose/postgres-init/20-service-databases.sh devops/release/bundle/config/postgres-init/20-service-databases.sh
rg -n "attributes are never inherited|read fails 42501|FORCE ROW LEVEL SECURITY" src/Findings/__Libraries/StellaOps.Findings.Persistence/Migrations/001_findings_consolidated_baseline.sql
rg -n "OpenSystemConnectionAsync|IsNullOrWhiteSpace|set_config" src/Findings/StellaOps.Findings.Ledger/Infrastructure/Postgres/LedgerDataSource.cs
rg -n "ListTenantsAsync|foreach.*tenantId|ReadNextBatchAsync" src/Findings/StellaOps.Findings.Ledger/Infrastructure/Projection/LedgerProjectionWorker.cs

Metrics & Observability

After migration, monitor:

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