PostgreSQL Row-Level Security: Multi-Tenant Isolation

Quick answer. PostgreSQL Row-Level Security enforces tenant isolation at the storage layer: after enabling policies and setting session context, a plain SELECT returns only rows the current session may see. A forgotten WHERE clause can't leak data across tenants because invisible rows are never returned, even on misconfigured queries. Row-Level Security moves tenant isolation from application code into the database. Instead of adding WHERE tenant_id = $currentTenant to every query and hoping no developer forgets it, RLS enforces it at the storage layer — the database literally returns only rows the current session is allowed to see. A misconfigured application query can't leak data between tenants because the rows aren't visible.

This post covers RLS from first principles: enabling policies, setting session context, using security-definer functions to avoid privilege issues, and combining RLS with audit policies for compliance.

How RLS Works

-- Without RLS: application must remember to filter
SELECT * FROM posts;  -- Returns ALL posts from ALL tenants
-- If app forgets WHERE clause → data leak

-- With RLS enabled + policy set:
SELECT * FROM posts;  -- Returns ONLY posts where tenant_id = current_setting('app.tenant_id')
-- Even if app omits WHERE clause → safe

RLS is enforced for all users except superusers and table owners unless you explicitly SET ROW SECURITY = FORCE ROW LEVEL SECURITY or the user bypasses with BYPASSRLS.

---

1. Basic Tenant Isolation

-- Enable RLS on tables that contain tenant-specific data
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE posts FORCE ROW LEVEL SECURITY; -- Apply even to table owner

-- Create policy: users see only their tenant's rows
CREATE POLICY tenant_isolation ON posts
  USING (tenant_id = current_setting('app.tenant_id', true)::UUID);

-- The policy applies to SELECT, UPDATE, DELETE, and INSERT by default
-- 'true' in current_setting means "return NULL if not set" instead of throwing

-- Separate policies for different operations (more control)
DROP POLICY tenant_isolation ON posts;

CREATE POLICY posts_select ON posts
  FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id', true)::UUID);

CREATE POLICY posts_insert ON posts
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::UUID);

CREATE POLICY posts_update ON posts
  FOR UPDATE
  USING (tenant_id = current_setting('app.tenant_id', true)::UUID)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::UUID);

CREATE POLICY posts_delete ON posts
  FOR DELETE
  USING (tenant_id = current_setting('app.tenant_id', true)::UUID);

Setting Session Context in the Application

// src/lib/db/rls-client.ts
import { PrismaClient } from '@prisma/client';
import { AsyncLocalStorage } from 'async_hooks';

interface RLSContext {
  tenantId: string;
  userId: string;
  roles: string[];
}

const contextStorage = new AsyncLocalStorage<RLSContext>();

export function runWithTenantContext<T>(
  context: RLSContext,
  fn: () => Promise<T>
): Promise<T> {
  return contextStorage.run(context, fn);
}

export function getCurrentContext(): RLSContext | undefined {
  return contextStorage.getStore();
}

// Prisma extension: set session variables before every query
export const db = new PrismaClient().$extends({
  query: {
    $allModels: {
      async $allOperations({ args, query }) {
        const context = getCurrentContext();

        if (!context) {
          // No context = system/admin operation — RLS policies won't apply
          // (only safe if using a restricted app role, not superuser)
          return query(args);
        }

        // Use $transaction to set session variables + run query atomically
        return db.$transaction(async (tx) => {
          // SET LOCAL applies only to this transaction
          await tx.$executeRaw`
            SELECT set_config('app.tenant_id', ${context.tenantId}, true),
                   set_config('app.user_id', ${context.userId}, true),
                   set_config('app.roles', ${context.roles.join(',')}, true)
          `;

          return query(args);
        });
      },
    },
  },
});

// Middleware: extract tenant context from request headers and run handler
export function withRLS(tenantId: string, userId: string, roles: string[]) {
  return <T>(fn: () => Promise<T>): Promise<T> =>
    runWithTenantContext({ tenantId, userId, roles }, fn);
}

Next.js Route Handler with RLS Context

// src/app/api/posts/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { getServerSession } from 'next-auth';
import { db, withRLS } from '../../../lib/db/rls-client';

