Here's step-by-step to fix the Auth.Users table not showing when you select trigger. TLDR: Supabase thought the users table was too important to let you edit it through their UI so they changed it. You can only access it through code. But don't be afraid! 1. On the left side bar, click SQL Editor (third icon from the top) 2. Copy and paste this into your editor : create trigger new_user_trigger after insert on auth.users for each row execute function create_profile_for_new_users (); Note : your function have to be called create_profile_for_new_users like it stated in the video. I used Supabase AI to on the SQL Editor (The green bar) to generate the code. Here was my prompt "create a trigger in Supabase that reacts to new user sign-ups by inserting a row into another table called Profile". Hope this helps!
Awesome thank you Lucas. I really need to do a follow video on this but appreciate you pointing this out. Its very community spirit of you to provide this solution. Such a shame RUclips does not allow video updates.
Thank you, I keep getting: "Failed to create user: duplicate key value violates unique constraint "profile_email_key" any idea how I fix that? after using the steps you described here. before I get a okay but no row is generated in profile
@mrgolddd that's strange but usually when there's an error like that it meant the table isn't set up right. I usually plug such error in chatgpt with context its from supabase and ask it to explain to me how to solve it. Sorry wasn't very helpful but hope that works
Wow!! Excellent tutorial on Supabase. I have couple of questions. I watched your other series on “Goals tracking” app where you went over creating tables on Supabase at the beginning for creating accounts. Only this time, the difference is you created a “Function” and “Trigger” on Supabase. Do I need to create this “Function” and “Trigger”? Wouldn’t FF populate this automatically when you create an action to insert user details in table fields when user signup?
Hello @the_digitalpro ! Everything was going swimingly until when I needed to select the auth table in the trigger ui. Only my public tables were available and not anything else! Is there a reason why I can't make triggers from any of the locked shemas?
Same here...function is fine, but trigger creation is only available on public schema tables. This has recently been reported elsewhere too. The only thing I can think of is that Supabase have perhaps changed the way create trigger roles/permissions work?
Hey there thanks for the amazing video I got this error after creating the trigger when I want to add user: _Failed to create user: Database error creating new user_ what should I do?
Is there any Update on this? I needed to select the auth table in the trigger UI. Only my public tables were available and not anything else! Is there a reason I can't make triggers from any of the locked schemas?
This did the trick for me : -- inserts a row into public.members create function public.handle_new_user() returns trigger language plpgsql security definer set search_path = public as $$ begin insert into public.members (id, email) values (new.id, new.email); return new; end; $$; -- 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(); Using Supabase SQL Editor
Hi. Thank you for your comments on this video. I would like to go back to the video and walkthrough the process again and validate any changes that may have occurred on the Supabase end. This is sometimes the problem with evolving products that certain things change over time which results in having to provide updated videos. Thank you for your info. I'll take a look!
create or replace function insert_user_profile () returns trigger as $$ BEGIN INSERT INTO public.profile (id, email) VALUES (NEW.id, NEW.email); RETURN NEW; END; $$ language plpgsql security definer; create trigger on_new_user after insert on auth.users for each row execute procedure
I love your tutorials! I've just begun watching this, but I don't see any reference to deleting users. This is something that is not discussed in any videos that I've watched so far :) I have lots of test users with no clue as to how to delete them.
Great tutorial, thank you so much for all series you created about flutterflow and supabase. I hope to continue these series. I have a question please, I'm creating an app for school and I need to know how to manage users roles in both flutterflow and supabase. my app has many roles like (App_Admin who manage and create other roles, student, teacher, school_principal, and social_worker), can you please create a tutorial to explain how to do this process? or at least give me an idea about how to create these roles in supabase and how to manage them in flutterflow. Will I create user roles in profile table or there is another way? thank you so much for your support.
Hello thank you very much for the kind words and suggestion. There certainly are a number of different ways to implement roles in a FlutterFlow app. I would be happy to do this no problem. Leave it to me. Great suggestion. 👍
@@the_digitalprowould also like to see this tutorial. I’m trying to build an app that has two types of users, like airbnb, both consumers and hosts and a user can have both roles. Is this best achieved with two user profile tables?
You are absolutely correct. In my user delete video I do actually correct this as I forgot to apply this option in this specific video. Thanks for bringing it up here.
I keep getting errors trying to create the function - undefined variable for public.profile I've checked spelling over and over, no clue what's causing this.
There is an alternative way to implement the user profiles. As can be seen in the comments on this video, when you start using database triggers/functions/polices etc you are opening yourself up to a whole WORLD of PAIN. Just create the user profile table as a normal table with user_id text field and when you create an account in FF just add in a Supabase query to update the email and auth id to the email and user_id field in your user profile table. Job done. Also, if you do too much in Supabase then it isn't low/no code... its just db SQL coding which defeats the whole point/ethos of low/no code
Yes I do agree with your points but you cannot avoid locking down your Supabase database with rules to restrict access. This has to be done with getting your hands dirty. Hopefully the Supabase team will provide helpers. I tried to be careful in any complexity in this video with the hope followers could work out what was going on. Yes nocode it isn't but I could not avoid that at the time. Give it a year we should be good. Hopefully.
Is there a way to do this with signInWithOauth? I don't know how to distinguish between an oauth sign in and oauth signup. I want to use your strategy but I only know how to do it with signup with email.
Creating triggers for schema auth is disallow. Message: Currently viewing triggers from a protected schema The auth schema is managed by Supabase and is read-only through the dashboard. How to change the settings to allow creating triggers for auth through the dashboard? The following schemas are managed by Supabase and are currently protected from write access through the dashboard. auth cron extensions information_schema net pgsodium pgsodium_masks pgbouncer pgtle realtime storage supabase_functions supabase_migrations vault graphql graphql_public
This just didn't work. I created my function as create_profile_for_new_user , and the used the sql editor to create the trigger which worked but when I test, supabase just throws a generic error error message back it me telling me nothing about what has gone wrong. new user does not work... create trigger new_user_trigger after insert on auth.users for each row execute function create_profile_for_new_user ();
Hey Hugh. Sorry to hear you are having a few issues. What is odd is that I followed my own tutorial the other day to validate the steps and pretty much it check out okay. I did have to follow the odd steps in the comments but I was up and running. This is the problem with evolving products such as Supabase things do change since the video was recorded. What is your issue you now have?
Not possible anymore. This guide is dated, please take it down. Triggers works differently now and the schema: auth doesn't allow triggers to be created. Wasted my time.
Hello. Yes this version is slightly different. I will have an updated video coming out soon. I do have a written 2024 guide within my NoCode Academy but not a video yet. Watch this space.
Wondering if this Auth.jwt() and getting the id from the 'sub', is it going to be the same as just auth.id() = user_id? Do they both provide the same outcome?
Here is showing the following error: Failed to create user: Database error creating new user BEGIN INSERT INTO public.perfilUsuario (id, email) VALUES (NEW.id, NEW.email); RETURN NEW; END;
Acho que você é brasileiro, se entendeu isso, pode pular pro texto em português mesmo 🤣🤣 - english - I had the same error, I solved it this way. 1 - I created the security policies that he creates on 9:40 (I tested it before creating the policies and it wasn't working) 2 - in the function code check if the name of your profile table is written correctly. In my case the error was occurring because my table was called "profiles" (in the plural) and I had written in the code "public.profile" in the singular, the correct one in my case would be "public.profiles". After fixing those two things, I was able to create the user normally. I hope I helped, it took me a while to realize my silly mistake. - portuguese - Eu estava com o mesmo erro, resolvi da seguinte maneira. 1 - criei as politicas de segurança que ele cria a partir de 9:40 (testei antes de criar as politicas e não estava funcionando) 2 - no código da função "function" confira se o nome da sua tabela de perfil esta escrito da maneira correta. No meu caso o erro estava ocorrendo por que minha tabela se chamava "profiles" (no plural) e eu havia escrito no código "public.profile" no singular, o correto no meu caso seria "public.profiles". Depois de corrigir essas duas coisas, eu consegui criar o usuário normalmente. Espero ter ajudado, demorei para perceber meu erro bobo.
Remember the log is your friend - click on the log icon on the left side and you can see the error and what you are doing wrong. Now sometimes it will be really cryptic like in this instance it would say "profile" table does not exist or "profile" relation does not exist - which is a typo in names or forgetting to put public before the table name 😆🤣🤣 its like breadcrumbs to keep you on your toes but once you see enough you get to know what they "could" mean
@@mayconpelegrini i created the policies as he did and check my function code which is 100% correct... still get the same error.... Failed to create user: Database error creating new user
Here's step-by-step to fix the Auth.Users table not showing when you select trigger.
TLDR: Supabase thought the users table was too important to let you edit it through their UI so they changed it. You can only access it through code. But don't be afraid!
1. On the left side bar, click SQL Editor (third icon from the top)
2. Copy and paste this into your editor :
create trigger new_user_trigger
after insert on auth.users for each row
execute function create_profile_for_new_users ();
Note : your function have to be called create_profile_for_new_users like it stated in the video.
I used Supabase AI to on the SQL Editor (The green bar) to generate the code. Here was my prompt "create a trigger in Supabase that reacts to new user sign-ups by inserting a row into another table called Profile".
Hope this helps!
Awesome thank you Lucas. I really need to do a follow video on this but appreciate you pointing this out. Its very community spirit of you to provide this solution. Such a shame RUclips does not allow video updates.
Thank you, I keep getting:
"Failed to create user: duplicate key value violates unique constraint "profile_email_key" any idea how I fix that?
after using the steps you described here. before I get a okay but no row is generated in profile
@mrgolddd that's strange but usually when there's an error like that it meant the table isn't set up right. I usually plug such error in chatgpt with context its from supabase and ask it to explain to me how to solve it. Sorry wasn't very helpful but hope that works
You are a life saver
Thank you so much @lucashyde1164. I got it working :)
Thanks, way more helpful than all the other stack overflow answers i found
Thank you so much. Appreciate the feedback.
Thank you very much Steve for this tutorial, it was exactly what I was looking for! Straight to the point!
very amazing ! Very clear and full of useful knowledge , thanks
Wow!! Excellent tutorial on Supabase. I have couple of questions. I watched your other series on “Goals tracking” app where you went over creating tables on Supabase at the beginning for creating accounts. Only this time, the difference is you created a “Function” and “Trigger” on Supabase. Do I need to create this “Function” and “Trigger”? Wouldn’t FF populate this automatically when you create an action to insert user details in table fields when user signup?
Thank you for the effort and clarity in these videos! Super helpful.
Best tutorial!!👍👍👍thanx for the great video
Thank you very much appreciate it!
You are a life saver!! 🙂
Hello @the_digitalpro ! Everything was going swimingly until when I needed to select the auth table in the trigger ui. Only my public tables were available and not anything else! Is there a reason why I can't make triggers from any of the locked shemas?
Hello. Can you confirm in the video which point you experienced this issue? Just the timestamp will do.
7:37
Same here :(
Same here...function is fine, but trigger creation is only available on public schema tables. This has recently been reported elsewhere too. The only thing I can think of is that Supabase have perhaps changed the way create trigger roles/permissions work?
Hi there,
Great tutorial 👍. I have the same issue.
Has this been resolved?
Thanks, Mark
Thank you so much: How can I create an email confirmation flow with supabase and flutter?
God bless u
Hey there thanks for the amazing video
I got this error after creating the trigger when I want to add user:
_Failed to create user: Database error creating new user_
what should I do?
Is there any Update on this? I needed to select the auth table in the trigger UI. Only my public tables were available and not anything else! Is there a reason I can't make triggers from any of the locked schemas?
This did the trick for me :
-- inserts a row into public.members
create function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
insert into public.members (id, email)
values (new.id, new.email);
return new;
end;
$$;
-- 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();
Using Supabase
SQL Editor
Hi. Thank you for your comments on this video. I would like to go back to the video and walkthrough the process again and validate any changes that may have occurred on the Supabase end. This is sometimes the problem with evolving products that certain things change over time which results in having to provide updated videos. Thank you for your info. I'll take a look!
create or replace function insert_user_profile () returns trigger as $$
BEGIN
INSERT INTO public.profile (id, email)
VALUES (NEW.id, NEW.email);
RETURN NEW;
END;
$$ language plpgsql security definer;
create trigger on_new_user
after insert on auth.users
for each row execute procedure
I love your tutorials! I've just begun watching this, but I don't see any reference to deleting users. This is something that is not discussed in any videos that I've watched so far :)
I have lots of test users with no clue as to how to delete them.
Thank you this is a great point. Have you look at using the Supabase API's?
Hi, Can you also explain how to add the user in the storage bucket and delete the folder when the user is deleted
Cannot find how you set up the username input action in this video, please advise. 😊
have you something to delete the user in profile and in auth in the same time?
Great tutorial, thank you so much for all series you created about flutterflow and supabase. I hope to continue these series. I have a question please, I'm creating an app for school and I need to know how to manage users roles in both flutterflow and supabase. my app has many roles like (App_Admin who manage and create other roles, student, teacher, school_principal, and social_worker), can you please create a tutorial to explain how to do this process? or at least give me an idea about how to create these roles in supabase and how to manage them in flutterflow. Will I create user roles in profile table or there is another way? thank you so much for your support.
Hello thank you very much for the kind words and suggestion. There certainly are a number of different ways to implement roles in a FlutterFlow app. I would be happy to do this no problem. Leave it to me. Great suggestion. 👍
@@the_digitalprowould also like to see this tutorial. I’m trying to build an app that has two types of users, like airbnb, both consumers and hosts and a user can have both roles. Is this best achieved with two user profile tables?
Very help! Thank u!
You're welcome! Thanks
You should delete to cascade when creating the profile table
You are absolutely correct. In my user delete video I do actually correct this as I forgot to apply this option in this specific video. Thanks for bringing it up here.
I keep getting errors trying to create the function - undefined variable for public.profile I've checked spelling over and over, no clue what's causing this.
Now I wonder How can I get the First and Last names from the TextFields on my SignUp Page from FF into the profile table Supabase.
There is an alternative way to implement the user profiles. As can be seen in the comments on this video, when you start using database triggers/functions/polices etc you are opening yourself up to a whole WORLD of PAIN. Just create the user profile table as a normal table with user_id text field and when you create an account in FF just add in a Supabase query to update the email and auth id to the email and user_id field in your user profile table. Job done. Also, if you do too much in Supabase then it isn't low/no code... its just db SQL coding which defeats the whole point/ethos of low/no code
Yes I do agree with your points but you cannot avoid locking down your Supabase database with rules to restrict access. This has to be done with getting your hands dirty. Hopefully the Supabase team will provide helpers. I tried to be careful in any complexity in this video with the hope followers could work out what was going on. Yes nocode it isn't but I could not avoid that at the time. Give it a year we should be good. Hopefully.
Is there a way to do this with signInWithOauth? I don't know how to distinguish between an oauth sign in and oauth signup. I want to use your strategy but I only know how to do it with signup with email.
So much has changed on the Supabase end of things I didn't get that to work....
You are absolutely right. Products do move on. I would like to produce an update to this video to help people orientate the changes.
7:17 as of today, supabase does not allow to add new trigger in the auth schema, how to solve this ?
I tried doing it directly at psql console with SQL, but didnt have it working... Any joy at your side?
@@SpurgeonB ruclips.net/video/mcrqn77lUmM/видео.html
I cannot see auth -> users table in trigger settings (creation)
Please check the comments on this video because Supabase did an update since the video was recorded. This will resolve your problem.
ruclips.net/video/mcrqn77lUmM/видео.html
Creating triggers for schema auth is disallow.
Message: Currently viewing triggers from a protected schema
The auth schema is managed by Supabase and is read-only through the dashboard.
How to change the settings to allow creating triggers for auth through the dashboard?
The following schemas are managed by Supabase and are currently protected from write access through the dashboard.
auth
cron
extensions
information_schema
net
pgsodium
pgsodium_masks
pgbouncer
pgtle
realtime
storage
supabase_functions
supabase_migrations
vault
graphql
graphql_public
This just didn't work. I created my function as create_profile_for_new_user , and the used the sql editor to create the trigger which worked but when I test, supabase just throws a generic error error message back it me telling me nothing about what has gone wrong. new user does not work...
create trigger new_user_trigger
after insert on auth.users for each row
execute function create_profile_for_new_user ();
Hey Hugh. Sorry to hear you are having a few issues. What is odd is that I followed my own tutorial the other day to validate the steps and pretty much it check out okay. I did have to follow the odd steps in the comments but I was up and running. This is the problem with evolving products such as Supabase things do change since the video was recorded. What is your issue you now have?
@@the_digitalpro can you please give as new instructions because as mentioned before we cannot access auth tables to create triggers
Not possible anymore. This guide is dated, please take it down. Triggers works differently now and the schema: auth doesn't allow triggers to be created. Wasted my time.
Hello. Yes this version is slightly different. I will have an updated video coming out soon. I do have a written 2024 guide within my NoCode Academy but not a video yet. Watch this space.
Just for you checkout my latest video.
Wondering if this Auth.jwt() and getting the id from the 'sub', is it going to be the same as just auth.id() = user_id? Do they both provide the same outcome?
Yes absolutely these are the same!
Here is showing the following error:
Failed to create user: Database error creating new user
BEGIN
INSERT INTO public.perfilUsuario (id, email)
VALUES (NEW.id, NEW.email);
RETURN NEW;
END;
Acho que você é brasileiro, se entendeu isso, pode pular pro texto em português mesmo 🤣🤣
- english -
I had the same error, I solved it this way.
1 - I created the security policies that he creates on 9:40 (I tested it before creating the policies and it wasn't working)
2 - in the function code check if the name of your profile table is written correctly. In my case the error was occurring because my table was called "profiles" (in the plural) and I had written in the code "public.profile" in the singular, the correct one in my case would be "public.profiles". After fixing those two things, I was able to create the user normally.
I hope I helped, it took me a while to realize my silly mistake.
- portuguese -
Eu estava com o mesmo erro, resolvi da seguinte maneira.
1 - criei as politicas de segurança que ele cria a partir de 9:40 (testei antes de criar as politicas e não estava funcionando)
2 - no código da função "function" confira se o nome da sua tabela de perfil esta escrito da maneira correta. No meu caso o erro estava ocorrendo por que minha tabela se chamava "profiles" (no plural) e eu havia escrito no código "public.profile" no singular, o correto no meu caso seria "public.profiles". Depois de corrigir essas duas coisas, eu consegui criar o usuário normalmente.
Espero ter ajudado, demorei para perceber meu erro bobo.
@@mayconpelegrini kkk
É nois 👊🏼
Vlw irmão, vou testar aqui!
@@josucafd 🤜🏼🤛🏼
Remember the log is your friend - click on the log icon on the left side and you can see the error and what you are doing wrong. Now sometimes it will be really cryptic like in this instance it would say "profile" table does not exist or "profile" relation does not exist - which is a typo in names or forgetting to put public before the table name 😆🤣🤣 its like breadcrumbs to keep you on your toes but once you see enough you get to know what they "could" mean
@@mayconpelegrini i created the policies as he did and check my function code which is 100% correct... still get the same error.... Failed to create user: Database error creating new user