I guess cloud big boys would be using key management systems to move the key off the local instance
Yes, AWS uses KMS - by default everything like RDS is encrypted at rest through the AWS default KMS key (default for your account, not globally default). I'm still not entirely sure what the point is, since once you login to the AWS console, or connect to the database, everything is decrypted by default anyways. So I suppose the main thing it protects from is physical access.
You can make it more complicated by having more complicated KMS schemes, for example, see Demystifying KMS keys operations - That has a pretty good explanation of what KMS is, and the point of encrypted at rest (at AWS).
A reason customers could ask for encryption at rest could be that they want to be in control of the decryption key. Then at any point that would give them the ability to revoke the decryption key, and practically revoke your access to their data
But as @recursive_recursion mentioned, you should probably ask the stakeholder what the point is. 90% of the time the point is just some checkbox on a ISO27001 or SOC2 form. And "really providing any extra security" is not
Could be a feature where servers would add your IP to a list, and send it to the clients (like a list somewhere in case of a website)
Then clients would start sending random metasploit-esk requests to those IPS