Use Supabase RLS with Drizzle

readme.md

Supabase RLS Policy functions and Postgres transaction configuration association

source

Supabase's RLS Policy functions	PgTransaction config to set	Description
auth.uid()	set_config('request.jwt.claim.sub', <current_user_uid>, true)	<current_user_uid> comes from your own way to get the current user uid
auth.email()	set_config('request.jwt.claim.email', <current_user_email>, true)	<current_user_email> The current user email
auth.role()	set_config('request.jwt.claim.role', <current_user_role>, true)	<current_user_role> The current user role
auth.jwt()	set_config('request.jwt.claim', <current_user_jwt>, true)	<current_user_jwt> The current user jwt token. 🚨 I'm note sure about the config name, found nothing in Supabase repo

These user datas can come from Supabase auth or a user table. What matters is that it matches when you use these functions in your RLS Policy

Use Supabase RLS with Drizzle Transaction

You have to use a transaction to isolate the user queries

Supabase RLS doc

const data = await db.transaction(
  async (tx) => {
    // You use `auth.jwt()` in your RLS policy
    await tx.execute(sql`SELECT set_config('request.jwt.claims', '${sql.raw(jwtClaim)}', TRUE)`);
    
    // You use `auth.uid()` in your RLS policy
    await tx.execute(sql`SELECT set_config('request.jwt.claim.sub', '${sql.raw(userUid)}', TRUE)`);
    
    // You use `auth.email()` in your RLS policy
    await tx.execute(sql`SELECT set_config('request.jwt.claim.email', '${sql.raw(userEmail)}', TRUE)`);
    
    // You use `auth.role()` in your RLS policy
    await tx.execute(sql`SELECT set_config('request.jwt.claim.role', '${sql.raw(userRole)}', TRUE)`)

    // do not use the default role (Drizzle uses your root user with `postgres` role) because it will bypass the RLS policy, set role to authenticated
    await tx.execute(sql`set local role authenticated`);

    // All the following will be run with the user context set with `set_config`
    await tx.select(...);
    await tx.update(...);

  // ...
  }
);

Maybe what Drizzle can do

const data = await db.transaction(
  async (tx) => {
    // Drizzle has applied the configuration for you
    
    // All the following will be run with the user context set by Drizzle with `set_config`
    await tx.select(...);
    await tx.update(...);

  // ...
  },
  {
    configs: [
      // You use `auth.jwt()` in your RLS policy
      {
        name: "request.jwt.claims",
        value: jwtClaim,
        isLocal: true,
      },
      // You use `auth.uid()` in your RLS policy
      {
        name: "request.jwt.claims.sub",
        value: userUid,
        isLocal: true,
      },
      // You use `auth.email()` in your RLS policy
      {
        name: "request.jwt.claims.email",
        value: userEmail,
        isLocal: true,
      },
      // You use `auth.role()` in your RLS policy
      {
        name: "request.jwt.claims.role",
        value: userRole,
        isLocal: true,
      },
    ],
    role: 'authenticated',
  }
);

Open for custom configuration helpers

async function authenticated(){
  // Your own way to get the current user session, depending on what framework you use
  const session = await getSession();

  const jwtClaim = decodeJwt(session.access_token);
  const role = session.user.role;
  const userUid = session.user.sub;
  const userEmail = session.user.email;
  const userRole = session.user.role;

  return   {
    configs: [
      // You use `auth.jwt()` in your RLS policy
      {
        name: "request.jwt.claims",
        value: jwtClaim,
        isLocal: true,
      },
      // You use `auth.uid()` in your RLS policy
      {
        name: "request.jwt.claims.sub",
        value: userUid,
        isLocal: true,
      },
      // You use `auth.email()` in your RLS policy
      {
        name: "request.jwt.claims.email",
        value: userEmail,
        isLocal: true,
      },
      // You use `auth.role()` in your RLS policy
      {
        name: "request.jwt.claims.role",
        value: userRole,
        isLocal: true,
      },
    ],
    role,
  } satisfies PgTransactionOptions
}

const data = await db.transaction(
  async (tx) => {
    // Drizzle has applied the configuration for you
    
    // All the following will be run with the user context set by Drizzle with `set_config`
    await tx.select(...);
    await tx.update(...);

  // ...
  },
  await authenticated()
);

Thanks to @zaynetro and @jhonsfran1165