How can you make SSH read the password from stdin, which it doesn't do by default?
You can't with most SSH clients. You can work around it with by using SSH API's, like Paramiko for Python. Be careful not to overrule all security policies.
Any idea why it doesn't work? Intuitively it should.
It shouldn't work, because that would inspire junior sysadmins to write scripts with the SSH password entangled within the logic in the script. In an environment for network and system administration, there needs to be a clear separation between security-sensitive information on the one hand and getting things done on the other. Password authentication is just not safe enough. Since ssh is widely used in sysadmin contexts, trying to keep users away from password-only security is a good idea.