Auth.uid() is only used with PostgREST (used for SB database API) calls. In a delete trigger function you need to use old.id. This assume you are using the UI or auth function to delete.

begin
  delete from public.users where id = old.id;
  return id;
end;

is not working

You normally return either new or old from a trigger function, although for delete anything not null might work. Docs show using old. https://www.postgresql.org/docs/current/plpgsql-trigger.html What does “not working” mean? You can also use: raise log string to send debug info to the Postgres log.

not working, meaning it wasn't deleting from public.users the user associated with the user I deleted manually from auth.users. It works now if I return old. I didn't knew about that variable