Database error granting user

[shahid-0]

Hello folks, I try to create the trigger whenever I update the user on auth.users table it updates the user on the public tables based on the role of the user. Below is the code of the trigger:

CREATE OR REPLACE FUNCTION public.update_public_tables_on_auth_user_update() RETURNS TRIGGER AS $$
DECLARE
  update_email TEXT;
  update_phone_number TEXT;
  update_password TEXT;
  table_name TEXT;
BEGIN
  IF NEW.data::jsonb ->> 'user_type' = 'employee' THEN
    table_name := 'employee';

    IF NEW.email IS NOT NULL AND NEW.email <> OLD.email THEN
      UPDATE public.employee
      SET email = NEW.email
      WHERE id = NEW.id;
    END IF;

    IF jsonb_exists(NEW.data::jsonb, 'phone_number') AND NEW.data::jsonb ->> 'phone_number' <> OLD.phone_number THEN
      UPDATE public.employee
      SET phone_number = NEW.phone_number
      WHERE id = NEW.id;
    END IF;

  ELSIF NEW.data::jsonb ->> 'user_type' = 'owner' THEN
    table_name := 'restaurant_owners';

    IF NEW.email IS NOT NULL AND NEW.email <> OLD.email THEN
      UPDATE public.restaurant_owners
      SET email = NEW.email
      WHERE id = NEW.id;
    END IF;

    IF jsonb_exists(NEW.data::jsonb, 'phone_number') AND NEW.data::jsonb ->> 'phone_number' <> OLD.phone_number THEN
      UPDATE public.restaurant_owners
      SET phone_number = NEW.phone_number
      WHERE id = NEW.id;
    END IF;

  ELSE
    table_name := 'clients';

    IF NEW.email IS NOT NULL AND NEW.email <> OLD.email THEN
      UPDATE public.clients
      SET email = NEW.email
      WHERE id = NEW.id;
    END IF;

    IF jsonb_exists(NEW.data::jsonb, 'phone_number') AND NEW.data::jsonb ->> 'phone_number' <> OLD.phone_number THEN
      UPDATE public.clients
      SET phone_number = NEW.phone_number
      WHERE id = NEW.id;
    END IF;

  END IF;

  RETURN NEW;
END;
$$ language plpgsql;

-- Create the AFTER UPDATE trigger on the auth_user table
CREATE trigger update_public_tables_trigger
AFTER UPDATE ON auth.users
FOR each row
EXECUTE FUNCTION public.update_public_tables_on_auth_user_update();

I know I'm getting the error due to this trigger because everytime I delete this trigger my authentications start works, but when I add this it again arise the below error:

HTTP Request: POST https://my-supabase-id.supabase.co/auth/v1/token?grant_type=password
Database error granting user
{'message': 'Database error granting user', 'name': 'AuthApiError', 'status': 500}

EDIT
I'm trying to update the user using supabase.auth.update_user (python) which takes these parameter:

res = supa.auth.update_user({
            "email": user["email"],
            "phone": user["phone_number"],
            "password": user["password"],
            "data": user["data"]
        })

so can I access my data using NEW.data or NEW.raw_user_meta_data?

Can anyone tell me what is the error in the above trigger?

[okbob]

From this error message is impossible to say where is the error. Have you some error message in postgresql's log? Or you can run some update of auth.users from psql or pgadmin and get better error message. Error message from http level has not any benefit for identification of database errors.

I have not supabase, and this is not supabase forum. You can show direct plpgsql error, or I cannot help.

[shahid-0]

While creating trigger or function I don't get any error, I update my question. you can look at it. also one more thing when I try to run this code to check whether the trigger is working well or not:

update auth.users
set
  email = 'example.com', data = '{"user_type": "client"}'::jsonb
where
  id = 'user uuid';

I get the below error

Error running SQL: Failed to run sql query: column "data" of relation "users" does not exist

While inserting the user we select the general data using raw_user_meta_data how can I select the the data which we pass in the data parameter?

[okbob]

While creating trigger or function I don't get any error, I update my question. you can look at it. also one more thing when I try to run this code to check whether the trigger is working well or not:

update auth.users
set
  email = 'example.com', data = '{"user_type": "client"}'::jsonb
where
  id = 'user uuid';

I get the below error

Error running SQL: Failed to run sql query: column "data" of relation "users" does not exist

While inserting the user we select the general data using raw_user_meta_data how can I select the the data which we pass in the data parameter?

can you show structure of table auth.users? The error message is clean, there is not column "data" in the table auth.users.