A total that could include another customer's invoices

One customer’s receivables total quietly including another customer’s invoices is a data breach that looks like a slightly high number. Nobody sees a name or an invoice reference. The finance lead sees a figure that is higher than expected and starts hunting for a data-entry error in their own books.

That was the fault found in this system’s customer outstanding and aging figures, in review, before any customer could see it. It would have passed a permission check, a tenant-scoped session and the isolation tests on every table underneath. It is worth describing because the tests passing is the reason it would have lasted.

What was actually going on

Customer outstanding here is not a stored balance. It is a projection: invoiced totals from issued invoices, less allocated amounts from recorded payments, grouped by currency. Aging is the same figure bucketed by invoice age. Both are database views over the invoice and payment tables.

Every one of those tables has row-level security enabled, with a policy restricting rows to the tenant on the current session. The application connects as a restricted role. Isolation is enforced in the database, so that a forgotten filter in application code cannot leak anything.

A view does not inherit that. By default a view runs with the privileges of its owner, not its caller. The owner is the role that ran the migration, which owns the tables, which is the role row-level security does not apply to. The policies were all still there, evaluated against a role they were never meant to constrain. Left as it was, the view would have returned every tenant’s receivables to every caller.

An aggregate is the worst place for this. A leak through a list endpoint is loud, because rows turn up with names that visibly belong to someone else, and a tester notices within minutes. A leak through a total is silent, and reads as a reporting quirk for as long as you like. That is why derived read models deserve more suspicion than the tables under them, not less.

What we changed

PostgreSQL has one option for this, and it is not the default:

CREATE VIEW receivables_by_customer
WITH (security_invoker = true) AS

With it set, the view’s queries run as the caller and the table policies apply. Every derived read model in the system now carries it: customer outstanding, aging buckets, low stock, and later a branch-grained outstanding view.

The proof was a hostile query rather than a passing test. A session with a tenant that owns nothing reads the view and the assertion is zero rows. A plain view returns everybody’s.

The option can also be lost. Every migration here is run forward, backward and forward again, and a view is dropped and recreated by that cycle. A view that has lost the option looks normal, with the same name, columns and output for the tenant being tested. So the round-trip check reads the database catalogue after the cycle and asserts the option is still set on every view.

What it did not fix

Restoring tenant isolation does not give you data scope, and confusing the two was a separate mistake the project also made. Row-level security answers which tenant. It says nothing about which branch, or which records belong to the calling user.

A branch manager reading the corrected view still gets their whole tenant’s receivables. That is right for a tenant-wide dashboard tile and wrong for an exportable report. It was settled per read model. Outstanding got a second view grained by branch, because narrowing by each customer’s home branch would still have shown that customer’s invoices raised at every other branch: the customer belongs to one branch, the money does not. Re-deriving the figure in application code would have created a second source of truth for a number the business argues about.

For a user who may see only their own records, the outstanding report refuses with a clear error rather than approximating. A dashboard tile is a glance and a CSV is a copy, and they do not carry the same risk.

What to ask your own team or supplier

• Is customer separation enforced by the database, or only by every query remembering a filter?
• If by the database, does that cover views and reports, or only the base tables? Who checked, and how?
• Has anyone run a query as a tenant that owns nothing and confirmed every report returns zero rows?
• After a migration rolls back and forward, what confirms the security settings survived?
• Separate from which customer: who decides which branch or which user may see a given report?

Where this ends up

Where the database decides who may see which rows, every view over a protected table is either part of that boundary or a hole in it. Settling that before anything new reads those tables is part of the multi-customer design work in our custom software development.