Build1 publisher3 min readPublished
Skipping SET nile.tenant_id returns every tenant's rows from a tenant-aware table
A tester on Nile's free tier made two tenants, then ran the same SELECT on a connection that never set nile.tenant_id and got every row back. Nile's docs describe that open read as deliberate, so the guarantee sits in application code.
The Engineer · Build desk
What happened
- A tester on Nile's free tier created two tenants, inserted two rows for tenant A and one for tenant B, and found that a query with the session set to each tenant returned only that tenant's rows.
- Writes behave differently, with an insert of a tenant B row under a tenant A session rejected as "Multiple tenant IDs specified in write query".
- Every connection runs through a proxy layer that does not forward EXPLAIN, so EXPLAIN ANALYZE on the test table returned "command tag EXPLAIN unhandled".
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- exposure Any code path that hands out a Nile connection without setting the tenant converts a per-tenant read into a full-table read, and nothing inside the database refuses it.
- decision Adopting Nile means nominating one owner for the SET statement, pool checkout or per-request middleware, and proving it fires before the first query.
- capability Misrouted writes announce themselves in an error you can log and alert on; a misrouted read succeeds, so catching it needs a test that deliberately skips the SET.
- constraint Tuning a slow tenant query has to happen somewhere other than a psql session, because the proxy rejects EXPLAIN and the physical relation named in error messages is invisible from the client.
The two paths do not fail the same way. On a write, Nile compares the tenant in the row against the tenant in the session and refuses a mismatch: `ERROR: Multiple tenant IDs specified in write query`, with the detail that writes to tenant-aware tables must specify exactly one tenant ID [9]. That error is good engineering. It names the rule it enforced, at the moment the bad write was attempted.
On a read there is no such comparison. With `nile.tenant_id` never set on a fresh connection, the same `SELECT tenant_id, title FROM todos` came back with all three rows [5]. Two belonged to tenant A and one to tenant B, so the unset session read the entire table [18]. Nile's docs say that is intended: a connection with no tenant context can read across all tenants, by design [6].
Nile is Postgres reengineered for multi-tenant B2B apps, where you mark a table tenant-aware and it isolates each tenant's rows beneath one connection string [1]. A tenant-aware table needs a `tenant_id uuid` column, and isolation comes from a session variable, `SET nile.tenant_id = '<uuid>'` [2][3]. The company raised an $11.6M seed from Benchmark in January 2024, with Eric Vishria taking a board seat, and was founded by Sriram Subramanian and Gwen Shapira [15].
Because the variable is the isolation, something in your code has to issue it before the first query on every checked-out connection. The tester, who says he has no affiliation with Nile and signed up for the free tier, framed the open question for pooled-connection apps as which layer is responsible for that call [16][17]. "What actually guarantees your app sets that context every time?" he wrote [8], and: "the isolation lives in the session rather than the table itself" [7].
How much of this transfers depends on the finding. The read behaviour is documented, so it should hold anywhere [6]. Two restrictions on the `tenants` table came out of trial and error on that free-tier account [12]: an `id` computed with `gen_random_uuid()` was rejected with a demand for a constant or a parameter reference [10], and a `DELETE` filtered on `name` was rejected because only an `id = tenant_id` condition is accepted [11].
Every connection goes through a proxy, and the proxy does not forward everything. `EXPLAIN ANALYZE SELECT * FROM todos` returned `ERROR: command tag EXPLAIN unhandled` [13]. A constraint violation in the same session named a physical relation, `todos_200149e`, that does not appear in `pg_class` or `pg_tables` from that connection [14]. The post does not explain what that relation is.
In my view the `SET` belongs at pool checkout rather than in each route handler, so one code path owns it and a handler that returns early cannot skip it. The statement is one line: `SET nile.tenant_id = '<uuid>'` [3].
What to watch
- Whether Nile documents the tenants-table rules the tester found by trial and error: a constant-or-parameter id, and DELETE filtered only on id.
- Whether the proxy starts forwarding EXPLAIN, which is the first move on a slow tenant query.
- Whether Nile ships a fail-closed option so a session with no tenant context reads nothing instead of everything.