export async function GET(req: NextRequest) {
  const session = await getServerSession();
  if (!session?.user) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
  }

  // Run all DB queries within this handler under the tenant's RLS context
  const posts = await withRLS(
    session.user.tenantId,
    session.user.id,
    session.user.roles
  )(() =>
    // RLS automatically filters to current tenant — no WHERE needed!
    db.post.findMany({ orderBy: { createdAt: 'desc' }, take: 20 })
  );

  return NextResponse.json({ posts });
}

---

2. Role-Based RLS Policies

-- Policy that combines tenant isolation + role-based access
CREATE POLICY posts_role_policy ON posts
  FOR ALL
  USING (
    tenant_id = current_setting('app.tenant_id', true)::UUID
    AND (
      -- Admins see everything in their tenant
      'admin' = ANY(string_to_array(current_setting('app.roles', true), ','))
      OR
      -- Authors see only their own posts
      (
        'author' = ANY(string_to_array(current_setting('app.roles', true), ','))
        AND author_id = current_setting('app.user_id', true)::UUID
      )
      OR
      -- Viewers see only published posts
      (
        'viewer' = ANY(string_to_array(current_setting('app.roles', true), ','))
        AND status = 'published'
      )
    )
  );

---

3. Security-Definer Functions for Cross-Tenant Operations

Superuser/admin operations (generating reports, backups, analytics) need to bypass RLS. Use SECURITY DEFINER functions to grant controlled cross-tenant access without exposing credentials.

-- Function runs as its OWNER (who has BYPASSRLS), not the calling user
CREATE OR REPLACE FUNCTION get_tenant_stats(p_tenant_id UUID)
RETURNS TABLE(
  total_posts BIGINT,
  published_posts BIGINT,
  total_users BIGINT
)
LANGUAGE plpgsql
SECURITY DEFINER  -- Executes as function owner, bypasses RLS
SET search_path = public  -- Prevent search_path injection
AS $$
BEGIN
  -- Validate caller has permission to request stats for this tenant
  IF current_setting('app.roles', true) NOT LIKE '%admin%' THEN
    RAISE EXCEPTION 'Permission denied: admin role required';
  END IF;

  RETURN QUERY
  SELECT
    COUNT(*)::BIGINT,
    COUNT(*) FILTER (WHERE status = 'published')::BIGINT,
    (SELECT COUNT(*)::BIGINT FROM users WHERE tenant_id = p_tenant_id)
  FROM posts
  WHERE tenant_id = p_tenant_id;
END;
$$;

-- Grant execute only to the app role (not superuser access)
GRANT EXECUTE ON FUNCTION get_tenant_stats(UUID) TO app_role;

---

4. RLS with Audit Policies

