Build1 publisher3 min readPublished
Misconfigured Postgres row-level security returns zero rows or every tenant's rows without an error
Postgres row-level security on a tenant_id column is the right default for most B2B SaaS apps, a dev.to guide argues, at two statements and a policy per table. Its warning is that a policy can look secure in a migration diff and still let one tenant reach another's data.
The Engineer · Build desk
What happened
- Without FORCE ROW LEVEL SECURITY, a table's owner bypasses every policy, so an app connecting as the role that created the tables gets nothing from ENABLE alone.
- A policy with USING but no WITH CHECK clause filters what a tenant reads while still letting it insert rows labelled with another tenant's tenant_id.
- When current_setting is called with its second argument true and the tenant is unset, it returns NULL and every query comes back empty without an error.
- An app role with BYPASSRLS, which every superuser has, or a new table nobody enabled RLS on also exposes all tenants; the author calls the new table the common case.
- The guide's CI query fails the build when any ordinary table in the public schema is missing either the RLS-enabled or the RLS-forced flag.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- decision Teams whose application connects as the table-owning role have to add FORCE to every table, or give the app a separate non-owner role, before any policy applies to their queries.
- constraint The CI check reads only two table flags, so an app role with BYPASSRLS or a policy without WITH CHECK still passes the build.
- exposure Under PgBouncer or Supavisor transaction pooling, a tenant setting scoped wider than its transaction can bleed into another tenant's request on the same server connection.
The guide's case against a bare tenant_id filter is about queries nobody has written yet. One endpoint that builds a query without the tenant predicate is enough to leak [5]. The post's examples are a new report, an admin tool and a background job reusing a helper [5]. The author wrote that code review catches this most of the time, and that this is exactly the problem, because most of the time is not an isolation guarantee [6]. With RLS on every table, the database enforces isolation even when someone forgets a WHERE clause, and the app keeps one schema and one migration path [1].
The heavier models suit narrower cases. Schema-per-tenant pays off only when a few large tenants have genuinely different needs, such as per-tenant restores or data residency, according to the post [2]. Database-per-tenant is an operations decision, for tenants that must be backed up, moved or deleted independently [3].
The application code is the best-engineered part of the post. Its node-postgres wrapper opens a transaction and sets the tenant with set_config($1, $2, true) [10]. The post equates that call to SET LOCAL, scoped to the one transaction, and warns against a session-level SET [10]. It uses the function because SET LOCAL cannot take a bind parameter [11]. String-concatenating a tenant id into a SET statement is, the author wrote, "an injection point in the one place you least want one" [11].
The empty-result failure usually shows up in code that got a connection outside the wrapper, such as a health check, a migration script or a queue worker [13]. The page renders with no data and no stack trace [13]. Of the two directions, this is the one that at least looks broken; the other looks fine [4]. The post's fix is to drop the true argument during development, so a missing setting raises an error instead of returning an empty list [14].
The CI query covers two of the three causes the post gives for the all-rows failure: a table owner without FORCE, and a new table without RLS [2]. Genuinely shared tables such as plans, feature flags and country codes go on an explicit allowlist [16]. I would pair it with a behavioural test that connects as the application role. It sets one tenant, selects another tenant's rows and expects none. It then inserts a row tagged with the other tenant and expects a rejection. The same suite should assert that the role lacks BYPASSRLS.
The post does not include incident counts. It presents its list as "the failure modes I keep running into, in the order they bite," and its view that the unprotected new table is the common cause comes from that experience [18][15].
What to watch
- Whether the guide's CI check gains assertions on the app role's BYPASSRLS attribute and on WITH CHECK clauses, closing the gaps its two-flag query leaves.
- Incident data from teams running tenant_id plus RLS would test the author's claim that a new table without RLS is the most common leak.