Skip to content
LEARNREACTSAAS
Chapter 56Lesson 4

Wiring RLS on audit_logs with policies and SET LOCAL

Enforce multi-tenant isolation at the database layer with Postgres Row-Level Security, declaring policies through Drizzle and carrying the tenant identity per request with SET LOCAL.

The last lesson settled which table earns Row-Level Security: of every table in this stack, audit_logs is the one. Now we wire it. Four concrete questions come up before you touch the keyboard. What does the actual policy syntax look like? How does a policy learn which organization it is filtering for, at the moment a query runs? How does the app hand the database that organization identity per request, without the value leaking onto the next request that shares the same pooled connection? And what test proves that another org’s row never comes back, even when you forget the where clause entirely?

By the end of this lesson you will have built four small pieces that answer those questions and compose into one guarantee. A pgPolicy block lives next to the table in your schema. A follow-up migration forces the policy past the table owner. A withTenant(orgId, fn) helper sets the tenant identity for the duration of a single transaction. And an isolation test lets you read exactly what “the database refuses to leak” looks like in code.

How a policy decides which rows you see

Section titled “How a policy decides which rows you see”

Before any Drizzle syntax, you need a precise mental model of what a policy actually is. A policy is a per-row boolean rule that Postgres evaluates on every query against a table. When a policy exists on a table and Row-Level Security is on, the database rewrites your query behind the scenes: every SELECT, INSERT, UPDATE, and DELETE gets the policy’s condition added to it, and rows that fail the condition simply don’t exist as far as that query is concerned. You don’t opt in per query, and you can’t forget it. The filter belongs to the database, not to you.

Here is the canonical shape, written in plain SQL. You won’t author it this way, since the next section writes it through Drizzle, but raw SQL is the clearest way to see what every piece does, and learning to read one of these fluently is worth a few minutes.

CREATE POLICY audit_logs_org_isolation ON audit_logs
  FOR ALL
  TO authenticated
  USING (organization_id = current_setting('app.org_id', true)::uuid)
  WITH CHECK (organization_id = current_setting('app.org_id', true)::uuid);

One policy covers all four commands: select, insert, update, and delete. You only split into per-command policies when the read and write predicates need to diverge, which is rare for plain tenant isolation. The same org owns the rows it reads and the rows it writes, so one predicate serves both.

CREATE POLICY audit_logs_org_isolation ON audit_logs
  FOR ALL
  TO authenticated
  USING (organization_id = current_setting('app.org_id', true)::uuid)
  WITH CHECK (organization_id = current_setting('app.org_id', true)::uuid);

This names the database role the policy applies to. authenticated is the role your request handlers connect as. This detail matters, and it has a catch: the policy applies to this role, not to the table’s owner role. That gets its own section shortly.

CREATE POLICY audit_logs_org_isolation ON audit_logs
  FOR ALL
  TO authenticated
  USING (organization_id = current_setting('app.org_id', true)::uuid)
  WITH CHECK (organization_id = current_setting('app.org_id', true)::uuid);

USING is the read filter: the set of existing rows the query is allowed to see. On a SELECT, UPDATE, or DELETE, any row where this expression is false is invisible. It can’t be returned, updated, or deleted, because as far as the query is concerned it isn’t there.

CREATE POLICY audit_logs_org_isolation ON audit_logs
  FOR ALL
  TO authenticated
  USING (organization_id = current_setting('app.org_id', true)::uuid)
  WITH CHECK (organization_id = current_setting('app.org_id', true)::uuid);

WITH CHECK is the write filter: the rows an INSERT or UPDATE is allowed to produce. This is the one most people conflate with USING, so hold the distinction clearly. USING gates rows on the way out (what you can see), and WITH CHECK gates rows on the way in (what you can write). An insert that tries to write a row for some other org passes no rows through WITH CHECK, so it is refused.

CREATE POLICY audit_logs_org_isolation ON audit_logs
  FOR ALL
  TO authenticated
  USING (organization_id = current_setting('app.org_id', true)::uuid)
  WITH CHECK (organization_id = current_setting('app.org_id', true)::uuid);

