Upon user sign-up, Supabase saves details such as unique ID, email, and password in the
auth.users
table. For security purposes, the auth schema is kept inaccessible publicly.Depending on the nature of your application, it may be essential to have a profile table to store user information and also allow users to interact with their profile.
In this guide, we'll walk through creating a profile table, securing it, and automating profile creation upon user sign-up, ensuring a smooth experience for you and your users.
1. Creating a profile table
Let's start by creating a profiles table with three columns:
id
first_name
last_name
bio
avatar
The
id
column references the auth.users
table and is set to cascade delete, ensuring that if the referenced user is deleted, the corresponding profile entry will be deleted as well.sqlCREATE TABLE public.profiles ( id uuid not null references auth.users on delete cascade, first_name text, last_name text, bio text, avatar text, primary key (id) );
2. Securing your profile table
By default, a table located in the public schema is accessible without any restrictions. However, to ensure data privacy and security, you should restrict access to this table so that users can only select and update their own data.
This can be achieved using
Row-Level Security (RLS)
.sqlALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
With RLS enabled, all access to the table must be allowed by a row security policy. In other words, "Everything is forbidden unless explicitly allowed."
Allow users to view their own profile
The first policy we'll introduce is aimed at permitting a user to
SELECT
their own profile data. Here’s the command to create this policy:sqlCREATE POLICY "Can only view own profile data." ON public.profiles FOR SELECT TO authenticated USING ((SELECT auth.uid()) = id);
Allow users to update their own profile
Following that, we'll add a second policy, allowing a user to
UPDATE
their own profile. The command for this policy is as follows:sqlCREATE POLICY "Can only update own profile data." ON public.profiles FOR UPDATE TO authenticated USING ((SELECT auth.uid()) = id) WITH CHECK ((SELECT auth.uid()) = id);
Through these policies, we are ensuring that users can view and modify only their own profile data.
3. Automating profile creation upon signup
Whenever a user signs up or signs in using a third-party provider (such as Twitter, GitHub, Google, etc.), a new entry gets added to the
auth.users
table.To automate the creation of a new profile simultaneously, a PostgreSQL function alongside a database trigger can be used.
Function
The
create_profile
function is responsible for inserting a new row into the profiles table:sqlCREATE OR REPLACE FUNCTION public.create_profile() RETURNS TRIGGER AS $$ BEGIN INSERT INTO public.profiles (id, first_name, last_name) VALUES ( NEW.id, NEW.raw_user_meta_data ->> 'first_name', NEW.raw_user_meta_data ->> 'last_name' ); RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER;
Please note that you can modify the above code based on the specific metadata you wish to include when creating a profile in your app.
Trigger
The
create_profile_trigger
is invoked with every signup and is responsible for calling the create profile function:sqlCREATE TRIGGER create_profile_trigger AFTER INSERT ON auth.users FOR EACH ROW WHEN ( NEW.raw_user_meta_data IS NOT NULL ) EXECUTE FUNCTION public.create_profile();
In this setup, the
create profile
function handles the insertion of new profile data into the public.profiles
table. Meanwhile, the create profile_trigger
ensures that this function is executed whenever a new user signs up and relevant user metadata is available.Notes for Bubble.io users
If you're planning to use Supabase with Bubble.io, you'll find our plugin to be quite handy. It's designed to make the integration between Supabase and Bubble.io a breeze.
Automatically associate a profile to new user signups
- Navigate to the plugin settings and set your Supabase project credentials.Read the documentation for more details.
- Add the Supabase Auth element to your page
- Create a user with metadata: call the action
Create a new user
and setfirst_name
andlast_name
as user metadata.
Conclusion
The outlined setup provides a secure and efficient approach to managing user profiles in an application supported by Supabase. By automating the creation of user profiles at sign-up and leveraging PostgreSQL functions and triggers, this method simplifies the user registration process.
Read More
For further insights you can explore the following resources: