Prisma•6mo ago

Prisma + Supabase RLS

Is there a way i could pass down the user info or whatever to implement RLS while still using the prisma ORM ?

5 Replies

You opted for speed from this bot. Let's conjure up an answer right now! Yes, you can implement Row Level Security (RLS) with Supabase and Prisma by passing user information (such as user ID or roles) to the database session using Prisma Client Extensions or middleware. The general approach is to set a session variable (e.g., with PostgreSQL's set_config or SET LOCAL) before executing your queries, so your RLS policies in Postgres can reference this context. A common pattern is to use a Prisma Client Extension that wraps each query in a transaction, first setting the relevant user context:

const getAuthPrisma = (userId: string) => {
  return prisma.$extends({
    query: {
      $allModels: {
        // @ts-expect-error: $allOperations typing
        async $allOperations({ args, query }) {
          const [, result] = await prisma.$transaction([
            prisma.$executeRaw`select set_config('user.userId', ${userId}, true)`,
            query(args)
          ]);
          return result;
        },
      },
    },
  });
};

const getAuthPrisma = (userId: string) => {
  return prisma.$extends({
    query: {
      $allModels: {
        // @ts-expect-error: $allOperations typing
        async $allOperations({ args, query }) {
          const [, result] = await prisma.$transaction([
            prisma.$executeRaw`select set_config('user.userId', ${userId}, true)`,
            query(args)
          ]);
          return result;
        },
      },
    },
  });
};

Then, in your Postgres RLS policy, you can reference the value with current_setting('user.userId', true)::text:

CREATE POLICY my_policy ON "Contract"
  USING (
    "Contract"."organizationId" = (
      SELECT "User"."organizationId"
      FROM "User"
      WHERE "User"."id" = current_setting('user.userId', true)::text
    )
  );

CREATE POLICY my_policy ON "Contract"
  USING (
    "Contract"."organizationId" = (
      SELECT "User"."organizationId"
      FROM "User"
      WHERE "User"."id" = current_setting('user.userId', true)::text
    )
  );

This approach ensures that every Prisma query runs with the correct user context, allowing your RLS policies to enforce access control at the database level. Note that you should avoid using a superuser (like postgres) for your Prisma connection, as it can bypass RLS policies—use a restricted user instead Prisma RLS Issue. For more advanced scenarios (e.g., transactions, batching), you may need to extend this pattern, but the core idea remains the same: set the user context before each query using Prisma's extensibility features Prisma RLS Example.

SPiercerOP•6mo ago

i want to use the jwt object from postgres @Prisma AI Help i want to use the jwt object from postgres

Prisma AI Help•6mo ago

🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.

Nurul•6mo ago

Hey! Was the bot answer helpful? You should be able to continue the conversation in #ask-ai Also, can you elaborate on your usecase, you want to pass jwt object to database session?

SPiercerOP•6mo ago

Yes i wanted that, but failed drastically

Gaming

Programming

Prisma + Supabase RLS

Did you find this page helpful?