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

1. Create a public.users table:

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

1. 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;

1. 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();

1. Invite a user via the UI

1. See the user with email in the table:

Let me know if I’m missing anything

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 ?

I had this problem, but it was solved by not using a capital starting letter in my table name. I used Profile as my table name, but after switching to profile it suddenly worked.

Yeah I always try to remember to @@map(lower_case_name)

And if you’re using Prisma, then you can omit creating a reference between the profile and the auth user. This is a drawback to using Prisma with Supabase 😦 We can’t establish a FK relation between (eg) public.user_profiles and auth.users

@mike-wax or if you use phone auth:

DROP FUNCTION IF EXISTS handle_new_user cascade;
-- inserts a row into public users
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.users (id, email, phone)
  values (new.id, new.email, new.phone)
  ON CONFLICT (id) DO UPDATE SET (email, phone) = (new.email, new.phone);
  return new;
end;
$$ language plpgsql security definer;

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

If you want to do it on insert or update:

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

create or replace function public.handle_new_user() 
returns trigger as $$
begin
  INSERT INTO public.users (id, email)
  values (new.id, new.email)
  ON CONFLICT (id) 
  DO 
    UPDATE SET email = new.email;
  return new;
end;
$$ language plpgsql security definer;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after update or insert on auth.users
  for each row execute procedure public.handle_new_user()

I’m using Prisma so I can’t create a public.users table via SQL. This is still a bug and this issue shouldn’t be closed.

Update: I triggered the function by adding a row to auth.users myself. After I did that and this function triggered successfully this bug was gone, I can now add a user again in the Supabase console.

@AminHero did you see this comment? Did you create the table thru Supabase?

Unfortunately, I’m still having this issue. I dropped all my functions and triggers and recreated @mike-wax solution. Afterwards I received the data error while saving new user again…

It seems @RilDev’s recommendation works!

Leaving this here in case it helps anyone else:

CREATE OR REPLACE FUNCTION auth.create_profile_for_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public."Profile" (id, email)
  VALUES (NEW.id, NEW.email);
  RETURN NEW;
END;
$$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
;

CREATE OR REPLACE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
    EXECUTE FUNCTION auth.create_profile_for_user();

CREATE OR REPLACE FUNCTION auth.update_profile_for_user()
RETURNS trigger AS $$
BEGIN
  UPDATE public."Profile"
  SET email = new.email
  WHERE id = new.id::text;
  RETURN NEW;
END;
$$
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
;

CREATE OR REPLACE TRIGGER on_auth_user_updated
  AFTER UPDATE ON auth.users
  FOR EACH ROW
    EXECUTE FUNCTION auth.update_profile_for_user();

(if you use a foreign key, you don’t need the ::text cast, that’s only to work around my Prisma’s setup lack of multi-schema)

@zephaniahong I use Datagrip to inspect and mutate databases. The auth table is not in the public schema but you can load the Auth schema and manipulate the Auth table like any other table.

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

1. Create a public.users table:

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

2. 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;

3. 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();

4. Invite a user via the UI

5. See the user with email in the table:

Let me know if I’m missing anything

it worked for me too, after making table from sql editor error was not there

I had this problem, but it was solved by not using a capital starting letter in my table name. I used Profile as my table name, but after switching to profile it suddenly worked.

And if you’re using Prisma, then you can omit creating a reference between the profile and the auth user.

And to “backfill” you can just “touch” all the records with a (hopefully) meaningless update

update auth.users
set role = 'authenticated'

Hey, sorry for the delay here - it seems as though I can’t repro what I was experiencing before, and your post 2 comments up works perfectly for me. Sorry if this was a superfluous issue and I was just doing something incorrectly

That’s great! I’ll close this for now but feel free to reopen if there is anything I missed 👍