I'm getting a "Database error finding user" on signup using Supabase Auth

[christopheragnus]

Bug report

Describe the bug

When I try to create a new user using the Auth components by Supabase. My code.

import { createClient } from '@supabase/supabase-js'
import { Auth, ThemeSupa } from '@supabase/auth-ui-react'
import { useSession, useSupabaseClient } from '@supabase/auth-helpers-react'

export default function Login() {
  const session = useSession()
  const supabase = createClient('https://jmysiwodgrecdxkhkipl.supabase.co', 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6ImpteXNpd29kZ3JlY2R4a2hraXBsIiwicm9sZSI6ImFub24iLCJpYXQiOjE2NzY5NDY3MzMsImV4cCI6MTk5MjUyMjczM30.l7Cik5GlWG68effPhbaG4WfM20lYn8f_EreYntnzZWQ')
  

  return (
    <div>
      <Navbar />
      <div className="" style={{ padding: '50px 0 100px 0' }}>
      <div className="flex justify-center">

      
      {!session ? (
        <Auth supabaseClient={supabase} appearance={{ theme: ThemeSupa }} theme="light" />
      ) : (
        <p>Account page will go here.</p>
      )}
      </div>
    </div>
  </div>
  )
}

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Go to the login page with Supabase Auth component
2. Try to create a new user
3. An error is returned "Database error finding user" when signing up or "Database error querying schema" when login is attempted

Network logs shows:

{code: 500, msg: "Database error finding user", error_id: "7a43a2d5463629b3-MEL"}
code: 500
error_id: "7a43a2d5463629b3-MEL"
msg: "Database error finding user"

Expected behavior

New user should be created.

Screenshots

https://imgur.com/P10rJaP

System information

• OS: Windows 11
• Browser (if applies): Chrome
• Version of "@supabase/supabase-js": "2.8.0",
• Version of Node.js: v18.12.1

Additional context

Would my schema have something to do with it?

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["storage", "auth", "public"]
}

model audit_log_entries {
  instance_id String?   @db.Uuid
  id          String    @id @db.Uuid
  payload     Json?     @db.Json
  created_at  DateTime? @db.Timestamptz(6)
  ip_address  String    @default("") @db.VarChar(64)

  @@index([instance_id], map: "audit_logs_instance_id_idx")
  @@schema("auth")
}

model identities {
  id              String
  user_id         String    @db.Uuid
  identity_data   Json
  provider        String
  last_sign_in_at DateTime? @db.Timestamptz(6)
  created_at      DateTime? @db.Timestamptz(6)
  updated_at      DateTime? @db.Timestamptz(6)
  email           String?   @default(dbgenerated("lower((identity_data ->> 'email'::text))"))
  users           users     @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@id([provider, id])
  @@index([email])
  @@index([user_id])
  @@schema("auth")
}

model instances {
  id              String    @id @db.Uuid
  uuid            String?   @db.Uuid
  raw_base_config String?
  created_at      DateTime? @db.Timestamptz(6)
  updated_at      DateTime? @db.Timestamptz(6)

  @@schema("auth")
}

