DT
Drizzle Team•3mo ago
Liam

RLS - Typesafety

I noticed in the docs the created RLS client doesn't have your schema passed to it, meaning you wouldn't be able to use dbClient.query - docs here: https://orm.drizzle.team/docs/rls Is there a reason for this? In my testing, if you pass the same Database type your usual client returns, you get full functionality. This might just be a docs miss, but want to be sure in case this was done this way for a reason!
Drizzle ORM - Row-Level Security (RLS)
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
2 Replies
Liam
LiamOP•3mo ago
export function createDrizzle<
  Database extends PostgresJsDatabase<typeof schema>, // Note the type here
  Token extends SupabaseToken = SupabaseToken,
>(token: Token, { admin, client }: { admin: Database; client: Database }) {
  return {
    admin,
    rls: (async (transaction, ...rest) => {
      return client.transaction(
        async (tx) => {
          await tx.execute(sql`
          -- auth.uid()
          select set_config('request.jwt.claim.sub', '${sql.raw(token.sub ?? "")}', TRUE);                                                
          `);
          await tx.execute(sql`
          -- role
            set local role postgres;                                            
          `);
          await tx.execute(sql`
          -- role
            set local role ${sql.raw(token.role ?? "anon")};                                            
          `);

          const result = await transaction(tx);
          await tx.execute(sql`
            -- reset
            select set_config('request.jwt.claim.sub', NULL, TRUE);
            `);
          await tx.execute(sql`
            -- reset
            reset role;
            `);
          return result;
        },
        ...rest,
      );
    }) as typeof client.transaction,
  };
}
export function createDrizzle<
  Database extends PostgresJsDatabase<typeof schema>, // Note the type here
  Token extends SupabaseToken = SupabaseToken,
>(token: Token, { admin, client }: { admin: Database; client: Database }) {
  return {
    admin,
    rls: (async (transaction, ...rest) => {
      return client.transaction(
        async (tx) => {
          await tx.execute(sql`
          -- auth.uid()
          select set_config('request.jwt.claim.sub', '${sql.raw(token.sub ?? "")}', TRUE);                                                
          `);
          await tx.execute(sql`
          -- role
            set local role postgres;                                            
          `);
          await tx.execute(sql`
          -- role
            set local role ${sql.raw(token.role ?? "anon")};                                            
          `);

          const result = await transaction(tx);
          await tx.execute(sql`
            -- reset
            select set_config('request.jwt.claim.sub', NULL, TRUE);
            `);
          await tx.execute(sql`
            -- reset
            reset role;
            `);
          return result;
        },
        ...rest,
      );
    }) as typeof client.transaction,
  };
}
And then an example working query: 
return await ctx.rlsDb((tx) =>
  tx.query.job.findFirst({
    where: eq(job.id, input.id),
      with: {
        location: true,
        mainPosition: true,
        client: true,
        placements: {
        with: {
        operative: true,
        },
      },
    },
  }),
);
return await ctx.rlsDb((tx) =>
  tx.query.job.findFirst({
    where: eq(job.id, input.id),
      with: {
        location: true,
        mainPosition: true,
        client: true,
        placements: {
        with: {
        operative: true,
        },
      },
    },
  }),
);
Angelelz
Angelelz•2mo ago
You are right, the docs just show an example You are still able to use the RQB as always

Did you find this page helpful?