Supabase stores authentication-related information (unique ID, email, password, etc.) in the auth.users
table when users sign up. The auth
schema isn't publicly accessible for security reasons, so you can't store additional information on your users in this table.
Depending on what type of application you are building, you might need a profile
table containing profile information your users can interact with. In this post, we will:
- Create a simple
profile
table in thepublic
schema. - Enable Row-Level Security on it and restrict access for users to their own profile data.
- Write a database trigger that automatically creates a row for every user on sign-up.
Creating & Securing a Profile Table
We start by creating a profile
table with two columns: id
referencing a user ID in the auth.users
table, and display_name
.
CREATE TABLE public.profile (
id UUID REFERENCES auth.users NOT NULL,
display_name TEXT NULL,
PRIMARY KEY (id)
);
Supabase uses PostgREST to access the Postgres database via its API (i.e., the supabase
API you use in your client application). By default, a table in the public
schema can be accessed without restriction.
However, in our case, we want to restrict access on this table so that users can only select and update data that belongs to them. We are going to use Row-Level Security for this.
ALTER TABLE public.profile
ENABLE ROW LEVEL SECURITY;
When Row-Level Security is enabled for a table, all access to this table must be allowed by a row security policy. The idea here is: "Everything is forbidden unless explicitly allowed."
The first row security policy we introduce allows a user to SELECT
their own profile data:
CREATE POLICY
"Can only view own profile data."
ON public.profile
FOR SELECT
USING ( auth.uid() = id );
The second row security policy allows a user to UPDATE
their own profile data:
CREATE POLICY
"Can only update own profile data."
ON public.profile
FOR UPDATE
USING ( auth.uid() = id );
The idea behind these policies is to specifically grant rights to SELECT
or UPDATE
rows where the id
is set to the authenticated user's unique ID. Since id
is the primary key of the profile
table, this effectively leaves only one row: the user's own profile data.
If you want to know how to handle public and private access, I recommend reading the page Managing User Data from the Supabase documentation.
Automatically Creating Profiles on Sign-Up
When a user signs up or signs in with a Third-Party provider (e.g. Twitter, GitHub, Google, etc.), a new row is inserted into the auth.users
table. Ideally, we want to create a new profile at the same time. An easy way to do so is to use triggers.
The following statement creates a trigger called create_profile_on_signup
that calls the function create_profile_for_new_user()
whenever a new row is inserted into the auth.users
table:
CREATE TRIGGER
create_profile_on_signup
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE PROCEDURE
public.create_profile_for_new_user();
The function create_profile_for_new_user()
simply inserts a new row into our profile
table:
CREATE FUNCTION
public.create_profile_for_new_user()
RETURNS TRIGGER AS
$$
BEGIN
INSERT INTO public.profile (id)
VALUES (NEW.id);
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
The NEW
keyword refers to a variable holding the new database row (i.e., the one that was just inserted into the auth.users
table).
There's one thing missing, though: The display_name
isn't set. Technically, this isn't a problem, as the column is nullable. For Third-Party logins, however, we can access certain metadata that allows us to prefill this column with a sensible value.
The auth.users
table has a JSONB
column called raw_user_meta_data
containing information on the user depending on the Third-Party provider used for signing in. For example, it usually contains a user_name
field.
Making use of this, we can prefill the display_name
with this username like this:
CREATE FUNCTION
public.create_profile_for_new_user()
RETURNS TRIGGER AS
$$
BEGIN
INSERT INTO public.profile (id, display_name)
VALUES (
NEW.id,
NEW.raw_user_meta_data ->> 'user_name'
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
Now, whenever a user signs up, a new profile
is created with its display_name
set to the username used with the Third-Party provider or to NULL
when you use Supabase's sign in (here you can fill it from within the client application).
Recap
- The
auth.users
table stores authentication-related information. - You can't access or modify this table from within your application for (obvious) security reasons.
- We created a
profile
table with two columnsid
anddisplay_name
. - We enabled Row-Level Security for this table and restricted access for users to their own data.
- We created a trigger and function to automatically insert a new row into
profile
when a new row is inserted intoauth.users
table. - When a user signs up or signs in with a Third-Party provider, a new row is inserted into
auth.users
.
References
- I can highly recommend the Next.js Subscription Payments Starter, which uses this idea here.
Top comments (13)
Hello Sebastian!
Thank you for making this great and useful article, I'm trying to add email address as the username whenever a user record is created, but I don't clearly know how to implement that. Is there any way to achieve that?
Hi Sina,
Sorry for the late reply!
First, you should add an
email
column to theprofile
table:Then, you can access the
email
column from theauth.users
table to insert the right value:The function
create_profile_for_new_user
is used as anAFTER INSERT
trigger for theauth.users
table. SoNEW
within this function refers to the new row inserted into theauth.users
table and thus allows you to access every column of it.Hope this helps! :)
Thanks for sharing how to save data from a Third-Party provider! Very helpful!
It was really helpful , thankyou
Hi Sebastian!
Thanks for this amazing article, trying this as well. but hitting a error when even using the plain ID only function.
I'm using GitHub as my login, but I always get redirect with:
http://localhost:3000/?error=server_error&error_description=Database+error+saving+new+user
Is there any way to see what would be wrong?
-- Edit
Managed to get it working with the following:
Hi Chris!
I assume the use of:
doesn't work with GitHub?! 🤔
The following PR has introduced this feature: github.com/supabase/gotrue/pull/127
I haven't had time to look deeper into this, but the
gotrue
repo of supabase should be the right place to investigate (or open an issue).Glad you could fix it and you enjoyed the article!
Hello Sebastian.
Thank's for sharing your knowledge with all of us..
Your article help me so much.!!
Can you help me with this:
When the user sign up...
How can you check if the email exists in auth.users table?
Hi Dimitris!
The
supabase.auth.signUp
function automatically checks if a user already exists. (See supabase.com/docs/reference/javasc... for further info.)Is that what you mean?
Best regards,
Sebastian
I'm using standard email and password sign up and passing data property in the second argument of supabase.auth.signUp(). I see this in the docs but I see no mentions of how to access it or view it in the admin.
How can I go about inserting meta data from signUp() into my profile table?
Thanks!
Hey, hope you've figured it out by now, still wanted to add this for future reference
This should work using
which I found in Discord.
Thanks for sharing this article. I was having an issue and I believe this will solve it. I appreciate it.
Really helpful article, thank you!!
hi @sruhleder I am getting this error while sign up in supabase i have taken the above steps still i am getting this error AuthApiError: duplicate key value violates unique constraint "user_pkey"