使用Prisma客户端扩展实现PostgreSQL RLS失效，求排查

我按照Prisma客户端扩展RLS指南实现，但RLS未生效，无法限制跨公司数据访问，麻烦帮忙排查哪里出错了？

相关代码片段 #

migration.sql（RLS策略定义）

-- Enable Row Level Security
ALTER TABLE "User" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Company" ENABLE ROW LEVEL SECURITY;

-- Force Row Level Security for table owners
ALTER TABLE "User" FORCE ROW LEVEL SECURITY;
ALTER TABLE "Company" FORCE ROW LEVEL SECURITY;

-- Create row security policies
CREATE POLICY tenant_isolation_policy ON "Company" USING ("id" = current_setting('app.current_company_id', TRUE)::uuid);
CREATE POL

schema.prisma（模型与配置）

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["clientExtensions"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Company {
  id    String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name  String
  users User[]
}

model User {
  id        String  @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  companyId String  @default(dbgenerated("(current_setting('app.current_company_id'::text))::uuid")) @db.Uuid
  email     String  @unique
  company   Company @relation(fields: [companyId], references: [id], onDelete: Cascade)
}

script.js（测试脚本）

//Set a company_id as current_company_id for RLS Validation
const setUserId = await prisma.$executeRaw`SET app.current_company_id = 'id_of_a_company';`;
// Create a given company with a user
await prisma.company.create({
        data: {
          name: "Company 1",
          users: {
            create: {
              email: "user1@company1.com",
            },
          },
        },
      });
// Try to query users from Company 1 as a user who should have access
    const company1Users = await prisma.user.findMany({
        where: {
        company: {
            name: "Company 1",
        },
        },
    });
    // Try to query users from Company 2 as a user who should not have access
    const company2Users = await prisma.user.findMany({
        where: {
        company: {
            name: "Company 2",
        },
        },
    });

错误分析与修复方案 #

1. RLS策略严重不完整

• migration.sql中CREATE POL语句未写完，User表完全没有定义RLS策略，导致User表的RLS仅开启但无执行规则，自然无法限制访问。
• Company表仅定义了USING（读取限制），缺少WITH CHECK（写入限制），会导致创建/修改Company时不受RLS约束，完全绕开隔离规则。

修复：补全RLS策略

-- Enable Row Level Security
ALTER TABLE "User" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Company" ENABLE ROW LEVEL SECURITY;

-- Force Row Level Security for table owners
ALTER TABLE "User" FORCE ROW LEVEL SECURITY;
ALTER TABLE "Company" FORCE ROW LEVEL SECURITY;

-- Create row security policies for Company（读写都限制）
CREATE POLICY tenant_isolation_policy ON "Company" 
USING ("id" = current_setting('app.current_company_id', TRUE)::uuid)
WITH CHECK ("id" = current_setting('app.current_company_id', TRUE)::uuid);

-- Create row security policies for User（读写都限制）
CREATE POLICY user_tenant_isolation_policy ON "User"
USING ("companyId" = current_setting('app.current_company_id', TRUE)::uuid)
WITH CHECK ("companyId" = current_setting('app.current_company_id', TRUE)::uuid);

2. 未实现Prisma客户端扩展绑定上下文

仅在schema中开启了clientExtensions预览特性，但没有编写扩展代码自动在每个请求中设置app.current_company_id。测试脚本中用$executeRaw执行SET仅对当前连接生效，而Prisma使用连接池，后续请求可能复用未设置该变量的连接，导致RLS规则失效。

修复：实现客户端扩展

// prisma/client.js
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient().$extends({
  query: {
    $allModels: {
      async $allOperations({ args, query, operation }) {
        // 实际应用中替换为从请求上下文获取当前公司ID的逻辑
        const currentCompanyId = process.env.CURRENT_COMPANY_ID; 
        if (currentCompanyId) {
          await prisma.$executeRaw`SET app.current_company_id = ${currentCompanyId};`;
        }
        return query(args);
      },
    },
  },
})

export default prisma

3. 测试逻辑矛盾

测试脚本中先设置app.current_company_id = 'id_of_a_company'，但创建的Company1是自动生成UUID，与设置的ID不一致，再加上Company表缺少写入限制策略，导致创建操作绕开RLS，后续查询的验证逻辑完全混乱。

修复：修正测试脚本

// 指定测试用的公司ID
const testCompanyId = 'your-test-uuid-here';
// 设置当前公司ID
await prisma.$executeRaw`SET app.current_company_id = ${testCompanyId};`;
// 创建指定ID的Company和关联User
await prisma.company.create({
  data: {
    id: testCompanyId,
    name: "Company 1",
    users: {
      create: {
        email: "user1@company1.com",
      },
    },
  },
});

// 后续查询会自动受RLS限制
const company1Users = await prisma.user.findMany(); // 仅返回当前公司的用户
const company2Users = await prisma.user.findMany({
  where: { company: { name: "Company 2" } },
}); // 返回空数组，无权限访问

内容的提问来源于stack exchange，提问作者Saksham arya