This is the heart of it. The row’s own organization_id is compared against a connection session variable named app.org_id. That variable is how the policy learns which tenant it is currently filtering for, and the app sets it per request, which we get to. Note the two small arguments along with it: the , true and the ::uuid cast. Both are deliberate fail-closed choices, and both get their own section near the end. For now, just register that they are not decoration.

1 / 1

Notice how this inverts the tenantDb(orgId) helper from earlier in this chapter. With that helper, the application supplies the org filter: it composes eq(organizationId, orgId) into every query so you can’t forget it. With RLS, the database supplies the filter, and it refuses to be talked out of it. Every query, every connection, and every code path gets the org predicate, whether it’s a Server Action, a background job, a one-off psql session at 2am, or a hand-written SQL script someone runs to “just check something,” because the filter lives below the application entirely.

This is the key idea to carry from the last lesson: tenantDb runs always, and RLS runs sometimes, on top. The two add up; they are never an either/or choice. tenantDb guards every tenant table at the application layer. The policy guards this one high-stakes table a second time at the database layer. The two layers are independent, and as you’ll see, each one catches the other’s bugs.

A session variable here means a connection-scoped key/value pair, read back with current_setting, that carries the current tenant’s id into the policy. Keep that term in mind, because the second half of the lesson is almost entirely about setting it correctly.

Authoring the policy in the schema with pgPolicy

Section titled “Authoring the policy in the schema with pgPolicy”

You could write that CREATE POLICY statement by hand into a migration file, but don’t. One of this stack’s architectural principles is that the schema is the single source of truth: the table’s shape, its indexes, and its policies all live together in one TypeScript file, and drizzle-kit generate derives the SQL migration from that file. If you hand-edit a generated migration to add a policy, your schema file no longer describes reality, and the next generate won’t know the policy exists. The two drift apart, and drift in a security boundary is exactly the kind of bug you don’t find until it’s a breach.

So the policy lives in the schema, declared on the table as a modifier. Here is the block.

export const auditLogs = pgTable(
  'audit_logs',
  {
    organizationId: uuid('organization_id')
      .notNull()
      .references(() => organizations.id, { onDelete: 'restrict' }),
    // remaining columns land in the next chapter's audit-logging lesson
  },
  (t) => [
    pgPolicy('audit_logs_org_isolation', {
      as: 'permissive',
      for: 'all',
      to: authenticatedRole,
      using: sql`${t.organizationId} = current_setting('app.org_id', true)::uuid`,
      withCheck: sql`${t.organizationId} = current_setting('app.org_id', true)::uuid`,
    }),
  ],
).enableRLS();

The third argument to pgTable is a callback that returns an array of table-level extras: indexes, constraints, and policies. It receives t, a handle to the table’s own columns, so you can reference them inside SQL templates.

export const auditLogs = pgTable(
  'audit_logs',
  {
    organizationId: uuid('organization_id')
      .notNull()
      .references(() => organizations.id, { onDelete: 'restrict' }),
    // remaining columns land in the next chapter's audit-logging lesson
  },
  (t) => [
    pgPolicy('audit_logs_org_isolation', {
      as: 'permissive',
      for: 'all',
      to: authenticatedRole,
      using: sql`${t.organizationId} = current_setting('app.org_id', true)::uuid`,
      withCheck: sql`${t.organizationId} = current_setting('app.org_id', true)::uuid`,
    }),
  ],
).enableRLS();

This is the Drizzle equivalent of the CREATE POLICY you just read. for: 'all' is the FOR ALL; to: authenticatedRole is the TO authenticated; as: 'permissive' is the policy mode, which the next term explains.

export const auditLogs = pgTable(
  'audit_logs',
  {
    organizationId: uuid('organization_id')
      .notNull()
      .references(() => organizations.id, { onDelete: 'restrict' }),
    // remaining columns land in the next chapter's audit-logging lesson
  },
  (t) => [
    pgPolicy('audit_logs_org_isolation', {
      as: 'permissive',
      for: 'all',
      to: authenticatedRole,
      using: sql`${t.organizationId} = current_setting('app.org_id', true)::uuid`,
      withCheck: sql`${t.organizationId} = current_setting('app.org_id', true)::uuid`,
    }),
  ],
).enableRLS();

