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: 'user@email.com'});

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: 'x@gmail.com'});
// 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: 'x@gmail.com'});
// 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.

[jarar21]

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
  }
}

this works

[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?

[ThingEngineer]

This is my user invitation logic:

-- Function to get user ID by email
create or replace function public.get_user_id_by_email(p_email text)
returns uuid
language plpgsql
security definer
as $$
begin
    return (
        select id
        from auth.users
        where email = p_email
        limit 1
    );
end;
$$;

-- Revoke all permissions from authenticated, anon, and public
revoke all on function public.get_user_id_by_email(text) from authenticated, anon, public;

-- Grant execute permission only to supabase_auth_admin
grant execute on function public.get_user_id_by_email(text) to supabase_auth_admin;

Call it on the server like this:

/**
 * Retrieves a user's ID by their email address using a database function.
 * @param {string} email - The email address to look up.
 * @returns {Promise<string | null>} A Promise that resolves to the user's ID, or null if not found.
 */
export async function getUserIdByEmail(email: string): Promise<string | null> {
    const supabase: SupabaseClient = createClient(SUPABASE_DATABASE_URL, SUPABASE_SERVICE_KEY);
    const { data, error } = await supabase.rpc('get_user_id_by_email', { p_email: email });

    if (error) {
        console.error('getUserIdByEmail error', error);
        return null;
    }

    return data;
}

Use it in your app like this:

... get slug params
... get jwt
... superform validation

// Insert user flow
const invitedUser = await inviteUserByEmail(form.data.email);
let userId: string;
let preExistingUser = false;

if (invitedUser === 'email_exists') {
    userId = await getUserIdByEmail(form.data.email);

    if (!userId) {
        return message(form, { status: 'error', text: 'Error inviting user' });
    }

    preExistingUser = true;

    // TODO: Notify user they were added to the company: in app notification, email, realtime publication that updates, the company select combo box, etc.
} else {
    userId = invitedUser.user.id;
}

the rest...

preExistingUser is handy when there is other logic in your post invite flow that is dependent on whether or not the invited user was already a user of another tenant/company/etc. This is from a multi-tentant app obviously.

update: comments

[GuillaumeMeheut]

Is there not any example for a real app using supabase to check the auth flow implementation ?

[JustFly1984]

I've just changed an alert message to:

Please check your inbox for email verification! if Email is not arrived shortly, please check if you already registered via Google or Apple!

and in settings I've added identity synchronization buttons https://supabase.com/docs/guides/auth/auth-identity-linking.

Hacking over this user flow is not safe.
Showing user a message that user already exists also not correct, cos somebody can just brute force for your userbase.

[mvp-studio-repo]

Best solution, without breaking any logic or crafting not reliable method, is to create a specific function in supabase to return a confirmation

It can be designed like this

---

create or replace function public.email_exists(p_email text)
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
select exists (
select 1
from auth.users u
where lower(u.email) = lower(p_email)
);
$$;

grant execute on function public.email_exists(text) to anon;

---

Callable from your app, locked by your supabase security access, fastly check if the email is used or not, easy to reproduce.

I couldn't find any better way to do this, as supabase do not provide a default build in function for it.

[unav4ila8le]

I've read all the answers, and while I think creating a specific function in Supabase to return a confirmation is a good idea, why not just doing this?

const { data: signUpData, error } = await supabase.auth.signUp(data);
 if (error?.code === "user_already_exists") {
    return {
      success: false,
      code: "user_already_exists",
      message: "This email is already registered. Please log in instead.",
    };
  }

I am interested in knowing what are the pro and cons of this.

[LIvvieLynn]

I see a lot of answers about changing access to auth tables or using functions (e.g., security definer/edge) to determine if an account exists. Each of these are a security risk. The solution I find that works is to allow the dummy data to return and present the user with a message that a confirmation link will be sent to their email if an account does not already exist. You can then provide both a resend and password reset link (simplest solution).

Or, what I prefer for a cleaner interface is to create an edge function using a maintenance key (service role) to check if the email is present and email has been confirmed. From the edge function you can then resend the email confirmation email as usual (if email is not confirmed) or send a custom email from the edge function notifying the user an account already exists. On the front end do not tell the user which email was sent.

This protects the privacy of users and from bad actors trying to determine which emails have registered. If you do not like the user not getting an initial email you can just build in the sign up functionality from the edge function.

That said, if you do use edge functions with CAPTCHA and will need to confirm the tokens manually. As well as be mindful when using a service role. You may need two clients. 1 for service role calls (email/confirmation status) and one for the authenticated user (user inserts/updates).

Providing brief snippet from an edge function for sending email. Your SMTP provider should have instructions you can follow for additional information. Below was for zepto.

    const response = await fetch(SMTP_URL, {
      method: "POST",
      headers: {
        "accept": "application/json",
        "content-type": "application/json",
        "authorization": SMTP_TOKEN
      },
      body: JSON.stringify({
        from: {
          address: "<email address>",
          name: "<email name>"
        },
        to: [
          {
            email_address: {
              address: email
            }
          }
        ],
        subject: subject,
        htmlbody: message
      })
    });