Migrations do not allow `auth.users` triggers

[oliviercperrier]

Bug report

Describe the bug

Adding a trigger using migration is not working. The SQL code for the trigger is not added to the migration for some reasons.

To Reproduce

1. Run the following SQL query:

CREATE FUNCTION handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY definer 
AS $$
BEGIN
  RETURN new;
END;
$$;

CREATE trigger on_auth_user_created
  AFTER INSERT ON auth.users
  for each ROW EXECUTE PROCEDURE handle_new_user();

2. supabase db commit add_trigger
3. Look at the migration file, the trigger is not there.

Expected behavior

All runned SQL queries should appear in the migration file

Screenshots

If applicable, add screenshots to help explain your problem.

System information

• OS: macOS
• supabase-cli: 0.15.10

[soedirgo]

This is related to https://github.com/supabase/cli/issues/96#issuecomment-1000255696. Basically migrations can't handle auth.users triggers until we (Supabase) rethink db permissions.

[oliviercperrier]

This is related to #96 (comment). Basically migrations can't handle auth.users triggers until we (Supabase) rethink db permissions.

@soedirgo So i need to add them manually?

[soedirgo]

Yeah, I think the workaround for now is to put it in supabase/seed.sql.

[oliviercperrier]

@soedirgo That mean that the seed.sql is ran when pushing on a remote database?

[soedirgo]

Nope, it doesn't get pushed, so for now you'd have to manually keep it in sync.

[oliviercperrier]

Sounds good thanks

[soedirgo]

I'll keep this open if you don't mind so others are aware this is being worked on.

Similar issues:

• https://github.com/supabase/cli/issues/61
• https://github.com/supabase/cli/issues/96

[soedirgo]

This is now supported.

[techjandro]

@soedirgo I was just noticing that my auth triggers are not included in the migrations between my remote and local databases, just arrived here but still couldn't make it work, am I doing something wrong? thanks in advance!

[soedirgo]

Hey @alemontesdev, I think this is just a limitation of the generated migrations - there's no easy way to include auth.users triggers without also including schema changes on the auth.users table itself (which is a no-no, these are managed internally).

But you should be able to include the triggers in your migration, and supabase db push should work.

[ebouther]

Aftersupabase db PULL triggers from auth.users are still missing for me (supabase 1.110.1).

[sweatybridge]

@ebouther the command should be supabase db pull --schema auth. See the last paragraph of https://supabase.com/docs/guides/cli/local-development#use-auth-locally