These are the same two predicates from the raw SQL, written as sql tagged templates. ${t.organizationId} interpolates the column reference, which Drizzle turns into the correct quoted identifier, and everything else inside the backticks is raw policy SQL passed straight through. The two predicates being identical is the whole signature of tenant isolation: you read what your org owns, and you write only what your org owns.

export const auditLogs = pgTable(
  'audit_logs',
  {
    organizationId: uuid('organization_id')
      .notNull()
      .references(() => organizations.id, { onDelete: 'restrict' }),
    // remaining columns land in the next chapter's audit-logging lesson
  },
  (t) => [
    pgPolicy('audit_logs_org_isolation', {
      as: 'permissive',
      for: 'all',
      to: authenticatedRole,
      using: sql`${t.organizationId} = current_setting('app.org_id', true)::uuid`,
      withCheck: sql`${t.organizationId} = current_setting('app.org_id', true)::uuid`,
    }),
  ],
).enableRLS();

This chained modifier tells Drizzle to emit the ALTER TABLE ... ENABLE ROW LEVEL SECURITY statement alongside the policy. Without RLS enabled, a policy is inert: Postgres won’t apply it. Remember this method name, because it’s about to matter that it enables RLS but does not force it.

1 / 1

A permissive policy is the default mode. When a table has several permissive policies, they are combined with OR, so a row is visible if any of them allows it. The other mode, restrictive, combines with AND and is for layering extra constraints on top. You don’t need it for single-tenant isolation, where one predicate says everything.

That block is the portable way to write the policy: it works on any Postgres you might deploy to. But this course runs Neon in production, and Neon ships a one-call shortcut worth knowing. The two tabs below show the same policy authored both ways.

pgPolicy('audit_logs_org_isolation', {
  as: 'permissive',
  for: 'all',
  to: authenticatedRole,
  using: sql`${t.organizationId} = current_setting('app.org_id', true)::uuid`,
  withCheck: sql`${t.organizationId} = current_setting('app.org_id', true)::uuid`,
}),

Works on any Postgres: a Docker container locally, RDS, Supabase, or Neon. This is the default for this course because portability is worth more than a few saved keystrokes, and it reads explicitly. The four CRUD commands all ride the single for: 'all'.

Lead with pgPolicy. It runs everywhere, and being explicit about each command is a feature in a security boundary, not noise.

Enable is not enough: forcing RLS past the table owner

Section titled “Enable is not enough: forcing RLS past the table owner”

This is the highest-value gotcha in the lesson, and it is the kind that ships silently.

Picture the sequence. You wrote the policy. You ran the migration, and the migration connects as the table’s owner role, which read and wrote every row without the policy applying to it at all. You wrote an isolation test, ran it, and watched it go green. You shipped. Then, in production, a real user’s request, connecting as the non-owner authenticated role, became the very first thing in the history of your app to actually exercise the policy.

The reason is a default that surprises almost everyone the first time: ENABLE ROW LEVEL SECURITY does not apply to the table’s owner. Owners and superusers bypass their own tables’ policies by default. The thinking is that the owner is trusted to manage the table, but in a SaaS your migrations and admin tooling run as the owner, and so do your tests if you’re not careful. That means your policy can be completely broken while every owner-run code path sails straight through it, telling you everything is fine.

The fix is one more line, and you need to understand why both lines exist:

  • ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY turns the policy on. This is what .enableRLS() generates for you.
  • ALTER TABLE audit_logs FORCE ROW LEVEL SECURITY makes the policy apply to the table owner too. Without it the owner bypasses the policy; with it, everyone is subject to it, with no exceptions and no trusted roles.

And here is the fact that ties it together: drizzle-kit generate emits the ENABLE but not the FORCE. The codegen has no force option, so this is something you must add by hand, every time, on every RLS table. The workflow looks like this:

  1. Generate the migration from your schema (drizzle-kit generate).
  2. Read the generated SQL. Make a habit of never shipping an unread RLS migration. Confirm the ENABLE line is present and the FORCE line is absent.
  3. Add the FORCE in a follow-up custom migration. drizzle-kit generate --custom --name force_audit_rls gives you an empty migration file, into which you write the single ALTER TABLE audit_logs FORCE ROW LEVEL SECURITY statement.

