You're working with Supabase/PostgreSQL, and you have Row-Level Security (RLS) enabled on your table. The issue you're facing is that when you insert a new record, you want to get the ID of the record back before it's linked to the user via RLS.

Here’s the situation: with RLS enabled, PostgreSQL controls access based on the user's context, and you can't directly insert a record and return its ID if the user is not authorized to see it immediately because of RLS policies. But you still want to get the ID before the link happens.

Simple Approach: Insert the record: Perform an insert and use the RETURNING clause to immediately get the ID of the inserted row. This will give you the ID even though the row is not fully "linked" to the user.

Example query:

sql Copy INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2') RETURNING id; Handle the row linkage: If the row has to be associated with a user or linked to some specific user context later (e.g., after RLS applies), you might need to insert the record and update the user relationship afterward. You can achieve this in two steps:

Insert the record and get the ID back. Update the row to link it with the user. Example in steps: Insert the row:

sql Copy INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2') RETURNING id; Use the ID from the RETURNING clause to update the row, linking it with the user:

sql Copy UPDATE your_table SET user_id = 'user_id_value' WHERE id = 'returned_id'; A bit more context: Why is this necessary? With RLS, your table rows are restricted based on the user's context, so when you're inserting data, it might not be immediately associated with the user. But since the insert is completed and you can get the ID back immediately, you can then update the row to associate it with the user.

Challenges with RLS: RLS is designed to ensure that users can only interact with data they are permitted to access. That’s why you can't just insert a record, as the permissions might be different based on the current user. The flow above gets around that by letting you insert first, then apply the user's access afterward.