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.
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();
Enjoying the course? Follow Jon Meyers on Twitter and subscribe to the YouTube channel.