How should I go about optimizing this row level security policy for reading an event?

Question

Here is a summary of my tables / entities that I am using for this policy (simplified for SO).

user
----
id (pk)

event
-----
uuid (pk)
host_id (fk -> user)
privacy: int

friendship
----------
user_id (pk, fk -> user)
friend_id (pk, fk -> user)

invitation
----------
user_id (pk, fk -> user)
event_uuid (pk, fk -> event)

ENUMS: 
- Privacy (Public, Friends, Private) = (0, 1, 2)

In my case, what I want to ensure, using either Row Level Security in PostgreSQL or some other method (where condition maybe?) is that a user can only see an event given one of the following conditions:

1. If they are the host of the event
2. If the event's privacy is public
3. If the event's privacy is friends and they are friends with the event's host
4. If they are invited to the event

I understand these are a lot of conditions, but I'm not sure what would be the best format for checking all of these. Currently, this is the RLS policy I'm using to check them all, but it seems quite verbose and I'm wondering if there's something I'm missing that would optimize it:

create policy read_event
    on event
    for select
    using (
        -- is host
        event.user_id = ?
        or
        -- is public
        event.privacy = 0
        or
        -- is invited
        exists (
            select *
            from invitation as i
            where i.user_id = ? and i.party_uuid = event.uuid
        )
        or
        -- is friends and party is friends-level privacy
        exists (
            select *
            from friendship as f
            where f.user_id = ? and f.friend_id = party.user_id
            or f.user_id = party.user_id and f.friend_id = ?
        )
        and 
        event.privacy = 1
    )

The '?' can be replaced by the current user's id that is making the query (using either a session variable or other alternative). Overall, this is a simplified version of what I'm trying to do, but I just wanted to see if I'm on the right path or if I'm missing something.

Additionally, this is somewhat out of the scope of this SO question, but still useful if anybody has insight, do RLS policies cascade when joining from other tables (e.g. if I create an invitation with a fk to an event, will it check the RLS policy for reading an event upon insert or no?)

Thank you so much for any help!

Answer 1

I didn't check if the conditions match your requirements, but the policy definition looks sane. The problems are

• How to get that ? if it is not associated with the user? Using a placeholder parameter will work, but the parameter can be changed any time with a SET statement. Perhaps that is goid enough for your security requirements.

• The condition from the USING clause will be added as filter to the query. It contains OR, so it could affect query performance drastically. You should test with realistic amounts if data.

Row level security works per table. So the visibility of rows with a foreign key constraint is not affected by row level security policies on the referenced table.