Cannot deploy postgres extensions in migrations

[davemooreuws]

Bug report

Describe the bug

I have a database that has the postgis and moddatetime extensions installed.

I linked to this database, then installed the extensions locally since the cli doesnt dump and restore from the linked project.

To Reproduce

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

1. Create a new supabase project with moddatetime or another non default extension enabled
2. Install the supabase CLI
3. Create a new directory and initialize git via git init
4. Run supabase init in the directory
5. Run supabase link --url <<< 'postgresql://postgres:<PASSWORD>@db.<PROJECT_ID>.supabase.co:5432/postgres' or equivalent on Windows in the directory
6. Run supabase start
7. Add an extension to the local database CREATE EXTENSION if not exists moddatetime with SCHEMA extensions;
8. Create a migration supabase db dump --name test
9. Deploy supabase deploy

Error: ERROR: permission denied for language c (SQLSTATE 42501)

I believe it might be caused by this (found within the migration sql):

CREATE OR REPLACE FUNCTION extensions.moddatetime()
    RETURNS trigger
    LANGUAGE 'c'
    COST 1
    VOLATILE NOT LEAKPROOF
AS '$libdir/moddatetime', 'moddatetime'
;

Expected behavior

I expect it to deploy.

System information

• OS: Windows 10 64 bit
• Postgres version 13.3
• Version of CLI: 0.10.4

[soedirgo]

Hmm, the postgres role used for deploying isn't a superuser, so it can't create/manage extensions.

One (ugly) workaround is to remove all extension-related stuff from the migration, deploy, then add the CREATE EXTENSION DDLs so supabase start can work. Let me know if that works for you - I'm not sure how to do this cleanly.

[TuhinNair]

Ran in to this exact problem. I'm using an external migration tool (sqlx) and received the following error while trying to run CREATE EXTENSION IF NOT EXISTS citext;:

error: while executing migrations: error returned from database: must be superuser to create a base type

For users who don't intend to use the UI to manage the database, like me, could we get a list of constraints?

I read this reply on another issue. Would it be possible to make it opt-in (i.e allow postgres to be the superuser)? So if it limits any of the features/guarantees Supabase makes the user can choose to make an informed decision.

[soedirgo]

Sorry about that. The known major constraints should be in the issue tracker already (auth.users triggers, storage policies, extensions).

We won't be allowing any superuser access in the future, so I don't think making it opt-in helps.

[soedirgo]

This is now supported.