Tom Raftery, a fantastic bankruptcy attorney who has answered many questions for me over the years, http://www.rafterylaw.com/, wondered whether he should sign the loss/damage waiver when he rents a car, or pay the extra charge for insurance. I too always have that concern before I hope for the best and initial the waiver at the car rental counter.
Judy Bearfield at DeGuglielmo Insurance Agency in Medford (my own insurance agent, who I highly recommend) told me that the standard Massachusetts auto policy covers rental cars in the United States and Canada. Other countries, including Mexico, are not covered.
I confirmed this by reviewing the current standard Massachusetts Auto Policy ("the Seventh edition" for those in the know). The optional collision coverage provides coverage for "collision damage to other private passenger autos while being used by you or a household member with the consent of the owner." This would include rental cars.
So the bottom line is that your auto policy provides collision coverage on a rental car to the same extent as it does on your own car. Keep in mind that collision coverage is optional; if you have a low-value car you may not have purchased it. In addition, the rental car company may argue that it suffered damages (for example, loss of the rental value of the car while it is being repaired) that may not be covered by the collision coverage of your policy.
Following up on Judy Bearfield's comment that rental cars are covered only in the United States and Canada, the "Where You Are Covered Provision" of the auto policy states that the Compulsory Bodily Injuries To Others (providing $20,000 in coverage if you injure someone else in a car accident) applies only to accidents in Massachusetts. All other coverages apply only to accidents in the United States or Canada.
I should add that with the recent deregulation of auto policies in Massachusetts, as of April 1, 2008 each auto insurer can file its own endorsements that change the optional portions of the policy.
Finally, some credit card companies provide coverage for rental cars--but, given the constant changes to credit card terms, even if you think your card provides coverage you should double check, and ask closely about the terms.