admin.updateUserById behavior with Anonymous Users

[MathieuUrstein]

Bug report

Describe the bug

When using supabase.auth.admin.updateUserById to force an email confirm on an anonymous user, there are multiple issues:

1. If you only provide an email_confirm like admin.updateUserById(id, {email_confirm: true}) and you already have the email column set
   1. Then you have the email_confirmed_at column set
   2. but no changes for is_anonymous, role or aud
2. If you provide an email and email_confirm like so admin.updateUserById(id, {email_confirm: true, email}) you get (on top of expected email and email_confirmed_at column changes):
   1. is_anonymous set from true to false
   2. but the aud (audience) and role columns are left to anonymous

Expected behavior

If there is no email provided and email_confirm is set to true, check if the column is already set.

1. If it’s not throw/return an error.
2. If it is proceed to the following expected behavior

If the value of email_confirm can be set to true (whether email was provided or not), do the following changes:

1. If row role is set to anonymous set it to authenticated
2. If row aud is set to anonymous set it to authenticated
3. If row is_anonymous is set to true set it to false

This is especially important because if aud and role columns are left to anonymous, methods like:

supabase.auth.admin.generateLink({
  email,
  type: "recovery",
})

Will throw User with this email not found

This last error message is also an issue. The user exists with this email but it’s not considered because it’s an anonymous one. The error message could be Authenticated user with this email not found

System information

• OS: macOS
• Version of supabase-js: 2.46.2
• Version of Node.js: 20.18.1

Additional context

#1578 Issue that partially fixed the situation

[MathieuUrstein]

In case someone has the same issue here the kind of stuff you can do to help:

await supabase.auth.admin.updateUserById(user_id, {
     email: user.email,
     email_confirm: true,
     role: "authenticated",
})

-- The following function and trigger can be dropped once this issue is fixed:
-- https://github.com/supabase/auth/issues/2013
-- Purpose: updates the "aud" column of the "auth"."users" table to "authenticated"
-- when the "role" column is changed to "authenticated".

-- Create the trigger function
create or replace function public.update_user_aud_on_role_change()
returns trigger
language plpgsql
security definer -- The function needs to operate on the auth.users table
-- Set search_path to an empty string to prevent schema hijacking and enforce explicit schema qualification.
set search_path = ''
as $$
begin
  -- Check if the role column was changed to 'authenticated'
  if new.role = 'authenticated' and old.role is distinct from 'authenticated' then
    -- Update the aud column to 'authenticated'
    new.aud := 'authenticated';
  end if;
  return new;
end;
$$;
-- Create the trigger on the auth.users table
drop trigger if exists on_user_role_change_update_aud on auth.users;
create trigger on_user_role_change_update_aud
before update of role on auth.users
for each row
execute function public.update_user_aud_on_role_change();