The two tabs below show the difference between what Drizzle gives you and what you actually need.

CREATE POLICY "audit_logs_org_isolation" ON "audit_logs"
  AS PERMISSIVE FOR ALL
  TO "authenticated"
  USING (...) WITH CHECK (...);


ALTER TABLE "audit_logs" ENABLE ROW LEVEL SECURITY;

Owner bypasses, tests lie. This is exactly what drizzle-kit generate produces. The policy exists and is enabled, but the owner bypasses it, so your migrations, your admin scripts, and any owner-run test read and write every row regardless. The test passes for the wrong reason, and the protection isn’t real until a non-owner hits it.

Make this a checklist item for every RLS table you add: enable and force. The bypass is invisible until the wrong role hits it, and “the wrong role” in production is a paying customer.

One scope note, so you don’t go looking for more than belongs here. This lesson wires the org-isolation policy, the FOR ALL tenant filter. The next chapter’s audit lesson adds a second, sibling policy on the same table: an append-only rule that denies UPDATE and DELETE at the database level, because an audit log you can edit is not an audit log. That’s defense in depth on top of the app-side discipline that appends entries only through the audit writer (logAudit(tx, event), whose signature forces a transaction). It’s a different concern, owned next to the table where the writer lives. Name it in your head now, and build it there.

Setting the tenant at request time: SET LOCAL and the withTenant helper

Section titled “Setting the tenant at request time: SET LOCAL and the withTenant helper”

The policy is inert unless the app.org_id session variable is set on the connection running the query. That’s the dependency the whole second half hinges on: every request that touches audit_logs must set the variable before the query runs, or the policy reads an unset value and, by design, returns nothing.

So how do you set it? There are two SQL forms, and the difference between them is the second of this lesson’s two serious footguns. Read both tabs carefully.

SET app.org_id = '...';

Leaks across requests on a pooled connection. SET sets the variable for the whole connection, the entire session. Your app doesn’t open a fresh connection per request; it borrows one from a pool and returns it when done. So after this request finishes, the connection goes back to the pool still carrying org A’s value. The next request to borrow that connection inherits org A’s app.org_id, and if it belongs to org B, it now reads and writes org A’s rows. That’s a cross-tenant leak, caused by a single missing keyword.

That word transaction is doing real work, and it explains a design choice in the helper you’re about to write. SET LOCAL, and its function-form cousin that we’ll get to, only persist inside a transaction. Run SET LOCAL outside one and the setting is discarded the instant the statement finishes, so the policy would see the variable unset and fall back to zero rows. That means the helper can’t just set the variable and run a query. It has to open an explicit transaction, set the variable inside it, and run the work on that same transaction. The transaction is the lifetime the variable lives within.

Here’s the helper that packages all of this into one call.

import 'server-only';
import { sql } from 'drizzle-orm';
import { db } from '@/db';
import type { Transaction } from '@/db';


export const withTenant = <T>(
  orgId: string,
  fn: (tx: Transaction) => Promise<T>,
): Promise<T> =>
  db.transaction(async (tx) => {
    await tx.execute(sql`select set_config('app.org_id', ${orgId}, true)`);
    return fn(tx);
  });

This helper imports the database client, which must never reach the browser bundle. The server-only import makes a bundling mistake a build error rather than a runtime leak.

import 'server-only';
import { sql } from 'drizzle-orm';
import { db } from '@/db';
import type { Transaction } from '@/db';


export const withTenant = <T>(
  orgId: string,
  fn: (tx: Transaction) => Promise<T>,
): Promise<T> =>
  db.transaction(async (tx) => {
    await tx.execute(sql`select set_config('app.org_id', ${orgId}, true)`);
    return fn(tx);
  });

The signature is the contract, and it’s written out explicitly rather than left to inference, the same discipline the tenantDb factory followed. orgId is the tenant. fn is the caller’s work: it receives the transaction and returns whatever it returns, carried out through the <T> generic.

