Row-level-security dual join

Question

I have three tables

journeys
id
user_id
...

sections
id
journey_id
...

stops
id
section_id
...

I want to use row level security to make sure that a user can only insert a stop if the uid() matches the user_id on the journey that is referenced by the stop via stops.section_id->sections.journey_id->journeys.user_id.

In other words, the user should only be able to set stops.section_id to a section and thus to a journey that belongs to him.

How can I achieve this in Supabase with row level security joins?

Answer 1

You can use an EXISTS condition:

CREATE POLICY may_insert_stop ON stops
FOR INSERT TO PUBLIC
WITH CHECK (EXISTS (SELECT 1
                    FROM journeys AS j
                       JOIN sections AS s ON s.journey_id = j.id
                    WHERE s.id = stops.section_id
                      AND j.user_id = uid()
                   )
           );