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?

[kangmingtay]

you can check your project's auth logs which should reflect the error (https://supabase.com/dashboard/project/_/logs/auth-logs)

it seems like the auth.schema_migrations table is being cleared, which causes the auth service to attempt to rerun all the migrations. This particular migration fails because it's not idempotent, since it's a data migration.

We're still investigating the issue but it seems like users experiencing this might be using a tool (prsima or supabase CLI) which resets the tables in the auth schema, perhaps when syncing local dev to remote, and this causes the rows in the auth.schema_migrations table to be cleared.

[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;

[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?

[kangmingtay]

It seems like the auth.schema_migrations table is being cleared, which causes the auth service to attempt to rerun all the migrations. This particular migration fails because it's not idempotent, since it's a data migration.

We're still investigating the issue but it seems like users experiencing this might be using a tool (prsima or supabase CLI) which resets the tables in the auth schema, perhaps when syncing local dev to remote, and this causes the rows in the auth.schema_migrations table to be cleared.

[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.

[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?

[kangmingtay]

It seems like the auth.schema_migrations table is being cleared, which causes the auth service to attempt to rerun all the migrations. This particular migration fails because it's not idempotent, since it's a data migration.

We're still investigating the issue but it seems like users experiencing this might be using a tool (prsima or supabase CLI) which resets the tables in the auth schema, perhaps when syncing local dev to remote, and this causes the rows in the auth.schema_migrations table to be cleared.

[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.

[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)

[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

[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;