https://supabase.com/ logo
Powered by
I want a RLS policy to limit users to see other us...
# help
m
I want a RLS policy to limit users to see other users only if they share a space
n
Hello @mathewcst! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates. Want to change the title? Use the ``/title`` command! We have solved your problem? Click the button below to archive it.
m
I'm trying to follow this example: https://database.dev/rls-policies-with-security-definer-function I have a spaces table (basically a team), a profiles table (users) and a members table to join both. I want a policy so users can only see other users from the same space
the example policy only return the spaces for that user, but don't allow me to see the other users from the same space
Copy code
sql

create policy "Team members can update team members if they belong to the team."
  on members
  for all using (
    team_id in (
      select get_teams_for_user(auth.uid())
    )
  );
these are my current policies
g
So what you probably need is something like:
Copy code
get_teams_for_user(auth.uid()) && get_teams_for_user(id)
Where && is array operator overlaps https://www.postgresql.org/docs/14/functions-array.html This looks like it could be painful performance if you were just to select * from profiles as both functions would get run for each row... Not sure if you will have a filter to narrow the results. You might have two functions and declare get_teams_for_current_user() a stable function in hopes it does not run also on each row.
m
ty so much for the quick answer @garyaustin . Is there any other way to do something like this without these RLS policies? Like having an actual backend and not using supabase clientside?
I'm used to use Mongo or some local SQL database. So trying to reproduce the same in supabase has been a bit of a challenge
g
It all depends on how you need to get the profiles. You could have rpc call to a postgres function that might be able to have a reasonably fast sql call to generated a return table. Or maybe even a view. I'm not enough of a Postgres guy to understand all the optimizations that the sql generator might do. There maybe a more sophisticated way to do the RLS, or have space data in an array column in profiles for this case as then you already know each profiles spaces without an extra query per row. It could also run fine for your dataset long enough to not worry about performance optimization until you need it. I would personally go with the RLS approach until I need to worry about it.
m
Ty again for the help
gonna try to implement this
For future reference, here's my solution: 0) Used example function to get all the spaces for that user:
Copy code
sql
create or replace function get_spaces_for_user(user_id uuid)
returns setof uuid as $$
    select space_id
    from members
    where user_id = $1
$$ stable language sql security definer;
1) Created a new function for getting profiles from the same space
Copy code
sql
create or replace function get_users_for_space(user_id uuid)
returns setof uuid as $$
    SELECT a.user_id
    FROM members a
    WHERE a.space_id IN (
        SELECT space_id
        FROM members
        WHERE space_id = a.space_id
    )
    
    
$$ stable language sql security definer;
2) New policies `members`: no policy `profiles`: (id IN ( SELECT get_users_for_space(uid()) AS get_users_for_space)) `spaces`: (id IN ( SELECT get_spaces_for_user(uid()) AS get_spaces_for_user))
n
Thread was archived by @mathewcst. Anyone can send a message to unarchive it.
PreviousNext
Powered by