import 'server-only';
import { sql } from 'drizzle-orm';
import { db } from '@/db';
import type { Transaction } from '@/db';


export const withTenant = <T>(
  orgId: string,
  fn: (tx: Transaction) => Promise<T>,
): Promise<T> =>
  db.transaction(async (tx) => {
    await tx.execute(sql`select set_config('app.org_id', ${orgId}, true)`);
    return fn(tx);
  });

This opens the explicit transaction the previous paragraph argued for. Everything inside runs on tx, the transaction handle, including the variable-setting statement and the caller’s work, so they share one transaction and therefore one lifetime for the variable.

import 'server-only';
import { sql } from 'drizzle-orm';
import { db } from '@/db';
import type { Transaction } from '@/db';


export const withTenant = <T>(
  orgId: string,
  fn: (tx: Transaction) => Promise<T>,
): Promise<T> =>
  db.transaction(async (tx) => {
    await tx.execute(sql`select set_config('app.org_id', ${orgId}, true)`);
    return fn(tx);
  });

Two things to see here. First, set_config(name, value, is_local) is the function form of SET, and its third argument true means local: transaction-scoped, exactly like SET LOCAL. Second, this is why we reach for set_config instead of raw SET LOCAL. Raw SET can’t take a bind parameter, but a runtime value like orgId must be parameterized, never string-concatenated, or you’ve opened a SQL injection hole. The ${orgId} inside the sql template is sent as a bound parameter, the safe way to inject a runtime value into this statement.

import 'server-only';
import { sql } from 'drizzle-orm';
import { db } from '@/db';
import type { Transaction } from '@/db';


export const withTenant = <T>(
  orgId: string,
  fn: (tx: Transaction) => Promise<T>,
): Promise<T> =>
  db.transaction(async (tx) => {
    await tx.execute(sql`select set_config('app.org_id', ${orgId}, true)`);
    return fn(tx);
  });

The caller’s work runs last, on the same tx, with the variable already set. Return its result so the transaction’s value flows back out to the caller.

1 / 1

Now watch the two layers compose, because this is the payoff. Inside a Server Action you already have a trusted orgId from requireOrgUser(), the only sanctioned source for it. You wrap your audit write in withTenant, and inside it you also pass organizationId explicitly:

src/app/.../actions.ts
const { orgId } = await requireOrgUser();


await withTenant(orgId, async (tx) => {
  await tx.insert(auditLogs).values({
    organizationId: orgId,
    // ...the rest of the audit row, built in the next chapter
  });
});

Count the enforcements on that one write. The application layer sets organizationId: orgId explicitly in values. The database layer’s WITH CHECK independently refuses any row whose organization_id doesn’t equal app.org_id. Now run the failure modes. If a bug in the application drops the explicit value, through a refactor or a wrong variable, the policy still pins the row to the org the variable names. If a bug in the policy slips through, a typo in the predicate or a forgotten FORCE, the explicit organizationId: orgId still writes the correct org. Each layer fails independently, and a single mistake has to defeat both to cause a leak. That is defense in depth, made literal in eight lines.

A couple of placement notes. The helper lands in your data layer under db/, alongside tenantDb, which is its application-layer sibling. Keep audit writes to pure database work inside the transaction. The rule against awaiting an external service mid-transaction still holds, but appending an audit row is exactly the kind of fast, local work transactions are for.

Two terms to carry forward. SET LOCAL is the Postgres command that sets a config value only until the current transaction ends. set_config is the function form of SET; its third argument true makes the setting transaction-local, and unlike raw SET it accepts a bind parameter, which is why the helper uses it.

Reading the request lifetime end to end

Section titled “Reading the request lifetime end to end”

You’ve now seen every piece. What ties them together is timing: exactly when the variable is set, when it’s cleared, and the fact that the connection goes back to the pool with nothing on it. Scrub through the lifetime of a single audit_logs write below.

