DEV Community

Alex Georgiev
Alex Georgiev

Posted on AI-assisted

Nile isolates Postgres tenants with a tenant_id column and one session variable

#postgres #database #security #saas

Disclosure: I have no affiliation with Nile. Nobody asked me to write this and nobody paid for it. I signed up for their free tier like anyone else would. I'm on my way to test products and share my findings with the community just in case someone wondered how will this perform and etc.

I created two tenants, put a row in each, then ran the same query without telling the connection which tenant it was. It returned both rows. Turns out that's intentional, documented behaviour, not a bug I'd stumbled onto — but it raised a question worth asking if you're building on this: what actually guarantees your app sets that context every time?

What Nile is

Nile is Postgres reengineered for multi-tenant B2B apps: instead of managing one database per customer, you mark a table as tenant-aware and Nile handles isolating each tenant's rows underneath a single connection string. It's an $11.6M seed company (Benchmark, January 2024, board seat taken by Eric Vishria), founded by Sriram Subramanian and Gwen Shapira, and it's had zero dev.to coverage despite that.

A tenant-aware table needs one thing: a tenant_id uuid column.

CREATE TABLE todos (
  id uuid DEFAULT gen_random_uuid(),
  tenant_id uuid,
  title varchar(256),
  complete boolean,
  PRIMARY KEY (tenant_id, id)
);
Enter fullscreen mode Exit fullscreen mode

Isolation is a session variable:

SET nile.tenant_id = '11111111-1111-1111-1111-111111111111';
Enter fullscreen mode Exit fullscreen mode

Where the isolation actually lives

I created two tenants, set the session to tenant A, inserted two rows, switched to tenant B, inserted one row. Querying as each tenant worked exactly as advertised:

=== query AS tenant A ===
              tenant_id               |    title
--------------------------------------+-------------
 11111111-1111-1111-1111-111111111111 | Acme task 1
 11111111-1111-1111-1111-111111111111 | Acme task 2

=== query AS tenant B ===
              tenant_id               |     title
--------------------------------------+---------------
 22222222-2222-2222-2222-222222222222 | Widget task 1
Enter fullscreen mode Exit fullscreen mode

Then I ran the identical SELECT tenant_id, title FROM todos; on a fresh connection, with nile.tenant_id never set at all:

              tenant_id               |     title
--------------------------------------+---------------
 11111111-1111-1111-1111-111111111111 | Acme task 1
 11111111-1111-1111-1111-111111111111 | Acme task 2
 22222222-2222-2222-2222-222222222222 | Widget task 1
Enter fullscreen mode Exit fullscreen mode

All three rows, both tenants. Nile's own docs confirm this is deliberate: without a tenant context set, a connection can read across all tenants, by design, presumably so admin and migration connections aren't locked out. So the isolation lives in the session rather than the table itself. The open question, for anyone building a pooled-connection app on top of this, is which layer is responsible for making sure SET nile.tenant_id gets called on every request. Worth checking early rather than assuming.

What happens if a write's tenant doesn't match the session

Naturally I tried inserting a row for tenant B while the session was set to tenant A:

SET nile.tenant_id = '11111111-1111-1111-1111-111111111111';
INSERT INTO todos (tenant_id, title, complete)
VALUES ('22222222-2222-2222-2222-222222222222', 'Smuggled into Widget', false);
Enter fullscreen mode Exit fullscreen mode 
ERROR:  Multiple tenant IDs specified in write query
DETAIL:  Writes to tenant-aware tables must specify exactly one tenant ID
Enter fullscreen mode Exit fullscreen mode

Rejected outright, with a clear error naming the problem. Good sign — the write path actively checks the session's tenant against the row's tenant.

What it refuses

The tenants table itself is tenant-aware on its own id column, and that column has a rule I didn't expect: it has to be a literal or a bind parameter, not an expression.

INSERT INTO tenants (id, name) VALUES (gen_random_uuid(), 'Acme Corp');
Enter fullscreen mode Exit fullscreen mode 
ERROR:  cannot determine tenant ID. Tenant ID must be a constant or a parameter reference (i.e: $1)
Enter fullscreen mode Exit fullscreen mode

A literal UUID works fine; you just can't compute it inline. And deleting from tenants only accepts one shape of WHERE clause:

DELETE FROM tenants WHERE name = 'Acme Corp';
Enter fullscreen mode Exit fullscreen mode 
ERROR:  DELETE operations on the tenants table have to provide a 'id'=tenant_id condition. Further conditions are not supported.
Enter fullscreen mode Exit fullscreen mode

