Yes, as you noticed that won't work because the service worker will get served from the CDN which violates the requirement for service worker to be served from the same domain i.e. your store URI.
To work around that (haha, the pun-iness in that) you would need to create an app proxy to serve that service worker from your app and through your domain. Mind you, the proxied SW will not work with CDN files so whatever you wish to have done with the SW will need to be proxied too so they're on the same path.
IMO, having played with some of that, it's all just a massive PITA. Proxies add latency plus you have to re-serve a bunch of assets. Personally, wouldn't bother with it using standard Shopify themes. If however you're up for creating an entirely standalone PWA, ditch Liquid and Shopify themes, then sure - but that too comes with a bag of headaches (as I keep discovering every day that I work on it).
Best wishes and hope it helps for what it's worth!
Karl has a point, you probably need to build a whole app in order to use the proxy to serve service worker.
Or, you can also just install one of the apps (disclaimer, my team built https://apps.shopify.com/progressify-me-1). It already solved the complicated problems with service worker so you won't lose time and money developing this. It works with standard Shopify themes out of the box, and the whole setup is finished in a few minutes. There is a free tier, try it out, maybe it solves your problem.
Re: original question - was wrong about assets needing to be on same path though. It should be possible to configure SWs served via app proxy to still work on the root using Service-Worker-Allowed = "/" header with proper scope settings.
So while the SW, manifest etc. are still likely to be served from an app proxy, most if not all should be pre-cacheable i.e. root assets, 3rd party requests etc.
Still need to dig around this more as SWs are typically just a webpack / rollup lib (with WorkBox under the hood) away with minimal config for me so I never really bothered truly understanding. Just wanted to update this thread as to not mislead anyone.