CONTRACT-FINDINGS-LEDGER-RLS-011: Row-Level Security & Partitioning

Status: Implemented Version: 1.1.0 Published: 2025-12-05 Owners: Platform/DB Guild, Findings Ledger Guild Unblocks: LEDGER-TEN-48-001-DEV, DEVOPS-LEDGER-TEN-48-001-REL

Overview

This contract is for platform and database engineers working on the Stella Ops Findings Ledger, plus reviewers auditing its tenant-isolation guarantees. It specifies the Row-Level Security (RLS) and partitioning strategy for the Findings Ledger module, based on the proven Evidence Locker implementation pattern and adapted for the Findings Ledger schema.

Implementation note (verified against source 2026-05-30). The RLS design described in this contract is implemented and live. RLS is enabled and the tenant-isolation policies, validation function, and admin bypass role are created by migration migrations/007_enable_rls.sql (and extended for asset_registry_events by migrations/017_asset_registry_event_visibility.sql). These migrations are embedded resources applied automatically on service startup via AddStartupMigrations(schemaName: "findings", moduleName: "FindingsLedger", …) in StellaOps.Findings.Ledger.WebService/Program.cs. The sections below describe the as-built behaviour. The earlier “Required Implementation (The Missing 10%)” framing is retained as a historical record of the gap that has since been closed.

Schema note. All Findings Ledger tables live in the PostgreSQL schema findings (not public). Connections set search_path TO findings, public (see LedgerDataSource.ConfigureSessionDefaultsAsync and FindingsLedgerDbContextFactory.DefaultSchemaName). The schema-qualified DDL in the migration files uses findings.<table> and findings_ledger_app.<function>.

Current State (Implemented)

The Findings Ledger has these foundational elements:

1. LIST Partitioning by Tenant

The append-path and projection tables are LIST-partitioned by tenant_id, each with a DEFAULT partition (see migrations/001_initial.sql):

-- Example from ledger_events (schema-qualified, with default partition)
CREATE TABLE findings.ledger_events (
    tenant_id TEXT NOT NULL,
    ...
) PARTITION BY LIST (tenant_id);

CREATE TABLE findings.ledger_events_default PARTITION OF findings.ledger_events DEFAULT;

Tables that are LIST-partitioned (migrations/001_initial.sql):

Tenant-scoped tables that are NOT partitioned (plain tables keyed on tenant_id; they rely on RLS + composite primary keys for isolation, not partition pruning):

All nine tables above are tenant-scoped and RLS-protected (see RlsValidationService.RlsProtectedTables). Only the first five use declarative LIST partitioning; the prior version of this contract incorrectly claimed all tables were partitioned.

2. Session Variable Configuration

Connection setup in LedgerDataSource.ConfigureSessionAsync (every tenant-scoped connection also runs SET TIME ZONE 'UTC' and SET search_path TO findings, public first, via ConfigureSessionDefaultsAsync):

await using var tenantCommand = new NpgsqlCommand(
    "SELECT set_config('app.current_tenant', @tenant, false);", connection);
tenantCommand.Parameters.AddWithValue("tenant", tenantId);
await tenantCommand.ExecuteNonQueryAsync(cancellationToken);

The third set_config argument is false (session-scoped, not transaction-local).

3. HTTP Header Tenant Extraction

From StellaOps.Findings.Ledger.WebService/Program.cs:

4. Application-Level Query Filtering

Repository queries include WHERE tenant_id = @tenant (defense in depth on top of RLS).

As-Built RLS Implementation (formerly “The Missing 10%”)

The DDL in this section is the as-built content of migrations/007_enable_rls.sql (tables 1-8) and migrations/017_asset_registry_event_visibility.sql (the asset_registry_events table). All statements are schema-qualified to findings / findings_ledger_app, matching the deployed migrations.

1. Tenant Validation Function

Created following the Evidence Locker pattern (migrations/007_enable_rls.sql):

-- Schema for application-level functions
CREATE SCHEMA IF NOT EXISTS findings_ledger_app;

-- Tenant validation function (TEXT version for Ledger compatibility)
CREATE OR REPLACE FUNCTION findings_ledger_app.require_current_tenant()
RETURNS TEXT
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    tenant_text TEXT;
BEGIN
    tenant_text := current_setting('app.current_tenant', true);
    IF tenant_text IS NULL OR length(trim(tenant_text)) = 0 THEN
        RAISE EXCEPTION 'app.current_tenant is not set for the current session'
            USING ERRCODE = 'P0001';
    END IF;
    RETURN tenant_text;
