Okay, I used to work in car rental so I'll tell you one of the reasons why you're offered rental car insurance.
It's extra "insurance" (but you cant call it insurance while your selling it) that you have to push on customers. Many rental car companies base promotions on sales of the coverages so basically, their jobs depend on selling to you.
The customer only benefits from the coverage if they do a huge amount of damage to the car because they wont have to pay a dime for the damage and they wont have to make a claim to their insurance company.
It does add extra cost because the fee is for each day of your rental.
I think it's ok to offer the "insurance" but not to have to stand there and make the customer say no 3 times before you stop offering it to them. If you really want the peace of mind that having the coverage can provide, you will purchase it. If you don't purchase it and the car is damaged, then the rental car company will still get money out of you.
If you do decline the insurance, and you have been pressured to purchase it, please be careful upon checking out your vehicle that you thoroughly inspect it for any pre-existing damage and that it is documented. Do this so that they don't try to blame damages on you.
I saw a lot of un-ethical practices in the rental car business in the short time I worked in the field, (number 1 reason I do not rent) so don't let it happen to you.
Hope this helps!