Users sign up and Row Level Security - Supabase Database tutorial

Sandro Maglione

Sandro Maglione

Web development

Implementing authentication is complex and tedious. Supabase can help! In this post we are going to learn how to setup a complete end-to-end authentication system for your app.

We are going to learn how to:

  • πŸͺ„ Sign up a user using the Supabase SDK
  • πŸ—‚ Create a SQL table to store the user information
  • πŸ”— Connect together Supabase Auth and Supabase Database to register our users' information
  • πŸ” Setup Row Level Security to protect the access to the database

At the end, you will have a complete and secure authentication system, without the need to implement everything on your own πŸš€.

Overview - How sign up works using Supabase

The following diagram shows what we are going to implement:

auth schema in Supabase

What we are going to do is the following:

  • Create the public.user table, with a reference to auth.users (automatically created by Supabase)
  • Create a trigger to insert new user inside public.user after a successful sign up
  • Setup Row Level Security for public.user

Signing up is a matter of a few lines of code using Supabase.

You can read Flutter Supabase Authentication - Complete tutorial to learn the details of how to implement authentication with Supabase

The Supabase SDK provides a simple signUp: you just need to collect the user information (email and password) and call the signUp method.

Note: Supabase also provides sign up using many different providers (Google, Facebook, Github, and others)

// Sign up using the javascript SDK
const { data, error } = await supabase.auth.signUp({
  email: 'example@email.com',
  password: 'example-password',
})
// Sign up using the Flutter SDK
final AuthResponse res = await supabase.auth.signUp(
  email: 'example@email.com',
  password: 'example-password',
);

Connect authentication and database

After calling signUp successfully, you will see a new user registered in the authentication dashboard of your Supabase project.

Under the hood, every Supabase project comes with an auth schema already pre-configured:

auth schema in Supabase

This schema has a users table. This table is used to store the information of a user after authentication (after calling signUp). The Supabase Authentication SDK does this automatically after a successful sign up.

We now want to have a new table inside the public schema containing the information about the user that we want to store for our own application.

The public schema is where we create all the tables specific for our project. public is automatically created and empty when you start a new Supabase project.

These are the steps that we are going to discuss in more details in this article:

  • Create a public.user table that contains all the information of the user
  • Reference (foreign key) the auth.users table from the public.user
  • Create a trigger to insert a new row inside public.user after a successful sign up
  • Setup Row Level Security, using policies, to limit the access to private information about the user

Create table to store user profile

The first step is to create our own user table inside the public schema.

The auth.users table is used internally by Supabase, so it's recommended to not change its structure and instead create a reference inside the public schema.

We can use the Supabase dashboard to achieve this in a few easy steps.

First open your Supabase project. You can access the database dashboard from the menu on the left.

Access the Supabase database dashboard

Click on "Create table". Add the name of the table (user) and an id column of type uuid as primary key. The other columns are the information about the user that you want to store for your specific project:

Create new user schema in Supabase

The most important step is to add a reference (foreign key) to the auth.users table. Click on the link button on the id column and select the users table's id column:

Add reference to auth table

Now you will see the link button highlighted: the reference has been created. Finally, click on Save to create the table:

Added foreign key, click to create table

The Supabase dashboard allows you to create a table using an easy visual editor. The same result can be achieved by running a create table query directly, similar to the following:

create table public.user (
  id uuid references auth.users not null,
  first_name varchar(255),
  last_name varchar(255),
 
  primary key (id)
);

Create a user after a sign up

The next step is actually adding a new row inside the public.user table after a sign up.

In order to achieve this we are going to create an SQL function which returns a trigger.

This function will be called every time a new row is added to auth.users and it will insert a new row inside public.user (using a trigger).

We are going to use the SQL editor inside the Supabase dashboard. First, access the editor from the menu on the left:

Open SQL editor to run queries

Click on "New query".

Define an SQL function which inserts a new row inside user every time a new user signs up:

-- Inserts a row into public.user
create function public.handle_new_user()
 
-- Return type of the function: a Postgres trigger
returns trigger
 
