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.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

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:

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
credentials ,database ,security ,encryption

Published at DZone with permission of Ceri Williams. See the original article here.

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}