/ Database Zone
Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Encrypt Your –Defaults-File

DZone 's Guide to

Encrypt Your –Defaults-File

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

by
·
Oct. 14, 16 · Database Zone ·
Free Resource

Comment (1)

Save
Tweet
{{ articles[0].views | formatCount}} Views

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 fu…

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.

$ sudo apt-get install gnupg gnupg-agent pinentry-curses

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:

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":

Bash Fu 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

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:

#!/bin/bash
set -e
declare -ra ARGS=( "${@}" )
declare -ri ARGV=${#ARGS[@]}
declare -rA ALIASES=(
   [smysql]=mysql
   [smysqldump]=mysqldump
   [smysqladmin]=mysqladmin
   [spt-show-grants]=pt-show-grants
   [spt-table-checksum]=pt-table-checksum
   [spt-table-sync]=pt-table-sync
   [spt-query-digest]=pt-query-digest
)

declare -r PROGNAME=$(basename ${0})
declare -r SEC_MYCNF=$(test -f ${1:-undef} && echo $_ || echo '.my.gpg')
declare -r SEC_FIFO=$(mktemp)
declare -a PASSTHRU=( "${ARGS[@]}" )
test ${ARGV} -gt 0 &&
test -f "${ARGS[0]}" &&
   PASSTHRU=( "${ARGS[@]:1}" )
set -u
function cleanup {
   test -e ${SEC_FIFO} && rm -f $_
   return $?
}

function decrypt {
   set +e
   $(which gpg) --batch --yes -o ${SEC_FIFO} -d ${SEC_MYCNF} >debug.log 2>&1
   test $? -eq 0 || $(which gpg) --yes -o ${SEC_FIFO} -d ${SEC_MYCNF} >debug.log 2>&1
   set -e
}

function check_cmd {
   local k
   local cmd=${1}
   for k in "${!ALIASES[@]}"; do
   test "${cmd}" = ${k} &&
   test -x "$(which ${ALIASES[${k}]})" &&
   echo $_ && return 0
   done
   return 1
}

function exec_cmd {
   local -r cmd=${1}
   set +u
   ${cmd} --defaults-file=${SEC_FIFO} "${PASSTHRU[@]}"
   set -u
}
function usage {
   local realfn=$(realpath ${0})
   cat <<EOS | fold -sw 120
    USAGE: $(basename ${0}) enc_file.gpg [--arg=val]
    use a GPG-encrypted my.cnf (default: ${SEC_MYCNF})
    currently supports:
    ${ALIASES[@]}
    create a symlink to match the alias (real app prefixed with 's')
    e.g.
    sudo ln -s ${realfn} /usr/local/bin/smysql
    sudo ln -s ${realfn} /usr/local/bin/spt-show-grants
    EOS
}

trap cleanup EXIT ERR
test -e ${SEC_MYCNF} || { usage; exit 1; }
cmd=$(check_cmd ${PROGNAME})
test $? -eq 0 || { echo ${ALIASES[${PROGNAME}]} is not available; exit 3; }
cleanup && mkfifo ${SEC_FIFO} && decrypt &
exec_cmd ${cmd}

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

Examples

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

Like This Article? Read More From DZone

A Gentle Introduction to Asymmetric Encryption and SSL Certificates
A Five-minute, No-jargon Introduction to Data Encryption
Transparent Data Encryption for Databases

Free DZone Refcard

Working With Time Series Data
DOWNLOAD
Topics:
credentials ,database ,security ,encryption

Comment (1)

Save
Tweet
{{ articles[0].views | formatCount}} Views

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

Database Partner Resources

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.linkDescription }}

{{ parent.urlSource.name }}
by {{ parent.authors[0].realName || parent.author}}
· {{ parent.articleDate | date:'MMM. dd, yyyy' }} {{ parent.linkDate | date:'MMM. dd, yyyy' }}
· {{ parent.portal.name }} Zone