Row level security when an ORM is doing the querying

You spent an afternoon writing row level security policies. Every table has enable row level security, every policy checks auth.uid(), and you tested them in the Supabase dashboard's SQL editor with a fake JWT. Then you wrote this in a server action:

const notes = await db.select().from(notesTable);   // or db.note.findMany()

and it returned every note belonging to every user in the system.

Nothing is broken. The policies are fine. They simply did not run.

Why the ORM sees everything

Supabase has two front doors into the same database.

PostgREST - the /rest/v1 endpoint you hit with @supabase/supabase-js - takes your JWT, sets the Postgres role to anon or authenticated, and puts the token's claims into request.jwt.claims. RLS policies then run for every statement. That is what makes the anon key safe to ship in a browser bundle.

A direct Postgres connection - what DATABASE_URL gives your ORM - authenticates as the postgres role. That role owns the tables and has BYPASSRLS. Postgres does not consult policies for a table's owner. There is no JWT, no auth.uid(), nothing to filter by.

So the moment you put an ORM in front of Supabase, RLS stops being your application's authorization layer for that path. Pretending otherwise is the actual danger: you write select().from(notes) believing something downstream will scope it, and it does not.

The wrong fix: connect the ORM as authenticated

The tempting move is to make the ORM respect policies by switching roles per query:

// Do not do this
await db.execute(sql`set role authenticated`);
await db.execute(sql`select set_config('request.jwt.claims', ${claims}, false)`);
const notes = await db.select().from(notesTable);

This appears to work and then fails in production, because on a transaction-mode pooler (:6543) consecutive queries are not guaranteed to land on the same backend connection. set role on one connection, select on another, and either you get unfiltered rows or you leak a role setting into somebody else's request. The false third argument to set_config makes it session-wide, which on a pooled connection means "everyone who reuses this socket". That is a cross-tenant data leak.

The right fix, part one: scope every ORM query explicitly

Treat the ORM connection as what it is - an admin connection - and make ownership a visible part of every query. Read the caller once, at the edge of the request, and pass the id down:

With Drizzle:

// src/app/notes/actions.ts
"use server";

import { and, eq } from "drizzle-orm";
import { db } from "@/db";
import { notes } from "@/db/schema";
import { requireUser } from "@/lib/auth/session";

export async function listNotes() {
  const user = await requireUser();
  return db.select().from(notes).where(eq(notes.userId, user.id));
}

export async function updateNote(id: string, body: string) {
  const user = await requireUser();
  const [row] = await db
    .update(notes)
    .set({ body })
    .where(and(eq(notes.id, id), eq(notes.userId, user.id)))
    .returning();

  // No row means the note does not exist *or* is not theirs. Same 404 either way -
  // distinguishing them tells an attacker which ids are real.
  if (!row) throw new Error("Not found");
  return row;
}

With Prisma, same shape, same rule:

// src/app/notes/actions.ts
"use server";

import { db } from "@/db";
import { requireUser } from "@/lib/auth/session";

export async function listNotes() {
  const user = await requireUser();
  return db.note.findMany({ where: { userId: user.id } });
}

export async function updateNote(id: string, body: string) {
  const user = await requireUser();
  // updateMany, not update: `update` takes a unique where clause, so it can only
  // match on id and the ownership check would have to happen after the write.
  const { count } = await db.note.updateMany({
    where: { id, userId: user.id },
    data: { body },
  });

  if (count === 0) throw new Error("Not found");
  return db.note.findUniqueOrThrow({ where: { id } });
}

The ownership term in the update clause is the important half in both. Filtering reads but not writes is the most common version of this bug.

The right fix, part two: keep the policies anyway

Policies are still worth writing, because the ORM is not the only client:

• The browser talks to PostgREST directly for realtime subscriptions and Storage. Realtime authorises changefeeds through RLS - without policies a subscriber receives every row in the table.
• Anyone with the anon key can curl your REST endpoint. That key is in the JS bundle of every visitor.
• A future edge function, a Retool dashboard, or an intern with the dashboard open will go through PostgREST.

RLS is the backstop for every path that is not your ORM. Your explicit where clauses are the control for the path that is. Both, not either.

When you do want the ORM to enforce policies

If you would rather have one enforcement point, run the ORM as the caller instead of as the owner, using a transaction so the role change cannot escape:

// Drizzle
export async function listNotesAsUser(claims: string) {
  return db.transaction(async (tx) => {
    // `true` = transaction-local. Reverts at commit or rollback, so a pooled
    // connection cannot carry it into the next request.
    await tx.execute(sql`select set_config('request.jwt.claims', ${claims}, true)`);
    await tx.execute(sql`set local role authenticated`);
    return tx.select().from(notes);
  });
}

// Prisma: an interactive $transaction, the same two statements.
export async function listNotesAsUser(claims: string) {
  return db.$transaction(async (tx) => {
    await tx.$executeRaw`select set_config('request.jwt.claims', ${claims}, true)`;
    // `set local role` takes an identifier, not a parameter, so it cannot go
    // through $executeRaw's placeholder machinery. The string is a literal with
    // nothing interpolated into it, which is the only form of Unsafe that is
    // actually safe.
    await tx.$executeRawUnsafe("set local role authenticated");
    return tx.note.findMany();
  });
}

claims is the JSON object of verified claims, not the raw token, at minimum {"sub":"<user id>","role":"authenticated"}. Building it from anything the client sent unverified hands the caller a sub of their choosing.

set local and set_config(..., true) are the load-bearing words: both are scoped to the transaction. Everything must happen inside that one transaction, and you pay two extra round trips per query. This is a good trade for a multi-tenant B2B app where a missed where clause means showing one customer another customer's data. It is overkill for a single-tenant product.

Do not mix the two styles in one codebase. Pick per project and write it down.

Checklist

• The ORM connection bypasses RLS. Assume it always will.
• Every query built from request input filters on the owner column - reads and writes.
• Policies stay on for PostgREST, realtime and Storage.
• Never set role outside a transaction on a pooled connection.
• Add an index on the ownership column. Every query now filters by it.
• Test the policy path separately from the ORM path; passing one proves nothing about the other.