Mahdi BEN RHOUMA

Posted on Aug 21 Originally published at iloveblogs.blog

Supabase "Database Error Saving New User" Trigger Fix

#supabase #auth #trigger #postgres

The error

You add a trigger to copy new auth users into a public.users (or
public.profiles) table. The first signup after that throws a vague error on
the client:

Database error saving new user

No user row in auth.users. No row in public.users. The signup is dead. The
message is intentionally generic — Supabase hides the underlying Postgres
error from the client — but the cause is almost always the same: your
trigger threw, and the transaction rolled back the auth insert with it.

This is the exact scenario in supabase/supabase#563:
an AFTER INSERT trigger on auth.users copies the row into public.users,
the copy fails for a constraint or permission reason, and the exception kills
the signup.

The reproduction

The trigger from that issue, lightly cleaned up:

create or replace function public.signup_copy_to_users()
returns trigger
language plpgsql
security definer
as $$
begin
  insert into public.users (id, email)
  values (new.id, new.email);
  return new;
end;
$$;

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

This looks fine. It is not. Three things go wrong with it in production:

AFTER INSERT runs in the same transaction. Any exception in the body rolls back the auth.users insert. Signup dies.
SECURITY DEFINER with no search_path. This is the CVE-2018-1058 search-path hijacking pattern. A hostile user with CREATE on public could shadow public.users with a malicious function. Postgres 15 made CREATE on public non-default (see Supabase "permission denied for schema public" fix), but you still pin search_path.
No error handling. A single bad row — a NULL on a NOT NULL column, a duplicate email, an RLS denial — breaks every signup until you fix it.

The fix

Two changes: pin the search path, and make the profile copy non-fatal.

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public          -- ✅ pin search_path, block hijacking
as $$
begin
  insert into public.users (id, email, created_at)
  values (new.id, new.email, now());

  return new;

exception
  when others then
    -- ✅ log the failure but do NOT roll back auth.users
    raise log 'handle_new_user failed for %: %', new.id, sqlerrm;
    return new;
end;
$$;

-- Replace the old trigger
drop trigger if exists signup_copy on auth.users;
drop trigger if exists on_auth_user_created on auth.users;

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

The two edits that matter:

set search_path = public on a SECURITY DEFINER function is now required best practice. Without it, the function resolves unqualified names against the caller's search_path, which an attacker can manipulate.
exception when others then return new decouples the profile copy from the auth insert. A failed copy logs the error and lets the signup proceed. The user can sign in; you fix the orphaned profile later from the log.

You can route the log to Supabase's log explorer or a public.signup_errors
table if you want to drive a backfill job from it.

Why SECURITY DEFINER at all

Without SECURITY DEFINER, the trigger runs as auth.users's owner on the
auth schema but inserts into public.users as whoever the caller is. If
public.users has RLS, the anon role may have no INSERT policy, and the
copy fails — rolling back the signup. SECURITY DEFINER runs the function as
its owner (typically the migrations role, which bypasses RLS), so the insert
goes through.

That is the same reason the getSession() security warning exists: relying on
the wrong privilege boundary. The rule for auth triggers:

SECURITY DEFINER so RLS on public.users cannot block the copy.
SET search_path = public so the definer cannot be hijacked.
exception when others so a copy failure cannot kill a signup.

For the matching server-side rule on reads, see
stop the Supabase getSession() security warning.

The RLS angle

Debugging a policy instead of a trigger? The free Supabase RLS Policy Debugger matches the common failure signatures (WITH CHECK, recursion, GRANTs) and returns the corrected SQL.

If public.users has RLS, the handle_new_user insert runs as the function
owner, not the new user. That is usually what you want — the new user has no
row yet, so a policy like using (id = auth.uid()) would block the very insert
that creates their row. Keep SECURITY DEFINER so the trigger can seed the
row, then let RLS govern subsequent access.

A common misconfiguration: a WITH CHECK (id = auth.uid()) policy on
public.users that works for updates but makes the initial insert depend on
auth.uid() resolving before the user exists. The trigger runs after the
auth.users insert, so auth.uid() is valid — but if you accidentally run
the insert as anon instead of via SECURITY DEFINER, it fails. The
Supabase RLS debugging guide covers
the auth.uid() resolution order in depth.

Check the target table first

Before blaming the trigger, confirm the target table is insert-able in
isolation:

-- As the migrations role, not anon:
insert into public.users (id, email)
values ('00000000-0000-0000-0000-000000000000', 'test@example.com');

If that fails, the trigger is not your problem — the table is. Common causes:

A NOT NULL column without a default that the trigger does not populate.
A unique constraint on a column you are not setting.
A foreign key to a row that does not exist.
A column type mismatch (e.g. text vs uuid on id).

Fix the table, then the trigger stops throwing.

Production recommendations

Deploy the trigger, then test signup immediately with a throwaway email. A broken trigger breaks every signup, so verify before you walk away.
Do not run DDL that recreates the trigger during a signup burst. The drop trigger / create trigger pair has a tiny window where inserts have no trigger; that is usually fine, but it does not need to be in your hot path.
Log exceptions to a table you monitor, not just raise log. The log explorer is easy to miss; a signup_errors table with a Slack nudge is not.
Pin search_path on every SECURITY DEFINER function, not just this one. It is the cheapest security win in Supabase. The Supabase security best practices guide treats this as a baseline.

TL;DR

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.users (id, email, created_at)
  values (new.id, new.email, now());
  return new;
exception
  when others then
    raise log 'handle_new_user %: %', new.id, sqlerrm;
    return new;   -- never let the copy roll back auth.users
end;
$$;

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

Three things: pin the search path, use SECURITY DEFINER so RLS cannot block
the seed insert, and swallow exceptions so a copy failure never breaks a signup.

Originally published at https://www.iloveblogs.blog

DEV Community