I think I have figured out how to do it. It's a little convoluted. So firstly, you need to download and install the ESRI Optimize Rasters toolkit.
You then need to add the toolbox to ArcPro and open it, following instructions in the "Using OptimizeRasters in ArcGIS Pro" section of the manual.
You then need to open the Optimise Rasters script in the toolbox, and configure as follows (this is to read COGS in Public buckets on AWS).
Configuration File: CreateRasterProxy
Input Source: Amazon S3
Input Profile: Public Bucket
Input Bucket Container: <bucket name> e.g. oin-hotosm (Open Aerial Map)
Input Bucket: <bucket folder path> e.g. 5faff8ddf5b34900056e78a7/0 (note, not full path, which is 5faff8ddf5b34900056e78a7/0/5faff8ddf5b34900056e78a8.tif
Output Destination: Local
Output Path: Your local path
As so often seems to be the case with ESRI products, there are caching issues, so I found I had to restart the software again to create more than one of these, even using different output folders.
This worked for me for Open Aerial map as above, but not at all for Sentinel 2 data, e.g. Input Bucket: sentinel-cogs, Input Bucket Path: sentinel-s2-l2a-cogs/2019/S2B_28RGU_20191209_0_L2A
I suspect this is because it's arranged as a STAC catalogue or something, but QGIS can download (e.g. https://sentinel-cogs.s3-us-west-2.amazonaws.com/sentinel-s2-l2a-cogs/2019/S2B_28RGU_20191209_0_L2A/B08.tif as a COG without any issue). There is a ticket for that here.
Another issue that I haven't quite got to the bottom of is what the program is doing with the data. It is creating a PNG file for what looks like an overview image and then creates an MRF file, which references the COG file. So a little bit complicated!