Users and the auth schema

A user in Supabase Auth is a row in the auth.users table of your project's database. Auth owns that table and the rest of the auth schema. Your app reads users through the client library and, when it needs more fields, through a table of its own in public that references auth.users.

Each SupaHost project has its own database, so users never cross between projects. A user of app-one doesn't exist in app-two.

The tables Auth manages

Treat the auth schema as read-only from your own code. Change users through the Auth API (or the admin API) so that Auth's own bookkeeping stays consistent. The auth schema isn't exposed through the Data API either: the exposed schemas on SupaHost are fixed to public, storage, graphql_public and pgmq_public.

Two kinds of metadata

Every user carries two JSON objects. They look alike but have very different trust levels.

• user_metadata (raw_user_meta_data in the table) is set at sign-up with options.data and can be changed by the user at any time with supabase.auth.updateUser({ data }). Use it for display name, avatar, preferred units in a recipe app. Never use it to decide what someone may do.
• app_metadata (raw_app_meta_data) can only be changed with the service_role key. Auth writes provider and providers there. It's the right place for things like a plan or tenant id that policies check. See Roles, custom claims and RBAC.

Both objects are copied into the user's access token, so policies can read them with auth.jwt().

const { data, error } = await supabase.auth.signUp({
  email: '[email protected]',
  password: 'correct-horse-battery',
  options: { data: { display_name: 'Sam', units: 'metric' } },
})

Reading the current user

In the browser, getUser() asks Auth for the latest copy of the user:

const { data: { user } } = await supabase.auth.getUser()
console.log(user?.id, user?.email, user?.user_metadata.display_name)

On a server, check the session with getClaims() or getUser() rather than trusting what's in the cookie. Server-side auth explains the difference.

A profiles table

Most apps want public user fields that other users can see, joins from other tables, and their own policies. Put those in a table in public with the user id as its primary key:

create table public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  display_name text,
  avatar_path text,
  created_at timestamptz not null default now()
);

alter table public.profiles enable row level security;

create policy "Profiles are visible to signed-in users"
on public.profiles for select
to authenticated
using (true);

create policy "Users update their own profile"
on public.profiles for update
to authenticated
using ((select auth.uid()) = id)
with check ((select auth.uid()) = id);

on delete cascade removes the profile when the user is deleted.

Create the profile automatically

A trigger on auth.users can insert the profile row as soon as Auth creates the user. The function runs as its owner (security definer) because the role Auth uses can't write to your tables, and it pins search_path to empty so it can't be tricked into calling the wrong function:

create function public.create_profile_for_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profiles (id, display_name)
  values (new.id, new.raw_user_meta_data ->> 'display_name');
  return new;
end;
$$;

create trigger on_auth_user_created
after insert on auth.users
for each row execute function public.create_profile_for_new_user();

Keep this function small and make sure it can't fail. If it raises an error, the sign-up fails with "Database error saving new user", and the user sees a generic 500. That KB article covers the usual causes, such as a not null column the trigger doesn't fill.

Looking at users

You can browse, search and delete users in Studio's Authentication page. On SupaHost, open Studio from Dashboard → your project → Database tab → Open Studio (owner only). See Using Supabase Studio.

You can also query the table in Studio's SQL editor. For example, sign-ups per day for the last week:

select date_trunc('day', created_at) as day, count(*)
from auth.users
where created_at > now() - interval '7 days'
group by 1
order by 1;

Referencing users from your tables

Point foreign keys at auth.users (id) or at your profiles table, and decide what happens when a user is deleted. on delete cascade suits rows the user owns outright, such as saved recipes. on delete set null suits shared records, such as an invoice that must survive after the person who created it leaves.

create table public.recipes (
  id bigint generated always as identity primary key,
  user_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  title text not null
);

create index recipes_user_id_idx on public.recipes (user_id);

The default auth.uid() fills in the signed-in user's id on insert, and the index keeps RLS checks on user_id fast. See Row Level Security.