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:

If they are the host of the event
If the event's privacy is public
If the event's privacy is friends and they are friends with the event's host
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!

score 1 · Accepted Answer · answered Jun 16 '21 at 06:10

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.

answered Jun 16 '21 at 06:10

Laurenz Albe

209,280
17
206
263

I'm setting the ? with the SET statement but it's secured behind an authentication wall on the server application. As for the other bullet, do you have any suggestions as to how to improve the USING clause that contains the OR statement? I'm pretty new to this so any advice would be greatly appreciated. – bryan-vh Jun 16 '21 at 06:52
If that's your condition, you need the `OR`s. You could either simplify the condition (for example, by adding a redundant column) or make sure that the filter never processes many rows. – Laurenz Albe Jun 16 '21 at 07:13
Realistically each exists clause should only result in one row being returned that would match each subquery, but I don’t know if that means the filters wouldn’t process many rows. – bryan-vh Jun 16 '21 at 08:24

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

1 Answers1