Getting error "relation "pgsodium.key_key_id_seq" does not exist (SQLSTATE 42P01)" when using "db remote commit"

[trulysinclair]

Bug report

Describe the bug

When using the latest CLI to init, start, link, and remote commit; supabase errors with a missing relation.

relation "pgsodium.key_key_id_seq" does not exist (SQLSTATE 42P01)

To Reproduce

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

1. supabase init
2. supabase start
3. supabase link --project ...
4. supabase db remote commit
5. supabase db reset

PS C:\Users\silen\Documents\GitHub\buildr-monorepo> supabase db reset        
Resetting database...
Initialising schema...
Applying migration 20220824061131_remote_commit.sql...
Error: ERROR: relation "pgsodium.key_key_id_seq" does not exist (SQLSTATE 42P01)

Expected behavior

The database should now reflect the migration.

System information

• OS: Windows 11
• Version of supabase-cli: 1.1.5
• Version of Node.js: 16.13.2

[ZetiMente]

gah, I just got the same error, did u make any progress on this?

[techjandro]

Hello, for me now it worked just deleting the migration file that I just created for the very first commit. But still I don't know why is it happening.

[trulysinclair]

No progress. I still don't understand why this happens. It's very annoying and I'm completely unable to sync my local to my remote.

[magicseth]

I'm hitting this as well

[ARMATAV]

Same here

[ARMATAV]

Fixed it;

https://github.com/supabase/cli/issues/246

1. supabase link --project-ref <project-id>
2. supabase db remote commit
3. supabase db reset
4. Cool, now we can see that we're screwed and we get extension errors like pgsodium.key_key_id_seq does not exist
5. Great, go into the 20220913230812_remote_commit.sql file - comment everything out, save without formatting
6. Create a new .sql file over at supabase/extensions.sql and put in whichever extension is failing you CREATE EXTENSION pgsodium SCHEMA extensions; in this case
7. Great, supabase db reset and it should work
8. However, we're still screwed because when we supabase db push to the remote, you'll see the timestamp is different
9. Go into your production database (or the remote as I called it), and go to supabase_migrations.schema_migrations - copy the one row in there (if you have multiple, it should be the first)
10. Paste that over your original timestamp in your migrations folder
11. supabase db push

And you're good to go. Verified that this works even when I add new tables and supabase db reset.

Hope this saves someone the absolutely agonizing experience of googling relation "pgsodium.key_key_id_seq" does not exist (SQLSTATE 42P01)

[ZetiMente]

Hi @ARMATAV I was able to get to step 10. Thank you !!! However, I don't want to do a supabase db push, I'm looking to just have my local db in sync with the remote. I don't want to push my local to my remote. However, when I do supabase db reset again, I ran into the error again once I uncomment the migration code.

[ARMATAV]

Hard to say @ZetiMente - I think you can just leave remote_commit commented out.

Also - are you using their latest CLI version?

[ZetiMente]

@ARMATAV Yes, I am on 1.4.6. Not sure what you mean by "leave remote_commit" commented out? If I leave the entire migration commented out, then how do I sync my local db to it?

[ARMATAV]

@ZetiMente Over here https://github.com/supabase/cli/issues/246#issuecomment-1092879386

[sweatybridge]

Hello @ARMATAV , supabase/extensions.sql is a file that we no longer support in CLI v1 and I suspect it was working for you by accident...

Could you and @ZetiMente help me check that your remote projects have pgsodium enabled under the pgsodium schema?

The second issue with off sync timestamp between remote and local migration files was fixed recently in v1.4.5.

[ZetiMente]

Hi @sweatybridge I do have PGSODIUM enabled now, thanks to your help on the other issue ! I no longer get that error. I now get this:

[sweatybridge]

Hi, could you try running with supabase db reset --debug flag to check the detailed error log?

[sweatybridge]

We updated the way shadow database is created in the latest version. Feel free to open this issue again if the problem still persists.

[fbinz]

I'm not sure if it's the same problem, but I'm still having trouble creating a local development setup with the steps described in the OP. If I understand correctly, supabase db remote commit (step 4) on a new project should basically create all necessary tables. While an SQL migration file is indeed successfully created, it references two views which are not present in the local postgres instance.

