Issue #946

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

{
  "avatar_url" : "https://pbs.twimg.com/profile_images/1603982230040334336/9NXYYH-B_normal.png",
  "email" : "onmyway133@gmail.com",
  "email_verified" : true,
  "full_name" : "Khoa 🔥",
  "iss" : "https://api.twitter.com/1.1/account/verify_credentials.json",
  "name" : "Khoa 🔥",
  "picture" : "https://pbs.twimg.com/profile_images/1603982230040334336/9NXYYH-B_normal.png",
  "preferred_username" : "onmyway133",
  "provider_id" : "56019754",
  "sub" : "56019754",
  "user_name" : "onmyway133"
}

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

Read more