Postgres RLS for Multi-Tenant AI: Tenant Isolation Done Right (2026)

TL;DR — When AI agents start writing SQL on behalf of users, app-layer tenant filtering becomes a liability. RLS pushes the tenant_id check into the database where no agent can route around it. Set up correctly, it adds <5% query overhead.

What you'll build

A multi-tenant schema where every AI agent query — direct, ORM-generated, or LLM-emitted — is automatically scoped to the calling tenant via session-level GUC variables and RLS policies.

Schema

CREATE TABLE conversations (
  id BIGSERIAL PRIMARY KEY,
  tenant_id UUID NOT NULL,
  agent_id UUID NOT NULL,
  transcript TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE conversations FORCE ROW LEVEL SECURITY;

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

CREATE INDEX conversations_tenant_idx ON conversations (tenant_id, created_at DESC);

Architecture

flowchart TD
  REQ[HTTP request] --> AUTH[Auth middleware]
  AUTH --> SET[SET app.tenant_id GUC]
  SET --> APP[App + Prisma]
  APP --> AGENT[AI agent SQL]
  AGENT --> PG[(Postgres + RLS)]
  PG -->|Policy enforced| ROWS[Only tenant rows]

Step 1 — Set the tenant per request

// Express / Next middleware
import { prisma } from "@/lib/db";

export async function withTenant<T>(tenantId: string, fn: () => Promise<T>): Promise<T> {
  return prisma.$transaction(async (tx) => {
    await tx.$executeRawUnsafe(
      `SET LOCAL app.tenant_id = '${tenantId}'`,
    );
    return fn();
  });
}

SET LOCAL is critical — it scopes the GUC to the current transaction so pooled connections don't leak.

Step 2 — Use a non-superuser app role

CREATE ROLE app_user NOINHERIT LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Superusers bypass RLS — never connect your app as one.

Step 3 — FORCE RLS on table owners

By default, table owners bypass RLS. FORCE ROW LEVEL SECURITY makes the policy apply to everyone except true superusers.

Step 4 — Test with two tenants

SET app.tenant_id = '11111111-1111-1111-1111-111111111111';
SELECT count(*) FROM conversations;  -- only tenant A

SET app.tenant_id = '22222222-2222-2222-2222-222222222222';
SELECT count(*) FROM conversations;  -- only tenant B

RESET app.tenant_id;
SELECT count(*) FROM conversations;  -- 0 rows (or error if NOT NULL cast)

Step 5 — Handle WITH CHECK for INSERTs

The WITH CHECK half ensures inserts also satisfy the policy. Without it, an attacker could write rows for a different tenant.

INSERT INTO conversations (tenant_id, agent_id, transcript)
VALUES ('22222222-...'::uuid, ...);
-- ERROR: new row violates row-level security policy

Step 6 — Index policies for performance

RLS is a transparent AND tenant_id = $1 appended to every query. Make sure every queried table has an index that leads with tenant_id.

CREATE INDEX conversations_tenant_created_idx
  ON conversations (tenant_id, created_at DESC);

Pitfalls

• Forgetting FORCE ROW LEVEL SECURITY — table owner bypasses by default.
• Using SET (not SET LOCAL) in a pooled connection — the GUC leaks to the next request.
• Connecting as superuser — RLS doesn't apply. Always use a constrained role.
• Casting in policy — tenant_id = current_setting(...)::uuid is fine, but mismatched types silently disable index usage.
• Subqueries that leak — security-definer functions can bypass RLS; audit them carefully.

CallSphere production note

OneRoof — CallSphere's property-management vertical — relies on RLS across 115+ DB tables to keep landlords from ever seeing each other's tenants, leases, or conversation history. Healthcare and Behavioral Health get the same treatment plus a separate healthcare_voice Prisma schema for HIPAA defense-in-depth; UrackIT layers RLS over Supabase + ChromaDB. 37 agents · 90+ tools · 6 verticals. Plans: $149/$499/$1,499 — 14-day trial, 22% affiliate.

FAQ

Q: RLS or schema-per-tenant? RLS for >50 tenants. Schema-per-tenant scales tooling badly past that.

Q: Performance overhead? 2-5% on indexed queries when policy uses current_setting + indexed column. Measure with EXPLAIN.

Q: Can RLS replace permissions in my app? RLS handles row visibility. App-layer authz still owns RBAC, audit logging, and rate limits.

Q: Does Prisma support RLS? Yes — set the GUC in a transaction, then call your Prisma queries inside it.

Q: How do I let admins see all tenants? Use a separate role with BYPASSRLS, or include OR is_admin() in the policy.

Sources

• AWS — Multi-tenant data isolation with RLS
• Crunchy Data — Row Level Security for tenants
• Permit.io — Postgres RLS implementation guide
• Nile — Multi-tenant RLS