The Definitive Guide to Managing Supabase Users the Right Way

Supabase is a powerful platform that makes it easy to authenticate and manage users in modern applications. In this post, we'll explore how Supabase handles users and how you can manage them efficiently and securely.

Supabase deals with users

Supabase provides a auth.users table that stores important information about each user and their sessions. However, for security reasons, the auth.users table should not be publicly accessible as it contains sensitive and confidential data. Therefore, it is crucial to find a way to provide this information securely without exposing private data.

Efficient and Secure Strategies for User Management on Supabase

An effective strategy is to create your own user tables within the profiles schema. This allows you to customize how user information is stored and displayed, while keeping sensitive data secure. Here are the recommended steps:

1. Create the Table to Store User Profiles
2. Protect the User Profile Table with RLS
3. Automate the Creation of User Profiles with Triggers and Functions

Step 01: Create the Table to Store User Profiles

To store user profiles, we will create a table called profiles. This table will link directly to the auth.users table to ensure that each profile is associated with an authenticated user. Specify the ON DELETE CASCADE clause to ensure that if a user is deleted, their profile will also be automatically deleted.

create table profiles (
  id uuid not null references auth.users on delete cascade,
  email text,
 
  primary key (id)
);

Step 02: Protect the User Profile Table with RLS

To protect the profiles table, let’s create a row security rule (RLS) that allows each user to modify only their own profile. This is done by linking the id column of the profiles table to the id column of the auth.users table.

-- Enable Row Security (RLS) on the 'profiles' table
alter table public.profiles enable row level security;
 
-- Policy to allow all users to see all profiles (optional if necessary)
create policy "Public profiles are viewable by everyone." on profiles
  for select using (true);
 
-- Policy to allow users to enter their own profiles
create policy "Users can insert their own profiles." on profiles
  for insert with check (auth.uid() = id);
 
-- Policy to allow users to update their own profiles
create policy "Users can update their own profiles." on profiles
  for update using (auth.uid() = id);
 
-- Policy to allow users to delete their own profiles
create policy "Users can delete their own profiles." on profiles
  for delete using (auth.uid() = id);

Step 03: Automate the Creation of User Profiles with Triggers and Functions

To automate the creation of user profiles, we will create a trigger that will fire whenever a new user is registered in the auth.users table. The trigger will call a function that will automatically create a corresponding profile in the profiles table.

-- Insert a profile for each new user
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public.profiles (id, email)
  values (new.id, new.email);
  return new;
end;
$$;
 
-- Triggers the 'handle_new_user' function whenever a new user is created
create or replace trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

Another strategy is to create a trigger that will fire whenever a user updates their profile in the auth.users table. The trigger will call a function that will automatically update the corresponding profile in the profiles table.

-- Updates the profile whenever a user updates their information
create or replace function handle_update_profile () returns trigger security definer language plpgsql as $$
begin
  update public.profiles
  set email = new.email
  where id = new.id;
  return new;
end;
$$;
 
-- Triggers the 'handle_update_profile' function whenever a user updates their information
create or replace trigger handle_update_profile_trigger
after
update on auth.users for each row
execute function public.handle_update_profile ();

Final result

If you followed all the steps, the result will be the same as shown in the video below:

Conclusion

Aaaand… here’s Lowcoder 🔥! With these strategies, you can efficiently manage user profiles on Supabase in a secure and scalable way.

References

• User Management Supabase