I'm trying to add Middleware to Prisma for multitenancy / row level security, which sets auth.principle in a transaction before actual query is ran. Auth.principle is used in DB-level in RLS-policy. Concept works if I create own method to prisma.service, but usability would be nicer if this could be done in a middleware with $use-notation or something similar. Any suggestions? Working solution with bad usability:

Prisma.service:
  async auth(func: any): Promise<any> {

    [ignore, result] = await this.$transaction([
      this.$executeRaw`SET local "auth.principal" = "1234567890"`,
      func,
    ]);
    return result;
  }


Usage:
async getUsers(): Promise<User[]>{
    return this.prisma.auth(this.prisma.user.findMany());
}

Tryout with $use, but queries are not run inside the same transaction -> auth.principal is not set when actual query is ran resulting empty array.

Prisma.service:
    this.$use(async (params, next) => {
      // Set auth variable in a transaction
      const setTransActionResult = await next({
        args: {
          query: 'SET local "auth.principal" = "1234567890"',
          parameters: {
            values: '[]',
            __prismaRawParamaters__: true,
          },
        },
        dataPath: [],
        runInTransaction: true,
        action: 'executeRaw',
        model: undefined,
      });

      params.runInTransaction = true;
      // Run original query here
      return await next(params);
    });

Usage: 

async getUsers(): Promise<User[]>{
    return this.prisma.user.findMany();
}

Hey 👋 Cerbos might be of interest for your use case. They do have integration with Prisma and can be useful in cases of multi-tenancy and achieving RLS.

Thanks, this looks interesting!

I haven’t used this personally yet, But from their official tutorial, I could say that authorization happens before invoking the prisma query. They do have an active slack community as well if you have any questions.

Yeah, I think so too. So cerbos is just using Prisma for data storage for roles and authorization. So it's not preventing developers making mistakes in Prisma query level like RLS does