I'm also trying to figure out why it makes sense to support an 8k
password and if we've really tried seeing what happens if pg_authid gets
a toast table that's actually used for passwords...
pg_authid.rolpassword stores a hash, so the password length does not affect it.
Of course, this also means that even in principle super-long passwords don't increase security, since one "can" (again, in principle) brute-force any password by guessing the first not-very-many-more-than-the-total-number-of-distinct-hashes possible passwords, starting with the shortest passwords and working up to longer passwords.
It's also obvious that past a certain point, longer passwords don't help anyway, because it's already enough to have a password that can't be guessed in, say, the expected duration of the Earth's existence using all the computing power currently available in the world.
I agree there should be a specific limit that is the same in libpq, on the server, and in the protocol. Maybe 128 characters, to get a nice round number? This is still way longer than the 32-byte SHA 256 hash. Or 64, which is still plenty but doesn't involve extending the current character buffer size to a longer value while still hugely exceeding the amount of information in the hash.