Supabase (Postgres) Row Level Security Based on Role in Table

Question

I'm new to Supabase and Postgres (though not new to SQL) but I'm having a difficult time wrapping my head around writing policies.

Let's say I have a member table structured like this:

// member //

id (text) | name (text) | group (text) | admin (int)
----------------------------------------------------
abc       | Bob         | alpha        | 0
----------------------------------------------------
xyz       | Sally       | bravo        | 1
----------------------------------------------------

The id in the member table matches the id of the authenticated users of my app.

I want to restrict other tables in my database based on group:

// program //

id (text) | name (text) | group (text)
--------------------------------------
def       | First       | alpha     
--------------------------------------
ghi       | Second      | bravo     
--------------------------------------

Desired policy #1:

Allow any user to SELECT if they are in the program's group.

Desired policy #2:

Allow any user to INSERT, UPDATE, or DELETE if they are in the program's group and if they are an admin.

I think I'm supposed to write a USING statement that is something like:

SELECT program 
WHERE group IN (
  SELECT group 
  FROM member 
  WHERE member.id == auth().id
)

...and then a similar one for policy #2 that adds AND member.admin = 1, but I'm not clear on this.

Am I on the right track? Any suggestions?

Answer 1

You are perfectly on track! You should be able to just paste the following section in your Supabase dashboard policy editor to get it working.

group IN (
  SELECT group 
  FROM member 
  WHERE member.id == auth().id
)

You can also run the following SQL from the SQL editor of the dashboard to get the same results.

create policy "Users can view data of their group"
  on program
  for select using (
    group IN (
      SELECT group 
      FROM member 
      WHERE member.id == auth.uid()
    )
  );