Drizzle Team•2w ago

RLS doesn't work when managing policies with Drizzle?

This is taking to long to understand and I would be grateful for some help!!! I have my schema distributed between a number of different files grouped by the table's functions/needs. All these files are in my src/schemas directory. One of these files is dedicated for policies. Nothing crazy. Something like image 1. userProfiles for example is correctly being imported ✅, drizzle-kit check passes with flying colors ✅, a push or a migrate call runs smoothly ✅ and everything is being created in Supabase as expected ✅. Or so I think? 🤔
I am following @rphlmr ⚡ 's createDrizzle flow in his SupaSecureSlack example nearly exactly based on my app's needs. Here's whats done so far: 1. tables created with .enableRls() 2. policy is being added in the policies file similar to the example above. 3. new role for rls_client was created and is being used for the client connection 4. tables are seeded with dummy rows 5. using createDrizzle to create a createDrizzleSupabaseClient() function exactly like the example. 5. drizzle config looks like Image 2 The issue: when running db.rls((tx)=> tx.select().from(userProfiles) as an example, I get zero rows back. Running db.admin.select().from(useProfiles) returns the right rows. So RLS is kinda working, but not really, because I definitely have the right policy. I can see it was created in Supabase. BUT, if I add a new policy directly inside of Supabase UI, exactly similar to the one already exists, then rls works. Can someone make it make sense? :nomore: 🥹

3 Replies

hussam_3hOP•7d ago

Update: Saw that using and withCheck clauses are migrating to null in the database. Here's the relevant snippet generated from Drizzle Kit:

CREATE POLICY "profile_CRUD_policy" ON "user_profiles" AS PERMISSIVE FOR ALL TO "authenticated" WITH CHECK (auth.uid() = account_id);--> statement-breakpoint
CREATE POLICY "profile_view_public_policy" ON "user_profiles" AS PERMISSIVE FOR SELECT TO public USING (TRUE);--> statement-breakpoint

CREATE POLICY "profile_CRUD_policy" ON "user_profiles" AS PERMISSIVE FOR ALL TO "authenticated" WITH CHECK (auth.uid() = account_id);--> statement-breakpoint
CREATE POLICY "profile_view_public_policy" ON "user_profiles" AS PERMISSIVE FOR SELECT TO public USING (TRUE);--> statement-breakpoint

The attached image is a result from SELECT * FROM pg_policies WHERE tablename = 'user_profiles'; Note the only one with qual = true is the one I created from Supabase. Anyone running into similar issues? p.s.: no I am not using the rls_client creds for migration. I am using the admin one, on a non-pooled connection.

rphlmr ⚡•7d ago

Looks like a pending issue https://github.com/drizzle-team/drizzle-orm/issues/4078 😬

GitHub

[BUG]: RLS "using" rule not applied to supabase · Issue #4078 · dri...

Report hasn't been filed before. I have verified that the bug I'm about to report hasn't been filed before. What version of drizzle-orm are you using? 0.38.2 What version of drizzle-kit...

hussam_3hOP•6d ago

Thanks @rphlmr ⚡ ! Btw, really appreciate your work, man.. You're a superstar!

Gaming

Programming

RLS doesn't work when managing policies with Drizzle?

Did you find this page helpful?