Wiring Row-Level Security on audit_logs

The last lesson settled which table earns Row-Level Security: audit_logs. Four small pieces compose into one guarantee — one org’s rows are invisible to every other org.

A pgPolicy block beside the table in your schema defines which rows a query may return. A follow-up migration forces that policy past the table owner, who would otherwise bypass it. A withTenant(orgId, fn) helper hands the database the current org’s identity for one transaction, so the value never leaks onto the next request sharing a pooled connection. An isolation test shows what “the database refuses to leak” looks like, even when you forget the where clause.

Note

This lesson wires only the protection around audit_logs. The table’s columns and the writer function that appends entries arrive in the next chapter; here we treat it as already existing and focus on making one org’s rows invisible to every other org.

How a policy filters every read and write

A policy is a per-row boolean rule. When a policy exists and Row-Level Security is on, Postgres adds its condition to every SELECT, INSERT, UPDATE, and DELETE against the table, and rows that fail the condition cease to exist as far as that query is concerned. You can’t opt out and you can’t forget it: the filter belongs to the database, not to you.

Here is the canonical shape in plain SQL, which shows each piece most clearly. The next section writes it through Drizzle.

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. The same org owns the rows it reads and the rows it writes, so one predicate serves both. You split into per-command policies only when the read and write predicates diverge, rare for plain tenant isolation.

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);

The database role the policy applies to. authenticated is the role your request handlers connect as, not the table’s owner role — a catch 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 existing rows the query may see. On a SELECT, UPDATE, or DELETE, any row where this expression is false is invisible, so it can’t be returned, updated, or deleted.

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 may produce. USING gates rows on the way out (what you can see), WITH CHECK on the way in (what you can write). An insert that writes a row for another org passes nothing 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);

The row’s organization_id is compared against a connection session variable named app.org_id, which the app sets per request to name the tenant it is filtering for. The , true and the ::uuid cast are deliberate fail-closed choices, each covered in its own section near the end.

← Previous 1 / 1 Next →

This inverts the tenantDb(orgId) helper from earlier in this chapter. There, the application supplies the filter by composing eq(organizationId, orgId) into every query. With RLS the database supplies it and won’t be talked out of it: every code path gets the org predicate, whether it’s a Server Action, a background job, or a one-off psql session, because the filter lives below the application.

The two layers add up rather than compete. tenantDb guards every tenant table at the application layer; the policy guards this one high-stakes table again at the database layer. They are independent, so each catches the other’s bugs.

The app.org_id session variable is the linchpin, so the second half of the lesson is almost entirely about setting it correctly.

Watch video

Paul Copplestone’s 18-minute POSETTE talk frames RLS as an implicit WHERE clause and walks through USING vs WITH CHECK, permissive-policy OR-ing, and the owner bypass.

Authoring the policy in the schema with pgPolicy

Don’t hand-write that CREATE POLICY into a migration. In this stack the schema is the single source of truth: a table’s columns, indexes, and policies live in one TypeScript file, and drizzle-kit generate derives the SQL migration from it. Hand-edit a generated migration to add a policy and the next generate won’t know the policy exists; the two drift apart, and drift in a security boundary is the kind of bug you find only once it’s a breach.

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

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 returning table-level extras: indexes, constraints, and policies. It receives t, a handle to the table’s 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();

The Drizzle equivalent of the CREATE POLICY above: for: 'all' is FOR ALL, to: authenticatedRole is TO authenticated, and as: 'permissive' is the policy mode, explained next.

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 same two predicates as sql tagged templates. ${t.organizationId} interpolates the column reference as the correct quoted identifier; the rest inside the backticks is raw policy SQL. The predicates being identical is the signature of tenant isolation: you read and 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();

Tells Drizzle to emit ALTER TABLE ... ENABLE ROW LEVEL SECURITY alongside the policy. Without it the policy is inert. Note the name: it enables RLS but does not force it, which is about to matter.

← Previous 1 / 1 Next →

A permissive policy is the default. Several permissive policies on a table combine with OR, so a row is visible if any allows it. The restrictive mode combines with AND, for layering extra constraints; single-tenant isolation needs one predicate, so skip it.

That block is the portable way to write the policy: it works on any Postgres. This course runs Neon in production, which ships a one-call shortcut. The two tabs show the same policy both ways.

pgPolicy (portable)

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 local Docker container, RDS, Supabase, or Neon. The course default, because portability beats a few saved keystrokes. All four CRUD commands ride the single for: 'all'.

