Check if the user exists in Auth

[cdedreuille]

Is there a way to check if a user exists in Auth by checking the email address?

[silentworks]

If you try to create an account with the same email you will be notified that the email is already in use. You could also create a stored procedure to copy the auth.user email into your own users table and you would be able to query that easily. You could probably also create a stored procedure to check if the email already exists in the auth.user table.

[liaujianjie]

@akashSharma135

The users table is my table.

create or replace function is_email_exist(email varchar)
returns boolean
language sql
security definer
set search_path = public
stable
as $$
DECLARE
val varchar;
BEGIN
   SELECT u.email INTO val FROM users u
   WHERE u.email = LOWER($1);

   IF FOUND THEN
      RETURN TRUE;
   ELSE
      RETURN FALSE;

END;
$$;

The procedure return a boolean value!

Note that this stored procedure will be callable on your client without any credentials. You may want to check that the role() is the service account role in production.

[zakaria-chahboun]

@liaujianjie
I used this procedure in my signup form in my project, To check if the email exist or not before the user send the form!
Just a UX thing!

[gfra54]

This anwser shouldn't be the accepted one, because as stated in @CalebLovell 's message, the behaviour of the signup method is different.

[silentworks]

@gfra54 the answer was correct at the time of asking, this behaviour has changed since then.

[gfra54]

Yes, but maybe the accepted answer shoudl also change ?

[CalebLovell]

I'm using supabase.auth.signUp() and if I try to create an account with an email that already exists, it just returns me that user's account information. It doesn't throw an error at all. Isn't that a security vulnerability? Apologies in advance if I'm doing something wrong or misunderstanding...

[Ejayz]

Thanks ill up this one. It solves the email only scenario .

[mellowcello77]

This helps, I hope this stays as it is!

[ellaboevans]

This saved me! Thanks a lot @indymaat

[nohack2]

The identities array is empty only for a confirmed user. If a user has registered but not yet confirmed, then you still get a non empty identities array in response and in which case you would have overridden the password of the non confirmed user now.
So, I don't think the identity check works.

[Leedwing]

@nohack2 you are right. However, this is already a good start, as you would likely always ask the user to confirm his email, at least in most of the cases.

[BrettHuang2019]

I have a work around:

