The solution comes in the form of an app, the Magic Eraser Background Editor app from the App Store.
I was a little skeptical of this actually working but gave it a try anyway and I was pleasantly surprised. I grabbed the logo for my Atari Gamer website and loaded it in Magic Eraser. The original logo PNG had a transparent background with a shadow effect around the logo image. This of course was changed by iOS to be on a white background and that was still the case when I opened it in Magic Eraser.
However, using the eraser tool quickly removed the background. I had to zoom in on parts of the image to remove everything but it did actually work. Unfortunately this also meant that my shadow effect was gone too, but this is a workaround after all.
After saving the image, the iOS Photos app correctly showed it with transparency. I could also use this image in a watermarking app, again the transparency worked as expected.
I'm pretty happy with this workaround and will continue using until Apple fixes transparency in PNGs.