An "invalid response was received from the upstream server" error when querying auth

[kangmingtay]

If you are observing an "invalid response was received from the upstream server" error when making requests to Supabase Auth, it could mean that the respective service is down. One way to confirm this is to visit the logs explorer and look at the auth logs to see if there are any errors with the following lines:

• running db migrations: error executing migrations/20221208132122_backfill_email_last_sign_in_at.up.sql

We're currently investigating an issue where the tables responsible for keeping track of migrations ran by Auth (auth.schema_migrations) are not being restored properly, which leads to the service(s) retrying those migrations. In such cases, migrations which are not idempotent will run into issues.

We've documented some of the migrations that run into this issue and their corresponding fix here:

Auth: operator does not exist: uuid = text

Temporary fix: Run insert into auth.schema_migrations values ('20221208132122'); via the sql editor to fix the issue.

If the migration error you're seeing looks different, please reach out to supabase.help for assistance.

[FutureFutureTo]

thanks for this - any progress on it being fixed? im in the early phases of an app and need to reset the auth tables often enough in production

[kangmingtay]

you shouldn't be resetting the auth.schema_migrations table as that table is used by the auth service to detect which migrations have be run already

[Demizon3433]

I just ran into this issue on two of my supabase projects. The temporary fix works. Thanks for that!

I'd like to paste in the exact error from the database log to make this issue easier to find by just pasting it into the browser.

{"level":"fatal","msg":"running db migrations: error executing migrations/20221208132122_backfill_email_last_sign_in_at.up.sql, sql: -- previous backfill migration left last_sign_in_at to be null, which broke some projects\n\ndo $$\nbegin\nupdate auth.identities\n set last_sign_in_at = '2022-11-25'\n where\n last_sign_in_at is null and\n created_at = '2022-11-25' and\n updated_at = '2022-11-25' and\n provider = 'email' and\n id = user_id::text;\nend $$;\n: ERROR: operator does not exist: uuid = text (SQLSTATE 42883)","time":"2024-04-30T12:41:58Z"}

[marcnajera]

Had the same problem this week. The fix worked (thanks!) but it was quite hard to figure out what was going on. Do you have updates on this?

[Tananga]

@kangmingtay Im often executes "supabase db reset --linked" and I have the same issue. #26247

[kangmingtay]

@Tananga before you ran supabase db reset --linked, did you check if the auth service was healthy or if it was down already?

[Tananga]

I didnt check it. But there were 2 commands which Im executing very often

Supabase db reset --linked

And from time to time:

delete from supabase_migrations.schema_migrations;

[sweatybridge]

npx supabase@beta db reset --linked should fix this issue. More details here https://github.com/orgs/supabase/discussions/20722#discussioncomment-9601951

[ThatGuySam]

npx supabase@beta db reset --linked should fix this issue. More details here https://github.com/orgs/supabase/discussions/20722#discussioncomment-9601951

Tried this today with that command which resolved to 1.172.4: No luck.

Ended up having to migrate over to a brand new database instance for that version.

Would still love a solution in case this happens on an instance I can't migrate the data for.

[tobiastornros]

Adding screenshot. Our staging project got this error after random shutdown today. Any progress on a fix?

Adding errors for search visibility:
error executing migrations/20221125140132_backfill_email_identity.up.sql ...
error executing migrations/20221208132122_backfill_email_last_sign_in_at.up.sql ...

Fix:
insert into auth.schema_migrations values ('20221125140132');
insert into auth.schema_migrations values ('20221208132122');

[tobiastornros]

Just fyi. It happens when the auth service restart and not when we use supabase cli to reset linked project.

[kangmingtay]

@tobiastornros does this happen everytime you restart the auth service? (i.e when you restart your project through the dashboard and then use supabase cli to reset the linked project?)

or does this happen randomly?

[tobiastornros]

Randomly. You can see in the screenshot above that the auth service get shutdown signal at 11:31. We run the reset command around 03:00 every night.

[kangmingtay]

@tobiastornros can you please send us a ticket for that particular project at https://supabase.help and link this thread in the ticket so we can dig into it?

[tobiastornros]

@kangmingtay Done

[tmountain]

Same bug over here. Can someone explain what is happening here?