model mfa_amr_claims {
  session_id            String   @db.Uuid
  created_at            DateTime @db.Timestamptz(6)
  updated_at            DateTime @db.Timestamptz(6)
  authentication_method String
  id                    String   @id(map: "amr_id_pk") @db.Uuid
  sessions              sessions @relation(fields: [session_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@unique([session_id, authentication_method], map: "mfa_amr_claims_session_id_authentication_method_pkey")
  @@schema("auth")
}

model mfa_challenges {
  id          String      @id @db.Uuid
  factor_id   String      @db.Uuid
  created_at  DateTime    @db.Timestamptz(6)
  verified_at DateTime?   @db.Timestamptz(6)
  ip_address  String      @db.Inet
  mfa_factors mfa_factors @relation(fields: [factor_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "mfa_challenges_auth_factor_id_fkey")

  @@schema("auth")
}

model mfa_factors {
  id             String           @id @db.Uuid
  user_id        String           @db.Uuid
  friendly_name  String?
  factor_type    factor_type
  status         factor_status
  created_at     DateTime         @db.Timestamptz(6)
  updated_at     DateTime         @db.Timestamptz(6)
  secret         String?
  mfa_challenges mfa_challenges[]
  users          users            @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([user_id, created_at], map: "factor_id_created_at_idx")
  @@schema("auth")
}

model refresh_tokens {
  instance_id String?   @db.Uuid
  id          BigInt    @id @default(autoincrement())
  token       String?   @unique(map: "refresh_tokens_token_unique") @db.VarChar(255)
  user_id     String?   @db.VarChar(255)
  revoked     Boolean?
  created_at  DateTime? @db.Timestamptz(6)
  updated_at  DateTime? @db.Timestamptz(6)
  parent      String?   @db.VarChar(255)
  session_id  String?   @db.Uuid
  sessions    sessions? @relation(fields: [session_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([instance_id])
  @@index([instance_id, user_id])
  @@index([parent])
  @@index([session_id, revoked])
  @@index([token])
  @@schema("auth")
}

model saml_providers {
  id                String        @id @db.Uuid
  sso_provider_id   String        @db.Uuid
  entity_id         String        @unique
  metadata_xml      String
  metadata_url      String?
  attribute_mapping Json?
  created_at        DateTime?     @db.Timestamptz(6)
  updated_at        DateTime?     @db.Timestamptz(6)
  sso_providers     sso_providers @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([sso_provider_id])
  @@schema("auth")
}

model saml_relay_states {
  id              String        @id @db.Uuid
  sso_provider_id String        @db.Uuid
  request_id      String
  for_email       String?
  redirect_to     String?
  from_ip_address String?       @db.Inet
  created_at      DateTime?     @db.Timestamptz(6)
  updated_at      DateTime?     @db.Timestamptz(6)
  sso_providers   sso_providers @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([for_email])
  @@index([sso_provider_id])
  @@schema("auth")
}

model schema_migrations {
  version String @id @db.VarChar(255)

  @@schema("auth")
}

model sessions {
  id             String           @id @db.Uuid
  user_id        String           @db.Uuid
  created_at     DateTime?        @db.Timestamptz(6)
  updated_at     DateTime?        @db.Timestamptz(6)
  factor_id      String?          @db.Uuid
  aal            aal_level?
  not_after      DateTime?        @db.Timestamptz(6)
  mfa_amr_claims mfa_amr_claims[]
  refresh_tokens refresh_tokens[]
  users          users            @relation(fields: [user_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([user_id])
  @@index([user_id, created_at], map: "user_id_created_at_idx")
  @@schema("auth")
}

model sso_domains {
  id              String        @id @db.Uuid
  sso_provider_id String        @db.Uuid
  domain          String
  created_at      DateTime?     @db.Timestamptz(6)
  updated_at      DateTime?     @db.Timestamptz(6)
  sso_providers   sso_providers @relation(fields: [sso_provider_id], references: [id], onDelete: Cascade, onUpdate: NoAction)

  @@index([sso_provider_id])
  @@schema("auth")
}

model sso_providers {
  id                String              @id @db.Uuid
  resource_id       String?
  created_at        DateTime?           @db.Timestamptz(6)
  updated_at        DateTime?           @db.Timestamptz(6)
  saml_providers    saml_providers[]
  saml_relay_states saml_relay_states[]
  sso_domains       sso_domains[]

  @@schema("auth")
}

model users {
  instance_id                 String?       @db.Uuid
  id                          String        @id @db.Uuid
  aud                         String?       @db.VarChar(255)
  role                        String?       @db.VarChar(255)
  email                       String?       @db.VarChar(255)
  encrypted_password          String?       @db.VarChar(255)
  email_confirmed_at          DateTime?     @db.Timestamptz(6)
  invited_at                  DateTime?     @db.Timestamptz(6)
  confirmation_token          String?       @db.VarChar(255)
  confirmation_sent_at        DateTime?     @db.Timestamptz(6)
  recovery_token              String?       @db.VarChar(255)
  recovery_sent_at            DateTime?     @db.Timestamptz(6)
  email_change_token_new      String?       @db.VarChar(255)
  email_change                String?       @db.VarChar(255)
  email_change_sent_at        DateTime?     @db.Timestamptz(6)
  last_sign_in_at             DateTime?     @db.Timestamptz(6)
  raw_app_meta_data           Json?
  raw_user_meta_data          Json?
  is_super_admin              Boolean?
  created_at                  DateTime?     @db.Timestamptz(6)
  updated_at                  DateTime?     @db.Timestamptz(6)
  phone                       String?       @unique
  phone_confirmed_at          DateTime?     @db.Timestamptz(6)
  phone_change                String?       @default("")
  phone_change_token          String?       @default("") @db.VarChar(255)
  phone_change_sent_at        DateTime?     @db.Timestamptz(6)
  confirmed_at                DateTime?     @default(dbgenerated("LEAST(email_confirmed_at, phone_confirmed_at)")) @db.Timestamptz(6)
  email_change_token_current  String?       @default("") @db.VarChar(255)
  email_change_confirm_status Int?          @default(0) @db.SmallInt
  banned_until                DateTime?     @db.Timestamptz(6)
  reauthentication_token      String?       @default("") @db.VarChar(255)
  reauthentication_sent_at    DateTime?     @db.Timestamptz(6)
  is_sso_user                 Boolean       @default(false)
  deleted_at                  DateTime?     @db.Timestamptz(6)
  identities                  identities[]
  mfa_factors                 mfa_factors[]
  sessions                    sessions[]
  profiles                    UserProfile?

  @@index([instance_id])
  @@schema("auth")
}

enum aal_level {
  aal1
  aal2
  aal3

  @@schema("auth")
}

enum factor_status {
  unverified
  verified

  @@schema("auth")
}

enum factor_type {
  totp
  webauthn

  @@schema("auth")
}

model Ride {
  id                  Int         @id @default(autoincrement())
  title               String
  originLocation      String
  destinationLocation String
  pickupDate          String
  pickupTime          String
  numberOfPassengers  Int
  description         String
  pricePerSeat        Int
  userId              Int
  createdAt           DateTime    @default(now())
  dropoffTime         String
  User                UserProfile @relation(fields: [userId], references: [id])

  @@schema("public")
}

model UserProfile {
  id          Int      @id @default(autoincrement())
  bio         String?
  firstName   String
  lastName    String
  phoneNumber String
  sex         Gender?
  email       String   @unique
  stripeId    String   @unique
  createdAt   DateTime @default(now())
  rides       Ride[]
  auth        users    @relation(fields: [authId], references: [id], onDelete: Cascade, onUpdate: NoAction)
  authId      String   @unique @db.Uuid

  @@map("User")
  @@schema("public")
}

enum Gender {
  Male
  Female

  @@schema("public")
}

[GaryAustin1]

There might be more specific information in the dashboard Postgres log right after the error.
I don't think it would be related to the error you are seeing, but do you have a trigger function on auth.users? If so you might want to disable the trigger just to rule that out.

[GaryAustin1]

Also any chance you use the Prisma multiSchema feature? (I don't know what that is exactly) but at least one other user had their grants on the auth schema messed up by using that.

[christopheragnus]

Hey Gary - all my triggers have been disabled. I am using the Prisma multiSchema feature. I have tried restoring grants using this: https://supabase.com/docs/guides/integrations/prisma#missing-grants

But the error still occurs.

I can't even get the project usage statistics to show up...

[GaryAustin1]

All I know is a discord user lost his auth grants using Prisma mulitSchema. The user never said what he did after, but I think started a new instance.

The schema fix shown does not include auth schema I don’t believe.

I suspect this is something new with Prisma/Supabase based on two of you in a week having it and not seeing this before.

[marc-tech]

I also get this error :

Describe the bug
I'm trying to create a new user with supabase-js and the local dev environment in a unit test. before test runs, I call

supabase db reset

const { data, error } = await supabase.auth.signUp({ email: "user1aaa@emailasdf.com", password: "password-1", });

this call give me :

 data: { user: null, session: null },
 error: AuthApiError: Database error finding user

every thing work fine on the second and all other call

[estebangarcia21]

Same here, the lack of documentation and issues ive been facing with local Supabase development (using Prisma and the Supabase client) definitely has me reconsidering my decision to use Supabase as a BaaS for our startup. Hopefully we can resolve work to resolve this issue soon as I was looking forward to using Supabase.

[estebangarcia21]

For me I resolved the issue by running supabase stop and then supabase start. Hopefully this can help someone out.

[marc-tech]

this was a supabase key missing for me, check your env and auth token and add --debug to see if any other error can occur

[kangmingtay]

hello everyone, you can take a look at the detailed error returned from the logs explorer on the dashboard: https://app.supabase.com/project/_/logs/explorer and reach out to us at support (https://app.supabase.com/support/new) if you see something strange.

[kangmingtay]

Hey @christopheragnus, @marc-tech, @estebangarcia21, i'm not really familiar with prisma's multi schema feature, but it would be great you guys can suggest possible remediation steps or solutions that worked for you. If Prisma makes modifications to the auth schema under the hood, it will cause gotrue to fail since the auth schema is supposed to be managed by gotrue only.

I'll be closing this issue since it's not related to gotrue but feel free to reopen it or comment in this thread.

[NicolasChusseau]

I have a similar error :
Describe the bug
After doing a migration, I am no longer able to log in or sign up. I used to be able to.
I have an error 500 saying nothing else but I when I look at my log I see these errors :

If anyone found the magic answer after a week, I'm interested.

[hixday]

Same here, Prisma has been nothing but problems and pain for me, i'm about to ditch it.