Signup fails: null value in column "provider_id" of relation "identities" violates not-null constraint

[jaulz]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

As of today, whenever I try to sign up I see this error in my local development environment:

null value in column "provider_id" of relation "identities" violates not-null constraint

To Reproduce

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

1. Run supabase db reset
2. Register via signUp function

const { data, error } = await supabase.auth.signUp({
        email,
        password,
      })

4. Check error return value which contains above mentioned value

Expected behavior

Registration with email and password should work without issues

Screenshots

n/a

System information

• OS: macOS
• Browser (if applies): chrome
• Version of supabase-js: 2.39.0
• Version of Node.js: 21.0.0

Additional context

My current workaround is to remove the null constraint:

alter table auth.identities alter column provider_id drop not null;

[JunsikChoi]

I have same issue.
Production auth.identities table does not match with local supabase auth.identities table.
I can not use the seed.sql for creating test users.

[J0]

Hey folks,

Thanks for flagging this! Could we trouble y'all to open a ticket here If you could state any relevant details as well as the framework(s)/tools you are using that would be great (e.g. nextjs with prisma, django with sqlalchemy, redwoodjs)

Thanks!

Upd: just realized this is local, please only open a ticket if you are facing this issue on the platform.

[hf]

@JunsikChoi Hmm... that's good information. We actually just finished releasing a version to all projects on prod, but CLI moves slightly faster than the prod deploys.

Can you check again?

I'll speak to the CLI team on making the releases synced.

[jaulz]

I just tried it again and it's the same issue on my side locally.

[sweatybridge]

I suspect this error is specific to projects on pg14. Please give npx supabase@beta start a try.

Feel free to reopen if this issue still persists.

[paynwahs]

Hi, the same issue persists on pg15.
Production auth.identities table does not match with local supabase auth.identities table (Both running pg15).
I cannot use the seed.sql for creating test users.

Problem persist even after running Supabase start using the latest beta release v1.123.6

[Shusanta]

yea my build processes are all failing with the exact same errors /:

[AndreMiras]

I got the issue when running npx supabase db reset after updating the CLI from 1.110.1 to 1.123.4 and my DB contains a seed with the following insert:

INSERT INTO auth.identities (id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)
VALUES (...);

Note how we provide the provider (which used to be a string) and the schema most likely changed to provider_id being a fk.
I gave npx supabase@beta start a try but it ends up with an error:

...
Status: Downloaded newer image for public.ecr.aws/supabase/postgres:15.1.0.147
database is not healthy
Try rerunning the command with --debug to troubleshoot the error.

Not big deal for local dev, I'll simply downgrade to a CLI version that works
Edit: it works up to version 1.121.1 and then I see the issue on version 1.122.0

[kangmingtay]

@AndreMiras interesting, the auth team introduced a migration to the auth service which makes the following changes:

• Remove the composite primary key on (auth.identities.id, auth.identities.provider)
• Rename auth.identities.id to auth.identities.provider_id
• Create a unique constraint on (auth.identities.provider_id, auth.identities.provider)
• Add a new primary key called auth.identities.id

This seems to affect seed.sql files that were relying on the old schema of the auth.identities table. I think it should work if you update all lines that contain:

INSERT INTO auth.identities (id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)

to

INSERT INTO auth.identities (provider_id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)

Note: The id should be renamed to provider_id and the new id column in auth.identities will be generated for you as the primary key on insertion.

[AndreMiras]

That did it @kangmingtay, thanks for the context

[jaulz]

@kangmingtay i don't have anything in seed.sql that inserts into identities table. Are there any other spots as well? 🤔

[EAnkerstjerne]

I suspect this error is specific to projects on pg14. Please give npx supabase@beta start a try.

Feel free to reopen if this issue still persists.

I have the issue that my local setup auth tables are not up to date with the new provider_id column in identities table. I have "major_version = 15" in supabase/config.toml. I have tried to run it with beta version of CLI and latest release version, no changes. BUT when I change major version to 14 "major_version = 14", it works as expected with the new provider_id column. It would seem the initial schemas on pg15 are not updated on the CLI.

Hope this helps in some way. ◡̈

[paynwahs]

Are there plans to upgrade the initial schemas on pg15 for Supabase CLI soon?

[sweatybridge]

Are there plans to upgrade the initial schemas on pg15 for Supabase CLI soon?

pg15 doesn't require initial schema because we run the migrations that came with gotrue docker image.

It's possible that you have have outdated image versions in supabase/.temp directory locally. Try deleting the directory to reset to the defaults, ie. rm -rf supabase/.temp.

[EAnkerstjerne]

It's possible that you have have outdated image versions in supabase/.temp directory locally. Try deleting the directory to reset to the defaults, ie. rm -rf supabase/.temp.

Thanks. This solved it for me!

I was thinking all along that I had outdated images. Just didn't know it was the .temp folder that held that information. Tried deleting all images and containers without luck. But this did the trick!

[robertsamarji]

Is there a solution to this other than downgrading the CLI version?

Using Supabase CLI v1.123.4 with pg15, removed supabase/.temp, reset the database and still getting the error (below) when signing up new users on local with supabase.auth.signInWithOtp.

"null value in column \"provider_id\" of relation \"identities\" violates not-null constraint"

[svapreddy]

Signup fails w/ same error for me as well. I don't have anything under supabase/.temp and I don't have anything in seed.sql that touches auth.identities. I am using the latest CLI version ^1.123.4.

[paynwahs]

Using Supabase CLI v1.123.6 with pg15, removed supabase/.temp, reset the database and still getting the error (below) when signing up new users on local with supabase.auth.signUp.

AuthApiError: null value in column "provider_id" of relation "identities" violates not-null constraint.

I am able to use seed.sql to populate local Supabase instance using the following code, thus verifing the latest pg15 schema.

INSERT INTO auth.identities (provider_id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)

I am using the @Supabase/ssr package and its createServerClient() function to create the Supabase client instance. I then use this instance to call supabase.auth.signUp when the AuthApiError occur.

Could this be a bug on the @Supabase/ssr package as it is unable to sign up new users while conforming to the pg15 auth.identities schema?

[artemgordash]

@paynwahs update to v1.128.1 solved for me sign up on local

[paynwahs]

@paynwahs update to v1.128.1 solved for me sign up on local

Thanks. This solved it for me!

[encima]

Closing this as it seems to be solved, confirmed working on v1.128.1 also.

Please comment here (with system information) if this is still occurring and the fix mentioned here does not work and we will reopen!