DB reset with extensions enabled

[chreck]

Describe the bug
Doing a db dump with npx supabase@v1.142.2 db pull, without any migration file stored, does not include the activated extensions like with npx supabase@v1.138.0 db pull .

To Reproduce
Steps to reproduce the behavior:

1. Create a supabase project with extensions
2. Write a postgresql function which is using ltree as an example and store it
3. npx supabase@v1.142.2 db pull
4. npx supabase@v1.142.2 start
5. npx supabase@v1.142.2 db reset
6. ERROR: type "public.ltree" does not exist (SQLSTATE 42704)

Function and extension ltree

CREATE EXTENSION IF NOT EXISTS "ltree" WITH SCHEMA "public";

CREATE OR REPLACE FUNCTION update_path(
    old_path ltree,
    new_path ltree
) RETURNS VOID AS $$
BEGIN
    UPDATE your_table_name
    SET path = new_path || subpath(path, nlevel(old_path))
    WHERE path <@ old_path;
END;
$$ LANGUAGE plpgsql;

Migration file supabase/migrations/20240219141944_remote_schema.sql

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE SCHEMA IF NOT EXISTS "public";

ALTER SCHEMA "public" OWNER TO "pg_database_owner";

CREATE OR REPLACE FUNCTION "public"."update_path"("old_path" "public"."ltree", "new_path" "public"."ltree") RETURNS "void"
    LANGUAGE "plpgsql"
    AS $$
BEGIN
    UPDATE your_table_name
    SET path = new_path || subpath(path, nlevel(old_path))
    WHERE path <@ old_path;
END;
$$;

ALTER FUNCTION "public"."update_path"("old_path" "public"."ltree", "new_path" "public"."ltree") OWNER TO "postgres";

GRANT USAGE ON SCHEMA "public" TO "postgres";
GRANT USAGE ON SCHEMA "public" TO "anon";
GRANT USAGE ON SCHEMA "public" TO "authenticated";
GRANT USAGE ON SCHEMA "public" TO "service_role";

GRANT ALL ON FUNCTION "public"."update_path"("old_path" "public"."ltree", "new_path" "public"."ltree") TO "anon";
GRANT ALL ON FUNCTION "public"."update_path"("old_path" "public"."ltree", "new_path" "public"."ltree") TO "authenticated";
GRANT ALL ON FUNCTION "public"."update_path"("old_path" "public"."ltree", "new_path" "public"."ltree") TO "service_role";

ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES  TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES  TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES  TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON SEQUENCES  TO "service_role";

ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS  TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS  TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS  TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON FUNCTIONS  TO "service_role";

ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES  TO "postgres";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES  TO "anon";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES  TO "authenticated";
ALTER DEFAULT PRIVILEGES FOR ROLE "postgres" IN SCHEMA "public" GRANT ALL ON TABLES  TO "service_role";

RESET ALL;

Expected behavior
The exported migration file should contain the missing extension. With command npx supabase@v1.138.0 db pull it does contain the extension info.

CREATE EXTENSION IF NOT EXISTS "ltree" WITH SCHEMA "public";

Screenshots
No screenshots

System information
Rerun the failing command with --create-ticket flag (preferred).

• Ticket ID:
• OS: macOS 14.3.1 (23D60)
• Version of CLI: 1.142.2
• Versions of services:
  supabase/gotrue: can not be used because of failed to pull docker image #1938

    supabase/postgres      │ 15.1.1.18            │ 15.1.1.18
    supabase/gotrue        │ 2.142.0              │ 2.142.0
    postgrest/postgrest    │ v12.0.2              │ v12.0.2
    supabase/realtime      │ v2.25.50             │ -
    supabase/storage-api   │ v0.48.1              │ v0.48.1
    supabase/edge-runtime  │ v1.33.5              │ -
    supabase/studio        │ 20240101-8e4a094     │ -
    supabase/postgres-meta │ v0.75.0              │ -
    supabase/logflare      │ 1.4.0                │ -
    bitnami/pgbouncer      │ 1.20.1-debian-11-r39 │ -
    darthsim/imgproxy      │ v3.8.0               │ -

Additional context
No

[sweatybridge]

Thanks for catching this. I will fix it in the next beta release.

[tvogel]

@sweatybridge Why is the --extensions flag only passed when no schema list is specified?

Because I am using a trigger on auth.users and a custom app namespace, I need to pass -s public,auth,app and then, I am missing the CREATE EXTENSION calls. Also, -s public,auth,app,extensions does not fix it. It dumps stuff from that namespace but not the CREATE EXTENSION calls themselves.

Am I missing the intended use?