Even SSL may not help. Cloudfront is now offering what they call "Flexible SSL". This means Cloudfront gets an SSL cert which allows them to impersonate the site, they offer an SSL connection to the user's browser, Cloudfront acts as an man-in-the-middle and makes a connection to the destination site. An unencrypted connection in many cases.
It's obviously not nearly as secure as end-to-end SSL, but it's probably still useful. The connection between the client's machine and the Cloudflare's server is more likely to be under attack (unencrypted Wifi, hacked personal routers, rogue exit TOR nodes, etc) than the connection between datacenters.
Or is the false sense of true security a bigger detriment?
That's a really tough call.
Cloudflare makes no security guarantees. They don't even commit to keeping your public key secure when you give it to them. That's a bad sign. One wonders how they fund their free MITM service.
This is important; there are cases of "just plain insecure" but other than that, it's very nuanced.
How about you randomly generate and write all your passwords down on a piece of paper in your wallet? For many threat models, that's far more secure than even using a password manager. For other threat models it's far less secure than using a password manager. Other than things that are just flat-out broken "more-secure" and "less-secure" don't exist without qualification.
How is this different than trusting the website's own load balancer, which might terminate the SSL connection and relay unencrypted traffic to the servers?
Since we're talking about issues with bad devops (unencrypted between Cloudflare and appserver), the same bad devops could apply here too. It wouldn't be the first time that someone left an "internal" appserver on a public IP, or a firewall rule was miswritten, etc.
Why wouldn't SSL help? Unless the offending exit node has the requested site's cert, there's almost no way they can carry out a MITM attack on an SSL request undetected. That's kind of the whole point of certs.
Or is this an indictment of Cloudfront offering to be your SSL termination point?
This is an indictment of Cloudfront offering to be your SSL termination point, and using multiple-domain certs to do it. Here's the Black Hat paper on how to exploit that.
This is SSL as security theater.