Fix: Database error saving new user (Supabase)

AuthApiError: Database error saving new user is a 500 from Supabase Auth caused by a trigger on auth.users throwing. The real error is in your Postgres logs, not the API response — and the fix is almost always making the trigger function SECURITY DEFINER with a pinned search_path, not disabling RLS on the table it writes to.

The error text tells you nothing on purpose. Auth caught an exception while inserting the user, rolled the whole thing back, and returned a generic 500 rather than leaking your database internals to an unauthenticated caller. That's correct behaviour, and it's why this error has been generating threads for four years running — including discussion #36440 and #32852, where the consistent answer is: it's your trigger.

Find the actual error first

Do not guess. Supabase logged the real Postgres error:

Dashboard → Logs → Postgres Logs, filtered to the moment of the failed signup. You'll see something like permission denied for table profiles, or new row violates row-level security policy for table "profiles", or null value in column "username" violates not-null constraint. Every one of those has a different fix, and picking the wrong one is how people end up disabling security.

If you're running locally, supabase db logs gives you the same thing without the dashboard round-trip.

Why does the trigger fail?

Because of who the trigger runs as. During signup, the insert into auth.users is performed by the internal supabase_auth_admin role. A trigger function declared SECURITY INVOKER — the Postgres default — inherits that role. And supabase_auth_admin holds only the privileges the Auth service needs, which do not extend to your public schema.

Supabase's own troubleshooting guide puts it plainly: when a trigger operated by supabase_auth_admin interacts outside the auth schema, it causes a permission error — and a SECURITY DEFINER function created by the postgres role is the documented way around it.

So the classic function:

create function public.handle_new_user()
returns trigger language plpgsql as $$
begin
  insert into public.profiles (id, email)
  values (new.id, new.email);
  return new;
end;
$$;

...runs as supabase_auth_admin, hits public.profiles, and dies with permission denied for table profiles. Auth rolls back, and the client sees Database error saving new user.

The four causes, in the order you should check them:

1. Missing privileges — the function isn't SECURITY DEFINER, so it runs as a role with no access to public. Most common by a wide margin.
2. RLS blocking the insert — the function is running as a role with grants, but that role isn't the table owner, so RLS still applies and there's no INSERT policy that matches. You'd see new row violates row-level security policy in the logs; the general form of that error is covered here.
3. A constraint the trigger doesn't satisfy — a NOT NULL, CHECK, or UNIQUE column the function never populates. This is what bit the reporter in discussion #36440, where the underlying log line was new row for relation "user_profiles" violates check constraint "check_username_format". Email/OAuth signups often lack the metadata you assumed would be there.
4. An unresolvable search_path — the function says insert into profiles (unqualified) and the executing role's search_path doesn't include public, so it fails with relation "profiles" does not exist.

The fix that doesn't break your security model

Make the function SECURITY DEFINER, own it with a role that can write the table, pin search_path, and fully qualify every identifier:

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''          -- pin it; force fully-qualified names
as $$
begin
  insert into public.profiles (id, email, full_name)
  values (
    new.id,
    new.email,
    coalesce(new.raw_user_meta_data ->> 'full_name', '')  -- never assume it exists
  )
  on conflict (id) do nothing;  -- idempotent: re-runs and retries stay safe
  return new;
end;
$$;

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

Four things are doing work here:

• security definer runs the body as the function owner (postgres in a standard Supabase project), which has the privileges and bypasses RLS on public.profiles. That's the intended use of the feature: a narrow, audited escalation for one specific operation.
• set search_path = '' is not optional. A SECURITY DEFINER function with a mutable search_path is a real privilege-escalation vector — anyone who can create objects in a schema that lands earlier on the path can hijack an unqualified call and run code as the owner. Supabase's own linter flags this as function_search_path_mutable, and the mechanics are worth understanding. Pinning it to '' forces you to write public.profiles and auth.users explicitly, which is exactly what you want.
• coalesce(...) on metadata — raw_user_meta_data is empty for a plain email/password signup and differently shaped per OAuth provider. Assuming a key exists is cause #3.
• on conflict do nothing makes the trigger idempotent, so a retried signup doesn't turn a transient failure into a permanent one.

Keep RLS enabled on profiles. The SECURITY DEFINER function bypasses it for this one insert; every other query still goes through your policies:

alter table public.profiles enable row level security;

create policy "users read own profile" on public.profiles
  for select using (auth.uid() = id);

create policy "users update own profile" on public.profiles
  for update using (auth.uid() = id) with check (auth.uid() = id);

The fix you'll find in forum threads — don't ship it

Search this error and you will, sooner or later, hit some version of this:

ALTER TABLE public.profiles DISABLE ROW LEVEL SECURITY;

GRANT INSERT, SELECT ON public.profiles TO anon, authenticated;

It works. Signup starts succeeding immediately, which is exactly why it spreads. What it actually does is make profiles — names, emails, whatever else you keep there — readable by anyone holding the public anon key, which ships in your browser bundle by design. You've swapped a signup bug for a full table disclosure, and because nothing looks broken afterwards, nobody revisits it. This is precisely how RLS ends up disabled in production.

The tell that you're about to do this: you're editing the table's security to fix the function's permissions. Fix the function.

Quick self-check

-- Any SECURITY DEFINER function without a pinned search_path?
select p.proname, p.prosecdef, p.proconfig
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.prosecdef
  and (p.proconfig is null or not p.proconfig::text like '%search_path%');

-- Any table with RLS turned off?
select tablename from pg_tables
where schemaname = 'public' and not rowsecurity;

Then test the whole path end to end: sign up a throwaway address, confirm the row lands in profiles, and confirm a second user can't read it. GuardLayer catches the migration-level half of this — disabled RLS, unscoped policies, tables granted to anon without policies — on every push, before the "temporary" fix becomes permanent.

FAQ

Where do I see the real error behind "Database error saving new user"? Dashboard → Logs → Postgres Logs, at the timestamp of the failed signup. The API deliberately returns a generic message; the specific Postgres error (permission denied, RLS violation, constraint violation) is only in the logs.

Why does my trigger get "permission denied" when it works fine in the SQL editor? The SQL editor runs as postgres. The signup trigger runs as supabase_auth_admin, which has no privileges on public. Declare the function SECURITY DEFINER so it executes as its owner instead.

Do I have to disable RLS on the profiles table? No, and you shouldn't. A SECURITY DEFINER function owned by postgres bypasses RLS for its own insert while leaving your policies enforcing everything else.

Signup works for email but fails for Google/GitHub. Why? The trigger is probably reading a raw_user_meta_data key that provider doesn't send, hitting a NOT NULL or CHECK constraint. Wrap metadata reads in coalesce and make the columns nullable.

Can I just drop the trigger and create the profile from my app? You can, but then the profile row depends on the client completing a second call. If you go that route, do it in a server action or route handler after signup — not from the browser — and keep RLS policies scoped to auth.uid().