This is SSL as security theater.
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.
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.
Or is this an indictment of Cloudfront offering to be your SSL termination point?