What row-level security won't protect you from

Postgres row-level security is one of my favourite features: you attach a policy to a table and the database itself refuses to return rows that do not belong to the current tenant, no matter how buggy the application above it is. Defence in depth, enforced where the data lives. I still recommend it. I also spent a while learning exactly how much it does not do on its own.

It does nothing against a superuser#

The first version had RLS policies on every table, FORCE ROW LEVEL SECURITY switched on, the works. Cross-tenant tests still leaked: every tenant could read every other tenant’s rows. For a SaaS product that is the worst line in any postmortem: one paying customer able to read another customer’s data. The policies were fine. The connection was not.

The local Postgres container creates its default user as a superuser, and superusers bypass RLS entirely. Worse, FORCE RLS, the thing that sounds like it closes exactly this hole, only constrains the table owner, not superusers. So it gave a false sense of safety. It had not shown up against the managed cloud database because that provider does not hand you a superuser at all.

The fix is architectural: two roles. A privileged migration and admin role that runs schema changes, seeds, and teardown, and a separate application role created NOSUPERUSER NOBYPASSRLS that the app and the tests actually connect through.

RLS is only as strong as the role that connects through it. If your tests run as a superuser, they are testing nothing. Test as the exact low-privilege role production uses.

Session context breaks under transaction pooling#

The way you tell RLS “who the current tenant is” is usually a session setting: open a transaction, set a config value to the tenant id, and the policy reads it back. Transaction-local, tidy.

That quietly depends on every statement in the transaction hitting the same physical connection. Put a transaction-mode connection pooler in front (a very common production setup) and it can route different statements to different backends. The session value vanishes mid-transaction, and RLS either errors or, worse, silently returns nothing. The tenant context evaporated and took your data access with it.

So the pooling mode became a hard, documented constraint, not an ops detail: session-scoped state (config settings, SET LOCAL, advisory locks, prepared statements) is fundamentally incompatible with transaction pooling. If your security model rides on a session variable, you have to pin the connection, or you have built an isolation bug that only appears under load.

The database trusted the request body#

Two application-level holes sat above all this. The auth library let custom user fields be populated from the sign-up request, and tenantId and role were among them. Which means a caller could send their own tenant id to join someone else’s tenant, or role: admin to escalate, right at account creation. The fix was to mark those fields server-assigned only, set from an invite token or a seed, never from the payload, and to disable open sign-up entirely.

The lesson is the one that keeps coming back: security-sensitive columns, tenant, role, entitlement, must never be writable from the request body. Every “additional field” a framework offers needs auditing for whether it is client-settable, because the default is usually permissive.

Letting untrusted callers write past RLS, safely#

The last one was a genuine design question: a public, unauthenticated intake endpoint has no session and therefore no tenant context, but it still needs to insert a row scoped to the right tenant. The wrong answer is to give it a broad privileged connection that bypasses RLS. The right one is a single, tightly-scoped SECURITY DEFINER function that resolves the tenant by slug and does exactly that one insert with elevated rights, and nothing else, with hard input validation and a bot check in front of it.

A definer function is a controlled hole in the wall, one shape, one purpose, easy to review. A privileged client is a missing wall.

And least privilege has a tail#

One honest footnote: doing this correctly is not free. The moment the app role lost its god-mode, everything that had quietly relied on god-mode broke. Test teardown that truncated tables failed, because the low-privilege role cannot truncate and, under RLS, can only see its own rows. Seeding, admin scripts, cleanup: all of it had to move to the explicit admin connection.

That is the actual cost of least privilege, and it is worth paying. When enforcing the real permission model breaks your teardown, that is not the model being awkward. That is it showing you every place you were secretly running as a god.