END;
$$;

COMMENT ON FUNCTION findings_ledger_app.require_current_tenant() IS
    'Returns the current tenant ID from session variable, raises exception if not set';

2. RLS Policies for All Tenant-Scoped Tables

Applied to each tenant-scoped table. Each block enables and forces RLS, drops any pre-existing policy (idempotent re-run), and creates the <table>_tenant_isolation policy. Tables are schema-qualified to findings. The first eight tables are created by migrations/007_enable_rls.sql; asset_registry_events is created by migrations/017_asset_registry_event_visibility.sql.

-- ============================================
-- ledger_events
-- ============================================
ALTER TABLE findings.ledger_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE findings.ledger_events FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS ledger_events_tenant_isolation ON findings.ledger_events;
CREATE POLICY ledger_events_tenant_isolation
    ON findings.ledger_events
    FOR ALL
    USING (tenant_id = findings_ledger_app.require_current_tenant())
    WITH CHECK (tenant_id = findings_ledger_app.require_current_tenant());

-- ============================================
-- ledger_merkle_roots
-- ============================================
ALTER TABLE findings.ledger_merkle_roots ENABLE ROW LEVEL SECURITY;
ALTER TABLE findings.ledger_merkle_roots FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS ledger_merkle_roots_tenant_isolation ON findings.ledger_merkle_roots;
CREATE POLICY ledger_merkle_roots_tenant_isolation
    ON findings.ledger_merkle_roots
    FOR ALL
    USING (tenant_id = findings_ledger_app.require_current_tenant())
    WITH CHECK (tenant_id = findings_ledger_app.require_current_tenant());

-- ============================================
-- findings_projection
-- ============================================
ALTER TABLE findings.findings_projection ENABLE ROW LEVEL SECURITY;
ALTER TABLE findings.findings_projection FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS findings_projection_tenant_isolation ON findings.findings_projection;
CREATE POLICY findings_projection_tenant_isolation
    ON findings.findings_projection
    FOR ALL
    USING (tenant_id = findings_ledger_app.require_current_tenant())
    WITH CHECK (tenant_id = findings_ledger_app.require_current_tenant());

-- ============================================
-- finding_history
-- ============================================
ALTER TABLE findings.finding_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE findings.finding_history FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS finding_history_tenant_isolation ON findings.finding_history;
CREATE POLICY finding_history_tenant_isolation
    ON findings.finding_history
    FOR ALL
    USING (tenant_id = findings_ledger_app.require_current_tenant())
    WITH CHECK (tenant_id = findings_ledger_app.require_current_tenant());

-- ============================================
-- triage_actions
-- ============================================
ALTER TABLE findings.triage_actions ENABLE ROW LEVEL SECURITY;
ALTER TABLE findings.triage_actions FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS triage_actions_tenant_isolation ON findings.triage_actions;
CREATE POLICY triage_actions_tenant_isolation
    ON findings.triage_actions
    FOR ALL
    USING (tenant_id = findings_ledger_app.require_current_tenant())
    WITH CHECK (tenant_id = findings_ledger_app.require_current_tenant());

-- ============================================
-- ledger_attestations
-- ============================================
ALTER TABLE findings.ledger_attestations ENABLE ROW LEVEL SECURITY;
ALTER TABLE findings.ledger_attestations FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS ledger_attestations_tenant_isolation ON findings.ledger_attestations;
CREATE POLICY ledger_attestations_tenant_isolation
    ON findings.ledger_attestations
    FOR ALL
    USING (tenant_id = findings_ledger_app.require_current_tenant())
    WITH CHECK (tenant_id = findings_ledger_app.require_current_tenant());

-- ============================================
-- orchestrator_exports
-- ============================================
ALTER TABLE findings.orchestrator_exports ENABLE ROW LEVEL SECURITY;
ALTER TABLE findings.orchestrator_exports FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS orchestrator_exports_tenant_isolation ON findings.orchestrator_exports;
CREATE POLICY orchestrator_exports_tenant_isolation
    ON findings.orchestrator_exports
    FOR ALL
    USING (tenant_id = findings_ledger_app.require_current_tenant())
    WITH CHECK (tenant_id = findings_ledger_app.require_current_tenant());

