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