Fix guide · updated 2026-11-09
Database error saving new user (Supabase): find the real error and fix sign-up
Database error saving new user · AuthApiError 500 · unexpected_failure
Nobody can sign up: your app shows Database error saving new user, and the Network tab shows a 500 for the request to /auth/v1/signup. Supabase Auth itself is rarely the cause. When it creates an account, your database also runs whatever your app attached to new users, usually a trigger that copies each new user into a profiles table. If that step fails, the whole sign-up is cancelled, no account is created, and Auth only reports this generic message. The real error is in the logs.
1. Read the real error
Make a sign-up fail once more, then open the logs:
- Lovable: More → Settings → Logs, then Authentication logs or PostgreSQL logs in the log type selector. Click Refresh, since the logs don't update by themselves.
- Bolt: the database icon at the top of your project, then Logs, then PostgreSQL or Authentication in the dropdown.
- Your own Supabase project: the Logs page of the dashboard, then the Auth or Postgres logs.
In the Auth logs, the failed sign-up carries the database's error at the end of a longer line. Here's Supabase's own example:
failed to close prepared statement: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: relation "profiles" does not exist (SQLSTATE 42P01)
Only the last ERROR matters. The Postgres logs show the same error, with the function and the line that raised it:
PL/pgSQL function public.handle_new_user() line 3 at SQL statement
Adding or inviting a user from the Supabase dashboard runs the same trigger, so it can fail with the same message.
2. Match the error
We reproduced the usual mistakes on Postgres 16, with Supabase's roles. The sign-up was written as Supabase Auth's own database role, supabase_auth_admin, the way Auth does it. Each mistake gave one of these errors:
permission denied for table profiles (SQLSTATE 42501). The function was created without security definer, so it runs as Supabase Auth's role, which has no rights on your tables. Recreate it with security definer, as in Supabase's example:
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
insert into public.profiles (id, first_name, last_name)
values (new.id, new.raw_user_meta_data ->> 'first_name', new.raw_user_meta_data ->> 'last_name');
return new;
end;
$$;
Keep the column list matched to your own profiles table. create or replace keeps the trigger attached to the new version.
relation "profiles" does not exist (SQLSTATE 42P01). With set search_path = '', every table needs its schema: public.profiles, not profiles. If the name already has its schema, the table really is missing: it was renamed, dropped or never created. Create it, or update the function to use the right name.
null value in column "username" of relation "profiles" violates not-null constraint (SQLSTATE 23502). The table requires a column the function doesn't fill, often a column added after the trigger was written. Or the app doesn't send that field when people sign up. Make the column optional, give it a default value, or make sure the app always sends it.
value too long for type character varying(50) (SQLSTATE 22001). The function copies a sign-up value into a column with a length limit, and someone's value is longer. In our test of this error, a 55-character full name and a full_name varchar(50) column stopped the new user from being created. The message gives the limit but not the column: see Supabase "value too long for type character varying" (22001) to find it and widen it.
duplicate key value violates unique constraint "profiles_username_key" (SQLSTATE 23505). A column that must be unique, like a username, got a value someone already has. This happens when the function builds it from the person's name or from the first part of their email. Let people choose a username after they sign up, or add a random part to the generated one.
invalid input value for enum public.app_role: "Admin" (SQLSTATE 22P02). The function turns sign-up data into a fixed list of values, here a role, and the app sent a value that isn't on the list. This one also points to a security hole: never take a role from sign-up data. That data comes from the person signing up, so anyone can send admin. Supabase's docs say users can change their own raw_user_meta_data, so it's no place for anything that grants access. Give every new user the default role in the function, and change roles from the dashboard or from an admin-only function.
3. Apply the fix and test it
To see the function as it is now, run this in the SQL editor (Supabase's query), or ask your AI tool to show you the code of handle_new_user:
select pg_get_functiondef(oid)
from pg_proc
where proname = 'handle_new_user';
After the fix, sign up with a new email address and check that a matching row appears in profiles. If people need to sign up before you can fix the function, you can remove the trigger for now:
drop trigger on_auth_user_created on auth.users;
Sign-ups work again, but new users get no profile until you put the trigger back, so ask your AI tool to create the missing profiles once it's fixed.
4. Less common causes
- A constraint you added on
auth.users, or a foreign key pointing to it, isn't met. The log names the constraint. Supabase's troubleshooting page suggests dropping it, or recreating a foreign key withon delete set nulloron delete cascade. must be owner of …in the log: a migration tool such as Prisma changed who owns the objects in theauthschema. Supabase's page "Resolving 500 status authentication errors" shows how to give them back tosupabase_auth_admin.- Changes to the
authschema itself, like row level security on its tables or edited columns. Supabase manages that schema, and these changes break Auth. Undo them.
If the message is Database error querying schema instead, and the log says converting NULL to string is unsupported, users were added to auth.users by a SQL insert rather than through Auth, which some AI tools do. Supabase's fix is to set the column named in the log to an empty string, for example update auth.users set confirmation_token = '' where confirmation_token is null;. For one statement that fixes all eight columns that can be NULL, and what else those rows break, see our guide to "Database error querying schema".
If sign-up works but sign-in fails with Error running hook URI, the function that fails is your Custom Access Token hook: see our guide to "Error running hook URI".
If sign-up works but sign-in fails with Database error granting user, a trigger that runs at every sign-in is failing, usually one after update on auth.users: see our guide to "Database error granting user".
Still stuck?
Write down the last ERROR from the log, the function's code and your profiles table's columns. That's what anyone helping you will ask for first, and comparing the function with the table often shows the problem right away.
Other Supabase problems? See Supabase fixes.