Page MenuHomePhabricator

MySQL "-p<password>" flag produces a warning in recent versions of MySQL
Open, WishlistPublic

Description

The suggested method to create MySQL database backups

phabricator/ $ ./bin/storage dump | gzip > backup.sql.gz

produces the following warning (stderr) on Ubuntu 16.04.1 (mysql-client-5.7)

mysqldump: [Warning] Using a password on the command line interface can be insecure.

While this warning does not matter when running the command manually, it is an issue when DB backups are scheduled via cron (which is the common use-case I assume). Suppressing stderr is not an option as other errors might go unnoticed.

How to reproduce:

  • Install Phabricator on Ubuntu 16.04.1
  • Execute above command

Event Timeline

chad claimed this task.
chad added a subscriber: chad.

It's not our intention to provide detailed examples for every scenario or operating system, given the tens of thousands of possible examples needed.

In T11433#188778, @chad wrote:

It's not our intention to provide detailed examples for every scenario or operating system, given the tens of thousands of possible examples needed.

It is not any scenario, it is the scenario described in the Phabricator configuration guide (/bin/storage command). Sooner or later any distro will ship with a mysqldump which complains about the way the password is handled.

A better way to think about this is, we only want Phabricator specific things in our documentation. Once an item falls outside of Phabricator, our expectation is you will manage that based on your preference / expertise. Unfortunately with a team of two people, maintaining a greater level of detail in the the documentation is significant and insurmountable.

There are just way better resources on the web for these kinds of things and we don't feel a need to duplicate them.

In T11433#189037, @chad wrote:

Can you point to me exactly where we state you should build a cron? https://secure.phabricator.com/book/phabricator/article/configuring_backups/

Cron is not mentioned explicitly, but no one does manual backups and I would assume that most use cron with the command example you provide.

But the problem is pretty much cron independent: A standard phabricator command displays an ugly warning about insecure password handling.

It is also annoying on the command line or anywhere else this command is executed.

mysqldump is not a phabricator command.

In T11433#189141, @chad wrote:

mysqldump is not a phabricator command.

mysqldump is an internal aspect of the standard phabricator command storage dump.

Oh, yeah that's probably a fair point. @epriestley ?

I think this is the corresponding MySQL bug:

https://bugs.mysql.com/bug.php?id=66546

This one is also related:

https://bugs.mysql.com/bug.php?id=68034

Briefly:

  • There is apparently no way to suppress or disable this warning.
  • The MySQL upstream generally seems disinclined to provide a way to suppress or disable it (it was added ~3 years ago)?

The alternative approaches appear to be:

  • Use MYSQL_PWD instead of -p. This is less secure but does not generate the warning.
  • Try to provide the password interactively. I don't see a way to do this immediately -- mysql appears to be doing weird direct tty magic or something and the parent TTY gets the prompt (which would prevent use of cron were we to rely on it). It's possible there's some kind weird mechanism for doing this using expect or bashisms that I'm not familiar with.
  • Write out a temporary file, pass that as a configuration file, then remove the temporary file. By default (e.g., power outage) this leaves your password in plain text in a temporary file on disk somewhere that you don't know about.
  • Maybe use mysql_config_editor to basically do the temporary file stuff after MySQL 5.6, but with the side effect of leaving the password in a config file on disk somewhere that you don't know about? (Apparently recommended.)
  • Specifically try to pick this warning out of stderr and suppress it.

Although it seems to be recommended, it's not really even clear that mysql_config_editor even works for us, since it seems like you still need to go interactive to set the credential in the first place:

$ /usr/local/mysql/bin/mysql_config_editor set --login-path=test1 --user=x --password=y
/usr/local/mysql/bin/mysql_config_editor: option '--password' cannot take an argument

All of these options feel extremely dumb, and this warning as a whole seems ill-conceived, particularly considering that the less-secure MYSQL_PWD does not warn. (The security risk here is that other users on the same host can observe passwords in ps output if they're fast enough to catch the command between the time it starts up and the time it rewrites its command line to blank the password, but no reasonable Phabricator install should give adversarial users shell on the same host. -pxxxx can be observed if you're fast, MYSQL_PWD can be observed always.)

It looks like this more-or-less works with expect on my system:

expect_script.txt
log_user 0
spawn mysqldump -p local_user
expect "Enter password:"
send "lol\n"
log_user 1
expect eof

I'm not sure how to safely escape the password for expect, and not sure how eager we should be to add this into the mix.

The "write to a temp file" approach is fairly easy (and is the best approach we currently have for a similar problem with ssh credentials) and probably the least bad of these various bad options, maybe.

Using MYSQL_PWD is clean and easy and does not meaningfully reduce security in this context, and there's a sort of poetic beauty in choosing a moderately less secure method in response to this warning, but I'm hesitant to make changes that reduce security like this, even when the reduction is symbolic.

