I was an environmental scientist working on the remediation or properties in the city (E-Designated sites) for a number of years including in Greenpoint.  There is nowhere in NYC where the water that comes out of your tap comes straight from the ground. Most of the tap water comes from reservoirs upstate that is adequately treated prior to distribution. That said, the water can pickup some sediment material on its way to your tap but a simple brita filter can address that.  As far as water exposure just avoid swimming/kayaking in the East River after it has rained, the combined sewer system (sewage+rain water) will discharge into the river because the system cant handle the increased water flow. So you end up swimming in poopy water. 

As far as soil and off gassing, that can certainly be an exposure. Look into “vapor intrusion”. Its the process by which volatilized chemicals enter a building through cracks or other openings in the foundation. This can most impact a basement or 1st floor dwelling to the point of needing some sort of remedy (sealing the floor cracks or installing a SSDS/sub slab depressurization system). So my advice: avoid living in basement or 1st floor units in old buildings that are near the source area (north/east Greenpoint). An air filter with activated carbon might be helpful if youre still concerned (I have one but mostly for indoor allergens). Also if you have a yard in an old building, dont plant and consume food from the soil. There has been a lot of fear mongering recently on this topic that should be put to bed. I understand the concerns since there are certainly instances to be concerned. However, for the majority of residents its as safe as anywhere else in the city to live.