Row-Level Security in a Multi-Tenant PostgreSQL Reporting Stack

RLS was never actually applied to the query path. Several mechanisms explain the leak:

1. Owner bypass. In PostgreSQL, a table owner bypasses row security unless ALTER TABLE ... FORCE ROW LEVEL SECURITY is set. svc_reporting owns the tables because migrations run as that role, so the policies on the base tables were bypassed for its sessions. SET LOCAL app.tenant_id had no effect because the policy was not enforced.
2. Materialized views. A materialized view stores a snapshot; querying it does not re-evaluate RLS on the underlying tables. mv_payroll_summary is owned by svc_reporting, contains all tenants' rows, and has no RLS of its own. The join therefore had an unfiltered side, so rows from one tenant could combine with rows from another.
3. View semantics. A normal view runs with the owner's privileges unless security_invoker = true (PostgreSQL 15+), and even then RLS depends on the owner not bypassing it. A security_barrier view prevents predicate pushdown that could leak rows through functions, but it does not add tenant filtering by itself.
4. Pooling and SET LOCAL. SET LOCAL is transaction-scoped. If the pool returns a session to the pool with a stale setting, or the query runs outside a transaction, the tenant context can be wrong or absent. Use set_config('app.tenant_id', ..., true) inside an explicit transaction, and reset on checkout.
5. Functions. SECURITY DEFINER functions run as the owner; if they are used in the reporting path, they can bypass RLS unless carefully written.

The fix is not to add another policy to the same role; it is to break the ownership/BYPASSRLS path.

Separate ownership from runtime.

• Schema owner: a NOLOGIN role or a tightly controlled migration role that owns tables and runs DDL. No application connects as it.
• Application role (svc_reporting): owns nothing; granted SELECT on tenant-scoped views and required DML on tables. FORCE ROW LEVEL SECURITY on every tenant table; policy USING (tenant_id = current_setting('app.tenant_id')::uuid) and WITH CHECK for writes. The reporting service should be read-only.
• Reporting views: tenant-filtered views with security_invoker = true, or dedicated tenant-keyed materialized views refreshed per tenant. Do not grant SELECT on the base tables or on the cross-tenant matview. If a matview is unavoidable, include tenant_id, index it, and filter in the view; refresh as a separate role.
• Support access: eliminate the shared support_ro login. Use named accounts with just-in-time elevation: a support role without BYPASSRLS by default, and a break-glass role that requires an approved ticket, is time-bound (for example 60 minutes), and is fully audited with session recording. Support should reproduce issues on anonymised or masked tenant copies, not production data.
• Column privileges: restrict last4 and expiry to roles that need them; mask in reporting; consider not storing expiry at all if it is not needed for payroll deduction reporting. Tokenisation covers the PAN, but last-4 plus expiry is still cardholder-adjacent data and personal data.
• Connection hygiene: set tenant context inside a transaction, reset on pool return, and reject queries with no tenant context.

Detection. Enable pgaudit for DML and DDL, not just DDL; log connections, disconnections, and statements from the reporting and support roles. Ship logs to a SIEM and alert on:

• queries returning rows with more than one tenant_id;
• use of the break-glass or any BYPASSRLS role;
• COPY TO, large result sets, or exports above a baseline (for example more than 5,000 rows by a support user);
• repeated failed tenant-context lookups;
• matview refreshes and schema changes.

Add a canary tenant with synthetic rows; if those rows appear in another tenant's report, there is a cross-tenant defect. Reconcile row counts per tenant after each matview refresh.

Backups. pg_dump files in object storage must use SSE-KMS with a customer-managed key and, preferably, client-side encryption before upload; restrict the bucket with least-privilege policies, block public access, enable versioning and MFA delete, and log access. Test restore into an isolated environment.

Response. For the 1,200-row incident, preserve query logs, identify the exact join and view version, notify the affected tenants per contract and applicable law, and add a regression test that joins the view with a base table for two tenants and asserts zero cross-tenant rows. Treat the 40,000-row export as a separate insider incident: revoke the shared account, recover the personal drive, review what was accessed, and assess notification obligations.