Server Action
withTenant transaction
Connection pooled
Postgres + policy
Pool
requireOrgUser() → trusted orgId
app.org_id unset not set yet
Server Action starts. requireOrgUser() returns a trusted orgId — from the server-validated session, never a URL, route param, or client-passed field.
Server Action
withTenant transaction
Connection pooled
Postgres + policy
Pool
checkout connection · BEGIN
app.org_id unset transaction open, variable still unset
withTenant(orgId, …) opens a transaction. It checks a connection out of the pool and begins an explicit transaction. The variable isn't set yet — the transaction has to exist first, because the variable will be scoped to it.
Server Action
withTenant transaction
Connection pooled
Postgres + policy
Pool
set_config('app.org_id', orgId, true)
app.org_id org A transaction-local
set_config('app.org_id', orgId, true) runs. The policy now has a tenant identity — scoped to this transaction only, by virtue of the true (local) argument.
Server Action
withTenant transaction
Connection pooled
Postgres + policy
Pool
INSERT INTO audit_logs … → WITH CHECK passes
app.org_id org A WITH CHECK compares against this
INSERT INTO audit_logs … The policy's WITH CHECK verifies organization_id = app.org_id. The values match, so the row is written. (Had they not matched, the insert would be refused.)
Server Action
withTenant transaction
Connection pooled
Postgres + policy
Pool
COMMIT → SET LOCAL value discarded
app.org_id cleared discarded by COMMIT
COMMIT. The transaction commits — and the SET LOCAL value is automatically cleared as part of the commit. Nothing about app.org_id survives.
Server Action
withTenant transaction
Connection pooled
Postgres + policy
Pool
next request starts fail-closed → 0 rows
app.org_id unset connection carries nothing
Connection returns to the pool — clean. It carries no app.org_id. The next request that borrows it starts fail-closed: an unset variable means current_setting('app.org_id', true) is NULL, the comparison is NULL, and NULL excludes every row. This spotless return is exactly what plain SET (without LOCAL) would have broken.

That last step is the whole reason the diagram exists. “The variable is gone after commit” is easy to nod along to and hard to actually believe, and seeing the connection return empty is what makes it real. Now put the order in your own hands.

Order the steps of one `audit_logs` write, from the Server Action through the connection returning to the pool. Drag the items into the correct order, then press Check.

requireOrgUser() returns the trusted orgId
withTenant(orgId, ...) opens an explicit transaction
set_config('app.org_id', orgId, true) sets the variable, transaction-local
INSERT INTO audit_logs runs and the policy’s WITH CHECK validates the row
COMMIT clears the local variable automatically
The connection returns to the pool with no app.org_id set

Failing closed: why , true and why ::uuid

Section titled “Failing closed: why , true and why ::uuid”

Two small arguments sit inside the policy predicate, and almost everyone copies them without knowing why. Both are deliberate, both are about failing closed, and getting either one wrong opens a subtle hole. They’re worth understanding precisely.

current_setting('app.org_id', true), the second argument true. That second argument is missing_ok. With it, an unset variable returns NULL instead of raising an error. Think about who hits this branch: a request that, through a bug, reached a query without going through withTenant first. What should happen? It should fail closed: the request should quietly see zero rows, not crash. That’s exactly what NULL buys, because organization_id = NULL evaluates to NULL, which is not true, so it excludes every row. Drop the , true, and current_setting throws when the variable is absent. The request then surfaces as a 500, the app looks broken, and in the rush to fix the “outage” someone is tempted to make the error go away in ways that are worse than an empty result. Failing closed and quiet beats failing loud and tempting.

::uuid (not ::text), the cast. Your organization_id is a uuid, so cast the variable to uuid before comparing. This isn’t pedantry; it’s a second fail-closed guard. If someone sets app.org_id to junk like 'all' or '%' hoping to “match everything,” the ::uuid cast rejects it: it’s not a valid uuid, so the statement errors loudly instead of silently matching surprising rows. The rule generalizes: when the variable’s domain has structure, cast to the structured type and let malformed input fail at the cast. Leaving it as text and comparing text to text is the anti-pattern. It works today, but the day someone changes the predicate to use LIKE, a value like '%' becomes a wildcard that matches every org. Cast to the real type and that whole class of mistake can’t compile.

