Drizzle Team•6d ago

Why Isn't RLS Working with Drizzle and Supabase?

Hi everyone, When I enable the new RLS (Row-Level Security) feature on Drizzle and Supabase and migrate, I can see it applied, but it doesn't seem to work as expected. Unfortunately, all data is still accessible, even though the default behavior should block access if no policies are defined. Do I need to write some specific queries with Drizzle for RLS to function properly? Or is there a different way to connect to Supabase to make it work? I'd be very grateful if anyone could help. Thanks in advance!

20 Replies

Darren•5d ago

rls is overridden if youre only using the 'postgres' role to access the tables, you need to use a non postgres/superuser role for rls to take effect

rphlmr ⚡•5d ago

hey 👋 if you use the default supabase connection string (super user) it will bypass all RLS policies I have a demo with Nextjs + Supabase and Drizzle with RLS https://github.com/rphlmr/drizzle-supabase-rls The instructions are in the README. I hope they are clear enough.

GanbatteOP•5d ago

Thank you so much, both of you. I went through the README and the files @rphlmr ⚡ , and to be honest, I felt quite disheartened seeing how much extra complexity this involves. +3 config files, and I also had to add a bunch of stuff to my existing config files. Also, from what I can see, the Supabase SSR package seems to be mandatory for this. Or is this SSR package only necessary for the auth part? Does this RLS actually work without Supabase Auth? Because I’m using auth.js. From what I can tell, it seems like it relies on the built-in roles of Supabase. I’ve created my own enum for roles, and this is connected to my user table. Thanks in advance!

Darren•5d ago

the ssr isnt required no, nor supabase auth, you just need some way of validating what role and id the 'user' has in the rls functions, for supabase auth its using the auth schema and the sub/role from the jwt, if youre using seperate auth, then you would need to use your own function to validate who the user is.

rphlmr ⚡•5d ago

You only need to pass your user access_token in a specific pg function (set_config) in a transaction. This is how Supabase know who is making the query. This is why I shared https://github.com/rphlmr/drizzle-supabase-rls/blob/main/database/drizzle.ts, it does this for you, until it is part of drizzle-orm/supabase You should only need: - Copy createDrizzle - Create 2 Drizzle client and export them like

const config = {
  casing: "snake_case",
  schema,
} satisfies DrizzleConfig<typeof schema>;

// ByPass RLS
const admin = drizzle({
  client: postgres(process.env.ADMIN_DATABASE_URL!, { prepare: false }),
  ...config,
});

// Protected by RLS
const client = drizzle({
  client: postgres(process.env.DATABASE_URL!, { prepare: false }),
  ...config,
});

// This example uses Next.js capabilities
export async function createDrizzleSupabaseClient() {
  const {
    data: { session },
  } = await createClient().auth.getSession();
  // Decode the access token on your own (from Supabase client, custom session cookie, etc.).
  const token = decode(session?.access_token ?? "");

  return createDrizzle(token, { admin, client });
}

const config = {
  casing: "snake_case",
  schema,
} satisfies DrizzleConfig<typeof schema>;

// ByPass RLS
const admin = drizzle({
  client: postgres(process.env.ADMIN_DATABASE_URL!, { prepare: false }),
  ...config,
});

// Protected by RLS
const client = drizzle({
  client: postgres(process.env.DATABASE_URL!, { prepare: false }),
  ...config,
});

// This example uses Next.js capabilities
export async function createDrizzleSupabaseClient() {
  const {
    data: { session },
  } = await createClient().auth.getSession();
  // Decode the access token on your own (from Supabase client, custom session cookie, etc.).
  const token = decode(session?.access_token ?? "");

  return createDrizzle(token, { admin, client });
}

the way you get your user session is your. You can replace createClient().auth.getSession(); with what you want This is why it's still not in drizzle-orm/supabase. We are not sure how to handle that because there are so many ways to manage auth.

Darren•5d ago

the auth js docs show how to also generate compatible supabase jwt, you would only need to alter the rls function to use next_auth.uid() not auth.uid()

rphlmr ⚡•5d ago

Ah! ok, Auth.js adds it's own tables! So yes, you are free to adapt if you know the underlying implementation What matters for RLS: - using a dedicated connection string with a low privilege Postgres user ex:

CREATE USER rls_client
WITH
   LOGIN PASSWORD 'your_strong_password_for_rls_client';

GRANT anon TO rls_client;

GRANT authenticated TO rls_client;

