Home Blog Don't Worry About Using <tt>mysql -p</tt>

DEV

Don't Worry About Using mysql -p

Posted by antoine on Sept. 21, 2015, 10:40 a.m.

I very often use mysql -p because it's very convenient. For those who aren't familiar with it, mysql -p gives you the ability to specify the password on the command line, like this:

1
mysql -ppassword database

This is very convenient because:

  • You can see the password as you type it
  • If you exit your mysql terminal by mistake and want to reconnect, you just go one command up in your history, and you can reconnect without having to look the password up.

But amongst system administrators, there's is this cargo cult that says, "mysql -p is bad." Nobody knows why, but they were told it was bad.

Let's tackle the reasons why it could be bad:

The password is now stored and readable in your .bash_history

Well, usually when I use mysql -p, there's is a PHP application, running as the same user, with the password hardcoded in its configuration files.

So putting the password in .bash_history is not diminishing any security.

The password is visible with ps -eF (the standard compliant version of ps aux)

First of all, mysql client replaces the password in the command line so that the password is not readable anymore.

Of course, you still have the possibility that someone would run ps -eF within the 2 milliseconds between when mysql starts up and when it replaces the password by "xxxxxxxxx."

But people really worried about security usually mount /proc with hidepid=2.

The real problem is that you're using password authentication

As Chris Hadfield says "there is no problem so bad that you can't make it worse." However, authenticating to your database with password is already pretty bad:

This allows other system users to authenticate.

If you were using POSIX authentication you wouldn't have to worry about other users being able to see your password, since they wouldn't be able to connect.

Password are hard to manage

Managing passwords is complicated, insecure, and prone to mistakes. Passwords end up on post-it notes, and they add complexity if you want to connect to the system.

How to do secure database authentication

There are only two good ways to connect to your database:

  • Using POSIX authentication if your database is on the same system that runs your application.
  • Using SSL authentication if your database is on a remote system.

POSIX authentication

POSIX authentication maps the system user connecting to the database user with the same username. No password is needed, the only way this could be compromised would be if there was a kernel exploit. This is only possible if the application using your database runs on the same system.

On MySQL, create a user user@'localhost', for example for your application running under www-data create the user 'www-data'@'localhost' and give this user permission to use your database.

On PostgreSQL create a user "www-data" and enable the peer authentication.

SSL Authentication

SSL Authentication uses cryptography to authenticate to a remote server. This method is built-in PostgreSQL.

There use to be no built-in way to do it in MySQL, however this seems to have changed now. I've never used it since we don't use MySQL anymore at Fusionbox. The way I've done it in the past is by using stunnel and forward SSL-authenticated traffic to MySQL's unix socket.