Implementing Postgres Row-Level Security in Next.js: The Drizzle Multi-Tenant Pattern

The first time we turned on Row-Level Security, nothing happened. Policies were in place, ENABLE ROW LEVEL SECURITY had run, and every query still returned every tenant's rows. Took a minute to remember: we were connected as postgres, the superuser, and superusers ignore RLS entirely. That one gotcha basically shaped the rest of this setup.

The problem we were actually trying to solve is boring and everyone's had it: a route handler forgets WHERE organization_id = ?, code review doesn't catch it because the query still "works," and Tenant A quietly gets to see Tenant B's data. Nobody notices until someone does.

Our answer was to stop trusting application code to remember the filter and push isolation into Postgres itself with Row-Level Security. The pitch is simple: if a query forgets tenant scoping, it should get zero rows back, not someone else's data. Here's the setup we landed on — two DB roles, a SET LOCAL session variable, a Next.js wrapper so route handlers don't have to think about any of this — plus the four things that actually bit us building it.

Two roles, two connections

Back to that first surprise: superusers and table owners bypass RLS by default. The docs say it plainly — superuser or BYPASSRLS, policies don't apply, full stop. Table owners get the same pass unless you explicitly add FORCE ROW LEVEL SECURITY. If your app connects as postgres, which is what basically every local Postgres setup defaults to, your carefully written policies are just decoration.

So we run two roles now:

Role	RLS	Used by
postgres (superuser)	Bypassed	Auth library, admin routes, migrations
app_user (non-superuser)	Enforced	All tenant-scoped queries

Each gets its own pool, wrapped as two Drizzle instances:

// db.ts
export function getDbAdmin(): PostgresJsDatabase<Schema> {
  if (!_dbAdmin) {
    const client = postgres({
      ...adminConnectionConfig, // DATABASE_URL, superuser
      max: 2,
      connection: { application_name: "app-admin" },
    });
    _dbAdmin = drizzle(client, { schema });
  }
  return _dbAdmin;
}

export function getDb(): PostgresJsDatabase<Schema> {
  if (!_db) {
    const client = postgres({
      ...appConnectionConfig, // APP_DATABASE_URL, app_user
      max: 4,
      connection: { application_name: "app-tenant" },
    });
    _db = drizzle(client, { schema });
  }
  return _db;
}

Worth flagging two things here. First, the lazy init isn't stylistic — Next.js runs module scope at build time, and if you open a connection pool at import time, next build needs a live database to succeed. That bit us once in CI. Wrapping construction in a getter defers the actual connection until something queries it.

We also wrap both in a Proxy so call sites can still just import db and use it like a normal object:

export const db = new Proxy({} as PostgresJsDatabase<Schema>, {
  get(_, prop) {
    return Reflect.get(getDb(), prop);
  },
});

I won't pretend this is elegant. It works, and it means nobody importing db has to know it's lazy underneath. Fine trade.

Second thing: we set a distinct application_name per pool. Costs nothing, and the first time something's connection-starved you'll be very glad pg_stat_activity tells you exactly which pool is choking instead of guessing.

The policy itself

Every tenant table gets the same shape:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
  USING (organization_id = current_setting('app.organization_id', true)::uuid)
  WITH CHECK (organization_id = current_setting('app.organization_id', true)::uuid);

Two things matter more than they look like they should.

That true as the second argument to current_setting means "don't error if the setting's unset, just give me NULL." NULL compared to anything is NULL, and Postgres treats a NULL policy result as "exclude this row" — same as false. So a query that runs without tenant context isn't a crash and isn't a leak, it's just an empty table. Someone forgets to set context, they get a confusing empty result, they go debug it. Nobody's data went anywhere in the meantime. That's the property I actually care about here.

And don't skip WITH CHECK. It's easy to write the USING clause, ship it, and feel done — but USING only governs reads. Without WITH CHECK, nothing stops a tenant from INSERTing or UPDATEing a row with someone else's organization_id in it. We missed this on the first pass of one migration and caught it in review, which is exactly the kind of thing you don't want to catch in review.

Getting context into the transaction: SET LOCAL

Policies check current_setting('app.organization_id'), so something has to set it per request. The obvious first attempt — plain SET app.organization_id = '...' — is wrong, and it's wrong in a way that won't show up until you're under load. SET is session-scoped, connections are pooled, and a session-scoped setting sticks around for whoever grabs that connection next. That's tenant bleed waiting to happen the moment your pool gets reused across requests, which it will.

SET LOCAL is the fix — scoped to the current transaction, reverts automatically on commit or rollback. Open transaction, SET LOCAL, run your queries, done, connection goes back to the pool clean.

In Next.js we wrapped this into one function every tenant route goes through:

export function withAuth(handler: AuthenticatedHandler) {
  return async (request: NextRequest, ctx?: RouteContext) => {
    const result = await authenticate(); // session lookup
    if (!result.success) {
      return NextResponse.json({ error: result.error }, { status: result.status });
    }

    const { user, organizationId } = result;

    return db.transaction(async (tx) => {
      await setOrgContext(tx, organizationId);
      return handler(request, { user, organizationId, tx: castTx(tx) }, ctx);
    });
  };
}

