Hey! I'm new to Supabase and I'm using for a side project. That project let users review their day (like a journal). So I have a reviews table and RLS policies were easy to implement (users can only read/write their own reviews). But now I'd like users to be able to become a member of a group. Once in a group, everybody can view each other's reviews. The problem I have is to write the RLS policy so that group members can only view each other's reviews. I followed this example https://supabase.io/docs/guides/auth#policies-with-joins, but when I tried my policy, a user could access all reviews (even from other group). Could somebody share a minimal demo of that RLS policy, in the same fashion as in that doc example?

I'm confused about what is not working, can you show what you have along with your table schemas?

here's my reviews table:

And your RLS policy?

the user who created the review

A suggestion would be to rename

user_id

in the reviews table to

created_by

to not confuse you later on

ah yes, good idea. done.

That's not the full policy

does this help?

Yeah that helps

I think my policy is missing something because there's no reference to the reviews table

Try this

sql
(
  auth.uid() IN (
    SELECT user_id
    FROM members
    WHERE reviews.group_id = members.group_id
  )
)

okay, I'll try it

So in mine the

group_id

should be from the

reviews

table or maybe we need to make it explicit because you have a

group_id

on the members table too

it's working! 😃

Ok I'll send my PayPal over to you now

💰

Happy to hear its working, just post on here if you have anymore questions

ah btw: if I want to validate the rating for the review (a number between 1 and 10), I also need to use RLS for this?

You could use a function with a trigger

okay, I'll look into it

It's probably best to use a function with a trigger

you mean a custom function? there's no built-in functions for this?

Yeah a custom function

I think that Supabase docs are already great and I like the YouTube videos, especially the newest one about RLS policies 😄

Yeah @User did an awesome job on that, he will be producing more content like that in the future too.

@User to use policies with joins is a good starting point for me too, but our use cases are more complex than @User 's example above. Yes, we have tables like user profiles (easy, just add a column

user_id

), but also more complex. For example

suppliers

table: - John must view all records, but only be able to create/update records where

suppliers.country

is "Italy" - Anna must be able to view/create/update/delete all

suppliers

- ... And we have a few such tables. So you can imagine it will be a nightmare to maintain a many-to-many table like

user_suppliers

for each individual user / supplier combo and different level of permissions. We need a proper RBAC basically. I guess it is more of a question of how I design all these roles/permissions tables. I hope the

auth.uid

will be enough and I can somehow join my way through to the user's role privileges without needing to create hundreds of policies or maintain thousands of many-to-many tables just for RBAC. Btw, I've been developing ERP projects for 10 years and the RBAC system there was insane. Sometimes I was setting up roles for months. I mean not design the RBAC system itself, but actually setting up what you call "policies" here. Anyways, I will try to make it work just with

auth.uid()

. If you guys are interested, I can share how I've done it / what I struggled with and maybe there is something you could add to Supabase to make it easier to design a bit more sophisticated RBAC compared to the simple user/team, user/review cases.

yeah