Skip to main content

Postgres + RLS Foundation

Status​

Implemented.

  • SRS references: multi-tenant SaaS isolation and tenant-owned data.
  • Client response references: separate database per tenant is rejected; logical isolation and row-level partitioning are Phase 1.
  • ADR references: Phase 1 platform stack ADR, AWS deployment ADR.
  • Task board references: OP-D016, OP-D017, OP-D018, OP-D019.

Problem Statement​

OneProtect must protect tenant-owned data without relying on a separate database per tenant. Client reviewers need a clear way to validate that tenant isolation is enforced below the API layer.

Architectural Intent​

Postgres is the canonical relational store. Tenant isolation is enforced with tenant_id, RLS policies, forced RLS, tenant-scoped repository methods, and tests that prove cross-tenant access fails closed.

What Was Implemented​

  • Postgres schema and migrations for the MVP tenant-owned models.
  • RLS-enabled runtime path behind the FastAPI backend.
  • SQL assertions and Python tests for tenant isolation.
  • Local SQLite compatibility kept only for local/test paths where documented.
  • Tenant context flows from verified auth context in production-shaped paths.

Components Involved​

  • FastAPI backend.
  • Postgres migrations under db/postgres/.
  • Tenant context helpers under services/common/.
  • Repository/read-model code under poc/ingest_api/.

APIs / Events / Schemas​

  • Tenant-scoped API reads and writes across alerts, evidence, audit, integration, and asset foundations.
  • OpenAPI remains the source for implemented endpoint shapes.
  • RLS behavior is validated by SQL and runtime tests rather than by UI claims.

Deployment Notes​

Local Compose supports the Postgres runtime. AWS planning targets RDS/Aurora PostgreSQL, but the managed AWS database is not deployed by this note.

Security / Tenant Isolation​

  • Tenant-owned tables include tenant_id.
  • RLS is enabled and forced on production-shaped tenant tables.
  • Cross-tenant reads and writes are tested.
  • Missing tenant context fails closed.
  • Auditor reads are scoped and redacted through the existing read paths.

Validation Steps​

UI Validation​

  1. Open the Admin Console.
  2. Confirm tenant/session context is visible in the top bar.
  3. Review Assets, Integrations, Audit, and Compliance Evidence pages for tenant-scoped data only.

API Validation​

curl -H "Authorization: Bearer <tenant-token>" \
  http://127.0.0.1:8000/api/v1/session

Use a token for a different tenant and confirm protected tenant reads fail or return only that tenant's data.

Smoke Validation​

make test-postgres
make test-postgres-runtime

Known Limitations​

  • AWS RDS/Aurora deployment is scaffolded, not fully applied from this note.
  • Per-tenant encryption key model is accepted in ADR-0018, but runtime KMS/S3 enforcement is not part of the RLS foundation itself.
  • BYOK remains Phase 2.

Follow-Up Work​

  • Apply AWS dev database infrastructure through gated IaC.
  • Implement runtime KMS/S3 use of ADR-0018 key refs when object storage is scoped.
  • Keep adding RLS assertions for every tenant-owned table.

Acceptance Criteria Mapping​

Acceptance criterionEvidence
Tenant data is row-level isolatedPostgres migrations and make test-postgres
Runtime path uses Postgres/RLSmake test-postgres-runtime
Separate DB per tenant is not requiredArchitecture docs and RLS tests
Missing tenant context fails closedAuth/runtime tests