[tmountain]

We have a Github action doing this when we deploy to our staging DB.

jobs:
  build:
    runs-on: ubuntu-latest
    env:
      supabase_db_url: ${{ secrets.STAGING_DB_URL }}

    steps:
      - uses: actions/checkout@v4

      - uses: supabase/setup-cli@v1
        with:
          version: latest

      - run: yes | supabase db reset --db-url "$supabase_db_url"

kiwicopple from Supabase indicated that this was okay on Reddit. It's now an important part of our dev workflow. It'd be really nice to have this supported without having sporadic outages on our instance.

[FutureFutureTo]

Yea, my particular use case here is that I am still working on my local schema and would like to be able to have my production match exactly once I have landed on what it is I have decided. I understand that once in production, this is bad practice, but I'm still tweaking and would like the flexibility to easily have production match exactly local.

[kangmingtay]

@tmountain does this always happen every time to CI runs or does it happen randomly?

[tmountain]

@kangmingtay It's random. Feels like a race condition of some kind.

[sweatybridge]

I've updated CLI beta channel to not reset the auth migrations table. You can update it on GHA like this

      - uses: supabase/setup-cli@v1
        with:
          version: 1.172.1

The next stable release will be on 1 June. After that, you can revert back to version: latest.

[sebmor]

Had it as well on production db

[etzelc]

I've seen this on our testing instance as well and applied the temporary fix successfully. Is there any Github issue tracking the initial problem of the untracked Auth migrations?

[etzelc]

Yes, we are using supabase cli to reset our testing instance. If needed I can provide more details on the exact command order on Thursday.

[kangmingtay]

that would be great @etzelc

[etzelc]

We've noticed that the issue occasionally arises on our testing instance. The testing instance is the only instance which is reseted during the deployment, because it is used to try new features, migrations, etc. from various feature branches. Here's an outline of the deployment steps:

- npx supabase link --project-ref ABC123XYZ789
- npx supabase db reset --linked # there are migrations in supabase/migrations and test data in supabase/seed.sql
- npx supabase secrets set ...
- npx supabase functions deploy # and some more commands to delete uncommitted (old) upstream functions

The supabase CLI version is either the newest stable release or one stable version released within the past weeks.

[sweatybridge]

We have updated CLI on 1 June so it should no longer reset the auth migrations table. See https://github.com/orgs/supabase/discussions/20722#discussioncomment-9601951

[etzelc]

Thanks for your response and the fix. We upgraded our CLI yesterday and haven't experienced the issue since.

[robertbodley]

I am experiencing the same issue (the temp fix worked)

[indie-rok]

Thanks! Can you point me on how to properly reset production db? (if supabase db reset --linked is not a good idea)

[sweatybridge]

The reset command was bugged. It is now fixed in CLI v1.172.2 so it no longer clears the auth migrations table. See https://github.com/orgs/supabase/discussions/20722#discussioncomment-9601951

[fvermaut]

@sweatybridge thanks for the update - but was it really the root cause? Because for me the issue happened randomly, not after a db reset

[rookledookle]

Experienced this issue today, fixed worked, but I'm wondering:

What did I do / what happened that caused this? I don't believe I made any changes since it was last working (yesterday) vs getting the error today.

Not sure if these details are relevant, posting in case it's helpful:

• I was testing my login flows on my staging and prod environment (both separate supabase projects) and doing them in the same browser without clearing cache when this happened
• I set up my prod db just yesterday by pulling from my staging db to local, and then doing a supabase link, then a supabase db reset --linked via the CLI

[rookledookle]

My main worry is if this is going to happen again / how do I prevent it as breaking auth on prod is really scary

[rookledookle]

Also, slight aside: am I able to get these types of errors proactively reported via alerting somehow? It's not captured in Sentry but I don't know if that's due to my instrumentation or if it just won't catch such errors.

[kangmingtay]

we don't offer alerting directly from the platform but there is a health check endpoint https://your_project_ref.supabase.co/auth/v1/health that you can periodically ping to get alerts if the service is down

I set up my prod db just yesterday by pulling from my staging db to local, and then doing a supabase link, then a supabase db reset --linked via the CLI

