Multi-tenant authorization without row-level security

Every multi-tenant B2B app has the same load-bearing question: how do you guarantee that organization A can never see organization B's data, and that within an organization a reviewer can't do an approver's job? Get one query's scoping wrong and you've shipped a data leak with a REST API in front of it.

The fashionable answer is Postgres row-level security — push the tenant filter into the database so no query can escape it. On Insur, a platform where insurance organizations build their own application forms and run submissions through a review pipeline, we went the other way and kept authorization in the application layer. This isn't a "RLS is bad" post — it's a "here's the real trade-off, and why the app layer won for us" post.

Two tiers, because there are two questions

Authorization on Insur answers two separate questions, so it has two tiers.

The first is organization-level: what can you do across the whole account? That's a small enum on the user:

enum OrgRole {
  admin        // runs the org: billing, members, every product
  form_builder // builds and publishes application forms
}

The second is per-product: each application form ("product") has its own review team, and your rights are scoped to the products you're actually assigned to. That's a separate enum, granted through a join row:

enum ProductRole {
  approver   // final approve / reject on submissions
  reviewer   // move submissions through the pipeline
  read_only  // look, don't touch
}
 
// One row per (user, product) — you only have a role on products you're on.
model ProductRoleAssignment {
  userId    String
  productId String
  role      ProductRole
  @@id([userId, productId])
}

A user carries one orgRole for the whole account and zero or more ProductRoleAssignment rows. That separation is the point: a form_builder can create forms all day without being able to approve a single application, and an approver on one product has no standing on the product next to it.

Collapsing two enums onto one ladder

The awkward part of two independent role systems is comparing them. Is an org form_builder allowed to do a reviewer's action on a submission? We answer it by mapping every role — org and product — onto a single numeric ladder, then comparing levels:

const ROLE_LEVEL: Record<string, number> = {
  admin: 4,
  approver: 3,
  reviewer: 2,
  form_builder: 2, // an org role that sits at reviewer level on submissions
  read_only: 1,
};
 
function resolveEffectiveRole(orgRole, assignmentRole) {
  if (orgRole === "admin") return "admin";      // org admin outranks everything
  if (assignmentRole) return assignmentRole;    // otherwise your product role
  if (orgRole === "form_builder") return "form_builder"; // baseline fallback
  return null;                                  // no role here at all
}

Two decisions in that small function carry a lot of weight. An org admin short-circuits to the top — they never need a per-product assignment. And form_builder is deliberately placed at reviewer level so the person who built a form can see its submissions, but can't approve them. Encoding a hierarchy like this in TypeScript is legible; expressing the same "take the max of your org role and your product role, with these two exceptions" logic as stacked SQL policies is where RLS starts to hurt.

Enforcing it at the edge

The comparison runs in a NestJS guard. Endpoints declare the minimum role they need with a decorator, and the guard resolves the caller's effective role for the product in the route and checks the level:

@Patch("submissions/:id/approve")
@RequireProductRole("approver")   // needs level >= 3 on this product
approve(@Param("id") id: string) { /* ... */ }

async canActivate(context: ExecutionContext) {
  const minRole = this.reflector.get(PRODUCT_ROLE_KEY, context.getHandler());
  if (!minRole) return true;
 
  const { user, params } = context.switchToHttp().getRequest();
  if (user.orgRole === "admin") return true;          // fast path
 
  const assignment = await this.prisma.productRoleAssignment.findUnique({
    where: { userId_productId: { userId: user.userId, productId: params.id } },
  });
  const effective = resolveEffectiveRole(user.orgRole, assignment?.role);
 
  if ((ROLE_LEVEL[effective] ?? 0) >= MIN_LEVEL[minRole]) return true;
  throw new ForbiddenException("Insufficient product role");
}

Every denial is logged to Sentry with the user, the product, the required role, and the URL — so a misconfigured assignment or someone probing endpoints shows up as a signal, not a silent 403 buried in a log file.

The re-check that catches what the guard can't

Here's the part that does the real tenant-isolation work, and it's not glamorous. The guard proves you have the role. It does not prove the row you're touching belongs to your org. So the service layer never trusts an id off the wire. Every data method takes orgId as a required argument and re-asserts ownership before it does anything:

async archiveProduct(id: string, orgId: string) {
  const product = await this.prisma.product.findUnique({ where: { id } });
  if (product?.orgId !== orgId) throw new ForbiddenException();
  // ...only now do we touch it
}

orgId isn't optional and isn't inferred — it's threaded down from the authenticated request into every query, and list queries filter on it directly (where: { orgId, productId: { in: [...] } }). This is belt and suspenders: the guard handles "are you allowed to do this kind of action," the service handles "and is this specific record actually yours." Cross-tenant access requires both to fail at once, which is a much narrower target than a single missed WHERE.

Why not RLS

The honest case for RLS is real: it makes tenant scoping structural. Forget the filter and the database refuses to return the row. What we're doing instead — a required orgId argument and an ownership check — is a discipline, and disciplines can be skipped. So why did we take that trade?

• Prisma bypasses RLS anyway. Our API talks to Postgres through Prisma over a pooled connection as a privileged role. RLS keys off the connection's role and JWT claims; a pooled service connection sails straight past it. Making RLS actually enforce would mean setting per-request Postgres session claims on every query — real work that buys us a guarantee we can already get in the app.
• The hierarchy is clearer in code. "Max of org role and product role, with an admin short-circuit and a form_builder floor" is four readable lines of TypeScript. As a set of USING policies it's a maze.
• It's testable without a database. The guard and the resolver are unit tests that run in milliseconds. We back them with integration tests that seed two orgs and assert one can't read the other — but the fast feedback lives in plain functions.

We buy the guarantee we'd get from RLS with a required argument, an ownership re-check, and tests — and we skip the per-request session-claim plumbing RLS would need to work through Prisma at all.

Where this stops working

This holds because of one property of our architecture: a single API owns all database access. Every read and write goes through services that thread orgId. The moment that stops being true — a second service hitting Postgres directly, an analytics tool with a raw connection, hand-written SQL spreading across the team — the app-layer boundary develops gaps, and RLS's "the database itself says no" becomes worth its friction. If we ever open the database to clients we don't control, we'll add RLS underneath this, not instead of it.

Until then, authorization on Insur is two enums, one guard, and one defensive re-check — small enough to hold in your head, fast enough to unit-test, and enforced in the one layer that currently touches the data. For a multi-tenant B2B app where one wrong query is a breach, that's not a compromise. It's the boundary in the right place.