On auth trigger only if email confirmed

I currently have a trigger that inserts a new row in public.users when a user is created and it works great.

However, I would like to insert in public.users if and only if email_confirmed_at is not null as follows:

create function public.handle_new_user()
returns trigger as
$$
  begin
    -- if new.email_confirmed_at is not null then
      insert into public.users (...)
      values (...);
      return new;
    -- end if;
  end;
$$
language plpgsql security definer;

create trigger on_auth_user_created
  after insert on auth.users
  for each row
    execute procedure public.handle_new_user();

create function public.handle_new_user()
returns trigger as
$$
  begin
    -- if new.email_confirmed_at is not null then
      insert into public.users (...)
      values (...);
      return new;
    -- end if;
  end;
$$
language plpgsql security definer;

create trigger on_auth_user_created
  after insert on auth.users
  for each row
    execute procedure public.handle_new_user();

create function public.handle_new_user()
returns trigger as
$$
  begin
    -- if new.email_confirmed_at is not null then
      insert into public.users (...)
      values (...);
      return new;
    -- end if;
  end;
$$
language plpgsql security definer;

create trigger on_auth_user_created
  after insert on auth.users
  for each row
    execute procedure public.handle_new_user();

create function public.handle_new_user()
returns trigger as
$$
  begin
    -- if new.email_confirmed_at is not null then
      insert into public.users (...)
      values (...);
      return new;
    -- end if;
  end;
$$
language plpgsql security definer;

create trigger on_auth_user_created
  after insert on auth.users
  for each row
    execute procedure public.handle_new_user();

If you keep the condition commented out, the row will be inserted every time, even if just invited by email.

If you add the condition, user invitation/creation doesn't work anymore and gives the following error:

Failed to invite user: failed to make invite request: Database error saving new user

Failed to invite user: failed to make invite request: Database error saving new user

Failed to invite user: failed to make invite request: Database error saving new user

Failed to invite user: failed to make invite request: Database error saving new user

The code is valid, but there is something in the "create user" function from the auth schema that is bothered by that condition.

Btw, I wanted to have a look at that function in pgAdmin but all my Supabase servers were disconnected, impossible to reconnect (all my other non-Supabase servers work just fine). Is there currently a problem that is still not reported on status.supabase.com?

On auth trigger only if email confirmed

Similar Threads

Similar Threads

Similar Threads