GitHub Workflow Failing: Supabase pgsodium Schema Missing on >= 2.20.4

[khaizter]

Describe the bug it seems to me that the supabase setup tried to create the pgsodium extension before the schema.

842fd6281a5f: Pull complete
Digest: sha256:0cfa54e096dcae4ed30162811fa8794f7c443708c41973329e07f7d44c1cc3e7
Status: Downloaded newer image for ghcr.io/supabase/gotrue:v2.170.0
Seeding globals from roles.sql...
Applying migration 20241104023929_roles.sql...
Applying migration 20241104023930_init_dump.sql...
Stopping containers...
ERROR: schema "pgsodium" does not exist (SQLSTATE 3F000)        
At statement 11:                                                
CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium"
Try rerunning the command with --debug to troubleshoot the error.
Error: Process completed with exit code 1.

Additional context this issue only occurs on >= 2.20.4

[johnCbogil]

Seeing the same issue, maybe related to https://github.com/supabase/cli/pull/3355 (cc @sweatybridge) ?

[sweatybridge]

It's because we updated postgres to 15.8.1.060 which removed the pgsodium schema by default.

As mentioned in the error message, the statement 11 of your supabase/migrations/20241104023930_init_dump.sql file is creating the pgsodium extension. This no longer works in latest postgres image because the pgsodium schema is no longer present.

You can fix the error by deleting or commenting out this line.

-- CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium"

[karimou5]

Same error here.

You can fix the error by deleting or commenting out this line.

It fixed the problem thx

[eliabieri]

Will this be fixed in any package/dependency or will deleting the line stay as the only option?

[sweatybridge]

Will this be fixed in any package/dependency or will deleting the line stay as the only option?

It's more complicated in this case because we plan to sunset the pgsodium schema on the managed platform soon. So IMO it's better to future proof your migrations by deleting this line.

We've made sure that new migrations from db dump no longer rely on this schema but unfortunately we can't easily fix older migrations that live inside your repo. So sorry that you have to find out this way. May be there's a better alternative that we are not aware of.

[szeka94]

@sweatybridge isn't the vault using pgsodium by default?

[sweatybridge]

isn't the vault using pgsodium by default?

Yes, it is at the moment, but it's an implementation detail of vault that we plan to change.

When you install vault with cascade, it will install the dependencies it needs, including pgsodium in this case. So either way, it's unnecessary to create pgsodium extension separately.

create extension if not exists "supabase_vault" cascade

[elyobo]

How will the upgrade work? We're pretty dependent on pgsodium at the moment, using it for dispatching webhooks with standard webhook signatures like how the custom auth hook does it.

[TasseDeCafe]

I'm not sure what the proper way to deal with this problem is. When I created my project I have run this line in prod in the first migration for the remote schema:

CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium";

Now I can of course comment out this line locally and even drop this extension in prod, but then my dev and prod migrations will diverge.

Is there a better way to do this?

[sweatybridge]

Now I can of course comment out this line locally and even drop this extension in prod, but then my dev and prod migrations will diverge.

Just to clarify, commenting out this line won't drop this extension currently because it's still a dependency of vault, which is installed by default. So both extensions are still enabled because it cascades.

Commenting out just makes our migration easier in the future because it's not explicitly created by a migration file.

[sweatybridge]

How will the upgrade work? We're pretty dependent on pgsodium at the moment, using it for dispatching webhooks with standard webhook signatures like how the custom auth hook does it.

This is indeed a problem that we need to address with pgsodium deprecation. I've looped in the postgres team to take a closer look. cc @soedirgo

[Garsdal]

@sweatybridge I am facing the same issue currently. I tried removing all pgsodium related lines in my migrations, which works locally, but still fails in my CI. How would I go about removing this from my staging and production migrations which are now broken?

When I go the Database Migrations page in the Supabase dashboard I can see that pgsodium is still present in my migration, but it is not clear to me how I can change migrations that have already been run and have now broken.

[Garsdal]

Nevermind, I did not manage to clean up all references to pgsodium. It seems a bit sketchy, but manually dropping all pgsodium references in my old migrations fixed the issue.

However I can still see CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium" in my first db migration if I go to the Database Migrations page in Supabase, is this intended?

