supabase: 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
userstable 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
About this issue
- Original URL
- State: closed
- Created 6 years ago
- Reactions: 22
- Comments: 32 (6 by maintainers)
Commits related to this issue
- Merge pull request #563 from supabase-community/silentworks/add-to-maintainers Add myself to maintainers — committed to NealShankarGit/supabase by deleted user 3 years ago
Hi all - I just tried with a new project and didn’t have any errors here. Here were the steps:
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 ?
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…
Yeah I always try to remember to
@@map(lower_case_name)@mike-wax or if you use phone auth:
If you want to do it on insert or update:
It seems @RilDev’s recommendation works!
Leaving this here in case it helps anyone else:
(if you use a foreign key, you don’t need the
::textcast, that’s only to work around my Prisma’s setup lack of multi-schema)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.usersmyself. 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?
Works when defining the function in the auth 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.
@fergusmeiklejohn Hi! May I know how you added a row to auth.users by yourself?
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
Profileas my table name, but after switching toprofileit 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
updateHey, 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 👍
Thanks. All is well now. I can confirm that a user is created via both the UI and my client sign up form. Thanks @kiwicopple
I nuked left over procedures with
DROP FUNCTION IF EXISTS handle_new_user cascade;just to be safe and start from scratch.