Automatically Generate a Profile for Every User with PostgreSQL Function Triggers

Share this video with your friends

Send Tweet

Supabase has an auth.users table that contains information about our user and their session. We want to display the user's name, username and avatar alongside their tweets, but the auth.users table cannot be publicly accessible, as it contains sensitive information.

In this lesson, we create a new table called profiles and populate it with the data we want to display from the auth.users table. Additionally, we set up a PostgreSQL Function and Trigger to create a new profile for any user added to the auth.users table.

Lastly, we create an RLS policy for the profiles table to enable read access, and re-generate our TypeScript definitions file to contain our new table.

Code Snippets

Create profiles table

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

Enable Row Level Security

alter table public.profiles enable row level security;

Enable read access with RLS policy

create policy "anyone can select profiles" ON "public"."profiles"
as permissive for select
to public
using (true);

Create PostgreSQL Function to create profile

create function public.create_profile_for_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public.profiles (id, name, username, avatar_url)
  values (
    new.id,
    new.raw_user_meta_data->'name',
    new.raw_user_meta_data->'user_name',
    new.raw_user_meta_data->'avatar_url'
  );
  return new;
end;
$$;

Create PostgreSQL Trigger to create profile

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.create_profile_for_user();

Resources

Brandon Perfetti
Brandon Perfetti
~ a year ago

If anyone else is having trouble creating the on_auth_user_created Database Function due to the Supabase UI not recognizing when you select the auth schema and only shows the public tables when you click "Create a new function".

Doing the following in the sql Editor works great:

create trigger on_auth_user_created after insert on auth.users for each row execute procedure public. insert_profile_for_new_user();

Also, if you notice that you're profile name, username, and avatar_url are double quoted as strings, updating the insert_profile_for_new_user Database Function to the following plpgsql should fix that:

begin insert into public.profiles (id, name, username, avatar_url) values ( new.id, (new.raw_user_meta_data->>'name')::text, (new.raw_user_meta_data->>'user_name')::text, (new.raw_user_meta_data->>'avatar_url')::text ); return new; end;