Building a Multi-Tenant SaaS with PostgreSQL Row Level Security (RLS): Code, Pitfalls, and PgBouncer Integration

Do you know what bug report scares you most when building a SaaS for the first time? For me, it was "Customer A can view Customer B's data." Even without experiencing it firsthand, just imagining it is enough to break out in a cold sweat. The problem is that the traditional defense — WHERE tenant_id = ? — exposes all data the moment a developer forgets it even once.

Row Level Security (RLS) has been supported since PostgreSQL 9.5, and honestly, it was quite a shock when I first encountered it, because it solves this problem fundamentally with far less code than I expected. By the end of this post, you'll have a structure that automatically applies tenant isolation to every query with a single 30-line middleware helper. We'll cover everything from the core mechanics of RLS, to pitfalls commonly missed in production (especially current_setting errors and the PgBouncer combination), to practical code for Express, Supabase, and Drizzle environments.

Core Concepts

What RLS Actually Does

The difference between a regular WHERE clause and RLS comes down to "who is responsible." A WHERE clause is the developer's responsibility when writing the query; an RLS policy is a hidden WHERE clause that the PostgreSQL engine automatically inserts at query execution time.

For example, when you fire SELECT * FROM orders with RLS enabled, the query actually executed inside the DB looks like this:

SELECT * FROM orders
WHERE tenant_id = current_setting('app.current_tenant')::uuid

It doesn't matter if the developer omits the WHERE. The DB adds it automatically.

Fail-closed design: If you enable RLS on a table but haven't defined any policies yet and fire SELECT * FROM orders, you'll get 0 rows back. By contrast, the traditional WHERE clause approach exposes all data if you forget it. RLS is designed to be "safe even when you make a mistake."

When Do You Need RLS?

To understand where RLS shines, you first need to understand the types of multi-tenancy architecture.

The third approach is realistic for early-stage startups. Migrations only need to run once even as tenants grow, and sharing infrastructure optimizes costs. Without RLS, tenant isolation in this structure depends entirely on developers never making a mistake.

Setting Up RLS in 3 Steps

I got confused about the order of setup at first too — it breaks down into three main steps.

Step 1: Add tenant_id column and create index

ALTER TABLE orders ADD COLUMN tenant_id uuid NOT NULL;
CREATE INDEX ON orders (tenant_id);

Step 2: Enable RLS

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- Force even the table owner to be subject to RLS
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

If you skip FORCE ROW LEVEL SECURITY, policies are ignored when connecting with the table owner account. This is something often missed in practice, so I want to call it out explicitly.

Step 3: Define the policy

CREATE POLICY tenant_isolation ON orders
  USING (
    tenant_id = current_setting('app.current_tenant', true)::uuid
  )
  WITH CHECK (
    tenant_id = current_setting('app.current_tenant', true)::uuid
  );

USING is the condition applied on SELECT/UPDATE/DELETE, and WITH CHECK is applied when writing data via INSERT/UPDATE. It's recommended to specify both.

Note on the missing_ok parameter: If you use current_setting('app.current_tenant') without the second argument, you'll get an unrecognized configuration parameter "app.current_tenant" error when no value has been set in the session. Passing true as the second argument returns NULL when no value is present, and NULL doesn't match any tenant_id, so access is blocked. Many people spend a long time unable to find the cause of this error on initial setup, so please keep it in mind.

Once you've created the index, it's also worth checking whether the RLS policy condition actually uses the index. Run EXPLAIN ANALYZE SELECT * FROM orders — if you see Index Scan using orders_tenant_id_idx, it means the query planner is handling the policy condition with an index scan.

Session Context Injection: The Most Important Link

No matter how perfect your RLS policy is, it's useless if the application doesn't put the correct value into app.current_tenant. This is actually the most vulnerable point in practice. Let's look at how to implement it in each environment.

Practical Application

Example 1: Express + PostgreSQL — The Basic Pattern

This is the most fundamental pattern. The key is the executeWithTenant helper function. It injects the tenant ID via set_config inside a transaction block, and when the transaction ends, the session variable is automatically reset so the previous tenant's context doesn't bleed into the next request.

// helpers/tenant.ts
import { PoolClient } from 'pg';
 
