I've not come up with a private solution that I consider perfect. Or a hosted solution, for that matter. My time as CIO of an electronics company has made me cautious about handling all the security aspects of an implementation by myself. Google Workspace security is quite advanced and flexible.
You do not handle the security, Synology does that. I take it you trust Google more than Synology; my view is the opposite. Sending all your personal stuff to Google is, as we say in the Netherlands, 'tying the cat to the bacon', IOW you're sure the bacon gets eaten by the cat. Synology has a different business model. The bacon is only eaten if a hacker penetrates the Synology system.
I could be wrong, but it's my view at this moment.
You have to open firewall ports to the Synology box, unless you run wide open, which I don't think is a good idea. Unless you have a DMZ, you have outside traffic on your internal network. Synology can't fix that.
You are right. I am more worried about local web servers and local networks being compromised than I am about either Google Workspace being compromised or somebody at Google taking my images.
It doesn't work that way. All you have to do is join the free 'connect.to' service of Synology and they provide some sort of tunnel from their servers to the DiskStation through the CloudStation app. I do not know how it works exactly, although I wondered. If you cut the power to your DiskStation you get an email stating 'connection to ... was lost at ... time'.
I used to have a DMZ with a small Synology running a website where clients could order images while the large original files and database were on a large Synology on the local network. So I do know what you talk about, but this CloudStation scenario works differently.
Not by design. I don't know of anyone who has set out to design an unsafe VPN. VPN quality is variable, and hard for IT people to understand, especially if not widely implemented with many customers. I wouldn't necessarily trust Synology's implementation. Do you know where their technology came from?
If people can access the Synology-served photos from a web browser, it needs more than a VPN connection to the internet. If they are doing their own hosting for the web users, then the VPN connection is sufficient.
And I guess that as long as the Synology and other computers are on the local network the files never touch the Internet but are exchanged between the CloudStation apps on the Synology and the computers. As you also have to install a CloudStation app on each computer that want to participate.