S
Supabase•2y ago
phil

Trigger on auth.users causes permission denied for schema public

When a user signs up, i have a trigger that runs to create an entry in another table called public.profiles. it runs this function: 
begin
  insert into public.profiles (id, email, first_name, last_name)
  values (new.id, COALESCE(new.raw_user_meta_data->>'email', new.email), split_part(new.raw_user_meta_data ->>'full_name', ' ', 1), substring(new.raw_user_meta_data ->>'full_name', (length(split_part(new.raw_user_meta_data ->>'full_name', ' ', 1)))+2,(length(new.raw_user_meta_data ->>'full_name')) - (length(split_part(new.raw_user_meta_data ->>'full_name', ' ', 1)))+1));

  insert into public.settings (id)
  values (new.id);
  
  return new;
end;
begin
  insert into public.profiles (id, email, first_name, last_name)
  values (new.id, COALESCE(new.raw_user_meta_data->>'email', new.email), split_part(new.raw_user_meta_data ->>'full_name', ' ', 1), substring(new.raw_user_meta_data ->>'full_name', (length(split_part(new.raw_user_meta_data ->>'full_name', ' ', 1)))+2,(length(new.raw_user_meta_data ->>'full_name')) - (length(split_part(new.raw_user_meta_data ->>'full_name', ' ', 1)))+1));

  insert into public.settings (id)
  values (new.id);
  
  return new;
end;
Now when I test this, i get the following error in the logs: 
"component":"api","error":"failed to close prepared statement: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: permission denied for schema public (SQLSTATE 42501)","level":"error","method":"POST","msg":"500: Database error creating new 
"component":"api","error":"failed to close prepared statement: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: permission denied for schema public (SQLSTATE 42501)","level":"error","method":"POST","msg":"500: Database error creating new
When I remove the trigger then the auth.user is created but of course the public.profiles/settings is not. Anyone see anything wrong here?
No description
No description
2 Replies
garyaustin
garyaustin•2y ago
The most common error is the function needs to security definer type. Check the Postgres logs for the real error.
The next most common thing is not getting column types correct.
phil
philOP•2y ago
So in the end i just deleted the trigger and function, and recreated it all from scratch it now it works 🤷‍♂️

Did you find this page helpful?