New User Signup Failing with RLS Error Despite Correct Policy
Hello everyone,
I'm facing a critical issue where new user signups are failing. The user is successfully created in auth.users (I receive the confirmation email), but the trigger to insert a corresponding row into public.user_profiles fails with a 401 Unauthorized error:
new row violates row-level security policy for table "user_profiles"
The problem is that my RLS policy for this action appears to be configured perfectly. I've verified using pg_policies that my single, active INSERT policy is:
* Role: authenticated
* WITH CHECK expression: (auth.uid() = id)
The most critical diagnostic step I've taken is to temporarily disable RLS on the user_profiles table. When I do this, the 401 error disappears, which proves that the RLS system itself is the source of the block, even though the policy seems correct.
I have already fixed other potential issues like NOT NULL constraints and cleaned up orphan rows from previous failed attempts. After re-enabling the correct RLS policy, the 401 error returns.
In summary: a perfectly configured RLS policy is not being respected by the database, and I am completely stuck. Has anyone encountered an issue like this? It feels like a project-specific bug.
Any help or ideas would be greatly appreciated. Thank you!
6 Replies
When are you doing this insert in relation to signup? There is no session right after auth.signup() if you confirm the email.
What is your insert call? If JS and you have select then you need to meet select policy. Other clients sometimes require that.
I missed you saying this is a trigger function on auth.users? Although normally an error on the trigger function would not insert the user into the table.
If so is your function security definer type?
Yes, you are correct. The setup is an AFTER INSERT trigger on the auth.users table, which calls a function to insert a corresponding row into public.user_profiles.
You've pointed out the most confusing part of this issue, and your observation is correct. I do receive a confirmation email, which suggests the user creation was successful.
However, the transaction ultimately fails. The trigger function is blocked by the RLS policy, and the initial INSERT into auth.users is rolled back. If I check the auth.users table after a failed attempt, the user is indeed not there.
My hypothesis is that the Supabase Auth service (GoTrue) queues the confirmation email to be sent before the database transaction that runs the trigger is fully committed. This would explain why I receive the email even though the transaction ultimately fails and the user is not saved to the database.
I've never seen it work that way.
But show your function and is it security definer type? The auth role does not have access does not get granted public schema or meet RLS normally.
Do you have more than one trigger on auth.user?
The error is in the auth and Postgres logs for the signup?
Thanks for the quick reply. Here are the answers to your questions:
1. Function Code and SECURITY DEFINER:
Yes, the function is a SECURITY DEFINER type. Here is the exact code I'm using to create it:
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = public
AS $$
begin
insert into public.user_profiles (id, full_name, role)
values (new.id, new.raw_user_meta_data->>'full_name', 'matchmaker');
return new;
end;
$$;
2. Number of Triggers:
I only have one trigger on the auth.users table. Here is the code used to create it:
CREATE OR REPLACE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.handle_new_user();
3. Error Logs:
The errors I have been reporting are from the browser's developer console. They appear immediately after the client-side supabase.auth.signUp() call fails. The console shows the 401 Unauthorized network error from the POST request to /rest/v1/user_profiles and the detailed JSON response body: "new row violates row-level security policy for table "user_profiles"".
So it is not the trigger failing. Security definer would not get an RLS error.
You are doing a POST, insert to profiles in your code. There is no session right after signup if you have confirm email being sent is the most likely reason.
Wow!
You are a genius, you solved my problem!
Thank you very much!