SPEC-003 — Tenant Isolation & Security

Status	Draft
PRD source	§8.1 (FR-8.1–8.5), §8.3 (FR-8.23–8.28), DM-3
Depends on	SPEC-001 (session identity)
Required by	Every tenant-scoped spec, SPEC-021
Milestone	M0

Description

The enforcement of "a user sees only their own investments" as a security boundary, not a query convention.

The distinction matters. If isolation depends on every query remembering WHERE user_id = …, then one forgotten clause in one report endpoint leaks one user's entire financial position to another. PostgreSQL Row-Level Security is specified as the floor beneath the application: even a query with no tenant predicate at all returns only the session tenant's rows.

Cross-tenant leakage is the most serious failure this product can have (PRD risk R6), and this spec exists to make it structurally difficult rather than merely unlikely.

Also covers the product-wide security posture: no credential of any kind is ever collected.

Business rules

ID	Rule
BR-003-01	Every table holding user data carries user_id NOT NULL with a foreign key to User. There is no tenant-scoped table without it.
BR-003-02	Every tenant-scoped table has an RLS policy restricting all of SELECT, INSERT, UPDATE and DELETE to the session tenant.
BR-003-03	The application connects with a database role subject to RLS. No request-serving code path uses a superuser, table owner, or BYPASSRLS role.
BR-003-04	Tenant context is set from the authenticated session at the start of each request or transaction, never from a client-supplied parameter, header or body field.
BR-003-05	A request that supplies a tenant identifier is not merely ignored — the attempt is logged as a security event.
BR-003-06	Shared reference tables (Asset, Institution, PriceQuote, LatestQuote, IndexSeries) hold no personal data and are exempt from RLS by explicit declaration, reviewed when added.
BR-003-07	Background jobs spanning tenants may touch only those shared reference tables. A cross-tenant job that reads user data requires documented justification and per-tenant context switching.
BR-003-08	No password, B3 credential, broker credential or bank credential is ever collected, transmitted or stored. This is a firm product boundary and the reason PRD §4.1 rejected credential-based scraping.
BR-003-09	Secrets live in a root-only .env on the VPS, injected via Docker Compose, with GitHub Actions secrets for CI. Never in source control, never in an effective-config view, never in logs or error reports. (Made concrete by ARCHITECTURE.md; previously "a managed secret store".)
BR-003-10	TLS 1.2+ in transit; encryption at rest for database and all backups.
BR-003-11	Standard web hardening applies: CSRF protection, strict CSP, security headers, rate limiting on authentication and upload endpoints.
BR-003-12	Uploaded files are validated by content, size-capped per config, and parsed in a resource-limited context. Spreadsheet parsers are a known attack surface (zip bombs, formula injection, XXE).
BR-003-13	Exported CSV cells beginning =, +, - or @ are neutralised to prevent formula injection in the recipient's spreadsheet.
BR-003-14	Dependency vulnerability scanning runs in CI and fails the build on known-exploitable severities.

Acceptance Criteria

• Every tenant-scoped table has user_id NOT NULL, verified by an automated schema test that fails when a new table is added without it.
• Every tenant-scoped table has RLS enabled and forced, verified by automated test.
• A query with no WHERE user_id clause, run as the application role with tenant A's context, returns only tenant A's rows.
• The application's database role cannot bypass RLS — asserted directly against the role's attributes.
• An automated isolation test exists for every tenant-scoped entity, exercised through the API, the report endpoints and the export endpoints, not only at the repository layer.
• Adding a new tenant-scoped entity without an accompanying isolation test fails CI.
• A request supplying another tenant's user_id returns the caller's own data and emits a security event.
• Cross-tenant background jobs are demonstrated to touch only shared reference tables.
• No input field anywhere in the product accepts a third-party credential.
• An uploaded zip bomb or oversized spreadsheet is rejected without exhausting server resources.
• An exported CSV containing a cell starting with = opens inertly in Excel and LibreOffice.
• CSP, CSRF and security headers verified by automated test against running endpoints.
• CI fails on a dependency with a known exploitable vulnerability.

Out of Scope

• Authentication itself (SPEC-001).
• LGPD data-subject rights, consent and retention (SPEC-004) — this spec is technical isolation, that one is legal obligation.
• Multi-user tenants, roles, permissions or sharing — every tenant is exactly one user.
• Field-level encryption beyond database-level encryption at rest.
• Penetration testing and formal security certification.
• WAF, DDoS protection and other infrastructure-layer controls.
• Support-side impersonation — deliberately not built.

Decision Logs

#	Decision	Alternatives considered	Rationale
DL-003-01	PostgreSQL RLS as the enforcement floor.	Application-layer scoping only; a shared query builder that always injects the predicate	Application-layer scoping fails open — one forgotten clause leaks everything, and the failure is invisible in testing because the developer's own data is returned. RLS fails closed: the worst case of a forgotten predicate is an empty result. A query builder helps but can be bypassed by any raw query.
DL-003-02	Application role is subject to RLS, never bypassing.	Run as owner for convenience; bypass for admin endpoints	A BYPASSRLS role in the request path makes every other control decorative. Operational access needing broader scope uses a separate, audited role outside the request path.
DL-003-03	Isolation tested through the API surface, not the repository layer.	Unit-test the data access layer	Leaks happen in the layers above the repository — a report aggregator, an export endpoint, a cache keyed without tenant. Testing at the repository proves the wrong thing.
DL-003-04	Shared reference tables are exempt from RLS by explicit declaration.	RLS on every table uniformly	Quotes and the asset catalog carry no personal data and are deliberately shared across tenants (it is what makes SPEC-008's budget viable). Uniform RLS would break that; the mitigation is that exemption is declared and reviewed, never incidental.
DL-003-05	No credential of any kind is ever collected.	Store B3 credentials to enable scraping-based sync	Would deliver true auto-sync, the product's biggest UX gap. Rejected because holding B3 credentials for many users creates a breach target with direct financial consequence, and LGPD exposure disproportionate to any feature. This is why PRD §4.1 chose file import.
DL-003-06	CSV export cells are neutralised against formula injection.	Export raw values	An exported transaction list can contain attacker-influenced text (asset names, institution strings from a parsed file). CSV formula injection turns a data export into code execution on the user's machine. Cheap to prevent, severe if missed.