Unique constraint violation on 2nd auth attempt

[jspears]

Bug report

Describe the bug

The first time a user uses OAuth (github/gmail) to login it works, the second throws an error. Digging into it seems to error on a constraint on email. Like it is doing an insert instead of an upsert. Attached below are the relevant logs.

Log ID
63e4e3e0-2f71-4671-a15f-772909e96277

Log Timestamp (UTC)
2023-02-01T18:05:14.488Z

Log Event Message
current transaction is aborted, commands ignored until end of transaction block

Log Metadata
[
  {
    "file": "/var/log/postgresql/postgresql.csv",
    "host": "db-gtxuwfqqizavtqcjehwl",
    "metadata": [],
    "parsed": [
      {
        "application_name": null,
        "backend_type": "client backend",
        "command_tag": "DEALLOCATE",
        "connection_from": "127.0.0.1:40538",
        "context": null,
        "database_name": "postgres",
        "detail": null,
        "error_severity": "ERROR",
        "hint": null,
        "internal_query": null,
        "internal_query_pos": null,
        "leader_pid": null,
        "location": null,
        "process_id": 26670,
        "query": "deallocate \"pgx_31\"",
        "query_id": 0,
        "query_pos": null,
        "session_id": "63d2e7b1.682e",
        "session_line_num": 16,
        "session_start_time": "2023-01-26 20:50:57 UTC",
        "sql_state_code": "25P02",
        "timestamp": "2023-02-01 18:05:14.488 UTC",
        "transaction_id": 1383,
        "user_name": "supabase_auth_admin",
        "virtual_transaction_id": "9/0"
      }
    ],
    "parsed_from": null,
    "project": "gtxuwfqqizavtqcjehwl",
    "source_type": "file"
  }
]


Log ID
c9209fe8-8a7a-41f7-bddb-dd2fdb7847c9

Log Timestamp (UTC)
2023-02-01T18:05:14.487Z

Log Event Message
duplicate key value violates unique constraint "users_email_partial_key"

Log Metadata
[
  {
    "file": "/var/log/postgresql/postgresql.csv",
    "host": "db-gtxuwfqqizavtqcjehwl",
    "metadata": [],
    "parsed": [
      {
        "application_name": null,
        "backend_type": "client backend",
        "command_tag": "INSERT",
        "connection_from": "127.0.0.1:40538",
        "context": null,
        "database_name": "postgres",
        "detail": "Key (email)=(XXX@gmail.com) already exists.",
        "error_severity": "ERROR",
        "hint": null,
        "internal_query": null,
        "internal_query_pos": null,
        "leader_pid": null,
        "location": null,
        "process_id": 26670,
        "query": "INSERT INTO \"users\" (\"aud\", \"banned_until\", \"confirmation_sent_at\", \"confirmation_token\", \"created_at\", \"email\", \"email_change\", \"email_change_confirm_status\", \"email_change_sent_at\", \"email_change_token_current\", \"email_change_token_new\", \"email_confirmed_at\", \"encrypted_password\", \"id\", \"instance_id\", \"invited_at\", \"is_sso_user\", \"last_sign_in_at\", \"phone\", \"phone_change\", \"phone_change_sent_at\", \"phone_change_token\", \"phone_confirmed_at\", \"raw_app_meta_data\", \"raw_user_meta_data\", \"reauthentication_sent_at\", \"reauthentication_token\", \"recovery_sent_at\", \"recovery_token\", \"role\", \"updated_at\") VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31)",
        "query_id": 7171562738777285000,
        "query_pos": null,
        "session_id": "63d2e7b1.682e",
        "session_line_num": 15,
        "session_start_time": "2023-01-26 20:50:57 UTC",
        "sql_state_code": "23505",
        "timestamp": "2023-02-01 18:05:14.487 UTC",
        "transaction_id": 1383,
        "user_name": "supabase_auth_admin",
        "virtual_transaction_id": "9/23490"
      }
    ],
    "parsed_from": null,
    "project": "gtxuwfqqizavtqcjehwl",
    "source_type": "file"
  }
]

To Reproduce

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

1. Go to [https://jspears.github.io/authbug] (https://jspears.github.io/authbug) source
2. Click on 'login github'
3. Click Logout
4. Click login 'github'

See error
https://jspears.github.io/authbug/?error=server_error&error_description=Database+error+saving+new+user?error=server_error&error_description=Database+error+saving+new+user

Expected behavior

No Error

Screenshots

System information

• OS: macOS
• Browser chrome,safari, firefox
• Version of supabase-js: @supabase/supabase-js": "^2.7.0"
• Version of Node.js: N/A

Additional context

I had an auth trigger, that I thought might have caused this error, but I removed it and it still happens. I also tried a 'clean' supabase instance and it did not seem reproduce.

From the schema it says

COMMENT ON INDEX auth.users_email_partial_key IS 'Auth: A partial unique index that applies only when is_sso_user is false';

I tried changing auth.is_user_sso to true/false/default, this did not fix the problem.

Add any other context about the problem here.

[jspears]

So i've toggled the is_sso_user boolean to true and the issue goes away. My assumption is if you use the OTM method first, than try to use SSO for auth, it will result in this condition.

[ruggi99]

Maybe related: #929

[kangmingtay]

hey @jspears, is the initial login via oauth a completely new user or does the user already exist? Also, did you have any triggers / functions that manually creates a user / modifies the data being inserted into the auth schema?

[KPreetham]

Hi @kangmingtay , I am facing similar issue.

When a user creates an account using OAuth (Google), the account gets created.

But when the user creates and account using email+password, the account gets created. But afterwards when user tries to sign in with google using the same email id, following error is thrown

duplicate key value violates unique constraint "users_email_partial_key"

[ferrannp]

I have a similar issue for merging users. I described the issue here #1109.

Are any updates to this @J0 ? Merging users with same email is quiet a deal breaker.

[bigp34ch]

any update on this? this is so annoying

[hf]

@kangmingtay could this be related to this fix #990, i.e. that fixes this issue?

[hf]

I believe this is fixed with #990. Will close this issue it's rolling out in the next few days on all projects.