For security purposes, the auth schema is not exposed on the auto-generated API. We can make a profiles table in public namespace and mirror data from auth.users when user signs up.
I need id, username and raw_user_metadata so I will mirror these columns. For easy, you can leverage Supabase SQL Editor -> Quickstarts -> User Management Starter script
I don't need Row Level Security as I manage database from server.
-- Create a table for public profiles
create table profiles (
id uuid references auth.users on delete cascade not null primary key,
username text,
raw_app_meta_data jsonb
);
Let's create a trigger function to add new row to profiles after row insert in auth.users
create function public.handle_new_user()
returns trigger as $$
begin
insert into public.profiles (id, username, raw_user_meta_data)
values (new.id, new.raw_user_meta_data->>'user_name', new.raw_user_meta_data);
return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_created
after insert on auth.users
for each row execute procedure public.handle_new_user();
The raw_user_meta_data is of type jsonb and it should look like below
If you get 403, you need to check in Project settings -> Auth -> User Signups to ensure new users can sign up
If you don't see new rows in profiles, make sure you sign up with new user or delete old user records in auth.users, since the trigger happens on insert only
For security purposes, the auth schema is not exposed on the auto-generated API. We can make a
profiles
table in public namespace and mirror data fromauth.users
when user signs up.I need
id
,username
andraw_user_metadata
so I will mirror these columns. For easy, you can leverageSupabase SQL Editor -> Quickstarts -> User Management Starter
scriptI don't need Row Level Security as I manage database from server.
Let's create a trigger function to add new row to
profiles
after row insert inauth.users
The
raw_user_meta_data
is of typejsonb
and it should look like belowIf you get 403, you need to check in
Project settings -> Auth -> User Signups
to ensure new users can sign up If you don't see new rows in profiles, make sure you sign up with new user or delete old user records inauth.users
, since the trigger happens on insert onlyRead more