when you run supabase link + supabase db reset, are those ran against the staging db or production db?

[rookledookle]

I do a supabase db pull from staging, and then a supabase db link (to production) and supabase db reset --linked (production)

Thanks! Let me know if you need more info

[sweatybridge]

We have updated CLI in release v1.172.2 so it no longer resets auth migrations table.

[LeMark0]

In case the quick fix is not working, one possible reason is that GOTRUE tries to add the record to the public.schema_migrations table instead of auth.schema_migrations.
To fix this, you'll need to either add the record 20221208132122 to public.schema_migrations or change the search order for your DB:

ALTER ROLE supabase_auth_admin SET search_path TO auth,public;

[Zeulni]

Had the same issue in production today and thus a downtime of 1-2 hours. The quick fix solved it.

Please prioritize this issue!

[fvermaut]

I also had this issue happen randomly today as well on staging.

Here's a screenshot of the logs (sorry didn't find a way to export it - the CSV export seems to export something different):

I agree ith Zeulni: please prioritize this, considering it can happen so randomly, and basically completely breaks auth until the fix is applied.

[etzelc]

Have you seen and tested this possible solution? It was just posted today.
#20722 (reply in thread)

[fvermaut]

For me the issue was not triggered by a CI, or any push action I did from the CLI. I really don't know why there has been a shut down at 13:45:44 (which appears to be the trigger).

[jchaselubitz]

I had this issue at exactly the same time (13:49:07)

[primalyeti]

Had this issue pop on me again, seemingly randomly when i updated my functions.

The fix was running insert into auth.schema_migrations values ('20221208132122');, same query i had to run a few days ago to fix this bug.

This should be a high priority as it completely destroys authentication

[stefan-girlich]

The fix was running insert into auth.schema_migrations values ('20221208132122');, same query i had to run a few days ago to fix this bug.

Thanks @primalyeti , this worked for me whereas supabase db reset --linked did not. Just experienced this issue for the first time just now when trying to create a user via Supabase Studio (cloud). Fully agree that this needs to be prioritized.

Concerning supabase db reset --linked: Wouldn't this erase the remote/production database? I'm not sure how this would then work as a general fix.

[sweatybridge]

The reset command no longer clears auth migrations table. But it does not restore the auth table if it's already cleared. Please don't run it as a general fix as it erases data from your remote project.

[hf]

Are folks using Prisma or some other ORM? Please react 👍 if yes.

[VitaliyHula]

I have the same issue
This happened after the first user registered via email and password.
Until that moment, we had no users who were registered via email and password

[nechita]

I have the same issue This happened after the first user registered via email and password. Until that moment, we had no users who were registered via email and password

Something very similar happened to me as well, the difference is that the migration which is failing is a different one.

running db migrations: error executing migrations/20230131181311_backfill_invite_identities.up.sql

The situation happened exactly after creating a user with email and password.

This was extremely frustrating, because I didn't change anything in the codebase, but somehow nothing was working :(.

[shahrierNafis]

the temporary fix worked with auth but realtime is still down

[warrenbhw]

I'm still hitting this issue. Any updates on the cause and a permanent fix?

[nechita]

I've applied the temporary fix suggested in the thread but is updating the CLI a solid solution to prevent this from happening again when doing supabase db push to linked projects?

[hojeffrey]

Doing local dev and ran into this issue, cant even run my containers. I stopped the containers and could not start it back up with the same errors. Am I doing something wrong here? Had to clear out my local volumes but that destroyed all the progress I made without even being able to make a migration file

[warrenbhw]

For now, the best answer is just to use the temporary fix in the original post. Quoting here:

Temporary fix: Run insert into auth.schema_migrations values ('20221208132122'); via the sql editor to fix the issue.

[warrenbhw]

It sounds like there is some failing migration in the supabase-setup migrations, perhaps because there's some edge case where supabase is trying to create or delete the same resource twice. Thus, subsequent migrations fail and the auth schema doesn't get set up as it should.

I am only hitting this error on rare occasions when I do a reset of my nonlocal supabase db. In the cases where this happens, I just have to run this sql statement one time to fix.

Hopefully the supabase team can find a permanent fix, but I did find that this unblocked me and did not lead to any subsequent issues.

