hey, I'm running into an issue with permissions I think. I'm trying to insert values into

public.shadow_users

after an insert on

auth.users

, however inserting a new user fails, because apparently the stored procedure that's triggered on

auth.users

does not have permission to write to

public.shadow_users

. Is this something I can fix on my end? is this a bug? Is this something that's working as intended and won't be fixed?

sql
create table users (
    id uuid references auth.users on delete cascade,
    email text unique not null,
    name text
);

create or replace function shadow_users()
  returns trigger
  language plpgsql
  as
  $$
  begin
    insert into public.users (id, email) values (new.id, new.email);
    return new;
  end;
  $$;


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

It's best to be explicit about which schema the function should be created in, so instead of the function being

shadow_users()

do

public.shadow_users()

instead, same goes for when you call it inside the trigger

EXECUTE FUNCTION public.shadow_users()

those still did not work

sql
create table users (
    id uuid references auth.users on delete cascade,
    email text unique not null,
    name text
);

create or replace function public.shadow_users()
  returns trigger
  language plpgsql
  as
  $$
  begin
    insert into public.users (id, email) values (new.id, new.email);
    return new;
  end;
  $$;

CREATE TRIGGER shadow_users_trigger
    AFTER INSERT ON auth.users
    FOR EACH ROW
    EXECUTE PROCEDURE public.shadow_users();

this still gives me

time="2021-10-04T12:38:24Z" level=error msg="500: Database error saving new user" component=api error="failed to close prepared statement: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: permission denied for table users (SQLSTATE 42501)" method=POST path=/signup referer="http://localhost:3000/" remote_addr="81.155.33.216:50776" request_id=c4acee10-bfe3-4a6f-86d6-58e0e3460f48

You might need to add the security definer to the function