[Temporarily resolved] moddatetime not found in local environment

[MaySoMusician]

Describe the bug

EXECUTE FUNCTION extensions.moddatetime(...) in database migrations fails because the 'moddatetime' extension does not exist.

To Reproduce

1. Clone the repository and checkout 7e3300b
2. Restart Supabase local environment (supabase stop && supabase start)
3. You will get the following error:

   Error: Error starting database: ERROR: function extensions.moddatetime() does not exist

Expected behavior

Supabase local environment gets running without any error

Screenshots

Desktop (please complete the following information):

• OS: Windows 10
• Docker: Docker Desktop for Windows

Additional context

Unfortunately, we have only a temporary workaround for now, according to the repository of Supabase CLI (e.g. supabase/cli#151)

Temporary Workaround 1 (old - no needed any more)

We assume we have supabase/migrations/20220218124404_commitUpdateAtChangesOnProjects.sql

1. Create a migration file manually, that is named with the previous number (in this case, supabase/migrations/20220218124403_foobar.sql)
2. Edit the file as below:

   CREATE EXTENSION IF NOT EXISTS moddatetime
       SCHEMA "extensions";

[MaySoMusician]

#30 now fixes the problem temporarily. However, the current migrations will leads to fail to push the migrations to the hosted instance

[MaySoMusician]

Also refer to:

• Migrations do not allow auth.users triggers supabase/cli#120
• Cannot deploy postgres extensions in migrations supabase/cli#61
• function extensions.moddatetime() does not exist for Migrations supabase/cli#151
• pg_net background worker never starts supabase/cli#137

[MaySoMusician]

#30 now fixes the problem temporarily. However, the current migrations will leads to fail to push the migrations to the hosted instance

An elevated postgres user can work around this problem.

Steps:

• Run ALTER ROLE postgres SUPERUSER; in the project's SQL editor
• Push the migrations that handle extension creation, by supabase db push
• If you don't want the user elevated any more, run ALTER ROLE postgres NOSUPERUSER;.