For reference, here are the views that are available on the hosted supabase.co database:

And here are the views in the locally created database:

The script generated by supabase db remote commit includes GRANT statements for those two missing views (decrypted_key, mask_columns). When applying the migrations using supabase db reset, these GRANT lead to errors, of course.

Commenting out these GRANT statements seems to work for now, but I guess it might lead to problems later.

[sweatybridge]

Hey, thanks for reporting. Did you try this on the latest version of CLI? This issue is different and it's mostly due to a pre-release of vault that was reverted. Updating CLI should fix it.

[jonathanperret]

I'm hitting the same issue as @fbinz on a fresh project after supabase db remote commit. The hosted database seems to use pgsodium version 3.1.5 whereas the CLI (version 1.29.1) sets up version 3.0.4 (according to pg_catalog.pg_extension).

[fbinz]

Thanks for responding so quickly. I was using the latest version of the CLI.

[sweatybridge]

The hosted database seems to use pgsodium version 3.1.5 whereas the CLI (version 1.29.1) sets up version 3.0.4 (according to pg_catalog.pg_extension).

The root cause is the same as https://github.com/supabase/cli/issues/766. I've updated the postgres image used by cli to match that of hosted platform.

[vikiival]

Hello @sweatybridge,

I am experiencing the same issue. CLI (version 1.29.6)

1. make dump from remote db - supabase db dump -f 1673369341_name.sql
2. mv 1673369341_name.sql supabase/migrations
3. supabase start

Error: ERROR: relation "pgsodium.decrypted_key" does not exist (SQLSTATE 42P01)
At statement 106: --
-- Name: TABLE "decrypted_key"; Type: ACL; Schema: pgsodium; Owner: supabase_admin
--

GRANT ALL ON TABLE "pgsodium"."decrypted_key" TO "pgsodium_keyholder"

[d-e-h-i-o]

Hi @sweatybridge,

experiencing the same bug. Used CLI with version 1.33.0 to generate a remote commit. Now when the Github CI script from the documentation is being run the following error is thrown:

Error: ERROR: relation "pgsodium.decrypted_key" does not exist (SQLSTATE 42P01)
At statement 958: --                                                                                
-- Name: TABLE "decrypted_key"; Type: ACL; Schema: pgsodium; Owner: supabase_admin
--                                                                                
                                                                                  
GRANT ALL ON TABLE "pgsodium"."decrypted_key" TO "pgsodium_keyholder"             
Try rerunning the command with --debug to troubleshoot the error.

Script:

name: CI

on:
  pull_request:
  workflow_dispatch:

jobs:
  test:
    runs-on: ubuntu-22.04
    steps:
      - uses: actions/checkout@v3

      - uses: supabase/setup-cli@v1

      - name: Start Supabase local development setup
        run: supabase start

      - name: Verify generated types are up-to-date
        run: |
          supabase gen types typescript --local > types.ts
          if [ "$(git diff --ignore-space-at-eol types.ts | wc -l)" -gt "0" ]; then
            echo "Detected uncommitted changes after build. See status below:"
            git diff
            exit 1
          fi

[devonkcopeland-serial]

Hi @sweatybridge,

I'm experiencing the same issue as @d-e-h-i-o.

Error: ERROR: relation "pgsodium.decrypted_key" does not exist (SQLSTATE 42P01)
At statement 568: --                                                                          
-- Name: TABLE "decrypted_key"; Type: ACL; Schema: pgsodium; Owner: postgres
--                                                                          
                                                                            
GRANT ALL ON TABLE "pgsodium"."decrypted_key" TO "pgsodium_keyholder"
Try rerunning the command with --debug to troubleshoot the error.
Error: Process completed with exit code 1.

[sweatybridge]

For future reference, this issue was addressed in v1.35.0 https://github.com/supabase/cli/pull/818. Feel free to reopen if updating CLI to latest doesn't help.

[wyozi]

Had this error locally when undergoing a major postgres upgrade (14 -> 15) and trying to run supabase reset.

Stopping and restarting supabase helped to force supabase cli to upgrade the used docker images