I have only used Postgres briefly, so still trying to learn.

I have 3 tables, “users”, “teams” and then to connect users to multiple teams “users_teams”.

I cant figure out how to set up row level security for the “users_teams” and “users” table to able to fetch the data. The teams table will be selectable for everyone so thats not a problem.

I cant figure out how to allow the user to fetch his data as well as getting the rows of other users connected to the same team as them.

Maybe this is not the right way to go about the table structure with RLS, so if you have a suggestions for a better way of doing it, then you are welcome to help me out.

I have only been focusing on getting the data from users_teams table for now.
I have tried a lot of different types of statements, but all of them return null from the database, which is strange, because when i only check on auth.uid() it returns the rights rows.
So it’s almost like the OR statement is working.

here are some examples of what i have tried:

CREATE POLICY user_team_access_policy
on users_teams
to authenticated
using (
  user_id = auth.uid() or
  team_id = (
    select team_id
    from users_teams
    where user_id = auth.uid()
  )
)

or 

CREATE POLICY user_team_access_policy
on users_teams
to authenticated
using (
  user_id = auth.uid() or
  team_id in (
      SELECT team_id from public.users_teams where user_id = auth.uid()
  )
)

and many more.