• Create a table with one column is linked to user email
• try to insert an entry with an email
• If success, the email is an exsited user
• If error, the email is not a user ( error: Key is not present in table 'users'

[florianwalther-private]

Couldn't we just query the user table for that existing email?
Edit: Ok, just realized that we'd have to expose all user emails publicly in order to query it directly.

[danvoyce]

In the response there's user.identities which has an empty array if the user has already signed up and confirmed their email. If they've not confirmed their email then there's an object in the array containing the temp / fake user details.

So we should be able to do something like:

if (user.identities.length) {
   // please confirm your email, or send again
} else {
   // already signed up, sign in instead?
}

I'd kinda like supabase to do this for us though and return an error.

[scalemaildev]

Doesn't user.identities.length also have entries on a successful signup? Or are you intending to have a generic 'please confirm your email' message that could be interpreted for either case?

[natfabulous]

Thank you. I don't know why this difference between success and failure exists in this "obfuscated" context, but I'm sure glad you found it!

[csalmeida]

I'm having the same issue here, I would like to query the users table to check if a user exists with a given email address to be able to show a message to the users saying the email is taken.

The reason I would like to do this is that I don't think signUp() can be trusted for this since it returns a fake user object instead of an error message when Confirm email is enabled in the Providers settings.

I guess the solutions at the moment are:

1. Create a public.users table with the email column to serve the purpose of checking for the email and adding data to it with a trigger.
2. Check if the returned object has any user.identities, although I'm not sure what identities is exactly. If identities lists all forms signing up, as in, email and OAuth providers then it can be used to show an error to the user.

[heikir]

However, when using signInWithOAuth then the response object is of type OAuthResponse which contains only the provider and some obscure url, therefore there being no chance to check whether user was a new signup or just an existing user logging in.

I don't really see a work-around here. If I need to introduce a stand-alone users table anyways just for this use case, I would rather migrate to next-auth and have more control over my auth implementation.

Anything I am missing here?

[csalmeida]

Using next-auth could be a good workaround if you want more control.

The way I see it after having worked with Supabase for a few months now, it's best to think of the user's table just for authenticating the user as much as possible and anything else should be added to a table for that purpose.

However, in this case, I did find a way to check if a user was previously created by testing and inspecting the returned object after creating a user.

If I remember correctly the createdUser.user.identities array is empty if a user already exists.

This means something along these lines could be done as a workaround to find out if a user had already signed up with those details. Here's a user being created:

  const supabaseClient = createBrowserClient()
    const { firstName, lastName, email, password } = formData

    const { error, data: createdUser } = await supabaseClient.auth.signUp({
      email,
      password,
      options: {
        emailRedirectTo: 'supabase.com',
      },
    })

Then the object can be inspected to check if the email is taken:

      // Currently the response of signUp returns a fake user object instead of an error.
      // For now we check the identities object which would be empty if a user already exits.
      const emailIsTaken = createdUser.user.identities?.length === 0

      // Example of setting an error message.
      if (emailIsTaken) {
        setMessage({ type: 'error', content: 'Email is taken' })
      }

A better solution in my view would be for the supabaseClient.auth.signUp method to return an error stating the email was taken. Hope this helps somehow. 🙏

Update: Supabase does throw a User already registered error but only when Confirm email is disabled. [1]

Note
The snippets include pseudo-code and it won't work unless adapted in the context of an application.

[ioucyf]

This is what I do. I created the function:

create or replace function get_user_id_by_email(user_email text) returns uuid
as $$
  declare
  user_id uuid;
begin
  select id 
  from auth.users 
  where email = user_email 
  into user_id;

  return user_id;
end;
$$ language plpgsql security invoker;

then I revoked all roles' privileges except for service_role using the following query:

do
$$
declare
    pg_role record;
begin
  for pg_role in select rolname from pg_roles
    loop 
      execute 'revoke all on function "public"."get_user_id_by_email" from ' || quote_ident(pg_role.rolname);
    end loop;
  grant EXECUTE ON function "public"."get_user_id_by_email" to service_role;
end;
$$

finally, I call it on the server with a supabase admin client (created with service role key) to call the function, like so:

const {data, error} = await supabase.rpc('get_user_id_by_email', {user_email: '[email protected]'});

if the user exists, you'll get an id, otherwise, the user does not exist.

---

P.S. I noticed that the "supabase_admin" role is also is able to bypass and invoke the function after revoking it from all roles except for service_role. I tested it on anon and authenticated, and they're both unable to invoke it as expected. I figured since supabase_admin is supposed to be granted only for admins, there's no harm.

To check the privileges for a specific function, i used the following query:

SELECT proacl,proname FROM pg_proc WHERE proname = 'get_user_id_by_email';

and as expected, it shows that the only role who has permission to invoke it is indeed just the service_role; I'm still trying to figure things out, but so far, this is what I have. I hope it helps.

[halofe]

it works in the SQL Edtor on supabase

[ioucyf]

@halofe

try running the following from your supabase project's dashboard SQL Editor:

grant ALL ON auth.users to service_role;

then try invoking the function again with supabase-js client:

const client = serverSupabaseServiceRole(event)
const { data, error } = await client.rpc('get_user_id_by_email', {user_email: '[email protected]'});
// this should work

If it works, I'd also try:

const anonClient = createClient(SUPABASE_URL, SUPABASE_ANON_KEY);
const { data, error } = await anonClient.rpc('get_user_id_by_email', {user_email: '[email protected]'});
// this should fail

to make sure that it fails and to confirm that the function only works with a client created with the service_role key and nothing else.

[halofe]

I've confirmed after the "grant" above, it works on the server and fails on the client.

[ioucyf]

Perfect!

I'm glad to hear that I was able to help!

I wish you the best of luck.

[ioucyf]

@halofe I'd like to add one more note if I may:

the grant ALL ON auth.users to service_role; is a bit "too much" to get the function to work. The function requires "read access" only, and "grant ALL" gives service_role the permission to do all operations "select insert update delete". It's a good "safety switch" to have on the service_role to prevent accidental data mutation.

I personally use my service_role client as admin to do certain operations on auth.users, that's why I grant all. However, if all you need is the necessary permissions to allow service_role to invoke and use the function, the following should be enough:

To revert the grant ALL:

revoke ALL on auth.users from service_role;

then to only give read permission, you can run the following:

grant select ON auth.users to service_role;

My bad, as I should've mentioned that earlier; I apologize for that.

[halofe]

OK，it's better to grant least permission. many thanks for your help

…

On Sun, Mar 12, 2023 at 20:37 ofeenee ***@***.***> wrote: @halofe <https://github.com/halofe> I'd like to add one more note if I may: the grant ALL ON auth.users to service_role; is a bit "too much" to get the function to work. The function requires "read access" only, and "grant ALL" gives service_role the permission to do all operations "select insert update delete". It's a good "safety switch" to have to prevent accidental data mutation. I personally use my service_role client as admin to do certain operations on auth.users, that's why I grant all. However, if all you need is the necessary permissions to allow service_role to invoke and use the function, the following should be enough: grant select ON auth.users to service_role; My bad, as I should've mentioned that earlier; I apologize for that. — Reply to this email directly, view it on GitHub <#1282 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A2TS3SS75WRVZMN6PAESYZLW3W7OZANCNFSM43S7LFZQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[PrabhdeepSingh]

I started using supabase recently, and I realized I could leverage the identities array inside the data.user object after calling supabase.auth.signUp to determine if the email address is taken or not. Does anyone know how to trigger another confirmation email if the user didn't receive the first one through code?

Example code:

let { data, error } = await supabase.auth.signUp(formData);

if (data && data.user) {
  // Check if the user got created
  if (data.user.identities && data.user.identities?.length > 0) {
    // success
  } else {
    // failed, the email address is taken
  }
}

[m1dok]

Hi ! see PR #631 , they added supabase.auth.resend({ email, type: "signup"}) call.

you can check if the response contains an identities array not empty and session null, means the mail has not been verified, and after user input use this call.

[NicoCieri10]

This worked for me with the current state of Supabase.

[alexmartinezm]

Hi there! After some testing, I've realized that new users do not have the properties confirmed_at nor recovery_sent_at in the user and session objects return from the verifyOtp function. I hope this helps someone.

[dennisjonda]

hey,
I'm exploring alternative methods to verify a user's existence using their phone number. Currently, I've linked a public user table with an authentication user table. While I can technically access data from the public.user table, I'm concerned about potential security risks. As a result, users are restricted to only viewing their own user row.

[aleksa-codes]

What should we do if the account is pending email verification? Currently, by default, it just sends the confirmation email again and again. Anyone got a clue?