[SOLVED] Conditional INSERT in supabase Postgres RLS

Conditional INSERT in supabase Postgres RLS

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.

Solution

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.