-- ============================================
-- airgap_imports
-- ============================================
ALTER TABLE findings.airgap_imports ENABLE ROW LEVEL SECURITY;
ALTER TABLE findings.airgap_imports FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS airgap_imports_tenant_isolation ON findings.airgap_imports;
CREATE POLICY airgap_imports_tenant_isolation
    ON findings.airgap_imports
    FOR ALL
    USING (tenant_id = findings_ledger_app.require_current_tenant())
    WITH CHECK (tenant_id = findings_ledger_app.require_current_tenant());

-- ============================================
-- asset_registry_events  (migration 017, added after 007)
-- ============================================
ALTER TABLE findings.asset_registry_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE findings.asset_registry_events FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS asset_registry_events_tenant_isolation ON findings.asset_registry_events;
CREATE POLICY asset_registry_events_tenant_isolation
    ON findings.asset_registry_events
    FOR ALL
    USING (tenant_id = findings_ledger_app.require_current_tenant())
    WITH CHECK (tenant_id = findings_ledger_app.require_current_tenant());

3. System/Admin Bypass Role

For migrations and cross-tenant admin operations. The migration creates a single NOLOGIN BYPASSRLS role inside a guarded DO block so re-runs are idempotent (migrations/007_enable_rls.sql):

-- Create admin role that bypasses RLS (idempotent)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'findings_ledger_admin') THEN
        CREATE ROLE findings_ledger_admin NOLOGIN BYPASSRLS;
    END IF;
END;
$$;

COMMENT ON ROLE findings_ledger_admin IS
    'Admin role that bypasses RLS for migrations and cross-tenant operations';

Not implemented in source. The migration does not grant findings_ledger_admin to any application/migration login role (no GRANT findings_ledger_admin TO stellaops_migration_user). In practice the startup migration runner and the runtime service connect with the database owner/login configured in LedgerServiceOptions.Database.ConnectionString; that login is expected to own the findings schema (and therefore implicitly bypasses FORCE RLS as the table owner). Granting findings_ledger_admin to a dedicated migration login is a deployment-time choice left to the operator, not part of the embedded migration.

Connection Patterns

These are the actual method signatures on LedgerDataSource (Infrastructure/Postgres/LedgerDataSource.cs).

Regular Connections (Tenant-Scoped)

OpenConnectionAsync(string tenantId, CancellationToken) (with an overload that adds a role label for connection metrics). The session is configured with SET TIME ZONE 'UTC', SET search_path TO findings, public, and then set_config('app.current_tenant', …):

public Task<NpgsqlConnection> OpenConnectionAsync(string tenantId, CancellationToken cancellationToken)
    => OpenConnectionInternalAsync(tenantId, "unspecified", cancellationToken);

public Task<NpgsqlConnection> OpenConnectionAsync(string tenantId, string role, CancellationToken cancellationToken)
    => OpenConnectionInternalAsync(tenantId, role, cancellationToken);

// ConfigureSessionAsync runs (per connection):
//   SET TIME ZONE 'UTC';
//   SET search_path TO findings, public;
//   SELECT set_config('app.current_tenant', @tenant, false);  -- when tenantId is non-blank

System Connections (No Tenant - Migrations / Validation)

OpenSystemConnectionAsync(CancellationToken) opens from the same NpgsqlDataSource and simply omits the app.current_tenant setting. There is no separate _adminDataSource and the connection does not log in as findings_ledger_admin — RLS is only bypassed when the connecting login owns the tables. Because no tenant is set, RLS policies will raise P0001 on any tenant-scoped table query unless executed by a bypassing/owner role.

public async Task<NpgsqlConnection> OpenSystemConnectionAsync(CancellationToken cancellationToken)
{
    // No tenant set. ONLY for: migrations, health checks, RLS validation, cross-tenant admin ops.
    var connection = await _dataSource.OpenConnectionAsync(cancellationToken);
    await ConfigureSessionDefaultsAsync(connection, cancellationToken); // UTC + search_path only
    return connection;
}

Compliance Validation

A runtime validator, RlsValidationService (Infrastructure/Postgres/RlsValidationService.cs), performs these checks programmatically. It validates nine tables (RlsProtectedTables): ledger_events, ledger_merkle_roots, findings_projection, finding_history, triage_actions, ledger_attestations, orchestrator_exports, airgap_imports, and asset_registry_events. It returns an RlsValidationResult (IsCompliant, TablesWithRlsEnabled, TablesWithPolicies, TenantFunctionExists, Issues) and reports compliant only when all nine tables have RLS enabled, all nine have a %_tenant_isolation policy, and the tenant function exists.

