Allow new auth user accounts from a specific email address domain.

[bwklein]

Feature request

Is your feature request related to a problem? Please describe.

I am building an application where the authenticated users are all from a specific domain. I want a way to only allow accounts to be created for email addresses from a specific domain.

Describe the solution you'd like

A way to specify a list of allowed domains for new account creation, or an example for how to use the existing system to check for the email address domain and allow/reject with an error, based on that check.

Describe alternatives you've considered

Handling the email validation on the registration form, but this can be bypassed on the client side.

[jjhbw]

This is my first day playing around with supabase, but maybe you can do this using a RLS rule similar to the one described here? https://supabase.com/docs/guides/auth/row-level-security#verifying-email-domains

[kiwicopple]

This definitely seems like a task for RLS

@bwklein - does @jjhbw 's suggestion work for you?

[bwklein]

@kiwicopple I received this reply from 'silentworks' on Discord about this question.

"Similar question was asked in the #ideas-and-suggestions channel. You can do so using a trigger but there is currently a pending issue that stops this from working fully as EXCEPTIONS inside of triggers on signUp don't bubble all the way up to the API level, so your user would be stopped but you wouldn't get an error message explaining why."

I then went to the channel mentioned and found this information.

[bwklein]

I raised the question on Discord and was directed to this thread in Ideas/Suggestions there...

[kiwicopple]

Ah yes, that sounds accurate - you won't be able to bubble up the error so you will need to "guess" that it's failing at the trigger. We definitely want to improve this (in the Auth server, which will require some work to make it work better with Postgres)

Is this a blocker for you @bwklein? Or is it a reasonable workaround for now?

[rotemrevivo91]

I'v used the same method to limit signups to a specific e-mail domain and i'm getting an error as expected:

1. create a before insert policy on auth.users:

create function check_user()
returns trigger
language plpgsql
as $$
begin
  if right(new.email, 11) = '@domain.com' then
    return new;
  else
    return null;
  end if;
end;
$$;

-- Triggers
--

-- trigger the function every time before a user is created - if a email is legal create user, otherwise returns an error
create trigger on_before_user_created
  before insert on auth.users
  for each row execute procedure check_user();

2. signup:

// gmail is not a whitelisted domain
await supabase.auth.signUp({email: "blabla@gmail.com", password: "..."})

3. then an error is thrown in the console:

{
  "code": 500,
  "error_id": "e6044037-cb3d-4a82-a784-34b32d112146",
  "msg": "Error creating identity"
}

if that helps anyone

[bwklein]

I'll have to give it a try myself, but from this it looks like @rotemrevivo91 solution would do the trick.

[bwklein]

@rotemrevivo91 does the user receive an email like silentworks mentioned in the reply or does that failure in your trigger stop the process before the email is sent?

[rotemrevivo91]

@rotemrevivo91 does the user receive an email like silentworks mentioned in the reply or does that failure in your trigger stop the process before the email is sent?

I'v disabled the need for users to verify their registration via e-mails, so no e-mail is being sent. I'v faced a few issues with the SMTP service of supabase. But, i'v set up a nodemailer service to do the same, at least temporarily.

[gregpalaci]

This should be in the docs #1057 versus https://supabase.com/docs/guides/auth/row-level-security#verifying-email-domains
I would mention to newbs that this is run in 'SQL QUERY' then click run and that (maybe obvious to others but the 11 is the length of '@domain.com' so when you update to your domain count your string and update accordingly :)

[vipulb2]

