Skip to main content

PostgreSQL RLS

Available in: Professional, Business, Enterprise tiers

Database-level row security with PostgreSQL Row-Level Security (RLS). Automatically enforce data isolation at the database layer with generated helpers, policies, and migrations.

Why Use PostgreSQL RLS​

Problem: Application-level security is fragile:

  • Easy to forget WHERE clauses that filter by tenant/user
  • Security bugs from missing filters in queries
  • Complex authorization logic scattered across codebase
  • No defense-in-depth at database level

Solution: Leverage PostgreSQL's built-in Row-Level Security to enforce data isolation at the database layer.

Benefits​

  • Database-Level Security: Enforced at PostgreSQL layer, not application
  • Defense in Depth: Additional security layer below Prisma
  • Auto Filtering: Queries automatically scoped to user/tenant context
  • Developer Proof: Impossible to bypass via code mistakes

Prerequisites​

# PostgreSQL database required
# Ensure your DATABASE_URL uses PostgreSQL

# Core dependencies
pnpm add @prisma/client

# PZG Pro license required

Generate​

Add to your schema.prisma:

generator pzgPro {
  provider = "node ./node_modules/prisma-zod-generator/lib/cli/pzg-pro.js"
  output = "./generated/pro"
  enablePostgresRLS = true

  // Required: this pack does not inherit `output`
  postgresRls = "{ \"outputPath\": \"./generated/pro/postgres-rls\" }"
}
Set outputPath explicitly

Unlike the other packs, PostgreSQL RLS does not read the generator's shared output directory. Left unset, it writes to postgres/rls/ relative to the directory you ran prisma generate from, which is why the files can seem to be missing. Set postgresRls.outputPath as above.

Then run:

prisma generate

Generated Files​

generated/
  pro/
    postgres-rls/
      rls-helper.ts       # RLS context management
      migration.sql       # Database setup migration
      policies.sql        # Example RLS policies
      README.md           # Applying the policies

Basic Usage​

Use the client withContext gives you

The context lives in transaction-local settings, so withContext opens one transaction, sets the context on it, and hands you that client. Queries issued on the outer prisma run in a different transaction and will not see the context.

import { PrismaClient } from '@prisma/client'
import { createRLSHelper } from '@/generated/pro/postgres-rls/rls-helper'

const prisma = new PrismaClient()
const rls = createRLSHelper(prisma)

const posts = await rls.withContext(
  { userId: 'user-123', tenantId: 'tenant-456', roles: ['admin'] },
  // Use `tx`, not `prisma`.
  async (tx) => tx.post.findMany(),
)

To check your wiring, run this inside the callback — it should return your tenant id, not an empty string:

await tx.$queryRaw`SELECT current_setting('app.current_tenant_id', true)`

Requires 2.4.1+. Earlier versions set the context in its own statement, which PostgreSQL discarded before the callback ran, so policies evaluated against an empty current_setting() and the callback took no argument.

Prisma Middleware​

Prisma 4 only

prisma.$use() does not exist on Prisma 7 — verified against a generated v7 client, where the property is undefined. Use rls.withContext(...), which sets the context and runs your queries inside one interactive transaction; set_config(..., true) is transaction-scoped, so the context has to be established and used in the same transaction.

prisma.$use(rls.createMiddleware())

// Now you can pass context via query params
await prisma.post.findMany({
  context: {
    userId: 'u1',
    tenantId: 't1'
  }
})

Database Setup​

Apply the generated migration to set up RLS helper functions:

-- From generated/pro/postgres-rls/migration.sql
-- Helper functions:
-- - set_current_user_context(user_id, tenant_id, roles)
-- - clear_user_context()
-- - get_current_user_context()

See generated SQL files for:

  • Example RLS policies
  • Helper function definitions
  • Migration scripts to adapt to your schema

Integration​

Express Middleware​

app.use(async (req, res, next) => {
  if (!req.user) {
    return res.status(401).json({ error: 'Unauthorized' })
  }

  await rls.setContext({
    userId: req.user.id,
    tenantId: req.user.tenantId,
    roles: req.user.roles
  })

  res.on('finish', async () => {
    await rls.clearContext()
  })

  next()
})

Next.js API Routes​

import { rls } from '@/lib/rls'

export default async function handler(req, res) {
  const session = await getSession(req, res)

  return rls.withContext(
    {
      userId: session.user.id,
      tenantId: session.user.tenantId,
      roles: session.user.roles
    },
    async () => {
      const posts = await prisma.post.findMany()
      res.json(posts)
    }
  )
}

See Also​