rain water fresh out of sky = safe
rain water that had to flow through your roof, insulation, and onto your ceiling = not safe
Essentially this, but let me extend this.
While some bacteria spores and viruses have been know to be distributed through rain water and clouds, it's highly unlikely that any living thing in the water would be infection to humans. There may however be concerns, if you like in a densely populated area near heavy traffic or industrial facilities, about dissolved pollutants. It is true that some kinds of pollution will be picked up by rainwater, most of the ones to be concerned about are heavily controlled. So, while I don't recommend it as a primary water source, unless pollution is particularly bad where you live, rain water collected using safe means, should be safe enough to drink.
As for water that's leaking into your house, there are a number of reasons
not to just drink that. The biggest reasons being contamination with random material from the building, possibly including things like drywall/plaster, fiberglass, arsenic (from some types of treated lumber), low levels of asbestos, mold spores, etc, etc. There's no way to say easily what could be in it. If you really wanted to, you could run it through a carbon filter (and maybe boil it, just to be sure), but at that point, you might as well just filter your tap water.
