The rule “you can only see your own company’s tickets” used to be checked in roughly forty routes. Now it is written once, as a policy in the database, and every query obeys it whether or not the code remembered to. This is why, and what it cost.
The system: a support desk serving many client companies. Staff need a board showing everything; each client needs a portal showing only their own tickets. The system I was replacing checked that in every route. It worked, in the sense that nobody had found the route where the check was missing. That is not the same as being correct, and with an AI coding tool generating new routes on request, the number of places to forget the check was only going to grow.
Move the rule below the code
Postgres has row-level security. You write the rule once, as a policy on the table, and the database applies it to every query, from every screen, every report and every live subscription.
That changes what the application layer is for. Server components query the database directly, because there is no longer a REST layer whose only job was to re-check what the policy already enforces. Writes go through server actions with validation at the boundary. The routes that survive are the ones with a genuine external caller: the auth callback, inbound webhooks, cron.
Three things followed that I did not expect to get for free.
Multi-row operations. Anything that has to move several rows atomically becomes a database function. With the function set to run as the caller, the policies apply inside it too, so a client physically cannot move another tenant’s row even by passing its id. Views need the same setting or they silently run as their owner, which is the kind of sentence you only write after being bitten.
Cross-cutting writes. The old system called a logging helper at every mutation. A helper silently loses entries when a new code path forgets it. A trigger cannot be forgotten. Activity logs moved to triggers.
Live updates. The realtime change feed is governed by the same policies. That is why the portal shipped with live updates in the first version rather than “later”: there was no second authorization layer to build.
What the database cannot do
This is not a claim that the app layer is empty.
Sanitization stays in the app. Stored cross-site scripting crosses exactly the tenant boundary the policies protect, so rich text is sanitized in the server action before insert, not trusted because the row is isolated.
A service-role connection bypasses row-level security entirely. One privileged query in a codebase that believes “the policies protect us” is a hole, not a backstop. Part of my security review is now simply checking which role each connection string uses.
The regression net
Policies are code. They can be widened by accident. Supabase grants full table-wide access to authenticated users by default, and a narrower column grant alongside that default is a no-op until the table-level grant is revoked. I found that out from a failing test, which is the only acceptable way to find it out.
So the port shipped with a policy test suite, written in the database’s own test framework, that proves each isolation rule and fails when one is loosened. New features inherit the isolation for free. The tests are what let me believe that sentence.
When to do this
Use it when the app is multi-tenant, runs on Postgres, and every table has a clean tenant key.
Skip it when authorization depends on data outside the database, or when the rules are so dynamic that a policy would just call back into application logic.
For the ticketing system it was the right call, and the forty routes became one place a new query cannot forget.