Sign-up database trigger to insert into public users table

[skoshy]

Hi, I'm trying to set up a trigger to automatically copy new users from auth to my public users table. I've drafted up the below, but I get a database error every time I try to signup a new user ("Database error saving new user"). Any ideas on what could be the issue?

CREATE OR REPLACE FUNCTION auth.signup_copy_to_users_table()
RETURNS TRIGGER LANGUAGE plpgsql AS $function$
  BEGIN
    INSERT INTO public.users(id)
    VALUES(NEW.id);
  
    RETURN NEW;
  END;
$function$;

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

In general it'd be useful to be able to keep the auth/public users tables in sync with the id and email fields at least.

[stfnsr]

Same issue here.
Interestingly, there's no problem if i e.g. update the auth.users table via the trigger function, but everything in the public schema is off-limits somehow.

[kiwicopple]

I suspect it's permissions - the trigger is probably getting called by the anon user. You can try to add security definer to the function, which will use the same permissions as the user who created the function.

Here is a snippet that should work:

/**
* This trigger automatically creates a user entry when a new user signs up via Supabase Auth.
*/ 
create function public.handle_new_user() 
returns trigger as $$
begin
  insert into public.users (id)
  values (new.id);
  return new;
end;
$$ language plpgsql security definer;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

[teamclouday]

Thank you it works for me too! I had a permission denied error from function that's inserting a RLS enabled public table with role supabase_auth_admin. Adding security definer solves it perfectly. I guess this role doesn't have sufficient permission.

[walifile]

I have tried this solution but I'm still having an error

{"code":500,"msg":"Database error saving new user","error_id":"9b354033-3bb8-4c5d-af3c-82f67695373c"}

I am also getting this, is there a link to this issue and its resolution?

Anyone still getting this, make sure you don't have non-nullable fields in the table. Spent an hour trying to figure out why my use case wasn't working, and it was because of that.

I really appreciate it! I was getting the same error and couldn't fix it. This comment actually works. Thank you!

[CamiloTello002]

Thank you!

[boiwantlearncode]

Thanks, still works in 2025

[tfrank11]

I hope both sides of your pillow are cold. Tysm.

[stfnsr]

Wow, thanks @kiwicopple - this works 👍

Would this be a useful thing to include in the docs?
E.g., in the area where creation of a public.user table is advised?

[inian]

For anyone coming along, remember to turn on RLS and to disable realtime on your public.users table. If not, anyone with an anonkey will be able to access it.

[user72356]

For anyone coming along, remember to turn on RLS and to disable realtime on your public.users table. If not, anyone with an anonkey will be able to access it.

Hey I'm coming in late, but what do you mean by that? I don't use RLS in my account and I don't have realtime enabled. Am I safe? Can you explain a little better/more details?

[GaryAustin1]

@user72356 You need RLS enabled and policies set or anyone can access (and even change) your data with the anon key which is exposed on browsers.
https://supabase.com/docs/guides/auth/row-level-security

[user72356]

OK so just to confirm, if I enable realtime publications for a table, and I disable RLS for that table, all clients connected with the anon key will receive updates.

However, if I disable realtime publications and disable RLS for a table, the anon key can't read that table in any way?

Guess I'm in that situation: #4547

I'm surprised the DB is open to the public by default, quite a shocker to me. I was not planning on using RLS because my API is doing the heavy lifting with regular SQL queries, and I only need the supabase client for auth and subscriptions on one small table.

[GaryAustin1]

They are two different things. Ignoring Realtime, if you have RLS disabled, anon key can read all your tables thru the REST API.
You have to Enable RLS and grant policies for authenticated users, or whatever control you want.

[TStone45]

I am unable to work out what to do in terms of overcoming the fact that I am successfully adding email first name last name JWT and password into the auth.users table but try as I might syntax errors prevent me from finishing a function, I don't even know how to set up a function that observes changes from the auth table. I need more than help I need Jesus to take the wheel.

[activenode]

I'm not jesus but what you need is a function that also listens on the updates,not just the insert. And then determine for yourself if it's done.

I do this by checking if the entries in metadata are not empty like tldr:

DECLARE
has_proper_metadata boolean;

BEGIN
    has_proper_metadata = EXISTS(SELECT 1 FROM auth.users a WHERE 
      (raw_app_meta_data::jsonb ->> 'foobar1') IS NOT NULL
      and
      (raw_app_meta_data::jsonb ->> 'foobar2') IS NOT NULL
    AND a.email = NEW.email);

  IF has_proper_metadata THEN
    -- do your stuff
  ELSE
    RETURN NEW; -- continue 
  END IF;
