I think part of the hate, especially in these forums, comes from resentment when you get the first game that needs it (in this case probably Civ V for most people), and you have to install this big third-party online app just to play your game. It feels a bit intrusive, and I know I resented it when I first had to install it for Half Life 2 or Team Fortress or whatever it was. It's really only when you start getting a few games on there that you realise its benefits, I think.
Then there's people who hate it because it's also DRM - to which I'd argue it's pretty unobtrusive DRM as far as these things go, and the more publishers feel they can rely on something like that, the less they're likely to add their own painful DRM nonsense (or GFWL, god forbid). In the real world, publishers/shareholders are always going to want some anti-piracy measure, and I think Steam mostly strikes a pretty reasonable compromise (even if its offline mode can be awful flaky). When there's one bit of software that a publisher can just rely on for the selling, downloading, updating, multiplayering, achievementing and DRMing, that makes it an attractive package for going with rather than implementing some other horrible solution.
My major gripe with it is that it replicates the retail pricing for a lot of games - so to buy a new AAA game from Steam from an Australian IP will usually cost $80-100 (it's the same in shops); and the Australian dollar buys about $1.06 US, so we're paying double what everyone else pays for exactly the same thing. You can buy from elsewhere and activate on Steam usually, but it's not always easy and it's a hassle. So I usually wait for sales.
I'd really like Steam to have a serious competitor(s) to hopefully get rid of this crazy price gouging.
But yeah on the other hand there's the convenience and the crazy sales and the indies. Steam's HUGE role in the resurgence of the PC indie makes it hard for me to be angry with it.
Yeah, your issue with the pricing is really the only issue I have with it too. You'd think lower production cost would mean lower prices for the consumer.
You also raise a good point about indie developers. Through digital distribution, indie developers who might not have the resources to produce, package, and distribute 100 million physical copies of their games can get their games out to the masses.