-- Audit table (no RLS — system-level, admin access only)
CREATE TABLE audit_log (
  id          BIGSERIAL   PRIMARY KEY,
  table_name  TEXT        NOT NULL,
  operation   TEXT        NOT NULL, -- INSERT/UPDATE/DELETE
  tenant_id   UUID,
  user_id     UUID,
  row_id      UUID,
  old_data    JSONB,
  new_data    JSONB,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Don't enable RLS on audit_log — it's for system use only
-- Restrict access via GRANT instead:
REVOKE ALL ON audit_log FROM app_role;
GRANT INSERT ON audit_log TO app_role; -- Only INSERT allowed

-- Audit trigger function
CREATE OR REPLACE FUNCTION audit_row_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO audit_log (
    table_name, operation, tenant_id, user_id, row_id, old_data, new_data
  ) VALUES (
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(
      (CASE WHEN TG_OP = 'DELETE' THEN OLD.tenant_id ELSE NEW.tenant_id END),
      current_setting('app.tenant_id', true)::UUID
    ),
    current_setting('app.user_id', true)::UUID,
    CASE WHEN TG_OP = 'DELETE' THEN OLD.id ELSE NEW.id END,
    CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE to_jsonb(OLD) END,
    CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE to_jsonb(NEW) END
  );
  RETURN NULL; -- AFTER trigger, return value ignored
END;
$$;

-- Attach audit trigger to sensitive tables
CREATE TRIGGER audit_posts
  AFTER INSERT OR UPDATE OR DELETE ON posts
  FOR EACH ROW EXECUTE FUNCTION audit_row_changes();

CREATE TRIGGER audit_subscriptions
  AFTER INSERT OR UPDATE OR DELETE ON subscriptions
  FOR EACH ROW EXECUTE FUNCTION audit_row_changes();

---

5. Testing RLS Policies

-- Test RLS as a specific tenant — without needing separate DB connections
BEGIN;

-- Simulate tenant context
SELECT set_config('app.tenant_id', 'tenant-a-uuid', true);
SELECT set_config('app.user_id', 'user-1-uuid', true);
SELECT set_config('app.roles', 'viewer', true);

-- This should return ONLY tenant-a's published posts
SELECT id, title, tenant_id, status FROM posts;

-- This should fail the INSERT check (wrong tenant)
INSERT INTO posts (tenant_id, title, author_id, status)
VALUES ('tenant-b-uuid', 'Hack attempt', 'user-1-uuid', 'published');
-- ERROR: new row violates row-level security policy

ROLLBACK; -- Reset session

// src/lib/db/__tests__/rls.test.ts
import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import { PrismaClient } from '@prisma/client';

// Use a test database with RLS enabled
const db = new PrismaClient({
  datasourceUrl: process.env.TEST_DATABASE_URL_RLS,
});

describe('Row-Level Security', () => {
  const tenantA = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
  const tenantB = 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb';

  it('tenant A cannot see tenant B posts', async () => {
    // Set context to Tenant A
    await db.$executeRaw`
      SELECT set_config('app.tenant_id', ${tenantA}, true),
             set_config('app.roles', 'admin', true)
    `;

    const posts = await db.post.findMany();

    // All returned posts must belong to Tenant A
    for (const post of posts) {
      expect(post.tenantId).toBe(tenantA);
    }
  });

  it('insert with wrong tenant_id is rejected', async () => {
    await db.$executeRaw`
      SELECT set_config('app.tenant_id', ${tenantA}, true),
             set_config('app.roles', 'admin', true)
    `;

    await expect(
      db.post.create({
        data: {
          tenantId: tenantB,  // Wrong tenant!
          title: 'Cross-tenant insert attempt',
          authorId: 'some-user',
          status: 'draft',
        },
      })
    ).rejects.toThrow(); // RLS blocks the INSERT
  });
});

---

Performance Considerations

-- RLS filters are evaluated per row — ensure the policy expression is indexed
-- Good: tenant_id is almost always indexed already
CREATE INDEX idx_posts_tenant ON posts (tenant_id);

-- Check that RLS policy uses index (look for Bitmap Index Scan or Index Scan)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM posts
WHERE title ILIKE '%search%';
-- Should show Index Cond: (tenant_id = '...'::uuid) — using the index

-- If you see Seq Scan despite index:
-- 1. Run ANALYZE on the table
-- 2. Check if planner underestimates row count (use pg_stats)
-- 3. Consider partial index on tenant_id + frequently filtered columns

---

Cost Reference

---

You Might Also Like

• Multi-Tenant SaaS Architecture: Shared vs Isolated Database
• SaaS Audit Logging: Immutable Trails and SOC2 Compliance
• PostgreSQL Performance: Indexes, Query Plans, and Connection Pooling
• SaaS User Permissions: RBAC, ABAC, and OPA Integration
• Database Schema Versioning: Flyway, Liquibase, and Prisma Migrate

---

Our Approach at Viprasol

Building a multi-tenant SaaS and relying on application-level WHERE clauses for tenant isolation? One missing filter exposes every customer's data. We implement PostgreSQL Row-Level Security with proper session variable patterns, security-definer functions for admin operations, and audit triggers — giving you database-enforced isolation that can't be bypassed by application bugs.

Talk to our team → | Explore our cloud solutions →

PostgreSQL Official Documentation on JSONB and Row-Level Security

When you combine flexible JSONB columns with strict tenant isolation, the PostgreSQL official documentation on JSONB and row level security is the source of truth worth reading closely. RLS policies are evaluated as boolean expressions against each row, and those expressions can reference JSONB fields directly. A common multi-tenant pattern stores tenant metadata inside a JSONB column and writes a policy that compares a key in that document against the current session setting holding the tenant identifier. This keeps schema-flexible data and access control in one layer. Two cautions the docs emphasize: index your JSONB tenant key with a GIN or expression index so policy checks stay fast, and remember that table owners and roles with the BYPASSRLS attribute skip policies entirely. At Viprasol Tech, our senior engineers design these isolation models with full ownership, end to end.