On fittings that prevent locking themselves like bolts nuts, seatpost, etc any grease would do.
Where it matters most is bearings/gears/chains/etc. For bearings I use Mobil1 lithium grease (for automotive bearings ~8$/kilogram).

And where the proper grease makes all the differences is in heavy machinery that must not wear down, must not break, etc. (airplanes, industrial machinery, cars, etc - bikes do not fall in this category, we are talking about light stuff - almost any general grease will do here)

About that "water" developing. the grease is not going bad, it is normal for hygroscopic grease like calcium based. That water is better to be flushed out (drip the can upside down, the grease should not fall, water will). But anyway it's not a problem since the grease applied on exposed threads will absorb water anyway.