END;

Cheers activeno.de

[TStone45]

You may not be Jesus but you have forgiven me my syn…taxes and have resurrected my journey to the promised land. 🙌🏾

[activenode]

Well then, spread the word (and my YouTube channel) if I may :D

[TStone45]

Hey Boss another question:

Raw meta data apparently isnt recognising the following public trigger:

This is the function as its written

And heres the policies for that public.users table.

And finally this is the sql editor query that created the public handler

Why is it still not creating a record upon the user being added to auth.users
'??

[GaryAustin1]

Also you should pull email from new.email. If you signup with email the email will not be in the metadata. That only comes from OAuth signups.

[TStone45]

Thank you Gary and David really appreciate your support,

I will try to reference and pull in the email from the auth table (or do a new.email) and do away with that particular column in the public table,

the logs are below, I've actual been speaking to brianglass a SB guy about the triggers also not working correctly he advised me to turn on debugging, I have,

Logs: of most recently failure.

{"component":"api","error":"failed to close prepared statement: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: column "firstname" of relation "users" does not exist (SQLSTATE 42703)","level":"error","method":"POST","msg":"500: Database error saving new user","path":"/signup","referer":"http://localhost:3000","remote_addr":"111.220.138.33","time":"2024-05-10T00:19:42Z","timestamp":"2024-05-10T00:19:42Z"}
Timestamp
2024-05-10T00:19:42.000Z

Metadata
[
{
"message": null,
"timestamp": "2024-05-10T00:19:42Z",
"__MONOTONIC_TIMESTAMP": null,
"CODE_FUNC": null,
"instance_id": null,
"status": null,
"_CMDLINE": null,
"method": "POST",
"_SYSTEMD_CGROUP": null,
"CODE_FILE": null,
"EXECUTABLE": null,
"_EXE": null,
"UNIT": null,
"level": "error",
"_COMM": null,
"duration": null,
"issuer": null,
"_LINE_BREAK": null,
"_SOURCE_REALTIME_TIMESTAMP": null,
"msg": "500: Database error saving new user",
"action": null,
"login_method": null,
"_UID": null,
"host": "db-bzqvkknsoikjeqnonnkv",
"PRIORITY": null,
"_CAP_EFFECTIVE": null,
"_PID": null,
"INVOCATION_ID": null,
"_SYSTEMD_UNIT": null,
"source_type": null,
"SYSLOG_FACILITY": null,
"request_id": null,
"CODE_LINE": null,
"path": "/signup",
"component": "api",
"project": null,
"user_id": null,
"auth_event": [],
"args": [],
"referer": "http://localhost:3000",
"factor_id": null,
"provider": null,
"client_id": null,
"remote_addr": "111.220.138.33",
"_SYSTEMD_SLICE": null,
"_SYSTEMD_INVOCATION_ID": null,
"header": null,
"_MACHINE_ID": null,
"_AUDIT_LOGINUID": null,
"_TRANSPORT": null,
"_SELINUX_CONTEXT": null,
"MESSAGE_ID": null,
"__REALTIME_TIMESTAMP": null,
"metadata": [],
"_STREAM_ID": null,
"metering": null,
"time": null,
"_GID": null,
"_BOOT_ID": null,
"SYSLOG_IDENTIFIER": null,
"_AUDIT_SESSION": null,
"error": "failed to close prepared statement: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: column "firstname" of relation "users" does not exist (SQLSTATE 42703)"
}
]

Will update later today if still unresolved.

[GaryAustin1]

@TStone45 I missed it in your code. But Postgres does not like caps. If you use caps for names of tables or columns you MUST put them in double quotes or they go lower case. So your error is that all your column names are converting to lower case as you did not use "lastName". lastName will become lastname which will not match lastName as a column name. "lastName" will match.

All those have to be "myName". It would be better to not have used upper case for names to begin with.

[TStone45]

My stoopid Camelcase ass..... on god you are a very kind man, greatly appreciate your time and efforts.

I will update asap, working currently by day, coding by night.... not exactly the Dark Knight but, echoes....

[GaryAustin1]

@TStone45 It happens so much I have a meme I use...

[asiriPiyajanaka]

Here is how I did it using supabase triggers - https://stackoverflow.com/a/78582155/4350622