2

For my website (backend support by Supabase), I have the built-in auth.users table, and a public.profiles table that includes text fields for email, username, and school. I have created a trigger and function that would, upon insertion of a new row in auth.users, will insert a row into public.profiles using the email as email, full_name as the email's username component (myName for myName@email.com), and perform a query for a college name from a public.college_emails table to determine the school (if a person signed up with email with domain "@uci.edu", it will insert "University of California Irvine" in the profiles school field. If it cannot match the domain to a school, it will insert "other".

Example:

Say a user with the email "rickAstley@ucla.edu" registered at my site. Once this row is added into auth.users, the trigger should execute a function that will add a new row to public.profiles with the following fields: email: rickAstley@ucla.edu, full_name: rickAstley, school: University of California Los Angeles.

Here is the current Postgres code for the function, which is executed by a trigger on insert on auth.users:

create or replace function public.handle_new_user() 
returns trigger as $$
declare
  coll text ;
begin
  SELECT college
    INTO coll
    FROM college_emails 
  WHERE tag = SPLIT_PART(new.email, '@', 2);

  IF NOT FOUND
  THEN coll = 'Other' ;
  END IF ;

  INSERT INTO public.profiles (id, email, full_name, school, created_at)
  values (new.id, new.email, SPLIT_PART(new.email, '@', 1), coll, current_timestamp) ;
  return new;
end;
    
create trigger on_new_user_created
after insert on auth.users
for each row execute procedure public.handle_new_user();

When I directly perform an insertion into auth.users using a made-up uuid and school email such as:

insert into auth.users (id, email) values ('d1d19367-3d0b-d497-9191-18a9d8e37850','myLongName@ucr.edu')

The trigger and function execute beautifully as intended. However, if I try to add a user to auth.users the standard way, by going to the website as a guest and signing up, I get the error Database Error Saving New User. Here is the image, though I doubt it'll help much:

Error

I don't understand why this code works fine when directly inserting but not when inserting using Supabase's Auth component. This is the code that creates the signup feature:

<Auth supabaseClient = {supabase} socialLayout = "horizontal" socialButtonSize = "xlarge" />

Another thing to note is when I use a function that does not perform a query in the function, such as inserting the first part of the email domain before the first period instead of looking it up in public.college_emails, it works fine (for example, registering with domain "@uci.edu" will insert "uci" into the school field rather than query the full name).

This is that working function:

create or replace function public.handle_new_user() 
returns trigger as $$
begin
  insert into public.profiles (id, email, full_name, school, created_at)
  values (new.id, new.email, SPLIT_PART(new.email, '@', 1), SPLIT_PART(new.email, '@', 2)
,current_timestamp);
  return new;
end;
$$ language plpgsql security definer;

I am eager for any leads on this issue, thanks so much in advance.

Ryan Millares
  • 525
  • 4
  • 16

1 Answers1

1

For those with the same problem arriving here via Google (as I did), this is typically caused by a security issue.

By default, PostgreSQL functions are defined as SECURITY INVOKER.

This means that the function will execute as a different user depending on whether it is invoked by the client API or within the Supabase SQL console.

This is why the function works from the console, but not the website.

Another thing to note is when I use a function that does not perform a query in the function, such as inserting the first part of the email domain before the first period instead of looking it up in public.college_emails, it works fine

The second example shown in the question works because of the line:

security definer

This executes the function as the user that defined it, and so it succeeds no matter where it is executed from.

Changing the security to definer has security implications. Note the following warning from the Supabase documentation:

It is best practice to use security invoker (which is also the default). If you ever use security definer, you must set the search_path. This limits the potential damage if you allow access to schemas which the user executing the function should not have.

See https://supabase.com/docs/guides/database/functions for further detail (also Abusing SECURITY DEFINER functions).

Also see related discussions in Github issues:

hendalst
  • 2,957
  • 1
  • 24
  • 25