Scoping every query to the tenant

Multi-tenant apps have one rule that matters more than the rest: a request from one organisation must never see another’s data. This bug broke that rule in the most common way there is. An invoice lookup trusted the ID in the URL and never checked who was asking.

What went wrong#

The handler loaded an invoice by primary key. Authentication was in place, so every caller was a real, signed-in user. The query just never asked whether that user belonged to the organisation that owned the invoice.

export async function getInvoice(req: Request, ctx: Context) {
  const id = ctx.params.id;
  const invoice = await db.invoice.findUnique({ where: { id } });
  if (!invoice) return notFound();
  return Response.json(invoice);
}

It passed review because it looks complete. There’s a not-found branch, the route sits behind the auth middleware, and the ORM call is idiomatic. What’s missing is a single field.

The fix#

Scope the lookup to the caller’s organisation, and return the same 404 whether the invoice doesn’t exist or belongs to someone else. Different responses would let an outsider tell which IDs are real.

export async function getInvoice(req: Request, ctx: Context) {
  const id = ctx.params.id;
  const invoice = await db.invoice.findUnique({ where: { id } });
  const invoice = await db.invoice.findFirst({ 
    where: { id, orgId: ctx.session.orgId }, 
  });
  if (!invoice) return notFound();
  return Response.json(invoice);
}

Try the copy button on that block. You get the code as it reads after the change, without the removed line.

Defence in depth#

One scoped query fixes one route. Two more layers keep the next route from making the same mistake.

Row-level security in Postgres#

Even if application code forgets the filter, the database refuses to return other tenants’ rows:

alter table invoice enable row level security;

create policy tenant_isolation on invoice
  using (org_id = current_setting('app.org_id')::uuid);

The app sets app.org_id at the start of every transaction, from the verified session and never from the request body.

A test that fails loudly#

test('an invoice from another org is indistinguishable from a missing one', async () => {
  const a = await seedOrg();
  const b = await seedOrg();
  const invoice = await seedInvoice(b);

  const res = await asUser(a.owner).get(`/api/invoices/${invoice.id}`);
  expect(res.status).toBe(404);
});

Takeaways#

The client patched within six weeks and asked for a retest before disclosure. Thanks to their team for the quick turnaround.¹