RLS and Supabase

[user72356]

I'm using Supabase and I have recently enabled RLS. So far postgres.js has been working great as my DB client of choice in my API endpoints, but now that I have RLS enabled, I'm not sure how to issue queries as a Supabase user (auth.users table). I do use the Supabase JS client to retrieve the current user but I'm not sure what to do next. I've heard SET LOCAL current_user_id = XXX was an option but I'm not sure whether 1. it's working and 2. it's secure.

[porsager]

There are various ways, one is to use the connection object in options to set the variables if they persist for all queries. eg.

const sql = postgres(URL, {
  connection: {
    current_user_id: 123
  }
})

Another is to use select set_config('current_user_id', 123, true) eg, at the beginning of a transaction, (the last parameter says that the setting is only present in the current transaction).

[user72356]

In the first case (connection object), is there a chance that this could leak to multiple users if using pgBouncer?

[zaynetro]

I am using this helper function:

import { type Session } from "@supabase/supabase-js";

/**
 * Execute transaction in an authenticated context.
 * This will set JWT and user ID for the duration of a single transation to
 * enable RLS (Row Level Security).
 *
 * Thanks to https://github.com/prisma/prisma/issues/5128#issuecomment-1378541576
 */
export function authTxn<T>(
  session: Session,
  cb: (sql: postgres.TransactionSql) => T | Promise<T>,
): Promise<T> {
  const jwtClaim = decodeJwt(session.access_token);
  const userId = session.user.id;

  return sql.begin(async (sql) => {
    // Set JWT and user ID for this transaction to enable RLS.
    await sql`SELECT set_config('request.jwt.claim', ${jwtClaim}, TRUE)`;
    await sql`SELECT set_config('request.jwt.claim.sub', ${userId}, TRUE)`;

    return cb(sql);
  }) as Promise<T>;
}


/** Parse JWT into string without verifying it */
function decodeJwt(token: string): string {
  return atob(token.split(".")[1]);
}

[xerosanyam]

FYI, this slows down the query, by roughly 400ms

see set-config