[anarkrypto]

Same issue here running locally.

I tried insert into auth.schema_migrations values ('20221208132122')

But it returns: Error: ERROR: 23505: duplicate key value violates unique constraint "schema_migrations_pkey" DETAIL: Key (version)=(20221208132122) already exists.

The strangest thing is that sometimes it starts working on its own, but sometimes it doesn't come back at all.

So I try to stop the container... ok.
Then I try to start again and I get this error:

You are running outdated service versions locally:
supabase/gotrue:v2.153.0 => v2.155.3
supabase/storage-api:v1.6.4 => v1.6.8
Run supabase link to update them.
Stopping containers...
failed to create docker container: Error response from daemon: Conflict. The container name "/supabase_rest_my_supabase" is already in use by container "2a4ff47ac5a51e4353fbd44b3e9137c9c1045615992c57b0c95942aaeab2d295". You have to remove (or rename) that container to be able to reuse that name.

Then I try to start again and works.

Supabase 1.183.5

@sweatybridge

[jason1610]

Any updates? The temporary fix works but the fact that this happens randomly in production is nutty.

[kangmingtay]

@jason1610 we're working on locking down the permissions on the auth.schema_migrations table to only allow the supabase_auth_admin role to perform DDL statements on that table - this should help to prevent external tools from deleting / truncating the schema migrations table

[anarkrypto]

For me it only resolved after I deleted all docker containers, all docker images and all docker volumes.

[anarkrypto]

The error is back 😭

[kangmingtay]

@anarkrypto what's your Supabase CLI version? are you using prisma / @snaplet/seed ?

[blytheaw]

I started facing this issue recently and found this thread. I was already on a newer version of the CLI so I wasn't sure what was causing it. After some investigation, it appears that if using the @snaplet/seed package's $resetDatabase() function and you have your client configured to inspect the full auth schema, it will erase the data in the auth.schema_migrations table, causing the same issue discussed in this thread.

For anyone in this boat, make sure your seed.config.ts doesn't include all of the auth schema. Use this config:

  select: [
    // We don't alter any extensions tables that might be owned by extensions
    "!*",
    // We want to alter all the tables under public schema
    "public*",
    // We also want to alter some of the tables under the auth schema
    "auth.users",
    "auth.identities",
    "auth.sessions",
  ]

Ref: https://snaplet-seed.netlify.app/seed/integrations/supabase#2-set-up-seed

Better yet, if your seeding doesn't require any knowledge of the auth schema, don't include it at all.

Feedback for Supabase: Obviously users should be wary of using external tools that can delete things they shouldn't, but I also think Supabase should not let users so easily break a managed service. If this table is so sensitive it should be locked down and not touchable by end users, or extra care should be taken to make these migrations idempotent, as others have suggested.

[kangmingtay]

I also think Supabase should not let users so easily break a managed service. If this table is so sensitive it should be locked down and not touchable by end users, or extra care should be taken to make these migrations idempotent, as others have suggested.

@blytheaw agreed, we're working on locking down the permissions on the auth.schema_migrations table to only allow the supabase_auth_admin role to perform DDL statements on that table - this should help to prevent external tools from deleting / truncating the schema migrations table

thanks for identifying the issue with the @snaplet/seed package btw!

[mlawlerau]

I had this issue on my stage db and I had to make 2 manual edits to the database to make it work:
insert into auth.schema_migrations values ('20221208132122');
insert into auth.schema_migrations values ('20240806073726');

What is causing the migration errors - it seems lots of folks are encountering the issue and there isn't a real answer from supabase apart from "hack your database to resolve"?

How can we make sure this issue doesn't happen again?

[kangmingtay]

@mlawlerau we're working on locking down the permissions on the auth.schema_migrations table to only allow the supabase_auth_admin role to perform DDL statements on that table - this should help to prevent external tools from deleting / truncating the schema migrations table

[paulcalcraft]

To fix this today I had to run:
insert into auth.schema_migrations values ('20230131181311');

(different migration ID)

In general, it seems like you can look for the migration error in your auth logs https://supabase.com/dashboard/project/_/logs/auth-logs and just copy the timestamp/migration ID of the failing migration, and insert that. It fixed it straight away for me.

