Row-Level Security

How PostgreSQL RLS enforces tenant data isolation at the database level, and how Nebutra wires it through Prisma.

Row-Level Security (RLS) is a PostgreSQL feature that restricts which rows a database session can read or write, based on a policy evaluated for each row. Nebutra uses RLS as the primary mechanism for multi-tenant data isolation.

Why RLS instead of application-level filtering

Application-level WHERE tenantId = ? clauses work but have one critical weakness: a missing clause exposes all tenants' data. RLS moves the enforcement to the database engine itself, so even a query with no tenant filter is automatically restricted to the rows the current session is permitted to see.

Approach	Enforcement layer	Risk if developer forgets
`WHERE tenantId = ?`	Application code	Full data leak across tenants
Row-Level Security	PostgreSQL engine	Query returns empty set — safe by default

How `withRls` works

withRls from @nebutra/tenant returns a Prisma client extension that:

Opens a PostgreSQL transaction

Sets the session-local variable app.current_tenant_id to the provided tenantId

Executes the query — PostgreSQL evaluates RLS policies using this variable

Tears down the session variable on completion

import { db } from "@nebutra/db";
import { getCurrentTenant, withRls } from "@nebutra/tenant";

const tenant = getCurrentTenant();
const tenantDb = withRls(db, tenant.tenantId);

// PostgreSQL enforces: only rows WHERE tenant_id = 'org_abc123'
const projects = await tenantDb.project.findMany();

Under the hood, withRls executes:

SET LOCAL app.current_tenant_id = 'org_abc123';
SELECT * FROM projects;  -- RLS policy filters automatically

RLS policy examples

These are the SQL policies applied to tenant-scoped tables. Migrations in packages/platform/db/prisma/migrations/ apply them automatically.

SELECT policy (read isolation)

ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY "tenant_isolation_select"
  ON public.projects
  FOR SELECT
  USING (
    tenant_id = current_setting('app.current_tenant_id', TRUE)
  );

INSERT policy (write isolation)

CREATE POLICY "tenant_isolation_insert"
  ON public.projects
  FOR INSERT
  WITH CHECK (
    tenant_id = current_setting('app.current_tenant_id', TRUE)
  );

UPDATE and DELETE policies

CREATE POLICY "tenant_isolation_update"
  ON public.projects
  FOR UPDATE
  USING (tenant_id = current_setting('app.current_tenant_id', TRUE))
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', TRUE));

CREATE POLICY "tenant_isolation_delete"
  ON public.projects
  FOR DELETE
  USING (tenant_id = current_setting('app.current_tenant_id', TRUE));

current_setting('app.current_tenant_id', TRUE) — the second argument TRUE makes the function return NULL instead of raising an error when the variable is not set. This means queries run outside a tenant context return zero rows rather than throwing.

The `tenantId` column convention

Every table that stores tenant-specific data must include a tenantId column. The Prisma schema convention:

model Project {
  id          String   @id @default(cuid())
  name        String
  tenantId    String                         // ← required on every tenant-scoped model
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@index([tenantId])                        // ← index required for performance
  @@map("projects")
}

Never omit @@index([tenantId]) on large tables. Without an index, PostgreSQL performs a full sequential scan on every RLS policy check, which degrades query performance significantly as data grows.

Bypassing RLS for administrative operations

Some operations — such as cross-tenant analytics, billing jobs, or admin dashboards — need to query across all tenants. Use a separate database role with BYPASSRLS for these, not the application service role.

// Admin client — bypasses RLS entirely
// ONLY for internal background jobs and admin tooling
export const adminDb = new PrismaClient({
  datasources: {
    db: { url: process.env.DATABASE_ADMIN_URL },
  },
});

Never expose the adminDb client through a user-facing API route. It bypasses all tenant isolation. Restrict it to background workers, cron jobs, and internal admin tooling only.

Performance: indexes on `tenantId`

RLS policies add a filter on tenant_id to every query. For this to be fast, PostgreSQL needs an index on that column. Always add @@index([tenantId]) in the Prisma schema, which generates:

CREATE INDEX "projects_tenant_id_idx" ON "projects"("tenant_id");

For tables that are almost always queried by both tenantId and another column (e.g. status), use a composite index:

@@index([tenantId, status])

Verifying RLS is active

To confirm a table has RLS enabled and policies applied, run this query in your database:

-- Check RLS status for all tenant-scoped tables
SELECT
  tablename,
  rowsecurity AS rls_enabled,
  forcerowsecurity AS rls_forced
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;

-- List all policies on a specific table
SELECT policyname, cmd, qual, with_check
FROM pg_policies
WHERE tablename = 'projects';

Why RLS instead of application-level filtering

How `withRls` works

RLS policy examples

SELECT policy (read isolation)

INSERT policy (write isolation)

UPDATE and DELETE policies

The `tenantId` column convention

Bypassing RLS for administrative operations

Performance: indexes on `tenantId`

Verifying RLS is active

Using the DB Client

Multi-Tenancy

Schema & Migrations

Database Overview

On this page