Test your read on the first of these.

An engineer ships a code path that reads from audit_logs with a plain SELECT and no where clause, but forgets to wrap it in withTenant — so app.org_id is never set on the connection. The policy’s read filter is organization_id = current_setting('app.org_id', true)::uuid. What comes back?

Nothing — an empty result set.
Every row in the table, across all organizations.
A database error that surfaces to the user as a 500.
Whatever rows belong to the last organization that borrowed this pooled connection.

Proving it: the isolation test you read, not run

Section titled “Proving it: the isolation test you read, not run”

A policy you can’t test is a policy you don’t trust. So here is the canonical acceptance test, then the two ways it can lie to you, and why, unusually for this course, you read it here rather than run it.

The shape is simple. Insert two audit_logs rows, one for org A and one for org B, through a seed helper that connects as the owner role, since FORCE means even seeding two different orgs’ rows is subject to the policy. Then, inside a withTenant(A) transaction, run SELECT * FROM audit_logs with no where clause at all and assert you got back exactly one row, org A’s. Repeat inside withTenant(B) and assert you get B’s, and only B’s.

audit-logs.isolation.test.ts
test('audit_logs are isolated per organization', async () => {
  // seedAuditRow connects as the owner role to insert across both orgs
  await seedAuditRow({ organizationId: orgA });
  await seedAuditRow({ organizationId: orgB });


  const fromA = await withTenant(orgA, (tx) =>
    tx.select().from(auditLogs),
  );
  expect(fromA).toHaveLength(1);
  expect(fromA[0].organizationId).toBe(orgA);


  const fromB = await withTenant(orgB, (tx) =>
    tx.select().from(auditLogs),
  );
  expect(fromB).toHaveLength(1);
  expect(fromB[0].organizationId).toBe(orgB);
});

The missing where is the entire point. If you scoped the query yourself, you’d be testing your own filter, not the database’s. By querying everything and still getting back only your org’s row, you prove the isolation comes from the policy, from the database, independent of any application-layer filter. That’s the property RLS exists to provide, and it’s the only thing this test is checking.

Now, the two ways this exact test can give you a false pass. You’ve already met both, so this is consolidation:

  1. Run it as the owner role. The owner bypasses RLS, so both rows come back every time and the length assertion fails. Worse, if you’d written the test to expect both, it would pass while proving nothing. The test must connect as the non-owner app role. This is the FORCE lesson coming home: with FORCE, even the owner is subject, but you should still run the test as the role real requests use.

  2. Run it against an RLS-unaware database. If the database ignores policies, every row comes back, the policy is irrelevant, and the test passes meaninglessly. It must run against real Postgres.

Which is exactly why there is no live coding cell in this lesson.

That non-owner-role requirement implies a second connection identity, which is worth naming even though you won’t build it here. Request handlers connect as a non-owner app role that has no BYPASSRLS attribute. Migrations, admin tasks, and any legitimate cross-org read connect as the owner, through the separate unpooled client pointed at a DATABASE_URL_OWNER. Most managed Postgres setups (Neon, Supabase) ship this split for you; on a vanilla Postgres you create the app role explicitly. The full least-privilege role separation, creating the role, the grants, and managing the BYPASSRLS attribute, is its own chapter later in the course. Here, just register the two-URL shape and why it exists: one identity the policy applies to, and one identity that runs the migrations which would otherwise be unable to manage the table.

One practical note for the moment you clone this repo locally. You seed some data, open psql, query audit_logs, and see nothing, because you haven’t set app.org_id either. That’s the policy working, not a broken seed.

External resources

Section titled “External resources”
PostgreSQL — Row Security Policies
postgresql.org

The primary source: USING vs WITH CHECK, permissive vs restrictive, and the owner-bypass / FORCE behavior.
Drizzle ORM — Row-Level Security
orm.drizzle.team

The pgPolicy, enableRLS, and crudPolicy API surface for declaring policies in your schema.
Neon — Simplify RLS with Drizzle
neon.com

Neon's guide to crudPolicy and the authenticatedRole import, plus how the role split looks on Neon.