RLS syntax to see all rows if user ID in one of them
I think I may have to stick with doing this as two tables but on the off chance I'm just being dense about RLS syntax. I am experimenting with RLS using a use case of purely private rooms. I want to set RLS up so that a user can only see information for rooms they created/own or are a member of and to be able to see the other members of the room. There is a rooms table, a profiles table, and a room members table that is just FK to the room ID and the profile ID. I can't seem to figure out how to make it so that the room members table will only allow visibility into rows that correspond to rooms that the user is a member of. It creates an infinite recursion since it requires queries on the table itself. My solution is to have a "my rooms" table and a "room members" table which are essentially the same data with different RLS policies. Then the "Room Members" rule can reference the "my room" data in its RLS which breaks the recursion issue. It feels janky so I figured I'd post it here in case I'm missing something obvious.