We can also do nothing: this isn't a problem when running interactively, and the exit code for bin/storage is reliable when running from a script. Even if you have cron configured to email you all stderr output and don't want to change that, you can use shell tricks like subshell redirection to filter this warning:

$ sh -c 'echo "error\nwarning" 1>&2' 2> >(grep -v warning)
error
epriestley triaged this task as Wishlist priority.Aug 6 2016, 8:48 PM

The Aug 19 2015 reply from James Day in https://bugs.mysql.com/bug.php?id=66546 gives me some hope that MySQL may adjust behavior here. Although that comment is now almost a year old, it responds to a comment from about two and a half years prior, so this may actually be fairly new in MySQL timescales.

For now, I don't plan to implement any of the approaches above: they all increase complexity and I think none meaningfully improve security. With luck, MySQL will provide some remedy for this situation eventually. In the meantime, filtering these messages when integrating bin/storage into some more sophisticated pipeline is trivial.

epriestley renamed this task from MySQL database backup produces warning on Ubuntu 16.04.1 to MySQL "-p<password>" flag produces a warning in recent versions of MySQL.Aug 6 2016, 9:46 PM
epriestley removed chad as the assignee of this task.

I used the subshell redirection trick as mentioned by @epriestley to hide this warning from our cron output as well:

phabricator/bin/storage dump > "$database" 2> >(grep -v "Using a password on the command line interface can be insecure")

Edit: This only works properly in zsh, not in bash.

I'm not sure this works as expected.

phabricator/bin/storage dump > db.backup 2> >(grep -v "SOMETHING")

... shows no output as well. Which means you miss all errors on stderr, which might not be what you want.

It shows no output because there are no warnings. If you replace phabricator/bin/storage dump with a command that does give various formats of warnings on stderr, you see that it works:

$ sh -c 'echo "output1"; echo "error\nwarning\nerror2" 1>&2; echo "output2"' >foo.txt 2> >(grep -v warning)
error
error2
$ cat foo.txt
output1
output2

Ah, I see that it works differently in other shells. The above output was from zsh. In bash, indeed all output is written to the file:

$ sh -c 'echo "output1"; echo "error\nwarning\nerror2" 1>&2; echo "output2"' >foo.txt 2> >(grep -v warning)
$ cat foo.txt 
output1
output2
error
error2

Edited my post above to reflect this.

Yes, the behavior depends on the shell. I was using bash.

That's partly because "\n" isn't a newline in bash:

$ bash -c 'echo "output1"; echo "error\nwarning\nerror2" 1>&2; echo "output2"'
output1
error\nwarning\nerror2
output2

You might need some more sophisticated incantation like this:

epriestley@orbital ~/dev/phabricator $ bash -c 'echo "output1"; echo "error" 1>&2; echo "warning" 1>&2; echo "error2" 1>&2; echo "output2"' >foo.txt 2> >(grep -v warning 1>&2)
error
error2
epriestley@orbital ~/dev/phabricator $ cat foo.txt 
output1
output2

...or try my personal recommendation: don't write anything you care about working correctly in shell script.

The main problem here is that command "./bin/storage dump" exposes password on system process list in clear text. It happens due to way the storage tool calls mysqldump command. In my opinion this is universally applicable to every Linux distribution Phabricator can run on.

(shell) # ps -ef
phd 4691 4690 65 15:02 ? 00:00:47 php /var/opt/phd/phabricator/bin/storage dump --compress --output /mnt/backup/phd-storage-dump.sql.gz
phd 5206 4691 0 15:03 ? 00:00:00 sh -c mysqldump -p'_EXPOSED_PASSWORD_' '--hex-blob' '--single-transaction' '--default-character-set=utf8' '-u' 'root' '-h' 'localhost' '--max-allowed
phd 5207 5206 4 15:03 ? 00:00:02 mysqldump -px xxxxxxxxxxxxx --hex-blob --single-transaction --default-character-set=utf8 -u root -h localhost --max-allowed-packet 1G -- phabricator

As you can see, when the command is executed, mysqldump is displayed twice:

  • For "sh -c" process with PID 5206 (parent PID is 4691 - guilty storage dump). This line exposes password in the cleartext.
  • For "mysqldump" process with PID 5207 (parent PID is 5206). This line is safe with masked password.

I can live with this because no unpriviledged user has access to the system. But if you have users with shell access who don't need to know PHD database password, then this may be a vulnerability.

MYSQL_PWD can be observed always

I think that’s only the case if you’re root or the user that owns the process, and if an attacker has access to one of those then that's clearly bad news.

Anyway, meanwhile, I notice that MySQL grew a plugin to allow authentication of local users without passwords, so perhaps we can dare to dream of one day looking forward to a utopian passwordless future.