Postgres Row Level Security in Practice: Multi-Tenant Data Without Application Guards

Most multi-tenant SaaS apps enforce tenant isolation at the application layer. Every query has WHERE tenant_id = ?. Every ORM model includes a tenant scope. Somewhere, there’s a middleware that sets a tenant context variable. And somewhere deeper, there’s a bug where a developer forgot to add the tenant filter.

Postgres Row Level Security (RLS) moves that enforcement into the database. Policies defined on tables control which rows each database role can see or modify. Application code that forgets the tenant filter doesn’t return cross-tenant data. It returns nothing. The database enforces it.

Supabase popularized RLS by making it required for their hosted Postgres product. But RLS predates Supabase by years and works on any Postgres instance.

The Core Concept

Without RLS, any role with SELECT permission on a table can see every row. With RLS enabled and a policy defined, the database evaluates the policy expression for each row and returns only rows where the expression is true.

-- Enable RLS on a table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Create a policy
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

Now a query like SELECT * FROM orders only returns rows where tenant_id matches the current tenant setting. The filter is invisible to the application; it always applies.

Setting the Tenant Context

Policies can use two mechanisms to identify who’s making the request: the current database role, or a session variable set by the application.

Session variables are the practical choice for multi-tenant apps. At the start of each request, the application sets a variable:

SET LOCAL app.current_tenant_id = 'abc-123';

SET LOCAL scopes the variable to the current transaction. This is important: if you use SET (without LOCAL), the variable persists for the entire database connection, which is dangerous with connection pooling where connections are reused across requests.

In a Node.js app:

async function withTenant<T>(
  tenantId: string,
  fn: () => Promise<T>
): Promise<T> {
  return db.transaction(async (trx) => {
    await trx.raw('SET LOCAL app.current_tenant_id = ?', [tenantId])
    return fn()
  })
}

// Usage
const orders = await withTenant(req.user.tenantId, () =>
  db('orders').select('*').orderBy('created_at', 'desc')
)

Every database call inside the transaction runs with the tenant context set. The policy applies automatically.

Writing Policies

Policies have two parts: USING controls which rows are visible to reads, and WITH CHECK controls which rows can be written.

-- Read-only: users can only see their own tenant's orders
CREATE POLICY orders_tenant_select ON orders
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Write: users can only insert/update rows for their own tenant
CREATE POLICY orders_tenant_modify ON orders
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY orders_tenant_update ON orders
  FOR UPDATE
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

The second argument true to current_setting makes it return NULL instead of an error when the variable isn’t set. This is important: without it, any query run outside a tenant context (migrations, admin queries) throws an error rather than returning empty results.

The FORCE ROW SECURITY Trap

By default, table owners and superusers bypass RLS entirely. A database user that owns a table won’t have its policies applied.

This is usually fine in production where your app runs as a restricted database role. But it becomes a problem if:

1. Your app connects as the Postgres superuser (don’t do this in production)
2. You’re running migration scripts that need to affect all tenants

To apply RLS even to table owners:

ALTER TABLE orders FORCE ROW LEVEL SECURITY;

With FORCE ROW LEVEL SECURITY, the policies apply to everyone including superusers. You’ll need an explicit bypass role for admin operations.

-- Create a role that bypasses RLS for admin use
CREATE ROLE admin_role BYPASSRLS;

-- Use this role only in admin/migration contexts
-- NEVER use it in the main application connection pool

Multiple Policies Per Table

Multiple policies on the same table are combined with OR. A user needs only one policy to allow a row, not all of them.

This is useful for adding admin access:

-- Regular users see only their tenant
CREATE POLICY tenant_isolation ON orders
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Admins see everything
CREATE POLICY admin_access ON orders
  FOR SELECT
  USING (current_setting('app.is_admin', true) = 'true');

An admin user sets both variables:

SET LOCAL app.current_tenant_id = 'abc-123';
SET LOCAL app.is_admin = 'true';

And sees all rows. A regular user sets only app.current_tenant_id and sees only their tenant’s data.

Performance: Does RLS Add Overhead?

The policy expression is pushed down into the query plan as a WHERE clause. If you have an index on tenant_id, the policy adds essentially no overhead. The index is used normally.

Without an index, every table scan includes the policy filter, which is fine but not optimal for large tables.

-- Make sure this index exists
CREATE INDEX idx_orders_tenant_id ON orders (tenant_id);

-- For multi-column access patterns
CREATE INDEX idx_orders_tenant_created ON orders (tenant_id, created_at DESC);

