Is it possible to create rls policy based on an input? For example: allow insert if the inserted id does not exist yet

This wouldn't be necessary as this is how relational databases work by default

Thanks for answering, but what do you mean by that? I have the signup inputs and profile inputs in the same form but I have a trouble creating user profile during sign-up (with email confirmation enabled) + rls policy that prevent insert to the profile table. My idea is (if possible) to allow insert if the user id FK, that is about to be inserted, does not exist yet in the profile table. Thus, making sure that there will ever be only one insert. Using trigger is probably out of question because I have different table for different kind of user (e.g. doctor table and patient table).

What you are describing is what happens by default in a relational database, what I think you are after is checking if the user profile info is unique, which is not related to the user_id FK.

Oof I'm not sure how to share the schema since I created the table using the UI (and I'm pretty much a SQL beginner). I'm creating a doctor listing. the schema is currently just a simple doctor table with name, some profession related stuff and FK to user ID. Patients doesn't have public profile so it's not really relevant. To clarify, I basically really need the doctor to create their profile during signup. Yet, I'm confused with the rls policy because I'm afraid of the upsert:true option will make it possible to overwrite and messed up the data. So I just disable insert completely. BUT THEN how do I create the doctor profile? this gives me headache

So there are numerous ways to go about this, you can use a stored procedure to create the profile while signing up, I have a stream I recorded on Twitch which shows how to do this https://www.twitch.tv/videos/1165927555, I was asking if there was any sort of unique information, because the method I'm showing in the recording wouldn't cater for that, so in my instance if the username was already taken, this would create an issue, because the user would have been registered but their profile creation would fail due to the username being taken already

hey thanks for the video! I think the user metadata can get me to what I need