Cannot signup user, missing `identities` table

[gmisail]

Bug report

I am deploying gotrue using Docker Compose and I am having some trouble signing up. I initially used the hosted instance on Supabase and that worked fine, which is why I'm confused why this is causing me problems. It looks like when looking up identities on signup, it is ignoring the schema (DB_NAMESPACE) that I have defined, causing the query to fail since the table does not exist.

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.
  • I could find a similar issue but it looks like it is unsolved: https://github.com/orgs/supabase/discussions/16860

Describe the bug

We are currently trying to look up user identities in the identities table, however it is actually located in auth.identities.

{"component":"api","error":"unable to find identity by email for duplicates: unable to fetch records: ERROR: relation \"identities\" does not exist (SQLSTATE 42P01)","level":"error","method":"POST","msg":"500: Database error finding user","path":"/signup","referer":"http://localhost","remote_addr":"172.19.0.1","time":"2023-10-07T17:16:48Z","timestamp":"2023-10-07T17:16:48Z"}

Do these queries not respect the DB_NAMESPACE=auth environment variable that I have set? The migrations are clearly applied (as pictured in Screenshots) however it does not look like it's querying with this prefix in mind.

To Reproduce

1. Deploy gotrue v2.99.0 & PostgreSQL using Docker Compose
2. Try to sign-up using auth.signUp(...)

Expected behavior

To lookup the identities in auth.identities when creating a new user.

Screenshots

System information

• OS: Docker
• Browser (if applies) [e.g. chrome, safari]
• Version of gotrue-js: 2.55.0

Additional context

Compose:

gotrue:
    image: supabase/gotrue:v2.99.0
    ports:
      - "9999:9999"
    environment:
      DATABASE_URL: MY_DATABASE_URL
      GOTRUE_JWT_SECRET: MY_SECRET_TOKEN
      GOTRUE_JWT_EXP: 3600
      GOTRUE_DB_DRIVER: postgres
      DB_NAMESPACE: auth
      API_EXTERNAL_URL: http://localhost
      GOTRUE_API_HOST: 0.0.0.0
      PORT: 9999
      GOTRUE_DISABLE_SIGNUP: "false"
      GOTRUE_SITE_URL: http://localhost
      GOTRUE_LOG_LEVEL: DEBUG
      GOTRUE_OPERATOR_TOKEN: MY_OPERATOR_TOKEN
      GOTRUE_EXTERNAL_PHONE_ENABLED: 'true'
      GOTRUE_MAILER_AUTOCONFIRM: "true"
      GOTRUE_SMS_AUTOCONFIRM: 'true'
      GOTRUE_SMS_PROVIDER: "twilio"
    depends_on:
      - postgres
      - api
    restart: 
      on-failure

[gmisail]

After some experimentation, it seems that everything works as expected once I add my DB_NAMESPACE as a prefix to every table name. Maybe all that is needed is a quick patch to prepend this string, if it exists. Unless this is intended behavior and I'm missing another configuration somewhere?

[Jonatthu]

This is happenint to be as well, where did you set the prefix? @gmisail

[Jonatthu]

@gmisail this is what postgres logs are

2023-10-09 05:43:27.288 UTC [86] ERROR:  relation "identities" does not exist at character 168
2023-10-09 05:43:27.288 UTC [86] STATEMENT:  SELECT identities.created_at, identities.id, identities.identity_data, identities.last_sign_in_at, identities.provider, identities.updated_at, identities.user_id FROM identities AS identities WHERE email = $1

[Jonatthu]

Only reason failing is because the ORM does not generate auth.identities with auth prefix

SELECT identities.created_at, identities.id, identities.identity_data, identities.last_sign_in_at, identities.provider, identities.updated_at, identities.user_id FROM auth.identities AS identities WHERE email = 'jonathan'

[Jonatthu]

Create a custom connection string with a custom search path, that's the fix.

[gmisail]

Yeah that was the fix, thank you for the help.

[BjoernRave]

@Jonatthu @gmisail could you elaborate what exactly you mean with a custom connection strign with a custom search path?

these are my env vars

GOTRUE_SITE_URL="http://localhost:3004"
GOTRUE_JWT_SECRET="secret"

GOTRUE_DB_DRIVER="postgres"
GOTRUE_DB_NAMESPACE="auth"
DATABASE_URL="postgresql://postgres:test@postgres:5432/db"
API_EXTERNAL_URL="http://localhost:9999"
GOTRUE_API_HOST="0.0.0.0"
PORT="9999"
GOTRUE_LOG_LEVEL=debug

Btw. it took me soo long to figure out that I need to put 0.0.0.0 for GOTRUE_API_HOST instead of localhost

[loganmzz]

@BjoernRave , @gmisail

I had same issue, until I notice some configuration are not using PostgreSQL User postgres but supabase_auth_admin one.

[gaetandezeiraud]

For posterity, the fix the following DATABASE_URL: "postgres://postgres:postgres@db:5432/postgres?sslmode=disable&options=-csearch_path=auth"