1

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.

1 Answer 1

0

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;
3
  • Try EXISTS instead of (SELECT count(*) ...) >= 1. And avoid OR if you can. Both will be good for performance. Feb 28, 2022 at 7:27
  • Thanks! I'll give that a go. As far as avoiding OR, how would I accomplish this logic then? Would I create multiple policies? Or would something like user.id in (user_connections.user_a_id, user_connections.user_b_id) be what you're getting at?
    – aduensing
    Mar 1, 2022 at 18:01
  • I have no idea how to avoid OR either. I just wanted to point out that it may be a performance problem. Mar 1, 2022 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.