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 #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 says CREATE EXTENSION IF NOT EXISTS "pgsodium" WITH SCHEMA "pgsodium". 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.

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

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