-- Language used, plpgsql is a Postgres-specific SQL language 
language plpgsql
 
-- Define security rules: trusted schemas (public)
security definer set search_path = public
 
-- Start definition of the function
as $$
begin
  insert into public.user (id)
  -- Reference the newly created `id`
  values (new.id);
  return new;
end;
 
-- Return the function
$$;

Read more about how to create a function in Postgres here

Finally, click on Run to execute the query:

Click on 'Run' to execute the query

You will see a message Success. No rows returned: this means that the query was executed successfully.

Success. No rows returned

You can read more about security and search_path here

Define trigger to insert new user

The last step is defining an SQL trigger which reacts to an every insert inside auth.users and calls the function we just created.

A trigger, as the name suggests, is a way to listen to any event in SQL and execute some code before or after this event occurs.

The trigger will listen for an insert event in auth.users and call the handle_new_user function we defined previously.

Use again the SQL editor in the Supabase dashboard, click on "Create query", paste and run the code below:

-- Trigger the function every time a user is created
create trigger on_auth_user_created
 
  -- New sign up: insert in `auth.users`
  after insert on auth.users
 
  -- Call `handle_new_user` function
  for each row execute procedure public.handle_new_user();

You can check that everything executed correctly directly inside the Supabase dashboard. From the menu on the left you can access the database information. Under Triggers you should see your newly created on_auth_user_created trigger:

View the trigger correctly created in the dashboard

Enable Row Level Security

The very final step is to enable Row Level Security and define some policy to allow the user to read his own information.

Row Level Security allows to automatically add a WHERE check to every request. In this case, the information of a user should be readable only by the user who owns them.

We already enabled Row Level Security when we initially created the public.user table:

Create new user schema in Supabase

Row Level Security is enabled, but we don't have any policy yet that allows to read and write in the table:

Table created with row level security, but no policies yet

If Row Level Security was not enabled, you can run the following query:

alter table public.user enable row level security;

Since Row Level Security is enabled, this table is accessible via the API but no data will be returned unless we set up some policy.

We can create them directly inside the Supabase dashboard. You can access the policies editor from the menu of the left:

Create new user schema in Supabase

Click on "Create policy", and then select full customization:

Create new custom policy

Let's now define a policy that only allows to insert inside public.user if the id matches the authentication id:

Insert policy for users

Here, auth.uid() is an helper function provided by Supabase which returns the ID of the user making the request. The policies make sure that a user can view and change only a row that references his own ID.

Click on Review and then Create policy to complete the creation:

Confirm policy creation

This corresponds to running the query below in the SQL editor:

CREATE POLICY "Users can insert their own profile." ON "public"."user"
AS PERMISSIVE FOR INSERT
TO public
 
WITH CHECK (auth.uid() = id)
 
-- Or also this
create policy "Users can insert their own profile."
  on "user" for insert
  with check ( auth.uid() = id );

You can now view the created policy directly inside the dashboard:

Policy created in the dashboard

We can then create other policies (select, update):

-- Same policy created above
create policy "Users can insert their own profile."
  on "user" for insert
  with check ( auth.uid() = id );
 
create policy "Users are viewable by users who created them."
  on "user" for select
  using ( auth.uid() = id );
 
create policy "Users can update own profile."
  on "user" for update
  using ( auth.uid() = id );

Finally, you can view all the policies created inside the dashboard:

View all created policies

This is all! You now have a complete end-to-end authentication system ready to be deployed!

Every time a new user signs up (using the super easy signUp method), the system will just work, and you will have a new user, fully secured, inside the user.public table, ready to start using your app!

The next step is to add more tables, use the Supabase Storage API, Edge Functions, and more!

You can subscribe to my newsletter here below to stay up to date with the latest releases, and more πŸŽ‰πŸ‘‡

Thanks for reading.

πŸ‘‹γƒ»Interested in learning more, every week?

Every week I build a new open source project, with a new language or library, and teach you how I did it, what I learned, and how you can do the same. Join me and other 700+ readers.