Usually, when a page requires the same password to be entered twice, it is in order to detect typing errors -- which are more common with passwords because of the "blind entry" thing. In particular with registration pages, because a wrongly entered password implies a recovery procedure later on, procedure which necessarily has a non-zero cost. Stating that the double entry is for "security reasons" is just a way to make the user comply; users are accustomed to go through weird hoops as long as it is a "matter of security". But this is not really about security.
More generally, there is a delicate balance between some desirable characteristics:
- The user shall accept to comply with the security features.
- The user shall gain confidence in the system being secure.
- The system shall be secure.
- The user should be able to behave in a non-security-obsessed way.
Point 4 is important if the user is a potential customer and we want him to finally enter his credit card number and buy stuff. Point 3, of course, is important if you want to avoid trouble. Point 2 is about the "peace of mind". Point 1 means that the user may become the enemy quite fast.
These characteristics are not independent from each other. For instance, if you want a secure system (point 3) and thus require users to have long passwords (e.g. more than 12 characters), then users will rebel and begin to select long-but-weak passwords, or write them down on paper notes (failure on point 1, implying a failure on point 3). Talking too much about security may make some users obsessed about it. Building user confidence is also part (but only part) of making the user non-paranoid.
An analogy can be made with airport security. System security (point 3) is achieved through various hidden measures, most of which being luggage X-ray scanning, and an awful lot of police intelligence work on travelers. User confidence (point 2) is built through a display of visible security features, such as full-body scanners and hordes of mean-looking guards. Here, user confidence is about making people aware that the power-that-be are doing something about the security problems that they worry about; however, it is not really necessary that the security features that the users see are also the security features that actually enhance security. Use compliance (point 1) is enforced by those scary placards which warn you, as an airplane travelers, that "making statements about security" can plunge you into deep trouble, including missing your plane, paying a big fine, or possibly going to jail. To some extent, travelers are made non-paranoid (point 4) by exposing them to airport employees who all look utterly obsessed about security; the traveler instinctively reacts by taking the opposite stance. All of this, of course, is expensive (a full-body scanner is not the cheapest piece of hardware ever, and guards receive wages on a regular basis).
So there is no harm in having a security feature which is useless with regards to actual security, as long as it provides some gain somewhere, e.g. in building user confidence. However, there may be some cost involved, and since human beings are not machines, assessing that cost can prove difficult.