Recommended way of enabling PostgreSQL extensions for local development

[erwinkn]

Feature request

Is your feature request related to a problem? Please describe.

Until recently, the undocumented but recommended way of enabling PostgreSQL extensions for local development was to create a supabase/extensions.sql file and define them there. (#263)

In release 0.38.3, this feature was removed from the supabase start command. Here are the relevant lines, from release 0.38.2: https://github.com/supabase/cli/blob/v0.38.2/internal/start/start.go#L413-L435

I understand this feature was not documented, but it seems like there is currently no proper way of enabling PostgreSQL extensions during local development.

(Side note: the change caught us by surprise, since it was not documented in the changelog of 0.38.3. We've had to retroactively add our extensions to an old migration, which is not ideal.)

Describe the solution you'd like

It would be very helpful to have a clear & documented way of creating PostgreSQL extensions before migrations run in local development.

Additional context

It was mentioned in #263 that the supabase/extensions.sql file would be deprecated once it would become possible to use CREATE EXTENSION in migrations.

I have not tried whether this is possible now. It may be possible that defining extensions in migrations is now the recommended way of synchronizing extensions between production and local development for new projects.

[soedirgo]

Sorry this wasn't documented - I've now updated the release notes to include this change. You should now be able to do CREATE EXTENSION in migrations - appending the contents of extensions.sql to the first migration should do the trick.

[bombillazo]

we are getting permission denied errors when using CREATE EXTENSION during migrations, how does the Supabase UI grant permissions when one activates an extension via the dashboard? We want to compare the SQL that runs to ensure our migration does the same.

the specific extension giving issues is pg_cron

[Shusanta]

@bombillazo got a solution? this shit never works

[sweatybridge]

Which extension are you having problem with? We have pgtap tests for enabling most of the extensions that you can use as example https://github.com/supabase/postgres/tree/develop/migrations/tests/extensions

They are run as the postgres user which should be the same as local development. Simply drop the corresponding create extension statement in your supabase/migrations locally.

[Shusanta]

@sweatybridge all of them within my migration file. I run supabase start and i either get ERROR: permission denied to create extension "pg_net" (SQLSTATE 42501) At statement 12: CREATE EXTENSION IF NOT EXISTS "pg_net" WITH SCHEMA "extensions" not sure why this happens only on this db but not my other ones. I ransupabase stop --no-backup as well. and its not for just this extension its all of them

[Shusanta]

I'm using 8 extensions right now, to copy pasta that much is a definite smell.

[Shusanta]

@sweatybridge even after pasting I get

[sweatybridge]

What the output from supabase services? It might be a problem with old postgres image.

You can try resetting versions to default by rm -rf supabase/.temp

[Shusanta]

@sweatybridge I really appreciate the responses. I tried, stil same error, supabase stop --no-backup before as well

[sweatybridge]

It looks like you are running rm in supabase directory so the delete didn't take effect.

Try rm -rf .temp instead.

I can confirm there's an issue with version 15.1.0.52

[Shusanta]

@sweatybridge ah I see but after i run link it resets it to the broken image? how can i force my linked db to update?

[sweatybridge]

how can i force my linked db to update?

The recommended way is to upgrade your hosted project via the dashboard https://supabase.com/dashboard/project/_/settings/infrastructure

Remember to review our upgrade guidelines and plan for some downtime if your project is used actively in production https://supabase.com/docs/guides/platform/migrating-and-upgrading-projects#upgrade-your-project