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.

I'm a little confused by your What I have tried section, the example you gave of a RLS, have you tried it? you would change record_id_being_inserted to the column name in the records_users table. — Andrew Smith
– Andrew Smith, Commented Aug 13, 2023 at 18:30
Yes, actually you are right. I gave it another shot and referencing the inserted column actually does work simply enough. I think the previous query on which I had tried that had the record_ids confused which caused it not to work initially. Thank you for your help — Fredaroo
– Fredaroo, Commented Aug 14, 2023 at 10:06

Fredaroo · Accepted Answer · 2023-08-14 10:08:15Z

0

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.

answered Aug 14, 2023 at 10:08

Fredaroo

4445 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Conditional INSERT in supabase Postgres RLS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related