Does homeowners insurance cover water damage when water main was not turned off when trying to fix a leaky shower faucet?

If you elected "Accidental Water Discharge" coverage when you purchased your policy then yes, you should have coverage up to the specified limits for this type of loss.