Problem When using Prisma with PostgreSQL Row Level Security (RLS), nested queries (using include) lose the RLS context, causing errors or potential security issues. The error manifests as:

Invalid `prisma.note.findUnique()` invocation:
Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(PostgresError { code: "22P02", message: "invalid input syntax for type uuid: \"\"", severity: "ERROR", detail: None, column: None, hint: None }), transient: false })

Setup

PostgreSQL RLS Configuration:

-- Enable RLS on Patient table
ALTER TABLE "Patient" ENABLE ROW LEVEL SECURITY;

-- Create policy using tenant ID
CREATE POLICY tenant_isolation_policy ON "Patient"
    USING ("userId" = current_setting('app.current_tenant_id')::uuid);

Prisma Client Setup:

// prisma.ts
import { PrismaClient } from '@prisma/client';

const prismaClientSingleton = () => {
    return new PrismaClient({
        log: ['query', 'info', 'warn', 'error'],
    }).$extends(extCreateDefaultSettings);
};

const db = globalThis.prismaGlobal ?? prismaClientSingleton();

export const prismaAuth = (userId: string) => {
    return db.$extends(extSetContext({ 
        tenantId: userId, 
        bypassRls: false 
    }));
};

Prisma Extension for RLS:

export type TenantContext = {
    tenantId?: string;
    bypassRls?: boolean;
};

export const extSetContext = (context?: TenantContext) => {
    return Prisma.defineExtension((prisma) => {
        return prisma.$extends({
            query: {
                $allModels: {
                    async $allOperations({ args, query }) {
                        const [setTenant, setBypass, verify, result] = 
                            await prisma.$transaction([
                                prisma.$executeRaw`
                                    SELECT set_config('app.current_tenant_id', 
                                    ${context?.tenantId || ''}, TRUE)`,
                                prisma.$executeRaw`
                                    SELECT set_config('app.bypass_rls', 
                                    ${context?.bypassRls ? 'on' : 'off'}, TRUE)`,
                                prisma.$queryRaw`
                                    SELECT current_setting('app.current_tenant_id')`,
                                query(args)
                            ]);
                        return result;
                    },
                },
            },
        });
    });
};

Repository Implementation That Fails:

class NoteRepository {
    constructor(private prisma: ExtendedPrismaClient) {}

    async update(id: string, data: Prisma.NoteUpdateInput) {
        // This fails because nested queries lose RLS context
        return this.prisma.note.update({
            where: { id },
            data,
            include: {
                patient: true, // This loses RLS context
                user: true    // This works because User table doesn't have RLS
            }
        });
    }
}

The Issue I think the problem occurs because: 1. Prisma executes nested queries (include) as separate operations 2. Each nested query needs its own RLS context 3. The PostgreSQL session variables set for the main query don't propagate to nested queries 4. This results in the RLS context being lost for included relations enter code here

Working Solution Split the queries to maintain proper RLS context

class NoteRepository {
    constructor(private prisma: ExtendedPrismaClient) {}

    async update(id: string, data: Prisma.NoteUpdateInput) {
        // Main update query
        const note = await this.prisma.note.update({
            where: { id },
            data,
        });

        // Separate queries for relations
        const [user, patient] = await Promise.all([
            note.userId ? 
                this.prisma.user.findUnique({
                    where: { id: note.userId }
                }) : null,
            note.patientId ? 
                this.prisma.patient.findUnique({
                    where: { id: note.patientId }
                }) : null
        ]);

        return {
            ...note,
            user,
            patient
        };
    }
}

Questions

1. Is this a known limitation of Prisma with RLS?

2. Is there a way to maintain RLS context in nested queries?