Postgres Row Level Security for Multi-Tenant APIs
Two routes on the same server, same account, same data. One filters by account ID. The other forgets. Account B asks for account A’s family member and gets back a 200 with somebody else’s records in it. No error, no stack trace, nothing a code review catches at 5:00 on a Friday. I built that leak on purpose in my AllFamilyTree demo API, then closed it with Postgres row level security — a feature that moves the tenant rule out of my code and into the database.
Why a missing filter survives code review
The demo API is small. Two tables, person and relationship, plus a handful of routes. Every row belongs to exactly one account, so every query needs account ID in the where clause. Every single one. At this size you see all of it on one screen and hold the whole rule in your head.
Real APIs don’t stay that size. Somebody adds a route and forgets. An agent writes one and misses it. And here’s the part that actually matters: when you review a diff, you don’t read every line at the same intensity. Nobody does. The whole reason you reached for an agent was to remove the review bottleneck. So if the answer to “how do I stay safe” is “read harder,” you gave up the thing you came for.
How Postgres row level security closes the gap
The fix isn’t more discipline. It’s a database feature that has shipped with Postgres for years, and most developers outside the Supabase world have never touched it. Here’s the whole idea in one sentence: you attach a rule to a table, and Postgres checks that rule against every row of every query, no matter who wrote the query. If the rule says no, the row isn’t there as far as the query is concerned. Not an error. Absent.
The rule needs to know who’s asking, so two pieces have to line up. First, you tell the database connection which account this request belongs to. Then you write a policy that returns only rows whose account ID matches that value. After that, it stops mattering whether the query carries a filter. The database has one.
The three things that have to be right
I enabled RLS, wrote the policy, ran the migration — and the leak was still there. Each step below marks a spot where I’ve watched this setup quietly do nothing.
Force the policy, or the owner walks right past it
Prisma connects as the role that owns these tables, and Postgres exempts a table’s owner from that table’s own policies. The policy existed. It was active. It simply didn’t apply to that role. A second migration that forces row level security fixed it. One caveat: forcing does nothing to superusers, who bypass everything. Your app should never connect to Postgres as a superuser anyway. Here that habit becomes load-bearing.
Set the account on the connection, inside a transaction
After forcing it, both accounts returned empty arrays. That’s correct. Nobody had told the connection who was asking, so the policy matched nothing. It’s also the right failure direction: when the system breaks, it breaks toward showing you nothing.
Setting the account has to happen inside a transaction, with the local flag on set_config turned on. The value then reverts when the transaction commits. That reverting is the point. Your connection goes back into the pool, and if the account ID rode along with it, the next request to grab that connection inherits somebody else’s identity. Under that broken configuration every individual request still looks fine. You only catch it under concurrency, which is why I had Claude Code write a script that hammers both accounts in parallel and checks for cross-contamination.
Make the scoped client the only client
A helper that wraps queries still depends on me remembering to call it on every route. That’s the same shape of problem, moved down one level. A Prisma client extension removes it. One module creates the raw client and never lets it out; what it exports takes an account ID and returns a client where every operation already sits inside a scoped transaction. Code review turns into one question about one file: does anything in here hand out a raw client? Thirty seconds, and only when that file changes.
One more half to the policy. USING controls what a query can read; WITH CHECK controls what it can write. Leave WITH CHECK out and your isolation runs in one direction only, so a POST can still write a row carrying a foreign account ID.
The honest tradeoffs
Postgres evaluates policies per row, on every query. On a big scan that’s real overhead, and it can shift your query plans in ways that surprise you. Measure it against your data, not mine.
Background jobs, migrations, and admin tools legitimately need to read across accounts, so you’ll end up with a bypass role that sees everything. The problem doesn’t disappear. It relocates, and it gets much smaller. Debugging also gets harder, because a missing account context and a genuinely empty result look identical — both are empty arrays. Logging the claimed account ID next to the resolved one on every request solves that. When those two disagree, that’s your bug.
Key Takeaways
- A multi-tenant leak rarely looks like a bug, because the request succeeds and returns a 200 that just happens to contain the wrong person’s data.
- Enabling the policy is not enough, since Postgres exempts a table’s owner from that table’s own rules until you force the policy on.
- You must set the account ID inside a transaction with the local flag, or it rides along on a pooled connection and contaminates a later request.
- A Prisma client extension turns tenant scoping from a convention you remember into the only client your code can obtain.
- USING controls reads and WITH CHECK controls writes, so a policy missing WITH CHECK protects you in one direction only.
Conclusion
None of this makes an agent write better code. It takes one category of bad code and removes its consequences. When a filter goes missing, Postgres row level security answers with an empty result instead of somebody else’s family records. Pick the one table where a leak would end you, enable it, force it, then write a test that asks for the wrong account and expects nothing back.
