Postgres row-level security for a multi-tenant SaaS: what I set up, and the bug that slipped past it anyway
I run a small multi-tenant SaaS on Postgres + Prisma. Every tenant-scoped table has an orgId, and for months my isolation story was the usual one: every query goes through a layer that adds where orgId = currentOrg. That works right up until the day someone writes a query that doesn't.
So I moved the boundary into the database. Here is what that actually looked like, including the part where a bug got through anyway.
1. The setup
A helper function reads a per-connection setting:
CREATE OR REPLACE FUNCTION current_org_id() RETURNS text
LANGUAGE sql STABLE AS $$
SELECT current_setting('app.current_org_id', true)
$$;
RLS is enabled on every tenant-scoped table, with policies keyed on current_org_id(). Before each query the app runs SELECT set_config('app.current_org_id', $1, true) on the same connection, with the org resolved from the session.
The property that matters: when the setting is unset, current_org_id() is NULL and every policy denies. Default-deny, not default-allow. Forget the tenant in a query and you get zero rows, not everyone's rows.
2. Check that your runtime role actually has RLS enforced against it
This is the step that nearly invalidated the whole exercise for me. RLS is not enforced against a superuser or the table owner, and different hosts connect as different roles. I had a note in my own docs claiming my provider's connection bypassed RLS. It was wrong - but I only found that out by testing: connect as the runtime role, leave the org unset, run a select, count the rows. If it isn't zero, your policies are decoration.
Add FORCE ROW LEVEL SECURITY if your app role could ever own the tables.
3. USING is not enough, you also need WITH CHECK
A policy with only a USING clause controls which rows you can see and modify. It does not stop you writing a row into another tenant. Every write policy needs WITH CHECK too, or a user can insert or update rows out of their own org and then - correctly, and invisibly - never see them again.
4. Background jobs are the part everyone forgets
Cron jobs and webhook handlers run with no user session, so they have no org context, so under default-deny they see nothing. Silently. Not an error - just zero rows and a job that reports success. The fix is to make the context explicit: loop per org and set it each time, or give the job a deliberately scoped path. What you should not do is hand the job a bypass role, because that is now the widest hole in the system and it runs unattended every hour.
5. And then the bug that had nothing to do with any of it
RLS protects rows against queries. It does not protect the value you key those policies on. My auth library exposes a user-update endpoint, and I had orgId and role as additional user fields - user-writable by default. So a user could update their own record to promote their own role, or reassign themselves into another tenant. RLS would then faithfully enforce the new, attacker-chosen org.
The fix is boring: mark those fields input:false so the endpoint refuses them, plus a before-update guard as a second layer. The lesson is less boring. A database-level tenant boundary is only as strong as the integrity of the column it keys on. If any request path can write orgId or role, you don't have isolation, you have a suggestion.
Checklist
- orgId on every tenant-scoped table
- RLS enabled on all of them, denying by default when the context is unset
- Verify as the runtime role, not your psql superuser
- WITH CHECK on every write policy, not just USING
- FORCE ROW LEVEL SECURITY where the app role could own tables
- Explicit per-org context for cron jobs and webhooks, no bypass role
- orgId and role not writable from any user-facing endpoint
- A test that sets no context and asserts zero rows
Happy to answer questions on any of it.
(This came out of building Estavo, a finance and renewal-tracking app for self-managing landlords - which I am now selling. Details on my profile.)
The RLS bug is more than a technical edge case because it changes the trust boundary you can honestly promise customers. After finding the writable orgId/role path, did it change how you position Estavo’s security or what customers need to see before trusting it with financial data?
It changed what I think is worth showing more than what I claim.
Before, the honest position was "I've been careful". That's unfalsifiable, and every founder says it. What I can hand someone now is the review itself: the finding written up with a severity, the exploit path, the fix, and the date it was verified in production. A fixed critical with a paper trail is more convincing than a clean record, because a clean record usually just means nobody looked.
Two things I'd want a customer or a buyer to see before trusting it with financial data. First, the isolation test - set no tenant context, run a select, get zero rows. That's a property you can demonstrate in thirty seconds rather than a claim you have to be believed on. Second, which code paths can write the field that isolation keys on, because that's exactly the bug you're pointing at and it is completely invisible from the outside.
The uncomfortable part is that "we found and fixed a privilege escalation" is the right thing to publish and also the thing a nervous reader hears as "this had a privilege escalation". I've decided to say it plainly anyway, on the theory that anyone sophisticated enough to be worried is sophisticated enough to know the alternative is that it's still in there and nobody has looked.