My error was for {"level":"fatal","msg":"running db migrations: error executing migrations/20230131181311_backfill_invite_identities.up.sql hence inserting 20230131181311

[kangmingtay]

hi @paulcalcraft, can you please check if you're using the latest version of supabase CLI?

[NicoCaldo]

This happened to me running supabase start on a clean installation. Supabase CLI updated on the latest version

[kangmingtay]

@NicoCaldo can you specify which version is this? also, are you using prisma or the @snaplet/seed package?

[flurret]

This just happened to me on a supabase hosted server, with id: gthsgmwqhrnaagwurdbv.
I think it might have been caused by me changing STMP settings which is odd.
I had both 20221208132122_backfill_email_last_sign_in_at.up.sql and 20240806073726_drop_uniqueness_constraint_on_phone.up.sql causing issues until I did the insert fix.

It seems like this is still a major problem, agree with all the above comments saying this should be fixed with high priority.
I'm just glad this db was not my production db, however it does make me nervous to host on supabase in the future.

[NicoCaldo]

At least it should display what type of error occurred and where, not a generic "an invalid response from upstream"

[kangmingtay]

@NicoCaldo the errors can be seen in your project's auth logs - https://supabase.com/dashboard/project/_/logs/auth-logs

[thor-vemove]

Hello all and hello @kangmingtay,
First of all, I want to thank the Supabase team for the fantastic product and ongoing support.

Since a support engineer forwarded this discussion to us, I wanted to share our experience with a similar issue.

We also experienced the issue with running db migrations: error executing migrations/20221125140132_backfill_email_identity.up.sql, but in our case it is caused by null value in column "provider_id" of relation "identities". Here’s the full error message:

{"level":"fatal","msg":"running db migrations: error executing migrations/20221125140132_backfill_email_identity.up.sql, sql: -- backfill the auth.identities column by adding an email identity \n-- for all auth.users with an email and password \n\ndo $$\nbegin\n\tinsert into auth.identities (id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)\n\tselect id, id as user_id, jsonb_build_object('sub', id, 'email', email) as identity_data, 'email' as provider, null as last_sign_in_at, '2022-11-25' as created_at, '2022-11-25' as updated_at\n\tfrom auth.users as users\n\twhere encrypted_password != '' and email is not null and not exists(select user_id from auth.identities where user_id = users.id);\nend;\n$$;\n: ERROR: null value in column \"provider_id\" of relation \"identities\" violates not-null constraint (SQLSTATE 23502)","time":"2024-10-23T07:06:09Z"}

We temporarily resolved the issue by adding the serial of the migration to the auth schema history. However, we encountered another issue: error executing migrations/20240806073726_drop_uniqueness_constraint_on_phone.up.sql because relation "unique_phone_factor_per_user" already exists. Here’s the full error message:

{"level":"fatal","msg":"running db migrations: error executing migrations/20240806073726_drop_uniqueness_constraint_on_phone.up.sql, sql: alter table auth.mfa_factors drop constraint if exists mfa_factors_phone_key;\ndo $$\nbegin\n    if exists (\n         select 1\n         from pg_indexes\n         where indexname = 'unique_verified_phone_factor'\n         and schemaname = 'auth'\n    ) then\n        execute 'alter index auth.unique_verified_phone_factor rename to unique_phone_factor_per_user';\n    end if;\nend $$;\n: ERROR: relation \"unique_phone_factor_per_user\" already exists (SQLSTATE 42P07)","time":"2024-10-23T07:06:12Z"}

Again, we manually added the serial of this migration to the schema history, which temporarily restored the auth module’s health.

However, the potential for these types of issues occurring again is concerning, especially when considering Supabase for a production environment. While we would gladly switch to a paid plan and use Supabase for production instead of self-hosting or exploring alternatives, these ongoing issues in our test system are making us hesitant.

Could the Supabase team provide more insight into the timeline for addressing this problem? As suggested by @blytheaw and others, making the migrations idempotent seems like a feasible solution to avoid similar errors in the future.

We are excited to continue using Supabase, and we look forward to resolving these issues so we can confidently upgrade to a paid plan.

Thank you for your attention to this matter, and kind regards,
Thor