I'm working on a social media app with PostgreSQL where I need the following features:
- Users must be able to search for other users based upon their display names (display names are public)
- Users should not be able to see not connected users' email addresses
- Users with confirmed connections must be able to view the email address of any connected users.
Right now, the "protected" (not sure if that's the right word) is just limited to email addresses, but I can see in the future wanting to do something similar with other columns that might be added.
Here is how the tables are currently set up. I'm really not great at database administration, so I could have this all totally wrong.
What's the best way to achieve the features and security set that I'm going for? Essentially what thought I needed was row level security based upon some sort of relationship established in a foreign key, but now I'm suspecting I need to look at it from a completely different angle.