Automatically Generate a Profile for Every User with PostgreSQL Function Triggers
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
Man: Each of our tweets have a user ID column who is the author of the tweet. This ID is not going to be very helpful when we come to styling our application.
If we have a look at Twitter, we have an avatar for the user, we have their name, we have their username, and so rather than this big string of characters, we want to look up the name, username, and avatar URL for the author of this specific tweet.
Back over in the Supabase dashboard, we can go to the table editor, and if we change this schema to auth and then look at our users table, we can see we have a whole bunch of data about our user. Let's say we wanted to look up this email column.
Back over in our application, where we're making a request for all tweets, we're getting back all of the columns for our tweets table, and this is the same as passing the string star. We can also grab columns from related tables, so in this case if we had auth.users, we could grab the email column.
If we go back to our application, we're seeing the value null instead of our list of tweets, so something's not working. The problem here is that while we can look up related tables, we can't look up tables that exist in a different schema.
Even if we could, this table is protected by RLS, as it contains sensitive information that we don't want to be publicly available. Instead, let's create a new table in the public schema called Profiles.
We're going to leave row-level security enabled, and then for our columns, we want to set up a foreign key relationship with this ID. To our auth.users table, we want to reference the ID column, and again, if that user is deleted, we want to cascade the deletes to this profiles table. Let's click save to set up that relationship.
We're going to copy across the data from the auth.users table that we want to display in our application. We don't really care about this created at column, but we want a new column for name, and the type for this one is going to be text. We're going to click this cog to say we don't ever want this value to be null, and then we want another column for username.
Again, this one is going to be text, and we don't want it to ever be null. Lastly, we want a column for avatar_url. This will also be text and not null. Let's click save to create our table, and now we want there to be a profile for every single one of our users, and so anytime a new user is added to the auth.users table, we want to create an associated profile with the same ID.
As for our name and username and avatar URL, we want to find this raw user metadata column, which is a big JSON blob, and if we extend this out, we'll see we have a name field. We also have a user_name, and an avatar_url. This is because we implemented authentication using OAuth with GitHub, so we get access to some extra information about our user that's publicly available on GitHub.
To copy this data across to our profiles table when a new user is added, we need to create a Postgres trigger function. We can do this by going to database and then Functions. We want to create a new function.
The name of our function is going to be insert profile for new user, and this one can have spaces that's why we're using these underscores. We want this to be on the public schema. The return type is going to be trigger, and then in the definition section, we need a begin and an end.
Then we want to insert into the public.profiles table, and the columns we're going to set are going to be ID, name, username, and avatar_url. We then need to provide values for each of those columns.
Because this function is going to be called by a trigger, we get access to a special variable called new, which, in this case, will represent the full row being inserted into the auth.users table. If we want to set our ID value, we can access that with new.id.
Then for our name, we can say new, and then this was in the raw_user_meta _data column, and then the way we access a particular property in that big JSON blob is with the - and then >> and then the key we want to access. In this case, name.
We then want the username, so we can copy most of this, and then rather than the name field, this was user_name, and then we also want the avatar_url. We need a semicolon for our insert statement, and then we need to return that new variable.
Under advanced settings, we want to scroll to the bottom and change our type of security from SECURITY INVOKER to SECURITY DEFINER. SECURITY DEFINER is used for system actions like moving data around in our database, and then SECURITY INVOKER is used when we want to call a Postgres function on behalf of the user of our application.
Let's click confirm to create that Postgres function. Now we have a function to create a new profile, but we need to set up a trigger to call this function when a new user is added to the auth.users table. Let's create a new trigger.
The name of this one is going to be on_auth.users_insert. The table we want to listen to is auth.users. The event we care about is insert, and then we want this trigger to fire after the event.
After we've created that new user, we want it to trigger for every row, and the function we want to call is our insert profile for new user function. Let's click confirm to create that new trigger. Now we can check this is working by logging our user out of our application and then going over to authentication.
Then under users, we see our user, and we just want to click these three dots to delete them and then click confirm. Because we set up cascade deletes, our tweets table will now be empty. Then let's create a new user by clicking the login button.
Then back over in Supabase, we have a look at our profiles table. We'll see our new user with their name, username, and avatar URL pulled from the auth.users table. Let's update the foreign key relationship from our tweets table so our user ID column points to our profiles table.
We can edit the relation here and then select the schema public. The table is profiles and the column is ID. Again, we want to set up an action to cascade the deletes. If our profile is deleted, that user's tweets are deleted too.
Let's click save and then save again to apply that change. Now, we can insert a new tweet with the title hello, and then select a record from our profiles table, which is our signed in user, and then click save to insert that new tweet.
Now our application is still showing the value null, so we still need to fix up our code. Now, rather than the auth.users table, we want the profiles table. Then rather than email, we can just grab all of the columns.
Then back over in our application, we're seeing the correct data for our tweet, but our profiles are null. This is because we've created these new profiles table and we've enabled RLS, but we haven't written a policy. Let's create a new policy.
We're going to create this from scratch. We want to enable read access for profiles. We want to enable the select action for all roles with the condition true. Let's click review and then save policy.
Then back over in our application, when we refresh, we see the profile data for the user that created that tweet. Now back over in our application, if we have a look at this tweets variable, doesn't really know much about our profiles and that's because we've changed our database schema.
We just need to run our Supabase CLI command to generate our TypeScript types. Now, we're back to full type safety across our application.
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;