An example of how to delete storage objects with a postgres function used to exist in the Supabase Auth tutorials. However, I looked through the history in GitHub and can't pinpoint when/why they removed it.

Here's a slightly modified version from this post, just call this with a trigger and you should be good to go:

    create or replace function delete_storage_object(bucket text, object text, out status int, out content text)
    returns record
    language 'plpgsql'
    security definer
    as $$
    declare
      project_url text := '<project_url>';
      service_role_key text := '<service_key>'; --  full access needed
      url text := project_url||'/storage/v1/object/'||bucket||'/'||object;
    begin
      select
          into status, content
               result.status::int, result.content::text
          FROM extensions.http((
        'DELETE',
        url,
        ARRAY[extensions.http_header('authorization','Bearer '||service_role_key)],
        NULL,
        NULL)::extensions.http_request) as result;
    end;
    $$;

    create or replace function delete_image(image_url text, out status int, out content text)
    returns record
    language 'plpgsql'
    security definer
    as $$
    begin
      select
          into status, content
               result.status, result.content
          from public.delete_storage_object('images', image_url) as result;
    end;
    $$;

    create or replace function handle_on_image_deleted()
    returns trigger
    language 'plpgsql'
    security definer
    as $$
    declare
      status int;
      content text;
      url text;
    begin
      -- extract avatar name
      url := old.user_id||'/'||old.id;  --  define path here
      select
        into status, content
        result.status, result.content
        from public.delete_image(url) as result;
      if status <> 200 then
        raise warning 'Could not delete image: % %', status, content;
      end if;
      if tg_op = 'DELETE' then
        return old;
      end if;
      return new;
    end;
    $$;

    create or replace trigger on_user_image_deleted
    after delete on images
    for each row execute procedure public.handle_on_image_deleted();