Run EXPLAIN ANALYZE on a query that goes through RLS to confirm the index is being used:

SET app.current_tenant_id = 'abc-123';
EXPLAIN ANALYZE SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;

You should see an index scan on tenant_id, not a sequential scan. If you see a sequential scan on a large table, add or adjust the index.

Connection Pooling Considerations

Connection poolers (PgBouncer, Prisma Accelerate, Supabase’s pooler) often reuse database connections across requests. This creates a problem: session variables set in one request could leak into another.

Transaction-mode pooling is safe. In transaction mode, the connection is returned to the pool after each transaction, so SET LOCAL variables are reset automatically.

Session-mode pooling is dangerous with RLS session variables. The connection persists and variables can leak. If you’re using session-mode pooling, reset variables explicitly:

async function withTenant<T>(tenantId: string, fn: () => Promise<T>): Promise<T> {
  try {
    await db.raw('SET LOCAL app.current_tenant_id = ?', [tenantId])
    return await fn()
  } finally {
    await db.raw("SET LOCAL app.current_tenant_id = ''")
  }
}

Or better: use transaction-mode pooling and SET LOCAL so cleanup is automatic.

Testing RLS Policies

Never assume a policy works. Test it explicitly.

-- Create a test role with limited permissions
CREATE ROLE test_app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO test_app_user;

-- Switch to that role
SET ROLE test_app_user;

-- Set tenant context for tenant A
SET app.current_tenant_id = 'tenant-a-uuid';

-- This should only return tenant A's rows
SELECT tenant_id, COUNT(*) FROM orders GROUP BY tenant_id;

-- Attempt to access tenant B's data directly (should return 0 rows)
SELECT COUNT(*) FROM orders WHERE tenant_id = 'tenant-b-uuid';

-- Reset
RESET ROLE;

In application tests, verify that cross-tenant data is never returned:

describe('RLS isolation', () => {
  it('should not return another tenant\'s orders', async () => {
    const orders = await withTenant('tenant-b', () =>
      db('orders').select('*')
    )
    
    orders.forEach(order => {
      expect(order.tenant_id).toBe('tenant-b')
    })
  })

  it('should not allow writing to another tenant', async () => {
    await expect(
      withTenant('tenant-b', () =>
        db('orders').insert({ tenant_id: 'tenant-a', amount: 100 })
      )
    ).rejects.toThrow()
  })
})

A Complete Setup Example

Here’s everything together for a simple multi-tenant app:

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE line_items ENABLE ROW LEVEL SECURITY;

-- Policies for orders
CREATE POLICY orders_select ON orders
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY orders_insert ON orders
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

CREATE POLICY orders_update ON orders
  FOR UPDATE
  USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- Foreign key tables need policies too
CREATE POLICY line_items_select ON line_items
  FOR SELECT
  USING (
    order_id IN (
      SELECT id FROM orders
      WHERE tenant_id = current_setting('app.current_tenant_id', true)::uuid
    )
  );

-- Indexes
CREATE INDEX idx_orders_tenant_id ON orders (tenant_id);
CREATE INDEX idx_line_items_order_id ON line_items (order_id);

-- App user (no BYPASSRLS)
CREATE ROLE app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders, line_items TO app_user;

The application always wraps database calls in a transaction:

// middleware/tenant.ts
export async function tenantMiddleware(req: Request, res: Response, next: NextFunction) {
  if (!req.user?.tenantId) {
    return res.status(401).json({ error: 'Unauthorized' })
  }

  req.db = {
    query: <T>(fn: (trx: Knex.Transaction) => Promise<T>) =>
      db.transaction(async (trx) => {
        await trx.raw('SET LOCAL app.current_tenant_id = ?', [req.user!.tenantId])
        return fn(trx)
      })
  }

  next()
}

// routes/orders.ts
router.get('/orders', async (req, res) => {
  const orders = await req.db.query(trx =>
    trx('orders').select('*').orderBy('created_at', 'desc')
  )
  res.json(orders)
})

The Key Tradeoff

RLS adds a layer of defense-in-depth against application logic bugs. It doesn’t replace careful application logic; it backs it up. A missing WHERE tenant_id = ? in application code returns empty results instead of cross-tenant data. That’s better than leaking data.

The tradeoff: RLS makes database logic less visible. Someone reading the application code won’t see why a query is returning filtered results. Document it. Add comments in your models or data access layer noting that tenant isolation is enforced at the database level. The invisibility is the feature, but you need to make sure your team knows it’s there.