FWIW, if you configure AWS RDS for Multi-AZ support, you essentially get a black box "cloud database" with synchronous replication and automatic failover. You're right, though, in that you're still essentially being allocated a fixed-sized single-node deployment of your favorite RDBMS.
I think the "obvious solution" is just not super easy to implement. If you want ACID compliance as with traditional RDBMSs but in a auto-scaling, distributed setting, it's not going to happen. Early RDMSs were built with a single node in mind, and any database-as-a-service will have to make some compromises. DynamoDB gives you the flexibility of not having to worry about scaling, and even offer strongly consistent reads, but without support for transactions, for example.
I think the "obvious solution" is just not super easy to implement. If you want ACID compliance as with traditional RDBMSs but in a auto-scaling, distributed setting, it's not going to happen. Early RDMSs were built with a single node in mind, and any database-as-a-service will have to make some compromises. DynamoDB gives you the flexibility of not having to worry about scaling, and even offer strongly consistent reads, but without support for transactions, for example.