Known source caveat — flag for follow-up. RlsValidationService filters its pg_tables / pg_policies lookups with schemaname = 'public', but the Findings Ledger tables actually live in the findings schema. The pre-deployment SQL below mirrors the correct schema (findings). The validator’s 'public' filter should be corrected to 'findings' (or made schema-aware); this is a code bug, not a doc-only issue, so it is recorded here rather than silently “fixed” in the SQL.

Pre-Deployment Checks (schema-corrected)

-- 1. Verify RLS enabled on all tenant-scoped tables
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'findings'
  AND tablename IN (
    'ledger_events', 'ledger_merkle_roots', 'findings_projection',
    'finding_history', 'triage_actions', 'ledger_attestations',
    'orchestrator_exports', 'airgap_imports', 'asset_registry_events'
  )
  AND rowsecurity = false;
-- Expected: 0 rows (all should have RLS enabled)

-- 2. Verify policies exist for all tables
SELECT tablename, policyname
FROM pg_policies
WHERE schemaname = 'findings'
  AND tablename IN (
    'ledger_events', 'ledger_merkle_roots', 'findings_projection',
    'finding_history', 'triage_actions', 'ledger_attestations',
    'orchestrator_exports', 'airgap_imports', 'asset_registry_events'
  );
-- Expected: 9 rows (one tenant-isolation policy per table)

-- 3. Verify tenant validation function exists
SELECT proname, prosrc
FROM pg_proc
WHERE proname = 'require_current_tenant'
  AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'findings_ledger_app');
-- Expected: 1 row

Runtime Regression Tests

Illustrative. The snippets below show the intended regression shape. As of 2026-05-30 no dedicated cross-tenant RLS regression test class exists under StellaOps.Findings.Ledger.Tests (this is a coverage gap, flagged for follow-up). The real tenant connection helper is LedgerDataSource.OpenConnectionAsync(string tenantId, CancellationToken); a no-tenant connection is obtained via OpenSystemConnectionAsync(CancellationToken).

[Fact]
public async Task CrossTenantRead_ShouldFail_WithRlsError()
{
    // Arrange: Insert data as tenant A (sets app.current_tenant = 'tenant-a')
    await using var connA = await _dataSource.OpenConnectionAsync("tenant-a", ct);
    await InsertFinding(connA, "finding-1", ct);

    // Act: Try to read as tenant B
    await using var connB = await _dataSource.OpenConnectionAsync("tenant-b", ct);
    var result = await QueryFindings(connB, ct);

    // Assert: No rows returned (RLS blocks cross-tenant access)
    Assert.Empty(result);
}

[Fact]
public async Task NoTenantContext_ShouldFail_WithException()
{
    // Arrange: Open a system connection (no app.current_tenant set)
    await using var conn = await _dataSource.OpenSystemConnectionAsync(ct);

    // Act & Assert: query on an RLS-forced table raises P0001 from require_current_tenant()
    await Assert.ThrowsAsync<PostgresException>(async () =>
    {
        await conn.ExecuteAsync("SELECT * FROM findings.ledger_events LIMIT 1");
    });
}

Migration Strategy

Migration File: 007_enable_rls.sql (as-built)

The real migration sets search_path TO findings, public, wraps everything in a single transaction, creates the app schema + tenant function, enables and forces RLS with idempotent DROP POLICY IF EXISTS + CREATE POLICY on the eight tables from migration 007, and creates the admin role inside a guarded DO block (PostgreSQL has no CREATE ROLE IF NOT EXISTS). The ninth table, asset_registry_events, gets the same treatment in migrations/017_asset_registry_event_visibility.sql.

-- migrations/007_enable_rls.sql
-- Enable Row-Level Security for Findings Ledger tenant isolation (LEDGER-TEN-48-001-DEV)

SET search_path TO findings, public;

BEGIN;

-- 1. Create app schema and tenant function
CREATE SCHEMA IF NOT EXISTS findings_ledger_app;