[sweatybridge]

However I can still see CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium" in my first db migration if I go to the Database Migrations page in Supabase, is this intended?

Since you've fixed the migrations locally and CI, the remote migration history table is now outdated. You can bring them back in sync by running supabase migration repair command. This only updates the history table without actually running any migrations.

[bgwdigital]

The workaround "When you run create extension if not exists vault, it will install the dependencies it needs, including pgsodium in this case. So either way, it's unnecessary to create pgsodium extension separately." does not work — the sodium dependencies are not installed (tested on macOS 15.4).”.

In addition to that, I find it more than unfortunate to apply such migration strategies in a production environment.

[sweatybridge]

@bgwdigital Updated my suggestion here https://github.com/supabase/cli/issues/3358#issuecomment-2765248401

CREATE EXTENSION IF NOT EXISTS "supabase_vault" CASCADE

It was previously missing cascade clause.

[avaid96]

Thanks guys, this was very helpful. I used the below and removed the pgsodium extension creation line from my migration. CREATE EXTENSION IF NOT EXISTS "supabase_vault" CASCADE

Do you suggest also disabling the pgsodium extension in Database / extensions in the remote DB manually?

[elyobo]

How will the upgrade work? We're pretty dependent on pgsodium at the moment, using it for dispatching webhooks with standard webhook signatures like how the custom auth hook does it.

For our specific use case we were able to use extensions.hmac instead of pgsodium.crypto_auth_hmacsha256 so the migration away was simple.

[Garsdal]

I am now facing this issue again:

I pulled a fresh seed file from my remote database, everything was working as expected while developing locally, but as I tried to apply my migrations through CI things are broken again.

I have identified the root cause to be from this part of the seed.sql file:

--
-- Name: key_key_id_seq; Type: SEQUENCE SET; Schema: pgsodium; Owner: supabase_admin
--

SELECT pg_catalog.setval('"pgsodium"."key_key_id_seq"', 1, false);

@sweatybridge How can you ask us to remove pgsodium schema from our migrations, but then still keep it around to break things later down the road?

[Garsdal]

Not sure if its related to https://github.com/supabase/supabase/issues/34964

I'll try to manually remove those lines from my seed.sql file to see if I can proceed.

[nalnir]

I commented out the line: CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium";, but it still gives me the same error.

Any news on the issue?

[iensu]

My colleague is seeing this issue but I'm not.

We both have the same supabase CLI version: 2.34.3 but our postgres images differ:

My version (works): public.ecr.aws/supabase/postgres:15.6.1.141 Colleague's version (doesn't work): public.ecr.aws/supabase/postgres:15.8.1.085

I would assume that we'd both have the same image versions if we use the same CLI version, but apparently not.

I've tried to update my image by running supabase stop --no-backup followed by docker system prune --all which does delete the supabase-related images. However, when I run supabase start it still uses postgres:15.6.1.141 in my case.

I have double checked that we indeed are running the intended CLI versions with supabase --version and which supabase.

Another issue is that all our database changes have been made using migrations, but nowhere in our codebase do we have a line similar to CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium".

EDIT: I realized that the image versions were listed in the supabase/.temp directory, so I deleted that directory followed by supabase stop && supabase start and now we both have the same images running, the difference is: he still gets the error, but I don't. Not sure what is causing the discrepancy.

[rrcabrera9625]

I have commented this line -- CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium"

And I am still getting the same error when creating preview branch

ERROR: schema "pgsodium" does not exist (SQLSTATE 3F000) At statement: 10 CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium"

[sultson]

To keep using Vault for providing project URL and anon key (without issues from config.toml setup [#3242] or pgsodium), we added an extra seed file:

select vault.create_secret('local-anon-key', 'SUPABASE_ANON_KEY', 'Anonymous key required for Postgres functions invoking edge functions');
select vault.create_secret('local-project-url', 'SUPABASE_PROJECT_URL', 'Project URL required for Postgres functions invoking edge functions');

This runs after the generated seed via sql_paths = ['./seed.sql', './superseed.sql'] in config.toml.

Note: this approach isn’t intended for storing sensitive values, just for local/dev convenience.