PostgreSQL Row-Level Security: Data Isolation at the Database Layer

Most application developers handle data isolation in their application layer — every query includes a WHERE user_id = $currentUser clause, and the assumption is that developers will remember to add this filter every time, in every query, forever.

That assumption fails. Developers make mistakes. A missing WHERE clause is a data breach. Bugs get introduced during refactoring. The application layer is a fragile place to enforce a hard security boundary.

Row-Level Security (RLS) moves this enforcement to the database layer. Even if the application forgets to filter, the database enforces the policy and the user only sees their own data.

What RLS Does

When you enable RLS on a table and define policies, PostgreSQL evaluates those policies for every query on that table. A policy defines which rows a particular user can see, insert, update, or delete.

The database applies these policies before returning results — the application has no way to bypass them without elevated privileges.

Basic RLS Setup

Enable RLS on a table and create a basic policy:

-- Enable RLS on the posts table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own posts
CREATE POLICY posts_user_isolation ON posts
  USING (author_id = current_setting('app.current_user_id')::UUID);

The USING clause defines the filter for SELECT, UPDATE, and DELETE. The current_setting function reads a session variable that your application sets at the start of each request.

In your application, set the session variable before running queries:

// Set the current user for RLS
async function withUserContext<T>(
  userId: string,
  fn: () => Promise<T>
): Promise<T> {
  return await prisma.$transaction(async (tx) => {
    await tx.$executeRaw`SELECT set_config('app.current_user_id', ${userId}, true)`
    return fn()
  })
}

// Usage
const posts = await withUserContext(userId, () =>
  prisma.post.findMany()  // RLS automatically filters to this user's posts
)

The true third argument to set_config makes the setting session-local — it resets when the transaction ends.

Separate Policies for Different Operations

RLS policies can be separated by operation:

-- SELECT: users see their own posts
CREATE POLICY posts_select ON posts FOR SELECT
  USING (author_id = current_setting('app.current_user_id')::UUID);

-- INSERT: users can only insert posts they are the author of
CREATE POLICY posts_insert ON posts FOR INSERT
  WITH CHECK (author_id = current_setting('app.current_user_id')::UUID);

-- UPDATE: users can only update their own posts
CREATE POLICY posts_update ON posts FOR UPDATE
  USING (author_id = current_setting('app.current_user_id')::UUID)
  WITH CHECK (author_id = current_setting('app.current_user_id')::UUID);

-- DELETE: users can only delete their own posts
CREATE POLICY posts_delete ON posts FOR DELETE
  USING (author_id = current_setting('app.current_user_id')::UUID);

The USING clause filters which rows are visible for read operations. The WITH CHECK clause validates which rows can be written.

Multi-Tenant RLS

For SaaS applications with multiple tenants, RLS enforces tenant isolation:

-- Add tenant_id to every tenant-specific table
ALTER TABLE posts ADD COLUMN tenant_id UUID NOT NULL;
ALTER TABLE users ADD COLUMN tenant_id UUID NOT NULL;

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Policy: users see only records in their tenant
CREATE POLICY tenant_isolation ON posts
  USING (tenant_id = current_setting('app.tenant_id')::UUID);

CREATE POLICY tenant_isolation ON users
  USING (tenant_id = current_setting('app.tenant_id')::UUID);

Set the tenant context in your request middleware:

// server/middleware/tenant.ts
export default defineEventHandler(async (event) => {
  const session = await getSession(event)
  if (!session) return

  // Store tenant context for database middleware to use
  event.context.tenantId = session.user.tenantId
  event.context.userId = session.user.id
})

// Apply context before database operations
async function withTenantContext<T>(
  tenantId: string,
  userId: string,
  fn: () => Promise<T>
): Promise<T> {
  return prisma.$transaction(async (tx) => {
    await tx.$executeRaw`
      SELECT
        set_config('app.tenant_id', ${tenantId}, true),
        set_config('app.current_user_id', ${userId}, true)
    `
    return fn()
  })
}

Admin Bypass

Administrators often need to see all records regardless of RLS policies. The clean way to handle this is with a separate database role:

-- Create an admin role that bypasses RLS
CREATE ROLE app_admin;
GRANT app_admin TO your_application_user;

-- RLS does not apply to table owner or roles with BYPASSRLS
ALTER ROLE app_admin BYPASSRLS;

Or use a policy that allows admins:

CREATE POLICY posts_policy ON posts
  USING (
    author_id = current_setting('app.current_user_id')::UUID
    OR
    current_setting('app.user_role') = 'admin'
  );

For Supabase users, the service_role key bypasses RLS — this is intentional for backend admin operations. Always use the anon or authenticated keys from client-side code.

RLS With ORMs

RLS works transparently with ORMs. The ORM runs queries normally; the database applies the policies before returning results.

With Prisma, wrap every operation in a context-setting transaction:

// lib/db.ts
export async function createUserPrisma(userId: string, tenantId: string) {
  return prisma.$extends({
    query: {
      $allModels: {
        async $allOperations({ query, args }) {
          const [, result] = await prisma.$transaction([
            prisma.$executeRaw`
              SELECT
                set_config('app.current_user_id', ${userId}, true),
                set_config('app.tenant_id', ${tenantId}, true)
            `,
            query(args),
          ])
          return result
        },
      },
    },
  })
}

// In your request handler
const db = createUserPrisma(session.userId, session.tenantId)
const posts = await db.post.findMany()  // RLS automatically applied

The Prisma extension approach creates a client instance with user context baked in — all queries from that client automatically have the right RLS context.

Performance Considerations

RLS policies add a WHERE clause equivalent to every query. Well-indexed RLS columns (tenant_id, author_id) make this overhead minimal. Poor indexing makes it expensive.

Always ensure the columns referenced in your policies are indexed:

CREATE INDEX idx_posts_tenant_id ON posts(tenant_id);
CREATE INDEX idx_posts_author_id ON posts(author_id);

Verify your policies are not preventing index usage:

-- Set context for testing
SELECT set_config('app.current_user_id', '123e4567-e89b-12d3-a456-426614174000', true);

EXPLAIN ANALYZE
SELECT * FROM posts WHERE status = 'published' ORDER BY created_at DESC LIMIT 20;

If RLS causes a seq scan where you would otherwise have an index scan, adjust your policy or add a composite index that covers both the RLS filter and the query filter.

When to Use RLS

RLS is particularly valuable for:

Multi-tenant SaaS applications: Tenant isolation enforced at the database layer is the most reliable defense against cross-tenant data access bugs.

Healthcare and financial applications: When regulatory compliance requires data isolation, RLS provides a documented, enforceable boundary.

Applications with complex access control: When access rules are complex but stable, encoding them in database policies is more reliable than application code.

Supabase applications: Supabase's architecture assumes RLS and provides tooling that makes it easy to use.

RLS is not a replacement for application-layer authorization — you still need to verify that a user is allowed to perform an action before trying to perform it. But it is an excellent defense-in-depth layer that catches bugs that would otherwise become data breaches.

Designing the security architecture for a multi-tenant application or implementing RLS for the first time? I can help you think through the policy design. Book a call: calendly.com/jamesrossjr.