crudPolicy (Neon)

crudPolicy({
  role: authenticatedRole,
  read: sql`${t.organizationId} = current_setting('app.org_id', true)::uuid`,
  modify: sql`${t.organizationId} = current_setting('app.org_id', true)::uuid`,
}),

Neon-only. crudPolicy collapses the four commands into one call when the read and write predicates match, which for tenant isolation they always do. Under the hood it expands into four pgPolicy definitions. Import crudPolicy and authenticatedRole from drizzle-orm/neon.

Lead with pgPolicy. It runs everywhere, and naming each command explicitly is a feature in a security boundary, not noise.

Note

Declaring a policy auto-enables RLS, so .enableRLS() is technically redundant. Keep it: it documents intent at the call site, so a reader sees RLS is on without knowing the auto-enable rule. The next section needs RLS forced, not just enabled, which is separate.

Note

This course pins Drizzle 0.45 and drizzle-kit 0.31, where .enableRLS() plus pgPolicy(...) is the correct surface. Drizzle 1.0 renames .enableRLS() to pgTable.withRLS(...); when you upgrade, that one method moves.

Why ENABLE ROW LEVEL SECURITY skips the table owner

This is the highest-value gotcha in the lesson, because it fails silently.

Here is the trap. The migration that creates the policy connects as the table’s owner role, which reads and writes every row without the policy applying. You run an isolation test as the owner and watch it pass. You ship. In production, the first request to connect as the non-owner authenticated role becomes the first code path to actually exercise the policy.

The cause surprises almost everyone: ENABLE ROW LEVEL SECURITY does not apply to the table’s owner. Owners and superusers bypass their own tables’ policies, and your migrations, admin tooling, and tests all run as the owner. So your policy can be completely broken while every owner-run path sails through and reports success.

The fix is one more statement, and you need both:

• ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY turns the policy on. This is what .enableRLS() generates.
• ALTER TABLE audit_logs FORCE ROW LEVEL SECURITY extends it to the owner, so the policy applies to everyone.

The catch: drizzle-kit generate emits ENABLE but not FORCE — there’s no force option — so you add it by hand on every RLS table:

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

The two tabs show what Drizzle gives you against what you need.

Enabled only

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. Exactly what drizzle-kit generate produces. The policy is enabled, but the owner bypasses it, so migrations, admin scripts, and any owner-run test read and write every row regardless. The protection isn’t real until a non-owner hits it.

Enabled + forced

-- migration: force_audit_rls (written by hand, --custom)
ALTER TABLE "audit_logs" FORCE ROW LEVEL SECURITY;

Now the policy applies to everyone, owner included. The hand-written follow-up adds FORCE. Owner-run tests and non-owner requests now exercise the same rules, so a passing test means something.

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

One scope note: this lesson wires the org-isolation policy, the FOR ALL tenant filter. The next chapter’s audit lesson adds a second policy on the same table, an append-only rule that denies UPDATE and DELETE so a log stays trustworthy. A separate concern, built next to the audit writer that owns it.

Setting the tenant per request: 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. Every request that touches audit_logs must set it before the query runs, or the policy reads an unset value and returns nothing.

Two SQL forms set it, and the difference between them is this lesson’s second footgun.

Session-level (SET)

SET app.org_id = '...';

Leaks across requests on a pooled connection. SET sets the variable for the whole connection. Your app borrows connections from a pool, so when this request finishes, the connection returns still carrying org A’s value. The next request to borrow it inherits that value, and if it belongs to org B, it reads and writes org A’s rows. One missing keyword, one cross-tenant leak.

Transaction-local (SET LOCAL)

SET LOCAL app.org_id = '...';

The only safe primitive on a pooled connection. SET LOCAL scopes the variable to the current transaction. When that transaction commits or rolls back, the value is discarded, so the connection returns to the pool clean.

That scoping drives the helper’s design. SET LOCAL only persists inside a transaction; run it outside one and the value vanishes the instant the statement finishes, leaving the policy to read an unset variable and return zero rows. So the helper must open an explicit transaction, set the variable inside it, and run the work on that same transaction.

Here’s the helper that packages 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);
  });

The database client must never reach the browser bundle. The server-only import turns a bundling mistake into 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 written out explicitly, like the tenantDb factory. orgId is the tenant; fn is the caller’s work, which receives the transaction and returns its result 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);
  });

