Encrypt your credentials using GPG
This blog post will look at how to use encryption to secure your database credentials.
In the recent blog post Use MySQL Shell Securely from Bash, there are examples of avoiding a ~/.my.cnf, but you still need to store credentials somewhere. MySQL 5.6.6 introduced the –login-path option, which stores credentials in an encrypted format. However, as shown in this post, those credentials can still be extracted.
Let’s improve this using gpg-agent, mkfifo, and some Bash techniques.
If you want to keep credentials secure, encryption is essential. GPG (GNU Privacy Guard) is a free implementation of OpenPGP that allows encryption and signing of data.
Install GPG (example uses Ubuntu 16.04):
|
1 |
$ sudo apt-get install gnupg gnupg-agent pinentry-curses |
Create a GPG key:
|
1 2 3 4 5 6 7 |
$ gpg --gen-key ... Real name: Ceri Williams Email address: notmyrealaddress@somedomain.com Comment: Encrypted credentials for MySQL ... Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O |
List keys:
|
1 2 3 |
$ gpg --list-secret-keys sec 4096R/C38C02B0 2016-10-06 [expires: 2021-10-05] uid Ceri Williams (Encrypted credentials for MySQL) |
Create GPG config:
|
1 2 3 4 5 |
$ cat <<EOF > ~/.gnupg/gpg.conf default-key C38C02B0 use-agent no-greeting EOF |
Create agent config:
|
1 2 3 4 5 |
$ cat <<EOF > ~/.gnupg/gpg-agent.conf pinentry-program /usr/bin/pinentry-curses default-cache-ttl 86400 max-cache-ttl 86400 EOF |
Test encryption:
|
1 2 3 4 |
$ echo hello | gpg -e --armor -r C38C02B0 -----BEGIN PGP MESSAGE----- ... -----END PGP MESSAGE----- |
Encrypt a MySQL config file:
|
1 2 3 4 5 6 7 8 9 |
$ cat <<EOF | gpg --encrypt --armor -r C38C02B0 -o ~/.my.cnf.asc [client] user = ceri password = mysecretpassword [mysql] skip-auto-rehash prompt = "smysql d> " EOF |
The following script decrypts credentials into a FIFO and passes them to MySQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
#!/bin/bash set -e SEC_MYCNF='.my.cnf.asc' SEC_FIFO=$(mktemp) cleanup() { rm -f "$SEC_FIFO" } decrypt() { gpg --batch --yes -o "$SEC_FIFO" -d "$SEC_MYCNF" } trap cleanup EXIT mkfifo "$SEC_FIFO" decrypt & mysql --defaults-file="$SEC_FIFO" |
Usage:
|
1 |
$ ./smysql.sh .my.test.asc |
You can extend this approach to other tools like mysqldump, mysqladmin, and Percona Toolkit tools by mapping commands and using symlinks.
|
1 2 |
$ ln -s ~/bin/smysql.sh ~/bin/smysql $ ln -s ~/bin/smysql.sh ~/bin/smysqladmin |
|
1 2 3 4 5 6 7 8 |
$ ~/bin/smysql -Bsse 'select 1' 1 $ ~/bin/smysqladmin proc ... $ ~/bin/spt-show-grants --only root@localhost | head -n3 ... |
Enjoy improved security for your database credentials across environments.
Resources
RELATED POSTS
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?
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).
Nice post Ceri, certainly something I’ll be investigating should the need arises. 🙂
Nice technique!