export async function executeWithTenant<T>(
  client: PoolClient,
  tenantId: string,
  queryFn: (client: PoolClient) => Promise<T>
): Promise<T> {
  await client.query('BEGIN');
  try {
    await client.query(
      `SELECT set_config('app.current_tenant', $1, true)`,
      [tenantId]
    );
    const result = await queryFn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  }
}

Using this helper in Express middleware means tenant isolation is automatically applied in all subsequent route handlers.

// middleware/tenant.ts
import { Request, Response, NextFunction } from 'express';
import { Pool, PoolClient } from 'pg';
import { executeWithTenant } from '../helpers/tenant';
 
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
 
export async function tenantMiddleware(
  req: Request,
  res: Response,
  next: NextFunction
) {
  const tenantId = req.user?.tenantId; // Tenant ID extracted from JWT
 
  if (!tenantId) {
    return res.status(401).json({ error: 'Tenant context missing' });
  }
 
  req.db = {
    query: async <T>(queryFn: (client: PoolClient) => Promise<T>): Promise<T> => {
      const client = await pool.connect();
      try {
        return await executeWithTenant(client, tenantId, queryFn);
      } finally {
        client.release();
      }
    },
  };
 
  next();
}

Example 2: Supabase — JWT Claims Pattern

If you're using Supabase, you can wire this up much more concisely. Note that auth.uid() and auth.jwt() are Supabase-specific built-in functions — they are not available in standard PostgreSQL environments, so be careful not to confuse the two.

-- Supabase-specific: looking up the organization via auth.uid()
CREATE POLICY tenant_isolation ON orders
  USING (
    tenant_id = (
      SELECT organization_id
      FROM memberships
      WHERE user_id = auth.uid()
    )
  );

Including organization_id directly as a claim in the JWT lets you skip the subquery, which gives better performance.

-- Reading organization_id directly from JWT claims
CREATE POLICY tenant_isolation ON orders
  USING (
    tenant_id = (auth.jwt() ->> 'organization_id')::uuid
  );

// Supabase client — JWT is automatically wired to RLS
const { data, error } = await supabase
  .from('orders')
  .select('*');
// WHERE tenant_id = <organization_id from JWT> is applied automatically

Because Supabase automatically connects the session context to the JWT, there's no need to call set_config separately. This is where the platform's convenience really shows.

Example 3: Drizzle ORM — Defining Policies in TypeScript

Since 2024, Drizzle ORM has natively supported RLS. You can declare policies directly in TypeScript code and apply them via migrations, meaning the policies are version-controlled alongside your codebase. This turns out to be quite useful in practice — a new team member can look at a single schema file and immediately know "this table is isolated with RLS."

// schema/orders.ts
import { pgTable, uuid, text, pgPolicy } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
 
export const orders = pgTable(
  'orders',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id').notNull(),
    status: text('status').notNull(),
  },
  (table) => [
    pgPolicy('tenant_isolation', {
      as: 'PERMISSIVE',
      for: 'ALL',
      to: 'authenticated',
      using: sql`${table.tenantId} = current_setting('app.current_tenant', true)::uuid`,
      withCheck: sql`${table.tenantId} = current_setting('app.current_tenant', true)::uuid`,
    }),
  ]
);

as: 'PERMISSIVE' specifies how policies are combined. PERMISSIVE (the default) combines multiple policies on the same target with OR, while RESTRICTIVE combines them with AND. When there's only one policy the difference doesn't matter, but this distinction becomes important later when adding role-based policies.

Example 4: PgBouncer Transaction Mode — Infrastructure Layer Considerations

While the previous three examples address "what tool to use with RLS," this section is about "what changes when a connection pooler is involved."

PgBouncer is middleware that multiplexes many application connections onto a small number of DB connections. When operating in transaction mode, connections are reused across multiple clients, meaning session variables (SET SESSION) can bleed into subsequent requests.

-- Dangerous approach (in PgBouncer transaction mode)
SET app.current_tenant = 'tenant-uuid';  -- Setting as a session variable
SELECT * FROM orders;
-- After the connection is returned, the session variable persists and can contaminate the next tenant's request
 
