How do I set up row level security based upon relationship in another table in PostgreSQL?

Question

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.

Users

table_schema	table_name	column_name	udt_name
public	users	id	uuid
public	users	email	text
public	users	display_name	text
public	users	last_post_published_at	timestamptz

User connections

table_schema	table_name	column_name	udt_name
public	user_connections	id	int8
public	user_connections	created_at	timestamptz
public	user_connections	user_a_id	uuid
public	user_connections	user_b_id	uuid
public	user_connections	user_a_confirmed	bool
public	user_connections	user_b_confirmed	bool

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.

Answer 1

Ok, so I figured out a way to do this. I don't have a lot of experience in this field, so there's probably better ways, but here it is.

The first is to set a policy that evaluates visibility based on presence of a matching row in user_connections for a given expression. The USING expression for the policy ended up looking like this:

(
    (uid() = id) OR 
    (
        ( SELECT count(*) AS count
            FROM user_connections
            WHERE (
                user_connections.user_a_confirmed AND
                user_connections.user_b_confirmed AND
                (
                    (user_connections.user_a_id = users.id) OR 
                    (user_connections.user_b_id = users.id)
                ) AND
                (
                    (user_connections.user_a_id = users.id) OR 
                    (user_connections.user_b_id = users.id)
                )
            )
        ) >= 1
    )
)

The second is to set up a publicly accessible view for users that filters down visible columns. I did this via:

CREATE OR REPLACE VIEW users_public AS
    SELECT id, display_name
    FROM users;