Rob Seaman

I was using Capistrano 2.5.2 to run a MySQL dump on a deployment target and what started as a simple implementation to prevent the command line password from showing up in the Capistrano log evolved into a research effort about the security of using passwords on the MySQL command line. This post describes how to suppress run execution lines from showing up in the Capistrano log, the real risks with using passwords on the MySQL command line and how to use Capistrano to avoid command line passwords without putting the password in a ~/.my.cnf file.

I'll start from the beginning ... the Capistrano logger level is set to DEBUG by default and that caused MySQL command line passwords to be displayed/logged whenever MySQL was invoked via run. I didn't like that so I came up with a very direct solution to hide the password and leave everything else alone. Here's the code segment from a Capistrano task to do that:

98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

require 'yaml'

database_yml = ""
run "cat #{current_path}/config/database.yml" do |_, _, database_yml| end
config = YAML::load(database_yml)['production']

mysql_dump = "mysqldump -u #{config['username']} -p#{config['password']} "+
  "-h #{config['host']} #{config['database']} > #{current_path}/tmp/dump.sql"

# surpress debug log output to hide the password
current_logger_level = self.logger.level
if current_logger_level >= Capistrano::Logger::DEBUG
  logger.debug %(executing "#{mysql_dump.sub(/-p\S+/, '-px')}")
  self.logger.level = Capistrano::Logger::INFO 
end

run mysql_dump do |ch, _, out| 
  puts out 
end

# restore logger level
self.logger.level = current_logger_level

It worked and I was satisfied for a while but a FIXME comment in a Joyent CodeSnippets post with similar code started bothering me:

22
23

# FIXME pass shows up in process list, 
# do not use in shared hosting!!! Use a .my.cnf instead

I knew, or I thought I knew, that MySQL took care of hiding passwords on the process list but I decided to verify how they did it. I found a lot of posts from from people claiming there was no protection at all and several others that like me thought MySQL had the issue resolved. Eventually I looked at the C source and found some good posts. Here's what I found:

There's a window of time when MySQL command line passwords are visible on the process list. That's not to say that you can always just grab MySQL command line passwords with ps -ef as many posts claim. Despite posts to the contrary, MySQL is smart enough to try to hide command line passwords and, for the most popular Unix systems, this works. In general the window of vulnerability only exists between the time a MySQL tool is launched and the time the MySQL code does the hiding (see here). Since it processes command line arguments very early, it's a small window. I read claims that you could put ps in a loop to exploit the window and find passwords. I never saw any proof that something so simplistic would work and given the frequency of command execution and the small window size I have my doubts. On the other hand, I'm sure it's possible to come up with some programatic way to exploit the window and then there's always the chance that someone could have lucky timing with a single ps -ef.

The hiding is done by modifying the password argv like this: while (*argument) *argument++= 'x'; then if it's the last argument it gets truncated to 0 length. From my search on the MySQL 5.1.30 source this hiding technique appears to be done for all commands, it's definitely done for mysql, mysqldump and mysqladmin. I already mentioned the window of vulnerability but there are two additional issues: First, unless the password is the last command line argument, its length can be determined by counting the x's. I didn't look at source for older versions but the x'ing out seems to have been in place for a very long time. On a 4.0.23 implementation that I have on phpwebhosting.com the x'ing out worked but the truncation didn't, from this post it appears to have been fixed in 4.1.

The bigger issue is that the method won't work for all Unix systems. It seems to be fine for some systems like Linux and Darwin (OS X) but not for others like Slackware that don't overwrite the process list entries when argv's are changed. The theory is that it works for the BSD-ish Unix versions and not for the SysV-ish flavors. May be, but during my search I did see what looked like a credible report that one user could see passwords after moving from Linux to FreeBSD. I'm curious about that but not so curious that I'm willing to create a FreeBSD VM with MySQL to check it out. It's easy to verify whether or not password hiding works on your system by starting mysql and then opening up another process and running ps -ef. On most Unix system you'll see the password x'd out as in -px xxxxxxxx or just -px. -px will show up alone if the -p option is the last command line argument.

In many environments the issues I mentioned just aren't real problems. Even some shared hosting environments manage to keep the process list private, this is true on DreamHost for example, so it's only an issue within your own accounts. OTH in other environments it is a big deal. Even if the password hiding works, if your processes show up when someone else does a ps -ef at just the right time they'll get your password. Now, I'm not trying to say that the MySQL password hiding is bogus. Security is almost always a matter of degree and the technique used protects passwords far better then doing nothing. That said, it seems wise not to use a command line password in a shared environment if it can be avoided and fortunately that's easy.

Avoiding the MySQL --password Option with Capistrano

The general recommendation I found to avoid the --password option is in line with the comment from the Joyent CodeSnippets post, put the password in ~/.my.cnf. That allows you to avoid command line passwords with any scripting tool including Capistrano. The downside is it means another configuration step to setup ~/.my.cnf and you have a plaintext password in an additional file (it's already in database.yml for Rails). In my Capistrano script I'm already loading the production server's database.yml so I decided to handle it in the run block by interactively responding to MySQL's password prompt. Here's the code fragment:

98
99
100
101
102
103
104
105
106
107
108
109
110
111

require 'yaml'

database_yml = ""
run "cat #{current_path}/config/database.yml" do |_, _, database_yml| end
config = YAML::load(database_yml)['production']

run "mysqldump -u #{config['username']} -p -h #{config['host']} "+
  "#{config['database']} > #{current_path}/tmp/dump.sql" do |ch, _, out| 
  if out =~ /^Enter password: /
    ch.send_data "#{config['password']}\n"
  else
    puts out 
  end
end

Since we're using a SSH channel the password is protected and as a bonus the code for suppressing log output is no longer needed making the code segment shorter and cleaner. If you want to see the task that actually uses this snippet then take a look at the the data:cache_prod_data task in the Production Data to Development post.

Limitations

The run code block solution is for executing on a remote server. It doesn't address executing MySQL commands from the local deployment client. Before trying to solve it you should consider whether or not you really need to. There's no logging issue when executing local commands and the process list security limitations aren't as likely to be a problem; they weren't for me. But, if this is something you need to or want to take care of then you'll need to use ~/.my.cnf or investigate ways to respond to the MySQL password prompt. Handling the prompt would mean leaving the simple Ruby subprocess methods like %x, `, exec and system behind. PTY.spawn and expect would probably work and look very similar to the run code block but pty is Unix specific. You may also be able to get popen to work but I'm not sure how you'd handle the prompting with popen.