-- Correct approach: always use SET LOCAL inside a transaction block
BEGIN;
SET LOCAL app.current_tenant = 'tenant-uuid';
SELECT * FROM orders;
COMMIT;
-- After the transaction ends, app.current_tenant is automatically reset

Choosing a PgBouncer mode:

• Statement mode: Does not support transactions at all, so it cannot be used with RLS.
• Transaction mode: Using SET LOCAL inside a transaction block is mandatory.
• Session mode: SET SESSION can be used, but connection pooling efficiency is lower.

If you're using Supabase, its built-in connection pooler Supavisor isolates RLS context for you, so you don't need to worry about this issue separately.

When I first learned this, I dug through the production code I was using and found two places where I'd casually used SET SESSION. Until I fixed them, there were latent bugs lurking that weren't visible to the naked eye.

Pros and Cons Analysis

Advantages

Disadvantages and Caveats

Of all the items in the pros and cons table, the one that nearly led to an actual outage for me was the second item — context propagation vulnerability. If an error occurs in the middleware layer, it can silently reuse the previous tenant's value, and because this rarely gets caught in a test environment, it took quite a long time to find the root cause.

BYPASSRLS: An attribute that can be granted to a PostgreSQL Role. Connecting with a role that has this attribute ignores all RLS policies. It is recommended to use this only for dedicated admin roles that require cross-tenant aggregation, and never grant it to general service accounts.

Most Common Mistakes in Practice

1. Omitting FORCE ROW LEVEL SECURITY: Setting only ENABLE ROW LEVEL SECURITY means policies are ignored when connecting with the table owner account. It's recommended to always run both commands as a set.

2. Using session variables without SET LOCAL: In PgBouncer transaction mode, using SET SESSION or a plain SET means the next tenant's request can execute under the previous tenant's context after the connection is returned. It's strongly recommended to always use SET LOCAL inside a transaction block.

3. Omitting WITH CHECK: Defining only the USING clause isolates SELECT/UPDATE/DELETE, but on INSERT it's possible to directly write another tenant's tenant_id and contaminate the data. It's recommended to always specify WITH CHECK alongside it.

Closing Thoughts

RLS is a tool that creates a structure where you can't forget the WHERE clause. Even if application code introduces a bug, the DB holds the last line of defense.

Three steps you can start right now:

1. You can start by enabling RLS on just one of your existing tables. Apply ALTER TABLE ... ENABLE ROW LEVEL SECURITY without any policy and fire SELECT * FROM orders — you'll get 0 rows back, and in that moment you can see fail-closed behavior firsthand.

2. You can create a policy that includes the missing_ok parameter and integrate the executeWithTenant helper into your middleware. Once this structure is in place, you won't need to separately manage context injection for any subsequent queries.

3. You can run EXPLAIN ANALYZE SELECT * FROM orders to verify the policy hits the index. If you see both Index Scan using orders_tenant_id_idx and Filter: (tenant_id = current_setting(...)) in the execution plan, RLS is working correctly.

Once you're comfortable with the basic RLS structure, the natural next step is to layer on role-based access control — team member, admin, viewer, and so on.

References

• Multi-tenant data isolation with PostgreSQL Row Level Security | AWS Blog
• Row-level security recommendations | AWS Prescriptive Guidance
• Shipping multi-tenant SaaS using Postgres Row-Level Security | Nile
• How to Implement PostgreSQL Row Level Security for Multi-Tenant SaaS | TechBuddies
• Row Level Security for Tenants in Postgres | Crunchy Data
• Postgres RLS Implementation Guide: Best Practices and Common Pitfalls | Permit.io
• Postgres Row-Level Security (RLS) Limitations and Alternatives | Bytebase
• Supabase RLS Best Practices: Production Patterns for Secure Multi-Tenant Apps | MakerKit
• Enforcing Row Level Security in Supabase: LockIn's Multi-Tenant Architecture | DEV Community
• Drizzle ORM — Row-Level Security (RLS) Official Docs
• How to Architect a Multi-Tenant SaaS with PostgreSQL RLS | Skyline Codes
• Why PostgreSQL Row-Level Security Is the Right Approach to Django Multitenancy | DEV Community