Safely replacing a custom Auth trigger function on hosted Supabase

[acauzo]

We need to update the body of a custom PL/pgSQL trigger function called by an AFTER INSERT trigger on auth.users.

The change should preserve the function’s signature, ownership, privileges, and existing trigger binding. It should not modify existing accounts or roles.

Environment and checks:

• Hosted Supabase; the function is owned by postgres.
• The execution role is not a superuser and cannot lock system catalogs.
• One administrator, no configured Cron jobs, and no connected GitHub deployment integration.
• The replacement function and rollback-on-error behavior have been tested locally.
• No production changes have been made.
  We intend to use a reviewed, versioned migration through the Supabase CLI, after checking the existing migration history and preparing a backup.

Questions:

1. Is a transactional CREATE OR REPLACE FUNCTION, with one coordinated deployer and pre/post checks, the recommended approach for this change?
2. What additional precautions are recommended regarding concurrent changes to the auth.users trigger or Supabase-managed maintenance?
3. If additional locking is necessary, what supported approach works with the available owner privileges, without granting broad catalog permissions or disabling platform hooks?
   We are looking for the standard supported procedure, not a guarantee against every possible platform operation.

[Dv2Corn]

Short version: yes, a plain CREATE OR REPLACE FUNCTION in a normal CLI migration is the standard way to do this, and you don't need any extra locking. There is one real trap, covered in point 1.

1. CREATE OR REPLACE FUNCTION is the right tool, but restate every attribute

Replacing keeps the same function OID, so the trigger on auth.users (which points at the function by OID) stays bound to it. Owner and grants stay as they are too: the Postgres docs for CREATE FUNCTION say "the ownership and permissions of the function do not change".

The same page also says every other property is reset to whatever the new command says or implies. So if the current function is security definer set search_path = '' (the pattern in the managing user data guide) and your new version leaves out those clauses, it silently turns into SECURITY INVOKER with no search_path. The trigger then runs as supabase_auth_admin, which usually can't write to your public tables. Every signup then fails with "Database error saving new user" (that's the error Auth returns when the insert into auth.users fails, signup.go).

I reproduced this on Postgres 17 with a non-superuser owner and a trigger on a table owned by a separate auth role:

• replacing with security definer set search_path = '' restated: same OID, same owner, same proacl, tgfoid unchanged, and the next insert used the new body
• replacing without those clauses: same OID/owner/ACL, but prosecdef = f, proconfig = NULL, and the next insert as the auth role failed with permission denied for table profiles

The safest way to write the migration is to start from the live definition, change only the body, and keep everything else as it is:

select pg_get_functiondef('public.handle_new_user()'::regprocedure);

Other things fail loudly rather than silently, which helps: a non-owner gets must be owner of function, a changed return type gets cannot change return type of existing function, and DROP FUNCTION without CASCADE is refused because the trigger depends on it. Don't use drop + create, because that would also drop the trigger.

2. Transactions, concurrency and Supabase-managed changes

• Don't put BEGIN/COMMIT in the migration file. The CLI already runs a migration file in one implicit transaction, and the row in supabase_migrations.schema_migrations goes in the same batch. So the function change and the history entry either both happen or neither does. That's true for v2 (file.go) and v3 (migration-apply.ts). In v3, a file with its own BEGIN/COMMIT runs exactly as written and the history row is written after it (L477-L478), so that atomicity is lost. In my test, an error after the replace inside the transaction left the old body, prosecdef and proconfig unchanged.
• Signups are not blocked. CREATE OR REPLACE FUNCTION takes no lock on auth.users and no lock on the function. The only locks it held were AccessShareLock on the language and the schema, plus its own transaction id. In my test, a signup insert that ran while the replace was still uncommitted went straight through and used the old body. The next insert after commit used the new body. The replace also did not wait for a signup transaction that was still open.
• Two concurrent replaces of the same function don't silently overwrite each other. The second one waits on the pg_proc row until the first commits, then fails with tuple concurrently updated, so its migration rolls back. Putting set lock_timeout = '5s'; at the top of the file makes it fail fast instead of waiting.
• Supabase Auth's own schema migrations (in supabase/auth/migrations) contain no trigger statements at all, so they don't touch user-created triggers on auth.users. That's what the current code shows, not a documented promise. Still, the post-deploy check below covers the trigger too.

3. Additional locking

Not needed, as explained above. If you want a guard between deployers anyway, use a transaction-scoped advisory lock. Any role can take one, it needs no catalog privileges, and it is released automatically at commit or rollback:

set lock_timeout = '5s';
select pg_advisory_xact_lock(hashtext('public.handle_new_user'));
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = ''   -- copy exactly from pg_get_functiondef
as $$
begin
  -- new body
  return new;
end;
$$;

Don't try LOCK TABLE auth.users: it isn't needed for this and it would block Auth's writes while it's held.

Pre/post checks

Run this before and after, and compare the output:

select p.oid, p.proowner::regrole, p.proacl, p.prosecdef, p.proconfig, p.provolatile,
       md5(p.prosrc) as body_md5,
       t.tgname, t.tgenabled, t.tgfoid = p.oid as trigger_points_here
from pg_proc p
left join pg_trigger t on t.tgfoid = p.oid and not t.tgisinternal
where p.oid = 'public.handle_new_user()'::regprocedure;

Everything except body_md5 should be identical, and tgenabled should still be O. Run supabase migration list before supabase db push --dry-run and then the real push, so you know the local and remote history line up. After the push, do one real signup and check the Auth logs for "Database error saving new user".