Conditional INSERT in supabase Postgres RLS

Question

The premise of my question is simple, I have three tables :

• users (user_id)
• records_users (record_id, user_id)
• records (record_id, created_by)

I would like users to be able to INSERT into the records_users table only if their id (column user_id from the users table) is in the created_by field of the records table.

In other words : I would like users to only be able to insert rows for which the record_id is an actual record that is tied to them in the record table through the created_by field.

What I have tried :

• Have looked through RLS options but I cannot seem to find how to integrate a condition based on what is being inserted. Does something like this exist ?

   CREATE POLICY "Enable insert on linked records"
   ON public.records_users
       FOR INSERT USING (
         record_id_being_inserted IN (SELECT record_id FROM records WHERE created_by                 = auth.uid()) 
       );
  

• I have created an RPC Postgres function which applies that logic, but it is blocked by the RLS policies applied to the records_users table and I do not wish to open inserts to all users without having this logic mandatory.

Any help would be appreciated.

Answer 1

As stated by @andrew smith, the answer was pretty straight forward. You can simply reference the fields inserted by using their column names. So for example :

 CREATE POLICY "Enable insert on linked records"
  ON public.records_users
      FOR INSERT USING (
   record_id_being_inserted IN (SELECT record_id FROM records WHERE created_by                 = auth.uid()) 
 );

This query would work if record_id_being_inserted is the name of the column field you want to check.