CREATE OR REPLACE FUNCTION findings_ledger_app.require_current_tenant()
RETURNS TEXT LANGUAGE plpgsql STABLE AS $$
DECLARE tenant_text TEXT;
BEGIN
    tenant_text := current_setting('app.current_tenant', true);
    IF tenant_text IS NULL OR length(trim(tenant_text)) = 0 THEN
        RAISE EXCEPTION 'app.current_tenant is not set for the current session'
            USING ERRCODE = 'P0001';
    END IF;
    RETURN tenant_text;
END;
$$;

-- 2. Enable + FORCE RLS and create *_tenant_isolation policies (see full SQL above)
-- ... (applied to the 8 tables in migration 007; asset_registry_events in migration 017)

-- 3. Create admin bypass role (idempotent, no CREATE ROLE IF NOT EXISTS in PostgreSQL)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'findings_ledger_admin') THEN
        CREATE ROLE findings_ledger_admin NOLOGIN BYPASSRLS;
    END IF;
END;
$$;

COMMIT;

Startup application. Both 007_enable_rls.sql and 017_…sql are embedded resources (<EmbeddedResource Include="migrations\**\*.sql" /> in StellaOps.Findings.Ledger.csproj) and are applied automatically on service startup by AddStartupMigrations — they are not manual operator scripts.

Rollback: 007_enable_rls_rollback.sql

This rollback script remains a manual operator tool. It is explicitly excluded from the embedded migration set (<EmbeddedResource Remove="migrations\007_enable_rls_rollback.sql" /> in the .csproj), so forward startup will not flag it as a pending release migration. Note the rollback in source does not drop the asset_registry_events policy/RLS (introduced later in migration 017) and intentionally does not drop the findings_ledger_admin role.

BEGIN;

-- Disable RLS on all tables
ALTER TABLE ledger_events DISABLE ROW LEVEL SECURITY;
ALTER TABLE ledger_merkle_roots DISABLE ROW LEVEL SECURITY;
ALTER TABLE findings_projection DISABLE ROW LEVEL SECURITY;
ALTER TABLE finding_history DISABLE ROW LEVEL SECURITY;
ALTER TABLE triage_actions DISABLE ROW LEVEL SECURITY;
ALTER TABLE ledger_attestations DISABLE ROW LEVEL SECURITY;
ALTER TABLE orchestrator_exports DISABLE ROW LEVEL SECURITY;
ALTER TABLE airgap_imports DISABLE ROW LEVEL SECURITY;

-- Drop policies
DROP POLICY IF EXISTS ledger_events_tenant_isolation ON ledger_events;
DROP POLICY IF EXISTS ledger_merkle_roots_tenant_isolation ON ledger_merkle_roots;
DROP POLICY IF EXISTS findings_projection_tenant_isolation ON findings_projection;
DROP POLICY IF EXISTS finding_history_tenant_isolation ON finding_history;
DROP POLICY IF EXISTS triage_actions_tenant_isolation ON triage_actions;
DROP POLICY IF EXISTS ledger_attestations_tenant_isolation ON ledger_attestations;
DROP POLICY IF EXISTS orchestrator_exports_tenant_isolation ON orchestrator_exports;
DROP POLICY IF EXISTS airgap_imports_tenant_isolation ON airgap_imports;

-- Drop function and schema
DROP FUNCTION IF EXISTS findings_ledger_app.require_current_tenant();
DROP SCHEMA IF EXISTS findings_ledger_app;

COMMIT;

Audit Requirements

  1. All write operations must log tenant_id and actor_id
  2. System connections must log reason and operator
  3. RLS bypass operations must be audited separately
  4. Cross-tenant queries (admin only) must require justification ticket

Reference Implementation

Evidence Locker RLS implementation:

Approval Checklist

Changelog

VersionDateAuthorChanges
1.0.02025-12-05Platform GuildInitial contract based on Evidence Locker pattern
1.1.02026-05-30Docs reconciliationReconciled to as-built source: status Published→Implemented; corrected schema to findings (was public); fixed partitioning claim (only 5 of 9 tenant tables are LIST-partitioned); added 9th RLS-protected table asset_registry_events (migration 017); schema-qualified all DDL and added idempotent DROP POLICY/guarded CREATE ROLE; corrected connection-pattern method names (OpenConnectionAsync/OpenSystemConnectionAsync, no _adminDataSource); pointed compliance section at RlsValidationService and flagged its schemaname='public' bug; noted no dedicated cross-tenant RLS regression test exists yet.