"Error 500: Database Error During User Signup in Supabase Authentication"

[hajarij]

Hello Supabase Support Team,

I’ve been encountering a persistent issue with my Supabase project for the past five days. Whenever a user tries to sign up via the /auth/v1/signup endpoint, the server returns a 500 error with the following details:

Error Type: HTTP Server Error
Status: 500
Error Message: Database error saving new user
API Endpoint:
https://ehwdnzkzoogxorncyyof.supabase.co/auth/v1/signup?redirect_to=https%3A%2F%2Fd1f85819-da5a-4f43-93a7-650320baf210.lovableproject.com%2Fauth%2Fcallback
Response Body:
json
le code :
{
"code": "unexpected_failure",
"message": "Database error saving new user"
}
I’ve verified the following on my end:

My Supabase authentication settings, including the redirect URL, are correctly configured.
The database schema related to user creation appears to be intact.
Resource usage is within the limits of my current free plan.
Despite these checks, the issue persists. Could you please help me identify the root cause and suggest a resolution? If it’s related to resource limits or any misconfiguration on my end, please let me know.

Thank you for your assistance. Let me know if you need any additional details or logs.

Best regards,
Arij Hajlaoui

[GaryAustin1]

Please check the auth logs and the Postgres logs in the dashboard for the exact database error.

This is almost always you have a trigger function on auth.users that is failing.

[hajarij]

I have implemented the changes as recommended . However, I am still encountering the following error message:
"Erreur de récupération de profil" (Profile retrieval error)

[GaryAustin1]

Can you show the actual postgres or auth log error?
Do you have an auth.users trigger function? If so maybe show that also.

[rovigomanucci]

Hi there @GaryAustin1, I am having the same issue, this is the auth log (I have a feeling it is policy issues as I was trying to define a couple of role owner, staff and probably it's missing something) :
{
"event_message": "{"component":"api","error":"failed to close prepared statement: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: invalid input value for enum public.user_role: \"\" (SQLSTATE 22P02)","level":"error","method":"POST","msg":"500: Database error saving new user","path":"/signup","referer":"http://localhost:3000\",\"remote_addr\":\"hidden\",\"request_id\":\"8fc667bf87571b99-DUB\",\"time\":\"2025-01-03T22:25:32Z\"}",
"id": "1fbea76b-d5bb-47cd-b5e7-b109a09bb83f",
"metadata": [
{
"host": "db-yvyfulexcngqmvzutbbj",
"component": "api",
"_SYSTEMD_CGROUP": null,
"request_id": "8fc667bf87571b99-DUB",
"mail_from": null,
"message": null,
"_SOURCE_REALTIME_TIMESTAMP": null,
"PRIORITY": null,
"_AUDIT_LOGINUID": null,
"panic": null,
"metering": null,
"UNIT": null,
"event": null,
"SYSLOG_FACILITY": null,
"msg": "500: Database error saving new user",
"mail_type": null,
"EXECUTABLE": null,
"user_id": null,
"_CMDLINE": null,
"action": null,
"auth_event": [],
"level": "error",
"_PID": null,
"path": "/signup",
"duration": null,
"_COMM": null,
"header": null,
"_MACHINE_ID": null,
"login_method": null,
"_STREAM_ID": null,
"source_type": null,
"_LINE_BREAK": null,
"_EXE": null,
"_AUDIT_SESSION": null,
"_TRANSPORT": null,
"x_forwarded_proto": null,
"time": null,
"mail_to": null,
"_GID": null,
"stack": null,
"x_forwarded_host": null,
"status": null,
"_UID": null,
"method": "POST",
"CODE_FILE": null,
"remote_addr": "hidden",
"provider": null,
"_SYSTEMD_UNIT": null,
"issuer": null,
"error": "failed to close prepared statement: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: invalid input value for enum public.user_role: "" (SQLSTATE 22P02)",
"client_id": null,
"MESSAGE_ID": null,
"referer": "http://localhost:3000",
"_SYSTEMD_INVOCATION_ID": null,
"CODE_FUNC": null,
"_BOOT_ID": null,
"INVOCATION_ID": null,
"__MONOTONIC_TIMESTAMP": null,
"timestamp": null,
"__REALTIME_TIMESTAMP": null,
"CODE_LINE": null,
"_SYSTEMD_SLICE": null,
"instance_id": null,
"args": [],
"SYSLOG_IDENTIFIER": null,
"metadata": [],
"_CAP_EFFECTIVE": null,
"factor_id": null,
"_SELINUX_CONTEXT": null,
"project": null
}
],
"timestamp": 1735943132000000
}

My payload is:
{
"email": "[email protected]",
"password": "xxxxxxxx",
"metadata": {
"role": "owner"
}
}

Verify Enum Values are there for the user table
The default value for role is "owner"

Thanks a mill, I've been cracking my head around to find this issue.

Rm

[GaryAustin1]

Pretty clear you are trying to do an enum and something is wrong with the value you are sending for that or the column it is going into.

My guess is you need to cast the jsonb string value with ::public.enumname to convert to an enum value.

But you don't show any code or enum definition. Also ask an AI about your trigger function.

[rovigomanucci]

Hi @GaryAustin1 ,

this my code:

signUp: async (email: string, password: string) => {
set({ isLoading: true, error: null });
try {

const { data, error } = await supabase.auth.signUp({
  email,
  password,
  metadata: {
    role: 'owner'
  }
  
});

// Log the response from Supabase
console.log('Signup Response:', { data, error });

if (error) {
  throw new Error('This: ' + error.message);
}

if (data.user) {
  set({
    user: {
      id: data.user.id,
      email: data.user.email!,
      name: data.user.user_metadata?.name,
      role: 'owner',
      createdAt: new Date(data.user.created_at)
    },
    isLoading: false
  });
}

} catch (error) {
console.error('Signup Error:', error); // Log any caught errors
set({
error: error instanceof Error ? error.message : 'Failed to sign up',
isLoading: false
});
}
}

I can't see anything wrong :(

[GaryAustin1]

I don't understand...
That is your client side code.
I assume you have a database trigger on auth.users which takes the metadata and inserts it to another table with an enum column...
Otherwise there would be no enum column involved.

Also if you are using JS that does not look a valid call at all...