javascript - Can't add trigger on insert in auth.Users in Supabase - Stack Overflow

admin管理员组

文章数量:1264022

I tried to create a trigger on auth.users to add information to a public table, but for some reason, it caused issues during registration. I get the following error:

AuthApiError: Database error saving new user.

Before this, I also had an issue where I called a procedure for auth.users (policies were fine), but it didn’t work. It worked in the SQL editor but failed through api requests (I still don’t know how to fix this), and maybe it’s somehow related.

Here is my function:

const handleSignUp = async (e) => {
  e.preventDefault();

  try {
    const { user, error } = await supabase.auth.signInWithPassword({
      email: formData.email,
      password: formData.password,
    });
    if (user) {
      alert("An account with this email already exists. Please log in.");
    } else if (error) {
      const { data, error } = await supabase.auth.signUp({
        email: formData.email,
        password: formData.password,
      });
      if (error) throw error;
      alert("Check your email for the verification link");
    }
  } catch (error) {
    alert(error);
  }
};

Here are the function and trigger:

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.users (user_id, email, Role)
  VALUES (NEW.id, NEW.email, 'Low')
  ON CONFLICT (user_id) DO NOTHING;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_insert
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION handle_new_user();

and table schema: .png

Has anyone encountered a similar problem?

I tried to create a trigger on auth.users to add information to a public table, but for some reason, it caused issues during registration. I get the following error:

AuthApiError: Database error saving new user.

Before this, I also had an issue where I called a procedure for auth.users (policies were fine), but it didn’t work. It worked in the SQL editor but failed through api requests (I still don’t know how to fix this), and maybe it’s somehow related.

Here is my function:

const handleSignUp = async (e) => {
  e.preventDefault();

  try {
    const { user, error } = await supabase.auth.signInWithPassword({
      email: formData.email,
      password: formData.password,
    });
    if (user) {
      alert("An account with this email already exists. Please log in.");
    } else if (error) {
      const { data, error } = await supabase.auth.signUp({
        email: formData.email,
        password: formData.password,
      });
      if (error) throw error;
      alert("Check your email for the verification link");
    }
  } catch (error) {
    alert(error);
  }
};

Here are the function and trigger:

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.users (user_id, email, Role)
  VALUES (NEW.id, NEW.email, 'Low')
  ON CONFLICT (user_id) DO NOTHING;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_insert
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION handle_new_user();

and table schema: https://i.sstatic/IvUq71Wk.png

Has anyone encountered a similar problem?

• javascript
• sql
• reactjs
• postgresql
• supabase

Share Improve this question Follow edited Feb 27 at 21:30 Dale K 27.4k1515 gold badges5858 silver badges8383 bronze badges asked Feb 27 at 19:06 DepressedChalkDepressedChalk 533 bronze badges 1

• Don't use images please, use table markdown or DDL to describe your schema. – Dale K Commented Feb 27 at 21:31

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

when I look at the example provided by supabase (here) I see some differences with your code, particularly your lack of schema prefixes for the function and not setting the search path. The supabase doco (here) also says when executing the function as definer you must also specify the search path.

本文标签： javascriptCan39t add trigger on insert in authUsers in SupabaseStack Overflow