They are encrypted at rest. This protects from someone hacking in to Apple, getting the data on disk but somehow not getting the keys, which Apple also possesses.

Apple (and thus, LEO) absolutely can look at your photos on iCloud. What you are missing is that "encrypted at rest" is essentially "not encrypted in any meaningful way".