As I see it, character limits aren't so much about security, as just a dumb way to be hostile to the user. All of my passwords are site-specific unique passwords generated by a password manager. I don't care if you store plain-text passwords, because if someone steals passwords out of your database then they already have all the access that my password to your site would've given.
But if a site rejects the password that my password manager generated (16 chars [a-zA-Z0-9]), then I have to work around it, make a password manually, and it's generally a pain in the ass that shouldn't be necessary. And since I'm doing it right and these sites doing it wrong, I'm not inclined to be forgiving.
The worst thing is when password boxes have paste protection so site-specific randomly generated passwords become a pain to use. A few sites have started doing it recently, it's nonsensical.
> if someone steals passwords out of your database then they already have all the access that my password to your site would've given
I see where you are coming from, however this premise is fatally flawed.
If my password is stored in plain text then an attacker needs only to read just my password and they have access to my account.
If the site is susceptible to SQL injection attacks it is perfectly reasonable that they can extract my password without having access to any other part of the database or system.
Well, you obviously have to define "long" and "any" in that sentence. The famous xkcd cartoon evaluates a four word "phrase" (four common random words) as 44 bits of entropy. But he's not comparing it to a random alpha-numeric string, he's comparing it to taking an uncommon word and doing a couple of letter substitutions to defeat complexity requirements.
A real random alpha-numeric password (what I get my password manager to generate, since I don't have to remember it) 12 characters long is more like 70 bits of entropy. You'd need 6 random words to match that. Essentially for every 2 random alphanumeric characters you need another random word.
If you read 128 bits of data from /dev/urandom, and then map the result to a space of 2128 possible passwords, then it doesn't actually matter what the possible passwords are, as long as it's a one-to-one mapping.
"But if a site rejects the password that my password manager generated (16 chars [a-zA-Z0-9])...it's generally a pain in the ass"
No, that just means your password manager sucks and is out of touch with the real world. Any good password manager lets you quickly generate passwords of any length.
But if a site rejects the password that my password manager generated (16 chars [a-zA-Z0-9]), then I have to work around it, make a password manually, and it's generally a pain in the ass that shouldn't be necessary. And since I'm doing it right and these sites doing it wrong, I'm not inclined to be forgiving.