CREATE USER rls_client
WITH
   LOGIN PASSWORD 'your_strong_password_for_rls_client';

GRANT anon TO rls_client;

GRANT authenticated TO rls_client;

- having a way to know who is making the query (access_token or anything that is required for a Postgres function that check the auth) - binding this to the Postgres function(s) in the transaction (like what is done in createDrizzle) I agree it's not a great developer experience at first and it requires understanding the underlying implementation 🤯

GanbatteOP•5d ago

First of all, thank you both for all these helpful messages. I agree with you on the complexity, it does indeed seem quite complex, especially from my perspective. I’m new to the backend world, and while I’ve been using Supabase for about a year, my knowledge of PostgreSQL and SQL is very limited. That said, I really like Drizzle. Before that, I used Prisma, but I prefer Drizzle. I like that it doesn’t hide what it’s doing under a lot of "magic," it’s fast, and it’s easy to create views with it, etc. If this was my previous DATABASE_URL: postgres://postgres:mySupabasePostgresPW@myServerIp:5432/postgres then with RLS it would be this, right? postgres://rls_client:mySupabasePostgresPW@myServerIp:5432/postgres The SQL you sent, should that be run in Supabase? Does it establish the base for the connection that I’ll link to the RLS URL? (process.env.DATABASE_URL)

rphlmr ⚡•5d ago

You should keep your previous DATABASE_URL and renamed it (ex ADMIN_DATABASE_URL) for "RLS bypass" queries + Drizzle Kit. Then, create an additional var env like you suggested. The script I shared is to create a new database user (nb: it's not a regular user, it's a PostgreSQL user) with no privileges (it should not be able to query the database without setting the access token). Let me (us) know if you need more help 🫡

GanbatteOP•5d ago

Now I understand the whole thing better. I went for a swim, and now I’ve started working on the implementation again, but unfortunately, I got stuck. I created the PostgreSQL user and the .env files as you mentioned. I also implemented the jws.ts and drizzle.ts files based on your repository, and I rewrote the index.ts file (which I believe is called db.ts in your case). My problem begins with the fact that I can’t find where you export the db object, so I can’t reference it anywhere. I also don’t fully understand how this data fetching, editing works with RLS. For example, I’m referring to this:


const db = await createDrizzleSupabaseClient();

const [user] = await db.rls((tx) =>
  tx.select().from(profiles).where(eq(profiles.email, email))
);

And that was the simpler part. I checked the documentation Darren mentioned about Supabase-compatible tokens for Auth.js: https://authjs.dev/getting-started/adapters/supabase If this is what he’s referring to, honestly, I don’t really understand how I should integrate it into your codebase. Thanks in advance if you have any suggestions!

Auth.js | Supabase

Authentication for the Web

rphlmr ⚡•5d ago

ok let's try to break this Can I assume that you know auth.js and have installed it as described in the documentation you linked? Reading the linked documentation, everything can be identical to what is in the demo repo since Auth.js also relies on request.jwt.claim.sub for its postgres next_auth.uid() function. The only thing that differs is in my db.ts

export async function createDrizzleSupabaseClient() {
  // ❌ should be adapted for auth.js
  const {
    data: { session },
  } = await createClient().auth.getSession(); 

  // Ok for me
  return createDrizzle(decode(session?.access_token ?? ""), { admin, client });
}

export async function createDrizzleSupabaseClient() {
  // ❌ should be adapted for auth.js
  const {
    data: { session },
  } = await createClient().auth.getSession(); 

  // Ok for me
  return createDrizzle(decode(session?.access_token ?? ""), { admin, client });
}

