With some older houses, grounding to a water pipe was code. after pvc repairs became common that changed. Now the ground rod is used and the water pipes are grounded to that , cold near the house main and the hot water pipe is bonded to cold at the water heater. Thats with copper pipe , with plastic different story.

I believe now, that two ground rods sunk or buried 5′-6′ apart , connected to a continuous solid wire, or a stranded wire in conduit, are required for service mains. Some jurisdictions allow one rod with special testing.

I am possibly a little rusty on the code but that’s what I went through last time I installed a main, about 5 years ago.