PostgreSQL new user profile function

One of the most asked question in r/supabase is how to populate the profile table when a new user is created in Supabase. We're also creating a JWT token each time we insert a new row in the profiles table.

First let's create the public.profiles table as follows:

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

alter table public.profiles enable row level security;

We can then make use of the RLS policies listed in Supabase's documentation page.

create policy "Public profiles are viewable by everyone."
  on profiles for select
  using ( true );

create policy "Users can insert their own profile."
  on profiles for insert
  with check ( auth.uid() = id );

create policy "Users can update own profile."
  on profiles for update
  using ( auth.uid() = id );

Now the fun part!

We will store a JWT token in the token column and for this example we're using the pgjwt extension which is already enabled in Supabase. Our payload will be the id value, a private string used to sign the JWT, and the encryption method.

Since the payload needs to be in JSON format we will have to make use of the json_build_object() wrapped with the json_agg() function.

select json_agg(
    json_build_object(
        'id', NEW.id
    )
)

We then use the extensions.sign() function from the pgjwt extension to create the token:

select extensions.sign(
  (
    select json_agg(
      json_build_object(
        'id', NEW.id
      )
    ),
    'secret',
    'HS256'
  )
)

We now have to complete the entire postgresql function and come up with this:

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $handle$
begin
  INSERT INTO public.profiles (id, token) VALUES (
    NEW.id,
    (select extensions.sign((select json_agg(json_build_object('id', NEW.id))), 'secret', 'HS256'))
  );
  return null;
end;
$handle$;

Let's finalize it by creating the AFTER TRIGGER here:

-- trigger the function every time a user is created
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

To verify the created token, we can use the online JWT decoder in jwt.io.