Opens the explicit transaction. Both the variable-setting statement and the caller’s work run on tx, so they share 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);
  });

set_config(name, value, is_local) is the function form of SET, and its third argument true makes it transaction-scoped, exactly like SET LOCAL. We reach for it over raw SET LOCAL because raw SET can’t take a bind parameter, and a runtime value like orgId must be parameterized, never string-concatenated, or you open a SQL injection hole. The ${orgId} inside the sql template is sent as a bound parameter.

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. Returning its result flows the transaction’s value back to the caller.

← Previous 1 / 1 Next →

Now watch the two layers compose. Inside a Server Action you have a trusted orgId from requireOrgUser(), the only sanctioned source. You wrap the audit write in withTenant and pass organizationId explicitly too:

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
  });
});

Two enforcements guard that one write. The application sets organizationId: orgId in values; the database’s WITH CHECK independently rejects any row whose organization_id doesn’t equal app.org_id. Drop the explicit value in a refactor and the policy still pins the row to the org the variable names; let a typo slip into the policy and the explicit organizationId still writes the correct org. A single mistake has to defeat both layers to cause a leak. That is defense in depth.

The helper lands in your data layer under db/, alongside its application-layer sibling tenantDb. Keep the work inside the transaction to fast, local database writes; the rule against awaiting an external service mid-transaction still holds.

SET LOCAL sets a config value only until the current transaction ends. set_config is its function form, and unlike raw SET it accepts a bind parameter.

Reading the request lifetime end to end

What ties the pieces together is timing: when the variable is set, when it’s cleared, and that the connection returns 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.

Prev

Next

The last step is the one that’s hard to believe: seeing the connection return empty is what makes “the variable is gone after commit” concrete.

Failing closed: why , true and why ::uuid

Two small arguments sit inside the policy predicate. Both are deliberate, and both are about failing closed; getting either wrong opens a subtle hole.

The second argument true in current_setting('app.org_id', true) is missing_ok: an unset variable returns NULL instead of raising. The request that hits this branch reached a query without going through withTenant first, through a bug, and it should see zero rows rather than crash. NULL buys exactly that, because organization_id = NULL evaluates to NULL, never true, so it excludes every row. Drop the , true and current_setting throws: the request surfaces as a 500, the app looks broken, and someone rushing to fix the “outage” is tempted to make the error go away in ways worse than an empty result.

The ::uuid cast (not ::text). Your organization_id is a uuid, so cast the variable to uuid before comparing. If someone sets app.org_id to junk like 'all' or '%' hoping to “match everything,” the cast rejects it and 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. Comparing text to text works today, but the day someone changes the predicate to use LIKE, a value like '%' becomes a wildcard that matches every org.

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

Here is the canonical acceptance test for the policy, the two ways it can lie to you, and why you read it 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 subjects even seeding to the policy. Then, inside a withTenant(A) transaction, run SELECT * FROM audit_logs with no where clause and assert you get back exactly one row, org A’s. Repeat for B.

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, independent of any application-layer filter. That’s the property RLS exists to provide.

Two ways this exact test can give you a false pass:

1. Run it as the owner role. The owner bypasses RLS, so both rows come back and the length assertion fails, or worse, passes while proving nothing if you wrote it to expect both. Run the test as the non-owner app role that real requests use.

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

That second mode is why there is no live coding cell in this lesson.

Caution

The in-browser Postgres these exercises run on (PGlite) does not enforce RLS. A “prove it” sandbox here would pass even with the policy deleted, teaching the exact false confidence this lesson warns against. So the test ships as code to read, and the real one runs in the integration suite against real Postgres. An RLS test that can pass without RLS is worse than no test at all: it turns “we’re not sure” into a green checkmark.

The non-owner requirement implies a second connection identity: request handlers connect as an app role with no BYPASSRLS, while migrations and admin tasks connect as the owner through a separate client at DATABASE_URL_OWNER. Managed Postgres (Neon, Supabase) ships this split for you. Creating the role and its grants is its own chapter later; for now, just register the two-URL shape.

Clone this repo locally, seed data, open psql, and query audit_logs, and you’ll see nothing, because you haven’t set app.org_id. That’s the policy working, not a broken seed.

Tip

For ad-hoc local exploration, connect as the owner role, or \set app.org_id to a seed org id in your ~/.psqlrc. Never grant the app role BYPASSRLS to ease local dev: it defeats the mechanism, and a habit formed locally follows you to production.

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.