It’s entirely possible for a city to completely run out of water. Eventually the water table dissipates and your pumping stations run dry. There’s a couple of ways you can work around this, ensuring your city will always have a clean water supply.
Rain, water from shorelines and sewage treatment plants will all slowly replenish the water table. The best option is to build a water pump next to your sewage treatment plant. The sewage treatment plant will replenish the water table as fast as the water pump is depleting it, thus providing the effect of infinite water. Make sure you’re using a filtration pump module, otherwise you run the risk of pumping germ infested, polluted water to your Sims.