Of course, you could use jsonwebtoken to decode the token in jwt.ts, but we really only need the access_token decoded (so it's on you to assert that it is a legitimate token. I guess Auth.js handles all of this)

rphlmr ⚡•5d ago

Now you have to figure how you get your session from auth js here. Maybe its

import { createClient } from "@supabase/supabase-js"
import { Database } from "../database.types"
 
const supabase = createClient<Database>()

import { createClient } from "@supabase/supabase-js"
import { Database } from "../database.types"
 
const supabase = createClient<Database>()

https://authjs.dev/getting-started/adapters/supabase#typescript But I know nothing about auth.js, so I would not give wrong information.

Auth.js | Supabase

Authentication for the Web

GanbatteOP•5d ago

Thanks for the info, but why exactly is it necessary to make the auth.js token compatible with Supabase? I don’t fully understand this part, and if you could also kindly answer my previous question, I’d really appreciate it. This is what I’m referring to: My problem begins with the fact that I can’t find where you export the db object, so I can’t reference it anywhere in my code. Why should I export it? I also don’t fully understand how this data fetching, editing works with RLS. For example, I’m referring to this:

const db = await createDrizzleSupabaseClient();
const [user] = await db.rls((tx) =>
  tx.select().from(profiles).where(eq(profiles.email, email))
);

Thanks in advance!

rphlmr ⚡•5d ago

why exactly is it necessary to make the auth.js token compatible with Supabase?

RLS relies on user auth (through an access token). By design, RLS works with Supabase Auth. I don't know Auth.js, but it seems to be an alternative to Supabase Auth. It provides a way to manage authentication by creating a schema+db (like Supabase) in your PostgreSQL database. Side note: We are not directly concerned, but, in order to be able to use Supabase SDK with an external auth system (like Authjs), you have to make Supabase aware of that (Supabase SDK is a web API that forwards your queries). This is why the doc speak about Supabase client etc. In our case, we don't really need all of this, we only need to pass a decoded access token to the PostgreSQL config (request.jwt.claim.sub). Then, you will be able to create policies using the helper next_auth.uid() to check who is querying (e.g., https://supabase.com/docs/guides/database/postgres/row-level-security). Because you are not using Supabase Auth, in these examples, you would have to replace auth.uid() with next_auth.uid().

My problem begins with the fact that I can’t find where you export the db object, so I can’t reference it anywhere in my code. Why should I export it?

If you have previously used Drizzle (with or without Supabase), it's just a drop-in replacement for the db object you export from (for example) database/db.ts. It's a server side utils (it should not be used client side). You export it because you need to set the current user's access token per request when the code is invoked. The RLS client makes isolated queries from other users because we use a Postgres transaction.

I also don’t fully understand how this data fetching, editing works with RLS.

I would advise watching some Supabase videos about RLS and why you may need them. RLS is a collection of rules that define who can read and/or write/update/delete a particular row based on some criteria (auth state, user_id, etc)

For example, I’m referring to this: const db = await createDrizzleSupabaseClient(); const [user] = await db.rls((tx) => tx.select().from(profiles).where(eq(profiles.email, email)) );

What happens here, based on the demo repo: const db = await createDrizzleSupabaseClient(); We get back a db object that is preset with our current user access_token. The token has been found in createDrizzleSupabaseClient, using Supabase auth (it reads auth from the current HTTP request cookie). this db object contains 2 things: admin and rls. admin is a Drizzle client created with the default Supabase PG connection string (full-power user). rls is a Drizzle client we created with our low power PG user. This special client is a function: a drizzle transaction() function. Why? Because this is the only way to use RLS, the only way to set the user's request.jwt.claims, isolated, per request. This is what makes it possible to authenticate our SQL query. Then, your RLS, on your postgres db will read this claims through auth.uid() / next_auth.uid() (because these function read from the postgres query context config and returns the value for the key request.jwt.claims)

GanbatteOP•4d ago

Thanks for all the help, now I understand why there are two, and how we refer to them. I have used RLS in Supabase before, and I'm currently using it, and what I meant in my previous message is that I don’t understand how Drizzle handles this to make RLS work. I’ve been working on it all day to get it to work, but I only got to the point where the data doesn’t come through on the tables where RLS is enabled when using the rls_user you mentioned, but it works on the admin URL. I think the issue is with how the user is being passed. I checked the Auth.js docs, but unfortunately, it’s not working and is incomplete, so I trying to do on my way. What format should I be passing the data to Drizzle? Because right now, it looks like this on my end, I have the role in the user session, and I extract it from the token along with the userID:

token: {
  role: 'basic_user',
  sub: 'cccdaa80-bfa7-4e79-bc89-b62772789633'
}

//....
const token = decode(session);
return createDrizzle(token, { admin, client });

Also, are these roles I create with Drizzle at the PostgreSQL level? Because with the projects I’m using the Supabase SDK for, I did the RLS like this: I had a role column in a separate user_data table, since I think the auth table can’t be edited, and I referenced the role column in user_data because there were four types of roles. So, they were only at the table level. Thank you in advance for your patience and help

GanbatteOP•4d ago

"Error: permission denied to set role 'basic_user' This is the error I get when I try to fetch the data in this way:

GanbatteOP•4d ago

I can see the roles, they appear at the bottom of the Supabase page.

Darren•4d ago

policy wise im not sure your user would ever be able to select anything: this is sort of an example:

    pgPolicy("delete", {
      as: "permissive",
      for: "delete",
      to: authenticatedRole,
      using: eq(table.userId, authUid),
    }),
    pgPolicy("insert", {
      as: "permissive",
      for: "insert",
      to: authenticatedRole,
      withCheck: eq(table.userId, authUid),
    }),
    pgPolicy("select", {
      as: "permissive",
      for: "select",
      to: authenticatedRole,
      using: eq(table.userId, authUid),
    }),
    pgPolicy("update", {
      as: "permissive",
      for: "update",
      to: authenticatedRole,
      withCheck: eq(table.userId, authUid),
      using: eq(table.userId, authUid),
    }),

    pgPolicy("delete", {
      as: "permissive",
      for: "delete",
      to: authenticatedRole,
      using: eq(table.userId, authUid),
    }),
    pgPolicy("insert", {
      as: "permissive",
      for: "insert",
      to: authenticatedRole,
      withCheck: eq(table.userId, authUid),
    }),
    pgPolicy("select", {
      as: "permissive",
      for: "select",
      to: authenticatedRole,
      using: eq(table.userId, authUid),
    }),
    pgPolicy("update", {
      as: "permissive",
      for: "update",
      to: authenticatedRole,
      withCheck: eq(table.userId, authUid),
      using: eq(table.userId, authUid),
    }),

The above would be sort of like say an email table, each user can only select/insert/update/delete their own entries if it was says posts the select would just return true in using Note the authUid here is from drizzle which maps to the supabase auth function for comparing the sub, this would not work if youre using auth js, since the users arent in the supabase auth schema you would need to use the auth js function they give you Also if youre validating cross tables you would use something like:

using: sql`(user_id IN ( SELECT users.id FROM users WHERE (users.user_id = ( SELECT uid() AS uid))))`

using: sql`(user_id IN ( SELECT users.id FROM users WHERE (users.user_id = ( SELECT uid() AS uid))))`

Note: you have 'using' on all bar insert (this checks the sub jwt against the row) then you have 'with checks' for insert and update (this checks the sub jwt against the user that is inserted/updated so can only input their own id) Lastly, the error you are getting refers to a user cant change role to a role they do not have now i dont know how you created this role, but usually if you GRANT authenticated thats enough, but it looks like you are using different roles here also note 'authenticatedRole' covers everything that isnt anon so i would remove roles for basic user and just use builtin authenticatedRole then jsut grant the specialrole to admins, and add the extra policy so admins using return true, you can also do this with no extra roles, and just use the using sql to check role for that user from users table along with id, for example an update checks if id matches or user is in admin role.. etc its up to you

GanbatteOP•4d ago

So, if I understand correctly, the focus here isn’t just on what I’m passing but rather on something more complex, which is why I should use this: https://authjs.dev/getting-started/adapters/supabase, right? The problem is that when I integrated it into my code, even the login stopped working, and it threw an error I couldn’t even find any information about. It couldn’t find the supabaseAccessToken part in the file. I don’t really understand the SQL part of it, but as far as I saw, it placed the tables in a separate schema, and from then on, Drizzle didn’t even allow me to reference them (e.g., it used user.id in other tables). On top of that, this package is barely used, and it’s made very clear that there’s no dedicated maintainer for it. From what I can see—but feel free to correct me—it seems like Auth.js has been in beta for over a year, and the project looks somewhat neglected. Everyone on Reddit says that Better Auth will replace Auth.js, but unfortunately, it’s so new that I didn’t dare to switch to it. I also tried SupabaseAuth in the morning, hoping it might solve this issue, even if it meant losing some of Auth.js’s flexibility. But since I’m self-hosting, as far as I could tell, the auth part isn’t included in the self-hosted version by default. You can only configure it with random .env files, which lack proper documentation, and everyone I’ve seen on StackOverflow and GitHub is struggling with it. If you can help with this in any way, I’d appreciate it! I use GitHub and Google auth in the project, and I don’t have major requirements, subdomain routing for auth is the only more important aspect. I’ll give it another try tomorrow, but any help would be greatly appreciated. Thanks in advance!

GanbatteOP•15h ago

Well, in the meantime, I've made a decision and replaced Next-Auth with Supabase Auth. I copied all the files from your repo @rphlmr ⚡ , I'm logged in, and the token is visible when I print it. However, it's throwing an error. Why? Thanks in advance! @Darren @rphlmr ⚡

Gaming

Programming

Why Isn't RLS Working with Drizzle and Supabase?