RLS would seem like a heavy solution for something like this if you can do it on the client side.:((

[silentworks]

I'd like to add that the raising of an exception issue I mentioned in chat with the OP from almost a year ago was fixed.

@gregpalaci we would have to create a triggers/trigger functions section in the docs for this. The section you mentioned in the docs only covers RLS examples.

[hf]

I'll transfer this issue to github.com/supabase/gotrue as we've been discussing implementing some form of allowlist / denylist behavior. Still no timeline on it, but best this is tracked there.

[gregpalaci]

I'd like to add that the raising of an exception issue I mentioned in chat with the OP from almost a year ago was fixed.

@gregpalaci we would have to create a triggers/trigger functions section in the docs for this. The section you mentioned in the docs only covers RLS examples.

Hey no worries, it came up in my search, I'm glad the original issue was resolved around exceptions 🎉 . I reckon the best course of action is the close the issue as solved.

Sorry if I miss commented I just wanted to eloborate on my findings to help new comers doing the same search as me and landing in the same result with some acltionable intel.

Thanks @hf for creating an issue in the correct place, sorry again for polluting your notifications y'all

[lauri865]

I'd like to add that the raising of an exception issue I mentioned in chat with the OP from almost a year ago was fixed.

@gregpalaci we would have to create a triggers/trigger functions section in the docs for this. The section you mentioned in the docs only covers RLS examples.

@silentworks, are you referring to the email issue or exceptions not being bubbled from signUp function call? I'm on the latest Supabase cli, and exceptions get only bubbled when the account exists, but not upon signup.

[BenLyddane]

I'v used the same method to limit signups to a specific e-mail domain and i'm getting an error as expected:

1. create a before insert policy on auth.users:

create function check_user()
returns trigger
language plpgsql
as $$
begin
  if right(new.email, 11) = '@domain.com' then
    return new;
  else
    return null;
  end if;
end;
$$;

-- Triggers
--

-- trigger the function every time before a user is created - if a email is legal create user, otherwise returns an error
create trigger on_before_user_created
  before insert on auth.users
  for each row execute procedure check_user();

2. signup:

// gmail is not a whitelisted domain
await supabase.auth.signUp({email: "blabla@gmail.com", password: "..."})

3. then an error is thrown in the console:

{
  "code": 500,
  "error_id": "e6044037-cb3d-4a82-a784-34b32d112146",
  "msg": "Error creating identity"
}

if that helps anyone

How would I alter this for a list of emails? I'm making an app where I only want certain domains to have access to different types of accounts (only engineering firms can make one account (store an array of whitelisted emails somewhere?) and manufacturers can make other types of accounts (any email can pass through))

[BenLyddane]

Here's my solution:

Step 1 make a table for your domains you want to verify against (Run these in sql editor)

CREATE TABLE allowed_domains ( domain_name VARCHAR(255) PRIMARY KEY );

Add the domain names you want to be valid as rows in the allowed_domains table. DO NOT INCLUDE THE '@' symbol.

example:

domain_name

example.com
test.com
sample.org

Then add a trigger on auth.users insert

CREATE FUNCTION check_user()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF RIGHT(NEW.email, POSITION('@' IN REVERSE(NEW.email)) - 1) = ANY (
    ARRAY(
      SELECT domain_name FROM public.allowed_domains
    )
  ) THEN
    RETURN NEW;
  ELSE
    RETURN NULL;
  END IF;
END;
$$;

CREATE TRIGGER validate_user_domain
BEFORE INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION check_user();

Then give your supabase admin permission to select from your domain table

GRANT SELECT ON TABLE public.allowed_domains TO supabase_auth_admin;

[epavanello]

For all the people who have created a table with whitelists/blacklists, it only works if you disable the RLS (Row-Level Security) or enable at least the select for anonymous users.

[lauri865]

For all the people who have created a table with whitelists/blacklists, it only works if you disable the RLS (Row-Level Security) or enable at least the select for anonymous users.

The anon role doesn't require any access to the table and would be a poor practice, as it could give free access to your customer list to anyone.

Either the authenticator role needs to have select granted to the table or the trigger function has to be defined as a SECURITY DEFINER (which means that the function is triggered with the privileges of the owner rather than the invoker). RLS you can skip altogether, unless you want the account owners to access their own data for whatever reason.

[E-Naeim]

Here's my solution:

Step 1 make a table for your domains you want to verify against (Run these in sql editor)

CREATE TABLE allowed_domains ( domain_name VARCHAR(255) PRIMARY KEY );

Add the domain names you want to be valid as rows in the allowed_domains table. DO NOT INCLUDE THE '@' symbol.

example:

domain_name

example.com test.com sample.org

Then add a trigger on auth.users insert

CREATE FUNCTION check_user()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF RIGHT(NEW.email, POSITION('@' IN REVERSE(NEW.email)) - 1) = ANY (
    ARRAY(
      SELECT domain_name FROM public.allowed_domains
    )
  ) THEN
    RETURN NEW;
  ELSE
    RETURN NULL;
  END IF;
END;
$$;

CREATE TRIGGER validate_user_domain
BEFORE INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION check_user();

Then give your supabase admin permission to select from your domain table

GRANT SELECT ON TABLE public.allowed_domains TO supabase_auth_admin;

I wanted to do same logic but with a small difference which is (checking email if it's existed in allowed_emails)

-- limit sign in to the users are pre-defined
CREATE OR REPLACE FUNCTION check_user_email()
RETURNS TRIGGER AS $$
BEGIN
  -- Check if the email is in the allowed_emails table
  IF NEW.email IN (SELECT email FROM allowed_emails) THEN
    RETURN NEW;
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_user_email_trigger
BEFORE INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION check_user_email();

desipte that email is found in the table it gives the following error:

AuthApiError: Database error saving new user
    at handleError (webpack-internal:///(rsc)/./node_modules/@supabase/gotrue-js/dist/main/lib/fetch.js:36:11)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async _handleRequest (webpack-internal:///(rsc)/./node_modules/@supabase/gotrue-js/dist/main/lib/fetch.js:84:9)
    at async _request (webpack-internal:///(rsc)/./node_modules/@supabase/gotrue-js/dist/main/lib/fetch.js:63:18)
    at async SupabaseAuthClient.signUp (webpack-internal:///(rsc)/./node_modules/@supabase/gotrue-js/dist/main/GoTrueClient.js:218:23)
    at async eval (webpack-internal:///(rsc)/./app/[locale]/page.tsx:67:33) {
  __isAuthError: true,
  status: 500
}

also I tried to disable RLS on the allowed_emails table, and tried to grant supabase_auth_admin select permission but it didn't work, any help?

[udiedrichsen]

Try this:

CREATE OR REPLACE FUNCTION check_user() RETURNS TRIGGER AS $$
BEGIN
...
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

and check in which schema the allowed_emails table is. Its import to prefix it with public. (if its is in public)