Ownership issue on pg_cron and pg_net extension access functions

[DuncanLHS]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I have a project started around 18 months ago originally in postgres 15, now updated to 17. I occasionally run supabase db pull on the linked project to my local setup just to catch any changes. To me it seems like good practice?? I've had no issue with this until recently.

There seems to have been a couple of system level changes recently to a couple of functions associated with extensions; grant_pg_cron_access(), grant_pg_net_access(). This results in a couple of create or replace statements being generated when I run a pull on the linked project. However, they won't run locally due to an ownership issue:
ERROR: must be owner of function grant_pg_cron_access (SQLSTATE 42501)

Now I'm not sure how to deal with this. I could maybe take ownership of these functions locally, but that ownership change will end up getting pushed to my remote db and I'm worried about breaking things that I don't fully understand. I could remove the statements from my local migrations but they will get re-generated every time I pull and will have to be manually removed.

Is there a better solution?

To Reproduce

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

1. Run supabase db pull --linked

Expected behavior

All generated statements to run locally without error

Additional Context

Here's the full migration that gets generated:

drop extension if exists "pg_net";

set check_function_bodies = off;

CREATE OR REPLACE FUNCTION extensions.grant_pg_cron_access()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF EXISTS (
    SELECT
    FROM pg_event_trigger_ddl_commands() AS ev
    JOIN pg_extension AS ext
    ON ev.objid = ext.oid
    WHERE ext.extname = 'pg_cron'
  )
  THEN
    grant usage on schema cron to postgres with grant option;

    alter default privileges in schema cron grant all on tables to postgres with grant option;
    alter default privileges in schema cron grant all on functions to postgres with grant option;
    alter default privileges in schema cron grant all on sequences to postgres with grant option;

    alter default privileges for user supabase_admin in schema cron grant all
        on sequences to postgres with grant option;
    alter default privileges for user supabase_admin in schema cron grant all
        on tables to postgres with grant option;
    alter default privileges for user supabase_admin in schema cron grant all
        on functions to postgres with grant option;

    grant all privileges on all tables in schema cron to postgres with grant option;
    revoke all on table cron.job from postgres;
    grant select on table cron.job to postgres with grant option;
  END IF;
END;
$function$
;

CREATE OR REPLACE FUNCTION extensions.grant_pg_net_access()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM pg_event_trigger_ddl_commands() AS ev
    JOIN pg_extension AS ext
    ON ev.objid = ext.oid
    WHERE ext.extname = 'pg_net'
  )
  THEN
    IF NOT EXISTS (
      SELECT 1
      FROM pg_roles
      WHERE rolname = 'supabase_functions_admin'
    )
    THEN
      CREATE USER supabase_functions_admin NOINHERIT CREATEROLE LOGIN NOREPLICATION;
    END IF;

    GRANT USAGE ON SCHEMA net TO supabase_functions_admin, postgres, anon, authenticated, service_role;

    IF EXISTS (
      SELECT FROM pg_extension
      WHERE extname = 'pg_net'
      -- all versions in use on existing projects as of 2025-02-20
      -- version 0.12.0 onwards don't need these applied
      AND extversion IN ('0.2', '0.6', '0.7', '0.7.1', '0.8', '0.10.0', '0.11.0')
    ) THEN
      ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;
      ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SECURITY DEFINER;

      ALTER function net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;
      ALTER function net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) SET search_path = net;

      REVOKE ALL ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;
      REVOKE ALL ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) FROM PUBLIC;

      GRANT EXECUTE ON FUNCTION net.http_get(url text, params jsonb, headers jsonb, timeout_milliseconds integer) TO supabase_functions_admin, postgres, anon, authenticated, service_role;
      GRANT EXECUTE ON FUNCTION net.http_post(url text, body jsonb, params jsonb, headers jsonb, timeout_milliseconds integer) TO supabase_functions_admin, postgres, anon, authenticated, service_role;
    END IF;
  END IF;
END;
$function$
;

[Hallidayo]

Transferred from the supabase repo.

[michaelyork]

Running into this as well; would really appreciate a fix.

[riderx]

I can't help on the exact issue, but I can share my thoughts about good practices. I've run Supabase DB for 4 years in my Saas in production.
Pull is an anti-pattern, I would say.

What worked for us:

• Using Supabase migration system.

First, you pull once to have the main migration file locally.
Then each time you develop a new feature, you reset the DB with Supabase db reset and then create a new migration file.
When you are satisfied, you push to prod the change by CI or yourself.
Your source of truth is then the migration files, not the prod.

Then both DBs are aligned with this file, and you never need to pull once again from prod.
All migration files should be commited.

My SaaS is open source, so you can take a look at the config and our CI/CD. You are welcome to copy what you need:
https://github.com/Cap-go/capgo