WHERE id = '...' is the only accepted filter. I hit both by trial and error, not from anything in the docs I'd read going in.

EXPLAIN doesn't pass through

Every connection to Nile goes through a proxy layer, and I found one command it doesn't forward:

EXPLAIN ANALYZE SELECT * FROM todos;
Enter fullscreen mode Exit fullscreen mode 
ERROR:  command tag EXPLAIN unhandled
Enter fullscreen mode Exit fullscreen mode

That rules out the usual first move for debugging a slow query. It also left me curious about something an earlier error had already hinted at: a constraint-violation message named a physical relation todos_200149e, which doesn't show up in pg_class or pg_tables from this connection. So todos isn't stored as one plain table underneath — I just couldn't see the plan or the structure to say more than that.

What you get for it

The one thing that's unambiguously generous: \dx on a fresh free-tier database lists 35 extensions already installed beyond the standard plpgsql every Postgres ships with, including vector and vectorscale (DiskANN) for embeddings, postgis for geospatial, pg_trgm and pg_bigm for text search, and less common ones like h3 and financial. On a stock RDS Postgres instance you'd be enabling most of these one at a time and, for some, not have the option at all.

What I got wrong on the way

My first attempt at inserting a row assumed SET nile.tenant_id would populate the column for me:

SET nile.tenant_id = '11111111-1111-1111-1111-111111111111';
INSERT INTO todos (title, complete) VALUES ('Acme task 1', false);
Enter fullscreen mode Exit fullscreen mode 
ERROR:  null value in column "tenant_id" of relation "todos_200149e" violates not-null constraint
Enter fullscreen mode Exit fullscreen mode

It doesn't. The session variable scopes what you can read and enforces what you're allowed to write, but you still have to supply tenant_id explicitly on every insert. That error message is also where the hidden relation name first showed up.

Run it yourself

Free tier, no credit card: sign up at console.thenile.dev, create a database, and grab the connection string from Settings → Connection. A tenants table already exists in every new Nile database, so the INSERTs below work immediately.

psql "$NILE_CONNECTION_STRING" <<'SQL'
CREATE TABLE todos (
  id uuid DEFAULT gen_random_uuid(),
  tenant_id uuid,
  title varchar(256),
  complete boolean,
  PRIMARY KEY (tenant_id, id)
);

INSERT INTO tenants (id, name) VALUES ('11111111-1111-1111-1111-111111111111', 'Acme Corp');
INSERT INTO tenants (id, name) VALUES ('22222222-2222-2222-2222-222222222222', 'Widget LLC');

SET nile.tenant_id = '11111111-1111-1111-1111-111111111111';
INSERT INTO todos (tenant_id, title, complete) VALUES ('11111111-1111-1111-1111-111111111111', 'Acme task 1', false);

SET nile.tenant_id = '22222222-2222-2222-2222-222222222222';
INSERT INTO todos (tenant_id, title, complete) VALUES ('22222222-2222-2222-2222-222222222222', 'Widget task 1', false);
SQL

# now compare a scoped read against an unscoped one
psql "$NILE_CONNECTION_STRING" -c "SET nile.tenant_id = '11111111-1111-1111-1111-111111111111'; SELECT tenant_id, title FROM todos;"
psql "$NILE_CONNECTION_STRING" -c "SELECT tenant_id, title FROM todos;"
Enter fullscreen mode Exit fullscreen mode

If you're evaluating Nile for a real app, the question worth answering early is which layer of your stack is responsible for calling SET nile.tenant_id on every pooled connection before it touches tenant data. That's a question about your own architecture as much as theirs.

Top comments (1)

Collapse
 
jo-do profile image
Jo Do •

"Querying a tenant-aware table with no nile.tenant_id set returns every tenant's rows" is the sentence that decides whether I trust the design. That's fail-open isolation: the guard's absence state is full visibility. The safe default for a tenancy boundary is the empty set, not the universe - if the session variable isn't set, the answer should be zero rows and a loud error, because "forgot to set context" is the MOST common production bug, not an edge case. Every multi-tenant system I've audited eventually hits the code path that runs before the middleware that sets the tenant: a migration, a cron, a connection pool warmup, an admin script. The test that matters is the one you wrote: unset the variable and assert the blast radius is zero. Good on you for testing the failure direction; most tenancy reviews only test that tenants can see their own data.