Supabase: Database error saving new user

What it means in plain English

Supabase's troubleshooting guide says you generally get this error when you invite a new user from the dashboard or insert a user with the table editor. You may also see it in the logs for failed sign-ups. It is normally a side effect of something else in the same database transaction.

Supabase's guide to errors when creating, updating, or deleting users says these errors return HTTP 500 with the code unexpected_failure. The same family includes Database error creating new user, Database error updating user, and Database error deleting user.

A common design is a trigger on auth.users that copies each new user into a profiles table. Supabase explains that the role that manages authentication, supabase_auth_admin, only has the permissions it needs. When a trigger run by that role touches a table outside the auth schema, it can fail with a permission error, and the whole sign-up fails with it.

Likely causes

Supabase lists these common causes:

• A trigger or trigger function on the auth.users table has an error.
• A constraint you added on auth.users is not being met.
• Prisma broke the permissions on the auth.users table.
• The trigger function inserts into a table that does not exist, for example a profiles table that was never created or was dropped. The log then shows relation "profiles" does not exist (SQLSTATE 42P01).
• The trigger function runs as security invoker, so it has no permission on your public table. The log can show permission denied for table profiles (SQLSTATE 42501).

How to fix it, step by step

1. In the Supabase dashboard, open the Auth logs. Supabase says they surface the specific error message.
2. If the Auth logs point to the database, open the Postgres logs and find the matching error. The context line often names the function, for example PL/pgSQL function public.handle_new_user().
3. Check the triggers on the auth schema in the dashboard's trigger section.
4. If the error says a relation does not exist, create the missing table in the public schema. If the table is not needed, edit or remove that reference in the function on the Database Functions page.
5. If the error is permission denied, find the functions that use security invoker in the dashboard's function section. Supabase's fix is to drop the function with CASCADE, which also drops its trigger, then recreate the function with security definer and recreate the trigger:

DROP FUNCTION <function name>() CASCADE;

6. Recreate the function and trigger. This is Supabase's example from the User Management template in the SQL Editor. Change the columns to match your profiles table:

create function public.handle_new_user()
returns trigger
set search_path = ''
as $$
begin
  insert into public.profiles (id, full_name, avatar_url)
  values (new.id, new.raw_user_meta_data->>'full_name', new.raw_user_meta_data->>'avatar_url');
  return new;
end;
$$ language plpgsql security definer;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

7. If the error is about a constraint, check foreign or primary key relationships between auth.users and your tables. Recreate the relationship with a less restrictive rule. For deletes, Supabase points to its cascade deletes guide, for example CASCADE or SET NULL.
8. If you or an AI tool added users with a direct SQL INSERT into auth.users, Supabase warns this can leave required columns as NULL. The Auth log then shows converting NULL to string is unsupported for a column such as confirmation_token. Supabase's fix sets that column to an empty string:

update auth.users
set confirmation_token = ''
where confirmation_token is null;

How to check it worked

• A new test sign-up succeeds without the 500 error.
• The new user appears in Authentication, and the matching row appears in your profiles table if you use one.
• The Auth and Postgres logs show no new error for that sign-up.

Sources

• https://supabase.com/docs/guides/troubleshooting/database-error-saving-new-user-RU_EwB (opens in a new tab)
• https://supabase.com/docs/guides/troubleshooting/dashboard-errors-when-managing-users-N1ls4A (opens in a new tab)