Why can't I pipe a password into mysql non-interactively?

Question

I want to run mysql client, giving the password non-interactively.

The standard solution is this

mysql -u root -e "foo" -p < password_file

but my situation is this

produce_password | mysql -u root -p

Here, mysql prompts for a password, even though data is being piped in. (Yes, produce_password is emitting data; echo foo | mysql behaves the same way.)

The Internet seems to think the above should work, but the fact is it doesn't. The workaround would be

produce_password > password_file
mysql -u root -p < password_file
rm password_file

But let's say I don't want to do this (e.g. policy demands that this password never be written to the disk)

How can I make mysql take the password from the input process without prompting, as it would for a file?

Answer 1

I don't know how to explain this, but when you pipe something, the stdout of the first program is forwarded to the stdin of the second program. You somehow confuse this, with the command line or whatever. You can pipe something to mysql, of course, but whatever you pipe is handled after you've authenticated yourself.

The solution would be

mysql -u root -p$(produce_password)

This generates your password with whatever program you have there and puts it in the right place on your commandline (my english is bad, can't explain better). When you have it in a file you could do

mysql -u root -p$(cat file)

I don't know, why you want to do it this way anyway, but you might be interested in having an encrypted file with your credentials, that you can use to log in without specifying a password. Read more about it here.

Answer 2

With thanks to fancyPants for explaining the cause, here is a solution which meets my requirements. (The encrypted .mylogin.cnf with mysql_config_editor isn't right for me, but thanks.)

To satisfy the security policy, mount /ramfs as a ramfs temporary file system. Assume file permissions are suitably restrictive.

ramdir="/ramfs"
cnf="$(mktemp "$ramdir/this-script-name-XXXXX")"
pw="$(produce_password)"

cat >"$cnf" <<EOF
[client]
user=root
password="$pw"
EOF

mysql --defaults-extra-file="$cnf" -e 'select 1'

rm "$cnf"