Converting a single-tenant app to multi-tenant. 40 tables, 3 developers, a customer who wants it in Q3.
Only our Node server talks to Postgres - no browser client, no mobile app hitting the database directly. Given that, RLS feels like a lot of policies to write and test for a guarantee I could get with a query wrapper that always adds where tenant_id = $1.
Tell me why I am wrong, or that I am not.
Third option nobody asked about: schema per tenant. Genuinely lovely at 20 tenants - no policy logic, dead simple isolation, easy per-tenant backup. Genuinely miserable at 400, because every migration is now 400 migrations and your connection pool has opinions. If you might have more than a hundred customers, do not start here.
Do both, but understand they protect against different failures.
The app filter protects against a bad query. RLS protects against a missing query - the raw pg call in a script, the analytics job, the intern's admin page, the ORM feature that generates SQL you did not write. In four years the only tenant leak I have personally seen came from a CSV export written in a hurry, and RLS would have caught it.
Cost is honest though: with set local app.tenant_id per transaction you now have to be sure every connection from the pool gets it, and a missed set local on a pooled connection is its own bug.
The pooler detail matters more than it sounds. With transaction-mode pooling set local is fine because it is scoped to the transaction, but a plain set leaks into whoever gets that connection next - which is the worst possible bug, because it is intermittent and it hands one tenant another tenant's context.
Practical migration warning: alter table ... enable row level security with no policies denies everything to everyone except the owner. If your app connects as the owner nothing appears to change, which is worse - you will think it works and it is doing nothing. If it connects as anything else, every query returns zero rows instantly.
Either way, do it one table at a time behind a migration you can reverse, with the read path tested before you move on.
Three devs and a deadline - whichever one you pick, write the leak test first. Two tenants, one loop over every endpoint, assert nothing crosses. That test is worth more than the choice.
@supa_okonkwo · 5mo ago · 3 replies
You are not wrong, and the deciding question is exactly the one you asked: who holds the connection?
If untrusted clients query the database directly, RLS is not optional - it is the only thing standing between a user and every row. If only your server connects, RLS is a second line of defence with a real cost: 40 policies, a test suite for them, and a class of confusing performance surprises.
With three people and a Q3 date I would do the app-layer wrapper properly - one function that every query goes through, tenant id from the request context, and a lint rule banning raw client access - then add RLS table by table afterwards, starting with the three tables where a leak would end the company.
Reply
Report
@layoverlurker · 5mo ago
"Start with the three tables where a leak would end the company" reframed this for me. It is not 40 policies, it is 3 policies now and 37 later or never.
Reply
Report
@edge_runtime_bo · 5mo ago
Just make sure "later" has a ticket with a date on it. Every team I know that said this shipped the three and then discovered two years later that the reporting service was reading a table nobody had gotten around to.
Reply
Report