Route handlers never see any of this. They just get a tx that's already scoped:

export const GET = withAuth(async (request, { tx }) => {
  const rows = await tx.select().from(projects); // only this org's rows, guaranteed
  return NextResponse.json({ data: rows });
});

No WHERE organizationId = ? anywhere in that query, and it's still safe. We keep the explicit filters in most places anyway — belt and suspenders, and it documents intent for the next person reading the code — but the point stands: if you forgot it, RLS still has you.

Server components got a matching helper since they're not going through route handlers at all:

export async function withOrgDb<T>(
  organizationId: string,
  callback: (tx: DbInstance) => Promise<T>,
): Promise<T> {
  return getDb().transaction(async (tx) => {
    await setOrgContext(tx, organizationId);
    return callback(castTx(tx));
  });
}

Gotcha #1: you can't parameterize SET

setOrgContext looks like this:

const UUID_RE =
  /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;

export async function setOrgContext(tx: DrizzleTx, organizationId: string) {
  if (!UUID_RE.test(organizationId)) {
    throw new Error("Invalid organization ID format");
  }
  await tx.execute(
    sql`SET LOCAL app.organization_id = '${sql.raw(organizationId)}'`,
  );
}

That sql.raw() should bother you a little — it bothered me too, first time I wrote it. Here's the thing though: SET is a utility command, not a regular statement, and utility commands in Postgres just don't take $1-style parameters. You cannot write SET LOCAL app.organization_id = $1. It'll error. You've got two real options — set_config('app.organization_id', $1, true), which is a normal function call and does accept a parameter, or raw string interpolation gated behind strict validation. We went with the second, gated on a UUID regex: the value either looks exactly like a UUID or the function throws before it ever touches SQL. I still don't love it stylistically, but there's no injection surface left once that regex has to match.

Point either way: you're going to hit this wall the first time you try to parameterize a SET, and it's not a bug in your code, it's just how the command works.

Gotcha #2: Drizzle's transaction type doesn't match its client type

Drizzle's transaction callback hands you a PostgresJsTransaction, and that type is not structurally the same as PostgresJsDatabase — even though every method you'd actually call (select, insert, update, delete) is identical between them. If AuthContext.tx gets typed as the transaction class, every shared data function downstream now needs that same internal type, which means importing a class Drizzle never really meant for you to reach into.

We just centralized the cast once and moved on:

// Extract the tx type without importing internal Drizzle classes
type DrizzleTx = Parameters<Parameters<DbInstance["transaction"]>[0]>[0];

export function castTx(tx: DrizzleTx): DbInstance {
  return tx as unknown as DbInstance;
}

Now shared data-layer code just takes a DbInstance and doesn't care if it's the root client, the admin client, or a live transaction. One cast, one comment explaining why, easy to delete later if Drizzle ever unifies the types properly.

Gotcha #3: don't let a stream hold your transaction

withAuth holds a transaction — and the pooled connection under it — for as long as the request runs. For a JSON endpoint that returns in 40ms, fine. For a Server-Sent Events endpoint that stays open for twenty minutes, that's a problem: with a pool of 4, three people leaving a dashboard tab open exhausts it and everyone else starts timing out.

We learned this the hard way and split off a separate wrapper for streaming routes — it authenticates, but deliberately never opens a transaction:

export const GET = withAuthStream(async (request, { organizationId }) => {
  // Short transaction for the initial state read
  const job = await withOrgDb(organizationId, (tx) =>
    tx.select().from(jobs).where(eq(jobs.id, jobId)),
  );

  const stream = new ReadableStream({
    /* ...on each event, use withOrgDb() again for any query... */
  });
  return new Response(stream, {
    headers: { "Content-Type": "text/event-stream" },
  });
});

The rule we now follow: RLS context only lives as long as a transaction, so keep the transaction as short as the query needs — never as long as the request.

Gotcha #4: knowing which connection you're on

Two Drizzle instances means the failure mode shifted from "forgot a WHERE clause" to "grabbed the wrong client." We ended up needing a cheat sheet:

Scenario	Connection
Tenant API route	tx from withAuth
SSE route	withOrgDb() per query
Server component, tenant data	withOrgDb()
Server component, auth tables only	dbAdmin
Admin route	dbAdmin
Auth library internals	dbAdmin
Migrations / background workers	superuser

The reassuring part: the mistake in the safe direction fails loudly. Query tenant tables through db with no context set, and you get zero rows — a visibly broken feature in dev, not a security incident. Using dbAdmin where you meant tenant scoping is the dangerous direction, and that's the one worth actually watching for in review. At least it's greppable.

Need help securing your architecture?

Our team specializes in building robust multi-tenant systems. Reach out to u11d to review your security implementation.

Contact Us