Database error saving new user when using trigger w/ public.users table

Bug report

Describe the bug

I created a public.users table, with id (uuid) and email (varchar) fields, and set up the trigger as mentioned here. This results in being unable to create a user through “Authentication > Invite New User”, and through a web app using supabase.auth.signIn({ provider: "google" })

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

Create new project
Add public users table with id: uuid, and email: varchar
Execute the following SQL

CREATE OR REPLACE FUNCTION signup_copy_to_users_table()
RETURNS TRIGGER AS $$
  BEGIN
    INSERT INTO public.users (id, email)
    VALUES(new.id, new.email);
    RETURN NEW;
  END;
$$
LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS signup_copy on auth.users;
CREATE TRIGGER signup_copy
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE PROCEDURE signup_copy_to_users_table();

At this point, you will be unable to invite a user through the Authentication UI

Issue Analytics

State:
Created 3 years ago
Reactions:5
Comments:22 (4 by maintainers)

Top GitHub Comments

26reactions

kiwicopplecommented, Feb 4, 2021

Hi all - I just tried with a new project and didn’t have any errors here. Here were the steps:

Create a public.users table:

create table users (
  id uuid references auth.users not null primary key,
  email text
);

create a trigger:

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

Trigger the function on invite:

-- trigger the function every time a user is created
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

Invite a user via the UI

See the user with email in the table:

Let me know if I’m missing anything

6reactions

ethicnologycommented, May 14, 2021

Hey, i’ve had the same issue and this works for me, thanks. It seems that the SQL function and trigger only works if the users table is created in pure SQL not via supabase Dashboard. Should we update the documentation to avoid any other lost adventurers like us ?