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

[activenode]

We had basically the same as Paul and then we tried the exact script from @kiwicopple but neither of those seem to work. What seems to work though is defining the same thing but with the function being part of auth. schema. Is there something from PGv15 that could cause this?

Also if you do so it works but the function doesn't show in the functions UI as they seem to not show funcs from other schemas.

[kiwimagic]

Adding my 2c worth. After days trying to create a parallel auth mechanism between firebase phone sign in and supabase the answer above "saved" me from further frustration - adding the magic " security definer;" after my function allowed me to do what I needed to create a user fully via postgres + API. Unfortunately there are so many people struggling with Auth that this answer is buried in the masses. As it is 3 years after this was first asked and still an issue (and not [clearly?] explained yet in the documentation) I will summarize my findings here so they may be helpful for someone in the future.

1. can't touch/don't touch the auth.users table - accept what supabase has/does with this - put your data in public.[own user table] name[s] and add FK to auth.users
2. you can access "create user" signup via api [...]supabase.co/auth/v1/signup (POST) {this is very buried in discussion threads}
3. you can send custom data to be held in the auth.users jsonb meta with variables (this is from my FlutterFlow API call definition Body JSON){
   "email": "",
   "password": "",
   "data": {
   "app_source": "<app_source>",
   "zone": "",
   "phone": ""
   }
   }
4. modify the scripting in the Function called public.handle_new_user() to do what you want data wise to tables in your PUBLIC schema eg create your own "user_profile" table(s) and link the auth.users id to a uuid (may or may not be PK depending on how you architect your public.user_profile schema) NOTE there is sample code provided in the Supabase SQL Editor in a script called User Management Starter (this appeared for me in one of the 2023 updates review this it's a good starter AND DOES have the "security definer" if I had started with this I would have been fine but I copied out the function code from the function UI and reworked my own without seeing this )
5. KEY at the end of your modified public.handle_new_user() trigger function make sure you add the security definer
   example framework:

CREATE OR REPLACE FUNCTION public.handle_new_user() RETURNS TRIGGER AS $$
DECLARE
...
BEGIN
...
do your public.[table] insert/update ..etc here
...
RETURN NEW; 

END;
$$ language plpgsql security definer; -- THESE 2 WORDS ARE THE MISSING LINK!

6. Note once I found the Supabase Log > Postgres [filter Severity = Error] the issue with the permission and tracking down the security definer mention in this discussion became a lot easier! Most of my time was spent trying to figure out what API to use and the syntax to call it. If you are stuck with a 500 ERROR - start with the logs! Good luck.

[DongHY1]

it works! thanks!

[davidpedrero]

This worked for me! Thanks! Adding the security definer to the function was the key for me

[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.

[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