Database error saving new user

What you see

supabase.auth.signUp() fails with a 500 response. The same thing can happen on a first OAuth sign-in, an invite, or Add user in Studio:

{ "code": 500, "error_code": "unexpected_failure", "msg": "Database error saving new user" }

supabase-js raises it as an AuthApiError with that message. After an OAuth sign-in it arrives in the redirect URL instead, as error_description=Database+error+saving+new+user.

Why it happens

Creating a user means inserting a row into auth.users. If anything in that insert fails, Postgres rolls it back and Auth replies with this generic message. The real Postgres error never reaches the client.

The cause is nearly always a trigger on auth.users, typically on_auth_user_created calling public.handle_new_user() to create a profile row. Ways it breaks:

• The function isn't security definer, so it runs as Auth's role, supabase_auth_admin, which has no rights on public.profiles.
• It sets search_path = '' (correct) but writes to profiles instead of public.profiles, which gives relation "profiles" does not exist.
• profiles has a not null column the function doesn't fill, or a unique column (such as username) that a second user collides with.
• It depends on a metadata key that some sign-up paths don't send. new.raw_user_meta_data ->> 'full_name' is set by your sign-up form but may be missing for GitHub users or invites.
• A column was renamed or dropped after the function was written.

How to fix it

1. Open Studio (Dashboard → your project → Database tab → Open Studio) and go to the SQL editor.

2. List the triggers on auth.users:

   select tgname, pg_get_triggerdef(oid) as definition
   from pg_trigger
   where tgrelid = 'auth.users'::regclass and not tgisinternal;
   

3. Read the function each trigger calls:

   select pg_get_functiondef('public.handle_new_user'::regproc);
   

4. Reproduce the failure where you can see the real error. Insert a fake user inside a transaction and roll it back:

   begin;
   insert into auth.users (id, email, raw_user_meta_data)
   values (gen_random_uuid(), '[email protected]', '{"full_name": "QA Test"}');
   rollback;
   

   The SQL editor now shows the actual error: permission denied, missing relation, null value or duplicate key. If your role isn't allowed to insert into auth.users, run the insert into public.profiles ... statement from the function body by hand instead.

5. Fix the function. This version covers the common failures:

   create or replace function public.handle_new_user()
   returns trigger
   language plpgsql
   security definer
   set search_path = ''
   as $$
   begin
     insert into public.profiles (id, display_name)
     values (
       new.id,
       coalesce(new.raw_user_meta_data ->> 'full_name', split_part(new.email, '@', 1))
     )
     on conflict (id) do nothing;
     return new;
   end;
   $$;
   

6. Sign up again from your app to confirm.

If the profile row is useful but not essential, you can wrap the insert in a begin ... exception when others then ... end block so a profile bug never blocks sign-ups. Record the failure in a table you check, or users will quietly end up without profiles.

On SupaHost

• The project Logs tab isn't available yet, so reproducing the insert in the SQL editor (step 4) is the quickest way to see the underlying error.
• On Business and Enterprise you can read the Auth service's log over SSH with sudo supahost-logs app-one auth. The Postgres error usually appears there next to the 500.
• Only the instance owner can open Studio. Team members can see the project but can't run these queries.

Related

• Triggers
• Users and the auth schema
• NEW is null in a trigger function
• Errors creating, updating or deleting users