Row-level security in SvelteKit: defense-in-depth that actually works with Drizzle ORM

sveltekit, postgres, security, tutorial, drizzle

Post body

Most SaaS apps enforce tenancy at the application layer: every query includes WHERE org_id = ?. This works until it doesn't — a missed filter, a new endpoint, a refactor that drops the check. Row-level security (RLS) in Postgres catches the cases the application layer misses.

Here's how to wire RLS into a SvelteKit app using Drizzle ORM, without fighting the framework.

The problem with application-only tenancy

In a typical SvelteKit + Drizzle setup, your service layer looks like this:

// src/lib/server/services/orgs.ts
export async function getMembers(db: DrizzleDB, orgId: string) {
  return db.select().from(memberships)
    .where(eq(memberships.orgId, orgId));
}

Every caller must remember to pass orgId. Every new endpoint must extract it from the session. Every new developer on the team must know the convention. One missed where clause and tenant A sees tenant B's data.

RLS adds a database-level safety net: even if the application forgets the filter, Postgres enforces it.

Step 1: set the identity per request

RLS policies need to know who is making the request. Postgres has a SET LOCAL mechanism that sets a transaction-local variable:

-- Set during each request transaction
SET LOCAL app.current_user_id = 'user-uuid-here';
SET LOCAL app.current_org_id = 'org-uuid-here';

In SvelteKit, you set this in your hooks server, right after authentication:

// src/hooks.server.ts
import type { Handle } from '@sveltejs/kit';
import { db } from '$lib/server/db';
import { sql } from 'drizzle-orm';

export const handle: Handle = async ({ event, resolve }) => {
  const session = event.cookies.get('session');
  if (session) {
    const user = await getUserFromSession(db, session);
    if (user) {
      event.locals.user = user;
      event.locals.membership = await getActiveMembership(db, user.id);
    }
  }

  // Wrap the response in a transaction that sets the RLS identity
  const response = await db.transaction(async (tx) => {
    if (event.locals.user) {
      await tx.execute(sql`SET LOCAL app.current_user_id = ${event.locals.user.id}`);
      if (event.locals.membership) {
        await tx.execute(sql`SET LOCAL app.current_org_id = ${event.locals.membership.orgId}`);
      }
    }
    return resolve(event);
  });

  return response;
};

Step 2: write the RLS policies

-- Enable RLS on the memberships table
ALTER TABLE memberships ENABLE ROW LEVEL SECURITY;

-- Users can only see memberships in their current org
CREATE POLICY memberships_org_isolation ON memberships
  FOR ALL
  USING (org_id = current_setting('app.current_org_id')::uuid);

-- Users can only see their own user record
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY users_self_only ON users
  FOR ALL
  USING (id = current_setting('app.current_user_id')::uuid);

Step 3: make Drizzle unaware (that's the point)

Here's the key insight: Drizzle doesn't need to know RLS exists. Your service layer stays clean:

export async function getMembers(db: DrizzleDB) {
  // No orgId parameter needed — RLS handles the filtering
  return db.select().from(memberships);
}

This is the defense-in-depth model: the application layer does filter by orgId (for performance and clarity), and the database layer also filters (as a safety net). If the application filter is correct, RLS is invisible. If the application filter is wrong, RLS catches it.

The gotcha: connection pooling

RLS uses SET LOCAL, which is transaction-scoped. This means it works correctly with connection pooling — the variable is set and cleared within each transaction, so there's no state leakage between requests.

But there's a catch: not all poolers support SET LOCAL correctly.

Pooler	SET LOCAL support	Notes
PgBouncer (transaction mode)	✅ Works	Recommended
Supavisor (transaction mode)	✅ Works	Supabase default
Node pg pool	✅ Works	Single-connection, no pooling issues
postgres.js	✅ Works	Connection-level, transaction-scoped
PgBouncer (session mode)	✅ Works	Less efficient but safe

The important thing: use transaction-mode pooling, not session-mode. Transaction mode gives each request a fresh transaction, which is exactly what RLS needs.

When to use this vs. application-only

Use application-only when:

• Single-tenant app
• Simple data model with few tables
• Team is small and disciplined

Add RLS when:

• Multi-tenant with shared database
• Multiple developers/agents touching the codebase
• You want a safety net that doesn't depend on human diligence
• Enterprise customers ask "how do you isolate our data?"

RLS is not a replacement for application-level checks — it's a second line of defense. The application layer catches errors early (with better error messages). The database layer catches errors late (but catches all of them).

What I packaged

I built this pattern (RLS as opt-in defense-in-depth + connection pooling guidance + Drizzle integration) into a tested SvelteKit + Postgres starter. The RLS policies are in rls/0010_rls_policies.sql — opt-in, not forced.

Live demo: postgres-starter.verdantstack-site.pages.dev — try the RBAC: owner sees admin controls, member gets a 403. The RLS policies are there as defense-in-depth behind the application-layer checks.

Source on GitHub: verdantstack/sveltekit-postgres-starter

Pricing: $79 early bird → $129 standard. One license, unlimited projects, lifetime updates, 30-day refund.