Nocodegarden | Build without limits

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.

CREATE 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).

ALTER 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:

CREATE POLICY "Can only view own profile data."
  ON public.profiles
  FOR SELECT
  USING ( 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:

CREATE POLICY "Can only update own profile data."
  ON public.profiles
  FOR UPDATE
  USING ( 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:

CREATE 
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:

CREATE 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

1. Navigate to the plugin settings and set your Supabase project credentials.
  Read the documentation for more details.
1. Add the Supabase Auth element to your page
1. Create a user with metadata: call the action Create a new user and set first_name and last_name as user metadata.

Signup and create profile on Bubble.io

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.

For further insights you can explore the following resources:

Creating user profiles on sign-up in Supabase

9 october 2023 · 10 minutes read