PostgreSQL Row-Level Security: Multi-Tenant Isolation, Session Variables, and Audit Policies

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

---

See Also

• 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

---

Working With 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 →