A view runs as its owner, which is how row-level security gets bypassed by a report
Customer outstanding in this system 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 how old each invoice is. Both are database views over the invoice and payment tables.
Every one of those base 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 rather than only in application code, precisely so that a forgotten filter cannot leak anything.
A view over those tables does not inherit that. By default a view executes with the privileges of the view’s owner, not the caller’s — and the owner is the role that ran the migration, which is the role that owns the tables, which is the role that row-level security does not apply to. The policies are still there. They are simply evaluated against a role they were never meant to constrain.
So the view would have returned every tenant’s receivables to every caller, through an endpoint that had a permission check, a tenant-scoped session, and passing isolation tests on all of its base tables.
The one clause that fixes it
PostgreSQL has an option for exactly this:
CREATE VIEW receivables_by_customer
WITH (security_invoker = true) AS
SELECT ...
With that set, the view’s underlying queries run as the caller, the base-table policies apply, and the view returns the caller’s tenant’s rows and nothing else. It is one clause. The problem is entirely that it is not the default, and that the failure mode without it is silent.
Every derived read model in the system carries it: customer outstanding, aging buckets, low stock, and later a branch-grained outstanding view added when reports needed narrowing. The check that it worked was not a code review. It was a query issued with a bogus tenant on the session, asserting that the view returned zero rows. A plain view returns everybody’s.
Why an aggregate is the worst place for this
A leak through a list endpoint is loud. Rows appear with names and identifiers that visibly belong to someone else, and a tester notices within minutes.
A leak through an aggregate is silent. The endpoint returns a total. The total is a number. Nobody looks at a receivables figure and thinks “that includes another company’s invoices” — they think the figure is higher than expected, and they go looking for a data-entry problem in their own records. The aging screen shows more in the ninety-plus bucket than anybody remembers. It reads as a reporting quirk for as long as you like.
That asymmetry is the reason derived read models deserve more suspicion than the tables under them, not less. The instinct runs the other way, because a view feels like a convenience over data that is already protected.
The check has to survive the migration cycle
There is a second-order problem that only shows up if you exercise your migrations properly.
Every migration in this project is verified by running it forward, backward and forward again, with the invariants queried afterwards rather than assumed. A view is dropped and recreated by that cycle. If the downgrade recreates it, or a later migration alters it, the option can be lost — and a view that has lost the option looks completely normal. It has the same name, the same columns, the same output for the tenant you are testing as.
So the round-trip check queries the database catalogue directly and asserts the option is still set on every view, after the cycle rather than before it. That is a strange-looking test until you have seen a view come back without it.
The general form of the rule: if a security property is expressed as an option rather than a structure, test for the option in the catalogue, because nothing about the object’s behaviour will tell you it is gone. A missing column raises an error. A missing constraint fails a write. A missing view option produces correct-looking output for the first tenant anybody tries.
What this does not fix
Getting the view right restores tenant isolation. It does not give you data scope, and confusing the two is a separate mistake this 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-scoped manager querying a correctly-invoker-scoped outstanding view gets their whole tenant’s receivables, which is right for a tenant-wide dashboard tile and wrong for a report they can export.
That distinction had to be settled per read model rather than in general. The eventual answer for outstanding was a second view grained by branch, because narrowing the customer-grained view 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 same figure in application code would have been the other option and would have created a second source of truth for a number the business argues about.
And for the narrowest scope — a user who may only see their own records — the outstanding report refuses outright with a clear error rather than approximating. A dashboard tile that is tenant-wide is a glance. A CSV the caller keeps is a copy. Those are not the same risk and they do not deserve the same fallback.
Rules
Assume every view bypasses row-level security until you have proved otherwise. Set the invoker option explicitly on every view over a protected table, and treat a view without it as a leak waiting for a caller.
Prove isolation with a hostile query, not a passing test. Set a tenant that owns nothing, run the view, assert zero rows. Passing tests on the base tables tell you nothing about the view.
Assert security options in the catalogue after a full migration round trip. Options are silently droppable in a way that structures are not.
Do not let row-level security stand in for data scope. One answers which tenant. The other answers which branch and which user, and it is application-layer work on every read model separately.
The uncomfortable part is that none of this was found by a scan or a test suite. It was found by someone asking what role a view actually runs as, which is a question you only ask if you already suspect the answer is not the obvious one.