Better Auth•9mo ago

integrating with postgres rls (supabase)

hi. i'm trying to migrate off of supabase auth so i wanted to go with better-auth. my question is, how could i correctly use the JWT integration to setup RLS auth? do i need to always fetch the endpoint to get the JWT token, or could i get it auto-provided every time? that would be much simpler for setup i need to setup RLS for maybe two days with supabase and then i'm migrating to neon (want auth and db to be staggered, don't want a big screwup to hit all at once)

38 Replies

bekacru•9mo ago

@daveycodez might help but check the jwks plugin https://www.better-auth.com/docs/plugins/jwt

JWT | Better Auth

Authenticate users with JWT tokens in services that can't use the session

oof2win2OP•9mo ago

yeah i saw that plugin + the usage davey has (https://discord.com/channels/1288403910284935179/1296058482289676320/1337526387967266847), just wondering how to set it up what complicates it more i feel is me migrating auth and then db - it makes sense to do like this but makes some parts more annoying / painful

daveycodez•9mo ago

The best way atm is to use the Tanstack library

daveycodez•9mo ago

https://github.com/daveyplate/better-auth-tanstack

GitHub

GitHub - daveyplate/better-auth-tanstack

Contribute to daveyplate/better-auth-tanstack development by creating an account on GitHub.

daveycodez•9mo ago

This provides the useToken hook

oof2win2OP•9mo ago

hell yeah, thank you hey @daveycodez, do you by chance have any knowledge on how to get supabase working with better-auth? i've looked at your package, but i have no idea how to get supabase to cooperate with the better-auth tokens - do you know how by chance? i have this so far

import { Database } from "@repo/types/supabase";
import { createBrowserClient } from "@supabase/ssr";
import { useToken } from "./use-auth-hooks";

export function useSupabase() {
  const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
  const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!;

  const { token } = useToken();

  if (!supabaseUrl || !supabaseAnonKey) {
    throw new Error("Missing Supabase environment variables");
  }

  const supabase = createBrowserClient<Database>(supabaseUrl, supabaseAnonKey, {
    accessToken: token || "",
  });

  return supabase;
}

import { Database } from "@repo/types/supabase";
import { createBrowserClient } from "@supabase/ssr";
import { useToken } from "./use-auth-hooks";

export function useSupabase() {
  const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
  const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!;

  const { token } = useToken();

  if (!supabaseUrl || !supabaseAnonKey) {
    throw new Error("Missing Supabase environment variables");
  }

  const supabase = createBrowserClient<Database>(supabaseUrl, supabaseAnonKey, {
    accessToken: token || "",
  });

  return supabase;
}

turns out i just had to forge my auth token on an api route and put some cache headers on it, kinda sick tbh

daveycodez•9mo ago

Got it to work? great One day I would suggest to migrate to Neon and implement a rate limiter, Supabase can be a lil riskay

oof2win2OP•9mo ago

yeah i know that's the next step - just need to setup proper auth so that i can migrate off supabase part by part @daveycodez i've been looking more and more into this, saw the issue here and convos in discord. how would you ideally have the jwt support work? like it's just stored in the session or something so it can be used on both the client and server in the ideal case, and can be revalidated every 5m or something

daveycodez•9mo ago

I'm hoping we can get an update where the JWT plugin simply adds "token" to sessionData so you have sessionData.session, sessionData.user, and sessionData.token It can simply be synchronized with the session and signed every time you get a session, since it's 0ms latency to sign a new one. So every session refresh has a new JWT as .token I might make my own plugin that does this in the future but my Tanstack solution is working for me in the meantime

XEDD•7mo ago

Looking forward to this

daveycodez•7mo ago

I’m not gonna lie I’ve totally migrated to InstantDB and it’s awesome

XEDD•7mo ago

Damn. No self hosted. Which is critical for me. (Heavy trafic)

XEDD•7mo ago

With your tanstack have you achieved all niuanses of authorization? https://github.com/daveyplate/better-auth-tanstack

GitHub

GitHub - daveyplate/better-auth-tanstack

Contribute to daveyplate/better-auth-tanstack development by creating an account on GitHub.

daveycodez•7mo ago

there I just have the useToken hook which will get your JWT It will also refetch before it expires, and when session changes

XEDD•7mo ago

Damn.

Lucas•7mo ago

@XEDD let me know how that goes. I just got away from CLerk exactly because dealing with the JWT token was so annoying. Didnt wanna have to go through this again. May I ask why youre moving away from supabase for DB?

bekacru•7mo ago

Could you gus let me know the issue exactly? also a heads-up - we're collbarinting soon with supabase to add direct integration

XEDD•7mo ago

Basically. It's the JWT tokens you cant manipulate them force dissconnect and etc. i would be glad using Cookie based auth. For example i creating my own role system which has its own subrole . yes thats not very secure. But the RLS policies should fix it. but still it's kinda pain in the ass because. You cannot add your own [ 40+ ] roles still you have to use direct postgress. For permissions 🙂 RPC also for some functions. There is no ready steady variant currently for react based auth. Which kinda sucks. I hadnt theese problems with PHP and Mysql 🙂 @Lucas im not moving away i will just not use the InstantDB (overpriced). No self host option is pretty much dealbreaker for me. I will host my own supabase instance on dedicated server eventualy, i will launch geoblocked to my country Results system.

Lucas•7mo ago

@XEDD as far as I know you cant manipulate the JWT but you can create and use a refresh token instead once the JWT expires but as I've understood @bekacru there's no easy way to sync Supabase RLS with the JWT token directly through Better Auth right now - it would require manual setup. Correct?

bekacru•7mo ago

Yea it does. Honestly, I haven’t looked much into it. I should pretty soon in preparation for the direct integration.

daveycodez•7mo ago

I thought Instantdb was a good price lol. But I'm looking at Triplit now for reasons

oof2win2OP•7mo ago

hey @daveycodez, i'm looking at some code you wrote on neon-drizzle-tanstack and integrating RLS. i'm wondering how you get the anon and authenticated DB with neon correctly? i can't seem to get them working with rls at the same time per se, how do you get anonymous to work correctly? passwordless auth doesn't work there

daveycodez•7mo ago

I used a separate instance for auth. I’m on a call with Neon tomorrow to come up with the best solution and some good docs for better Auth For anonymous they recommended creating a separate db URL for anonymous and use that when not logged in This URL will accept a password, so you need to copy the password for the anonymous role so you'll have DATABASE_URL for admins, DATABASE_AUTHENTICATED_URL for authenticated users (no password) and DATABASE_ANONYMOUS_URL with anonymous user and anonymous pw Since you log in as anonymous to the anonymous URL, those queries will only go through if your RLS policies allow anonymous to read or write there The other option is to use the anonymous users plugin for Better Auth, since those are just users, and create one on page load, and then do a custom check for isAnonymous on that user's JWT

oof2win2OP•7mo ago

yeah fair enough, that's what i have setup right now. supabase had it in only one query (based on if the JWT was present or not) which was IMO better for this type of setup but we'll see

daveycodez•7mo ago

Other option is to manually sign a guest JWT and just pass that Maybe there's a better way but you probably want to ask in their channel

insightautomate•6mo ago

@bekacru any idea on timings for the supabase integrration? I've just gone through manually implementing RLS via postgres functions and middleware, was not exactly a smooth experience so would be great to see a closer integration.

umang•6mo ago

hi @insightautomate - could you share how you went about implementing RLS? i'm failing completely at trying to make this work.
i'm migrating over from supabase auth, and i'm considering rolling back :/

sebastian•6mo ago

Why would you guys need RLS? Only reason I can think of is using their client side library.

daveycodez•6mo ago

If you use RLS you don't have to write manual API logic That being said, I wouldn't suggest using Supabase client side library anyways it exposes your Postgrest instance to abuse

insightautomate•6mo ago

@umang In short this is my initial implementation 1. Create database functions to get/set the current user ID in PostgreSQL session variables:

CREATE FUNCTION current_user_id() RETURNS text AS $$
  SELECT current_setting('my.user_id', true);
$$ LANGUAGE sql STABLE;

CREATE FUNCTION set_user_id(user_id text) RETURNS void AS $$
  SELECT set_config('my.user_id', user_id, false);
$$ LANGUAGE sql;

CREATE FUNCTION current_user_id() RETURNS text AS $$
  SELECT current_setting('my.user_id', true);
$$ LANGUAGE sql STABLE;

CREATE FUNCTION set_user_id(user_id text) RETURNS void AS $$
  SELECT set_config('my.user_id', user_id, false);
$$ LANGUAGE sql;

2. Enable RLS on your table and create a policy:

ALTER TABLE horse_accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users see own records"
ON horse_accounts
FOR ALL
USING (created_by = current_user_id());

ALTER TABLE horse_accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users see own records"
ON horse_accounts
FOR ALL
USING (created_by = current_user_id());

3. Create RLS-enabled client utilities that automatically set the user ID:

// Server-side
export const createRlsClient = async (cookieStore: any) => {
  const supabase = createClient(cookieStore);
  const session = await auth.api.getSession({ headers: await headers() });
  
  if (session?.user?.id) {
    await supabase.rpc("set_user_id", { user_id: session.user.id });
  }
  
  return supabase;
};

// Client-side  
export const createRlsClient = async () => {
  const supabase = createClient();
  const { data: session } = await betterAuthClient.getSession();
  
  if (session?.user?.id) {
    await supabase.rpc("set_user_id", { user_id: session.user.id });
  }
  
  return supabase;
};

// Server-side
export const createRlsClient = async (cookieStore: any) => {
  const supabase = createClient(cookieStore);
  const session = await auth.api.getSession({ headers: await headers() });
  
  if (session?.user?.id) {
    await supabase.rpc("set_user_id", { user_id: session.user.id });
  }
  
  return supabase;
};

// Client-side  
export const createRlsClient = async () => {
  const supabase = createClient();
  const { data: session } = await betterAuthClient.getSession();
  
  if (session?.user?.id) {
    await supabase.rpc("set_user_id", { user_id: session.user.id });
  }
  
  return supabase;
};

4. Use the RLS client instead of the standard client for all database queries:

// Instead of createClient(), use:
const supabase = await createRlsClient(cookieStore);

// Instead of createClient(), use:
const supabase = await createRlsClient(cookieStore);

I think with supabase's recent third-party auth support there is likely a better way to do it via jwt but I haven't dug too deep into that. Hopefully the official integration will do that @sebastian the supabase client with RLS allows us to skip out on building an API layer as well as utilising things like realtime (more important for us). To be honest I'm not the biggst fan of the supabase library and might end up switching out to setting up an API with an ORM but the realtime features are more interesting albeit requiring a slightly different setup. In what way does it expose it to abuse? With the right RLS (and CLS if necessary) you can lock things down pretty tightly

umang•6mo ago

@insightautomate thank you, this is really helpful! i'm using client side library for file uploads to supabase storage, so setting RLS policies mainly for peace of mind. DB queries go through my server. I could use their service_role token but i don't like using keys that have total admin privileges. they don't allow anything in between (that i could find)

daveycodez•6mo ago

Not really, I can loop a script to drain your egress 1000% They have no rate limiting If you don't care if someone attacks your site its cool to use it, otherwise never expose your Anon key. With anon key alone someone can write a simple script to send infinite messages on your public realtime channel Supabase has no protections from the client side for spamming queries to your postgrest endpoint, locking your database down and skyrocketing your costs if you're on the pro plan. And then they enable public realtime channels by default for all projects which also have metered billing. I did the math on it a while back, $13000/month cap on cost That's why I migrated to Neon and Better Auth anyway. The only way to use Supabase safely in a large scale production site is to message their support team to disable your Realtime channels and then run all of your queries through your own backend proxy with your own protections in place, using Upstash or Vercel firewall for rate limiting

bekacru•6mo ago

I heared from their team we should start integrating at the end of this month but not sure

insightautomate•5mo ago

Nice. Do you have any details on how it is likely be implemented? @bekacru do you have any further information on the timelines for this? Would be incredible to have the 2 working together seamlessly!

Arthur•3mo ago

any news on this integration ? @Ping thks for the help! @umang did you figure out on how to leverage supabase storage on the frontend with RLS enable and better auth? thks!

umang•3mo ago

I didn't - ended up staying on supabase auth for now, but hoping to see official support from better-auth that works out-of-the-box. insightautomate's workaround above seems good, although I didn't have the bandwidth to try it out.

insightautomate•2mo ago

@bekacru youguys have been killing it with the shipping lately so I'm sure you've been focussing on toher things but I wonder whether the supabase integration is still planned or if that's been shelved for now?

bekacru•2mo ago

It's on supabase unfortuntley I'll try to push them but that's the best I can do at the moment

Gaming

Programming

integrating with postgres rls (supabase)

Did you find this page helpful?