Invalid JWT if user signIn back via OTP after deleting his account

[iosephmagno]

Describe the bug The bug occurs when the user deletes his account and then signup back by using same phone number. The new user is added to Auth table but no crud can be performed on public.users table.

We are currently under review process with Apple and they are hit by this issue. Any quick help with this would be much appreciated.

To Reproduce 1) SignIn user via sms (we use Twilio Verify) 2) Delete user

await supabase.auth.admin
    .deleteUser('715ed5db-f090-4b8c-a067-640ecee36aa0');

3) Sign In back via otp 4) Perform a sql query, for example to update user field inside Users table. We get no error from query.

try {
// Even adding a refreshSession doesnt avoid issue.
  await PresenceAuth.auth.refreshSession();
  response = await Supabase.instance.client
      .from('Users')
      .update({
    'name': name,
    'surname': surname,
    'username': username,
  })
   .match({'id': userId!});
  } catch (e) {
      debugPrint('$_debugPrefix Failed to update user fields ${response.body}');
  }

The query performs well, throws no errors, but fields on the Users table are not updated.

We noticed that the JW is invalid by checking it with this service, you can decrypt the jwt, the sub field should be auth field in db for the user. https://jwt.io/

Expected behavior User should be able to delete his account and then signup again.

Version (please complete the following information):

├── supabase_flutter 2.5.6
│   ├── supabase 2.2.2
│   │   ├── functions_client 2.2.0
│   │   ├── gotrue 2.8.1
│   │   ├── postgrest 2.1.2
│   │   ├── realtime_client 2.1.0
│   │   ├── storage_client 2.0.2

[kangmingtay]

@iosephmagno after deleting the user in (2), did you check that the user is also deleted from the public.users table? This seems like the delete wasn't being cascaded to the public.users table to me.

after signing in with OTP again in step (3), a new user should be created in the auth.users table with a different ID (different sub claim in the JWT) - did you check if the sign up resulted in a new row created in the public.users table with the correct foreign key?

It would be great if you can also provide the triggers used and the schema for your public.users table.

[iosephmagno]

Hello @kangmingtay after deleting the user, it is deleted from public.users. We use cascade, see pic below.

After signing back in step 3 a new user is created but the above mentioned issue occurs. Can you replicate replicate the flow with test project and by signing user via OTP code?

[iosephmagno]

The key thing that triggers the issue is user deleting and registering back again with same number. For some reason Suapabase fails to cope with this case.

[ST1LLWATER]

And the dashboard says columns cannot be referenced even when they are already added as foreign keys. This relation has always worked. Maybe this is caused by any recent update.

[kangmingtay]

@iosephmagno I've replied to your ticket submitted but for posterity, i can't seem to reproduce the issue:

This is what I've tried

-- create public.users table
create table public.users (
 phone_number text,
 auth uuid references auth.users(id) on delete cascade
);

-- create function handle_new_user()
create function handle_new_user()                                                                                                  
returns trigger
security definer
language plpgsql
as $$                                                                                                                        
begin
 insert into public.users (phone_number, auth)
 values new.phone, new.id;
 return new;
end;
$$;

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

Then i tried the steps you mentioned in the github issue:

1. Sign-in the user via SMS
2. Delete the user
3. Sign-in back via OTP (with the same phone number as (1))

I observed the delete being cascaded to the public.users table and I'm able to sign in back via OTP successfully in (3). After (3), I also observe a corresponding row being inserted into public.users.

[kangmingtay]

@ST1LLWATER that seems like a bug with the frontend, i'm able to reference the column properly when i create the table through the SQL editor or directly via PSQL - ~will check with the frontend team on this~

edit: actually, it seems fine to me though

[kangmingtay]

closing this issue since we were unable to reproduce it