I cetainly don't trust them to control all my logins. But my level of trust for them is much higher than for most in terms of their ability to secure their databases and applications. I use different levels of passwords, depending on the type of site: nytimes.com < gmail.com < bankofamerica.com. I have tried to use password manager, and am currently trying one out on Safari, but the use of many computers makes it difficult.
For a huge percentage of all Internet users, a Google or Yahoo compromise is game-over; they're going to lose their bank account, and then their social, and their identity (if they lose the lottery). So centralizing on Google or Yahoo is a sensible plan.
As for your cautionary tale, I'm pretty familiar with the players here, axod. Why don't you tell us?
Google shut off my account for a week and I lost access to everything google controls - adsense, adwords, gmail, google code, youtube, blogger, google apps, google for domains etc etc They shut it off because "Someone tried to log in to it unsuccessfully"
Probably for the average person though as you say, centralizing control is probably easiest until something like that happens to them.
Wouldn't an idea be to centralize this with your ISP? The ISP already knows who you are, seems like they would be a good authority on handling authentication to websites for you. (OK, doesn't work for when you're using some hotel wifi etc)
Few months ago, someone I know (nontechnical) lost their password on a public blog server. Unfortunately, like most people, they used the same password on their Yahoo mail account. Inside of a day, they:
* Got locked out of their Yahoo mail account for a week
* Lost their GoDaddy account, got locked out of it, and had it redirected to a gay porn site
* Lost their bank account, had thousands in fraudulent charges racked up, and got locked out of the account
* Had all their Yahoo mailing lists scrubbed, and each mailing list member (including his kids soccer team, which he ran) spammed with gay porn stuff
* Had his tax dox and personal mail dumped in public.
It sounds like your Google experience sucked. But I can think of worse things that can happen than a beaurocratic SNAFU. Let's not just hope that people will get smart about their passwords.