Encrypt your –defaults-file

Encrypt your –defaults-file

Encrypt your --defaults-file using GPG

Encrypt your credentials using GPG

This blog post will look how to use encryption to secure your database credentials.

In the recent blog post Use MySQL Shell Securely from Bash, there are some good examples of how you might avoid using a ~/.my.cnf – but you still need to put that password down on disk in the script. MySQL 5.6.6 and later introduced the  –login-path option, which is a handy way to store per-connection entries and keep the credentials in an encrypted format. This is a great improvement, but as shown in Get MySQL Passwords in Plain Text from .mylogin.cnf, it is pretty easy to get that information back out.

Let’s fix this with gpg-agent, mkfifo and a few servings of Bash foo…

If you want to keep prying eyes away from your super secret database credentials, then you really need to encrypt it. Nowadays most people are familiar with GPG (GNU Privacy Guard), but for those of you that aren’t it is a free implementation of the OpenPGP standard that allows you to encrypt and sign your data and communication.

First steps…

Before we can go on to use GPG to encrypt our credentials, we need to get it working. GnuPG comes with almost every *nix operating system, but for this post we’ll be using Ubuntu 16.04 LTS and we’ll presume that it isn’t yet installed.

Once the packages are installed, there is a little configuration required to make things simpler. We’ll go with some minimal settings just to get you going. First of all, we’ll create our main key:

After typing a password and gaining sufficient entropy you will have your first key! You can show your private keys as follows:

We’ll now create our “gpg.conf” in which to keep a few settings. This sets the key that is used by default when encrypting, enables the gpg-agent and removes the copyright message.

Now we’ll add a few settings for “gpg-agent” and allow the key to be saved for one day to reduce the number of times you need to enter a password. Also, as this post concentrates on command line programs, we’ve enabled the ncurses pinentry to specify the password when requested.

You can find more information about setting up and using GPG in the GNU Privacy Handbook.

Encrypt your credentials

If all has gone well so far, you should be able to encrypt your first message. Here is a simple example to create armored (ASCII) output for a recipient with key “C38C02B0”:

Now that we have GPG working, we can secure our credentials and encrypt them to use later on. One of the default files MySQL reads is “~/.my.cnf”, which is where you can store your user credentials for easy command line access.

There you go, everything is nice and secure! But wait, how can anything use this?

Bash foo brings MySQL data to you

Most MySQL and Percona tools will accept the “–defaults-file” argument, which tells the program where to look to find what configuration to run. This will allow us to use our encrypted config.

The following script carries out the following actions:

  1. Creates a temporary file on disk and then removes it
  2. Creates a FIFO (a socket-like communication channel that requires both ends to be connected)
  3. Decrypts the config to the FIFO in the background
  4. Launches the “mysql” client and reads from the FIFO

You can use this script as you would normally with the “mysql” client, and pass your desired arguments. You can also optionally pass a specific encrypted config as the first argument:

There we go, MySQL access via an encrypted “–defaults-file” – and as long as your key is unlocked in the agent you do not need to enter the password.

But wait . . . what about all of the other tools that you might want to use? Well, with a slight tweak you can make the script a little fancier and get other tools to use the config, too (tools such as mysqladmin, mysqldump, pt-show-grants, pt-table-checksum, etc.). The key part of the next script is the specification of accepted commands (“ALIASES”) and the use of symbolic links to alias the script:

Now we can set up some symlinks so that the script can be called in a way that the correct application is chosen:


With some symlinks now in place we can try out some of the tools that we have enabled:

Enjoy some added security in your database environment, on your laptop and even on your Raspberry Pi!


Share this post

Comments (4)

  • Roel Van de Paar Reply

    wow Ceri, great post! It’s a mini-manual of GPG besides how to secure the defaults file. Thank you!

    It seems that the only caveat would be to make sure that the home directories are adequately protected. Otherwise, anyone (i.e. root, power users or worse) with access to them could impersonate access to the database server once they have read-only access to the script – correct?

    October 12, 2016 at 6:49 pm
  • Ceri Williams Reply

    Thanks Roel! Yes, you are correct, the usual precautions should be taken to secure access. This means keeping suitable file permissions, the use of access control lists, using a strong passphrase and 4096-bit encryption. If securing the system is outside of your control then you should either use a low max-cache-ttl, not use the agent at all (remove use-agent from gpg.conf), or not keep your key on the server (import before & delete after use, or use a separate trustdb and keyrings that can be removed from the server once done).

    October 13, 2016 at 4:27 am
  • Dave Rix Reply

    Nice post Ceri, certainly something I’ll be investigating should the need arises. 🙂

    October 13, 2016 at 5:22 am
  • David Bennett Reply

    Nice technique!

    October 13, 2016 at 11:02 pm

Leave a Reply