Test Tenant Isolation End to End
Attempt cross-tenant reads and writes with another tenant's ids, assert 404, and prove row-level security fails closed without context.
Task
Verify that no endpoint or query can read or modify another tenant's rows, even with a valid object id.
When to use this
A report endpoint accepts an invoice id and queries by id only. A user from another tenant guesses an id and receives someone else's invoice, because the tenant filter was applied in the UI, not in SQL.
Prerequisites
- A multi-tenant schema with a tenant_id column on every owned table
- Two tenants with separate users in the test database
- PostgreSQL 13 or newer if you add row-level security
Steps
1. Create two tenants and one object owned by the first.
const acme = await createTenant('acme');
const globex = await createTenant('globex');
const invoice = await createInvoice({ tenantId: acme.id, totalCents: 4200 });
Expected output: The invoice belongs to acme, globex has no invoices, and each user carries its own tenant claim.
2. Attempt cross-tenant reads and writes through every path that accepts the id.
for (const path of [`/api/invoices/${invoice.id}`, `/api/invoices/${invoice.id}/pdf`]) {
const res = await request(path, { as: 'globex-user' });
assert.equal(res.status, 404, path);
}
const patch = await request(`/api/invoices/${invoice.id}`, {
method: 'PATCH', as: 'globex-user', body: { totalCents: 1 },
});
assert.equal(patch.status, 404);
Expected output: Globex receives 404 on detail, export, and update, and the invoice total remains 4200.
3. Add row-level security as defense in depth, forcing it even for the table owner.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.tenant_id', true)::uuid);
Expected output: A direct SELECT as the application role returns only rows matching app.tenant_id.
4. Set the tenant for each transaction and never for the session.
await client.query('BEGIN');
await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
const { rows } = await client.query('SELECT * FROM invoices');
await client.query('COMMIT');
Expected output: The setting is scoped to the transaction; the next checkout of the same connection sees no tenant.
5. Prove the system fails closed when the context is missing.
await client.query('ROLLBACK');
await assert.rejects(
pool.query('INSERT INTO invoices (tenant_id, total_cents) VALUES ($1, 1)', [acme.id]),
/new row violates row-level security policy/,
);
Expected output: Without a tenant context, reads return zero rows and writes raise a policy violation instead of succeeding.
Troubleshooting
Problem
RLS policies seem to have no effect
Solution
The application connects as the table owner, which bypasses RLS unless FORCE ROW LEVEL SECURITY is set. Use a non-owner role in production.
Problem
Queries silently return empty results
Solution
The tenant context was never set on that transaction. Fail loudly at the repository layer rather than returning an empty list.
Problem
Tenant context leaks between pooled requests
Solution
Session-level SET persists on the connection. Always use SET LOCAL or set_config(..., true) inside the transaction.
Problem
Queries became slower after enabling RLS
Solution
The policy adds a predicate. Index tenant_id as the leading column and confirm the plan still uses the index.
Limitations
- RLS applies per table; shared reference tables and cross-tenant admin views need separate policies.
- Every transaction must set the context; one forgotten path either fails closed or leaks, depending on configuration.
- Hard-to-guess ids are not an authorization control and do not replace tenant filters.
Tenant isolation is a query property
Most multi-tenant leaks are not exotic. A new endpoint queries SELECT * FROM invoices WHERE id = $1, the id comes from the URL, and the tenant filter lives in the UI or in a middleware that only some routes use. The fix has to be structural: every query that touches tenant-owned data must be constrained by the tenant, and preferably the database should enforce it even if the query forgets.
Test from the outside in
Start with HTTP: a user from tenant B requests tenant A’s object by id on every route that accepts one, including exports and nested resources. Assert 404 rather than 403 if that is your disclosure policy, and verify the object was not modified. This catches handler-level mistakes regardless of how the query is built.
assert.equal((await request(`/api/invoices/${invoice.id}`, { as: 'globex-user' })).status, 404);Add RLS as the second layer
Row-level security moves the guarantee into PostgreSQL. Enable it, force it for the owner, and write a policy that compares tenant_id with a transaction-local setting. The application role must not be the table owner, and the setting must be applied at the start of every transaction. With both layers, a forgotten WHERE clause returns zero rows instead of someone else’s data.
CREATE POLICY tenant_isolation ON invoices
USING (tenant_id = current_setting('app.tenant_id', true)::uuid);Fail closed on missing context
The dangerous configuration is a policy that treats a missing setting as “allow all”. current_setting('app.tenant_id', true) returns null when unset, and tenant_id = null matches nothing, which is the safe direction for reads. For writes, the policy should also include a WITH CHECK, so a row cannot be inserted into another tenant. Test both: no context returns no rows, and an insert raises a policy violation.
Watch the operational cost
RLS adds a predicate to every query and can change plans. Index tenant_id as the leading column of composite indexes, and re-check the plans for the largest tables after enabling it. The performance cost is usually small compared with the risk of a cross-tenant leak, but it should be measured rather than assumed.