Oct
12
2016
--

Encrypt your –defaults-file

encrypt

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.

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

$ gpg --gen-key
gpg (GnuPG) 1.4.12; Copyright (C) 2012 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Please select what kind of key you want:
(1) RSA and RSA (default)
(2) DSA and Elgamal
(3) DSA (sign only)
(4) RSA (sign only)
Your selection? 1
RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (4096)
Requested keysize is 4096 bits
Please specify how long the key should be valid.
0 = key does not expire
<n> = key expires in n days
<n>w = key expires in n weeks
<n>m = key expires in n months
<n>y = key expires in n years
Key is valid for? (5y)
Key expires at Tue 05 Oct 2021 23:59:00 BST
Is this correct? (y/N) y
You need a user ID to identify your key; the software constructs the user ID
from the Real Name, Comment and Email Address in this form:
"Heinrich Heine (Der Dichter) <heinrichh@duesseldorf.de>"
Real name: Ceri Williams
Email address: notmyrealaddress@somedomain.com
Comment: Encrypted credentials for MySQL
You selected this USER-ID:
"Ceri Williams (Encrypted credentials for MySQL) <notmyrealaddress@somedomain.com>"
Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You need a Passphrase to protect your secret key.

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

$ gpg --list-secret-keys
/home/ceri/.gnupg/secring.gpg
-----------------------------
sec 4096R/C38C02B0 2016-10-06 [expires: 2021-10-05]
uid Ceri Williams (Encrypted credentials for MySQL) <notmyrealaddress@somedomain.com>

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.

$ cat <<EOF > ~/.gnupg/gpg.conf
default-key C38C02B0
use-agent
no-greeting
EOF

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.

$ cat <<EOF > ~/.gnupg/gpg-agent.conf
pinentry-program /usr/bin/pinentry-curses
default-cache-ttl 86400
max-cache-ttl 86400
EOF

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

$ echo hello | gpg -e --armor -r C38C02B0
-----BEGIN PGP MESSAGE-----
Version: GnuPG v1
hQIMA/T3pqGixN5nAQ/+IxmmgoHNVY2IXp7OAQUZZtCw0ayZu/rFotsJBiQcNG4W
J9JZmG78fgPfyF2FD4oVsXDBW7yDzfDSxCcX7LL9z4p33bzUAYOwofRP9+8qJGq/
qob1SclNN4fdFc/PtI7XKYBFYcHlfFeTIH44w9GEGdZlyfDfej+qGTJX+UHrKTo3
DaE2qpb7GvohEnDPX5WM0Pts3cATi3PcH4C9OZ5dgYizmlPB58R2DZl1ioERy2jE
WSIhkZ8ZPW9ezWYDCtFbgFSpgynzYeFRVv1rel8cxZCSYgHOHrUgQM6WdtVFmEjL
ONaRiEA9IcXZXDXaeFezKr2F8PJyaVfmheZDdRTdw54e4R6kPunDeWtD2aCJE4EF
ztyWLgQZ0wNE8UY0PepSu5p0FAENk08xd9xNMCSiCuwmBAorafaO9Q8EnJjHS/w5
aKLJzNzad+8zKq3zgBxHGj1liHmx873Epz5izsH/lK9Jwy6H5qGVB71XuNuRMzNr
ghgHFWNX7Wy8wnBnV6MrenASgtCUY6cGdT7YpPe6pLr8Qj/3QRLdzHDlMi9gGxoS
26emhTi8sIUzQRtQxFKKXyZ43sldtRewHE/k4/ZRXz5N6ST2cSFAcsMyjScS4p2a
JvPvHt4xhn8uRhgiauqd7IqCCSWFrAR4J50AdARmVeucWsbRzIJIEnKW4G/XikvS
QQFOvcdalGWKMpH+mRBkHRjbOgGpB0GeRbuKzhdDvVT+EhhIOG8DphumgI0yDyTo
Ote5sANgTRpr0KunJPgz5pER
=HsSu
-----END PGP MESSAGE-----

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.

$ cat <<EOF | gpg --encrypt --armor -r C38C02B0 -o ~/.my.cnf.asc
[client]
user = ceri
password = mysecretpassword
[mysql]
skip-auto-rehash
prompt = "smysql d> "
EOF

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
#!/bin/bash
set -e
declare -ra ARGS=( "${@}" )
declare -ri ARGV=${#ARGS[@]}
declare -r SEC_MYCNF=$(test -f ${1:-undef} && echo $_ || echo '.my.cnf.asc')
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 exec_cmd {
  local -r cmd=${1}
  set +u
  ${cmd} --defaults-file=${SEC_FIFO} "${PASSTHRU[@]}"
  set -u
}
trap cleanup EXIT
test -e ${SEC_MYCNF} || exit 1
cleanup && mkfifo ${SEC_FIFO} && decrypt &
exec_cmd /usr/bin/mysql

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:

$ ./smysql.sh .my.test.asc
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 56
Server version: 5.7.14-8 Percona Server (GPL), Release '8', Revision '1f84ccd'
Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
smysql (none)>

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:

#!/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:

$ mkdir -p ~/bin
$ mv smysql.sh ~/bin
$ ln -s ~/bin/smysql.sh ~/bin/smysql
$ ln -s ~/bin/smysql.sh ~/bin/smysqladmin
$ ln -s ~/bin/smysql.sh ~/bin/spt-show-grants

Examples

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

$ ~/bin/smysql -Bsse 'select 1'
1
$ ~/bin/smysqladmin proc
+----+------+-----------+----+---------+------+----------+------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------+----+---------+------+----------+------------------+-----------+---------------+
| 58 | ceri | localhost | | Query | 0 | starting | show processlist | 0 | 0 |
+----+------+-----------+----+---------+------+----------+------------------+-----------+---------------+
$ ~/bin/spt-show-grants --only root@localhost | head -n3
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.7.14-8 at 2016-10-07 01:01:55
-- Grants for 'root'@'localhost'

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

Feb
27
2014
--

DBA 101: Sometimes forgotten functionality in the MySQL client

The MySQL client has some functionalities some of us never use. Why would you use them and what is the added value of this?

Every DBA and developer has had a moment when he or she needs to connect to a MySQL database using the command line tool. Therefore I’ve written down an explanation of some command line commands you can insert in the CLI, most of them give added value and make your experience with the cli more enjoyable.

prompt

Who has never witnessed the scary feeling of not being connected to the write database when having several terminals open. I do, due to the fact I use the prompt functionality.

mysql >\R Production >
PROMPT set to 'Production > '

Or you can go a bit further and visualise the user, host and active database in:

mysql > \R \u@\h [\d]>
PROMPT set to '\u@\h [\d]>'
root@testbox [test]>

edit

In some situations editing the query in an editor instead of the cli can have several enhancements. It gives you the ability to fix typos, have a deep look at the queries before you submit them and etc.
If you’d like to edit the query you are making in your default editor instead of using the cli.

mysql> \e

The editor appears, in which you can create your query/ies.

use sakila;
select * from city
limit 10;
~
~
~

After closing this down and putting a delimiter in the cli, this query will be run against the database while outputting in the prompt.

mysql> \e
-> ;
+---------+--------------------+------------+---------------------+
| city_id | city               | country_id | last_update         |
+---------+--------------------+------------+---------------------+
|       1 | A Corua (La Corua) |         87 | 2006-02-15 04:45:25 |
|       2 | Abha               |         82 | 2006-02-15 04:45:25 |
|       3 | Abu Dhabi          |        101 | 2006-02-15 04:45:25 |
|       4 | Acua               |         60 | 2006-02-15 04:45:25 |
|       5 | Adana              |         97 | 2006-02-15 04:45:25 |
|       6 | Addis Abeba        |         31 | 2006-02-15 04:45:25 |
|       7 | Aden               |        107 | 2006-02-15 04:45:25 |
|       8 | Adoni              |         44 | 2006-02-15 04:45:25 |
|       9 | Ahmadnagar         |         44 | 2006-02-15 04:45:25 |
|      10 | Akishima           |         50 | 2006-02-15 04:45:25 |
+---------+--------------------+------------+---------------------+
10 rows in set (0.03 sec)

tee

Performing critical maintenance on a database could require to have an entire log of performed queries and actions. You can activate the full output of the MySQL client, including your performed queries. This utility is ideal if you prefer having a log of all of your actions. This could be for documentation stakes or a way to reread your actions if issues would occur.

Example:

mysql> \T /tmp/tee.log
Logging to file '/tmp/tee.log'

This will provide in the output the queries you perform.

dim0@testing101:~$ cat /tmp/tee.log
mysql> select * from city limit 5;
+---------+--------------------+------------+---------------------+
| city_id | city               | country_id | last_update         |
+---------+--------------------+------------+---------------------+
|       1 | A Corua (La Corua) |         87 | 2006-02-15 04:45:25 |
|       2 | Abha               |         82 | 2006-02-15 04:45:25 |
|       3 | Abu Dhabi          |        101 | 2006-02-15 04:45:25 |
|       4 | Acua               |         60 | 2006-02-15 04:45:25 |
|       5 | Adana              |         97 | 2006-02-15 04:45:25 |
+---------+--------------------+------------+---------------------+
5 rows in set (0.00 sec)

Whenever, you have only access to the MySQL interface and you need to access one of your created files to see what the output is, you can do so using the \! keystroke, which will execute system commands.
Running a shell command from the MySQL command line interface:

mysql> \! cat /tmp/tee.log
mysql> select * from city limit 5;
+---------+--------------------+------------+---------------------+
| city_id | city               | country_id | last_update         |
+---------+--------------------+------------+---------------------+
|       1 | A Corua (La Corua) |         87 | 2006-02-15 04:45:25 |
|       2 | Abha               |         82 | 2006-02-15 04:45:25 |
|       3 | Abu Dhabi          |        101 | 2006-02-15 04:45:25 |
|       4 | Acua               |         60 | 2006-02-15 04:45:25 |
|       5 | Adana              |         97 | 2006-02-15 04:45:25 |
+---------+--------------------+------------+---------------------+
5 rows in set (0.00 sec)

status

In some cases you’d like seeing the parameters currently active on your MySQL client. Therefore you can actually use the \s command. This command will clarify which of the options are active on the client. The info which is shown should not be confused with SHOW VARIABLES. which is focussed on the connection variables.
Trigger the status information of your connection using \s:

mysql> \s>
--------------
mysql  Ver 14.14 Distrib 5.6.15, for Linux (x86_64) using  EditLine wrapper
Connection id:		13149
Current database:	sakila
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.6.15-63.0-log Percona Server (GPL), Release 63.0
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			10 days 23 hours 32 min 57 sec
Threads: 1  Questions: 1203169  Slow queries: 43745  Opens: 626  Flush tables: 1  Open tables: 178  Queries per second avg: 1.268
--------------

clear

Clearing your current input query. Use \c to clear the input field:

mysql> SELECT *
-> FROM city
-> \c
mysql>

pager

Honestly one of the more useful tools in the mysqlclient is actually pager. For people prone to typing queries while forgetting adding a limit if they don’t need to view the full output.
‘\P less’ will output the query data using the UNIX command less.
You can also choose to output the query results in a parsable format on the filesystem using ‘\P cat > voila.log’.

for example:

mysql> \P cat > /tmp/voila.log
PAGER set to 'cat > /tmp/voila.log'
mysql> SELECT *  FROM city;
600 rows in set (0.01 sec)

This will create the file ‘voila.log’ in which only the output is saved of the query. This solution mimics the ‘SELECT INTO OUTFILE’ query.

During optimisation of your workload, it can be interesting to see if a query you’ve modified has the same query output

mysql >\P md5sum
PAGER set to 'md5sum'
 root@127.0.0.1 [sakila]>select * from city limit 10;
449d5bcae6e0e5b19e7101478934a7e6  -
10 rows in set (0.00 sec)
 mysql >select city_id, city, country_id, last_update FROM city LIMIT 10 ;
449d5bcae6e0e5b19e7101478934a7e6  -
10 rows in set (0.00 sec)

Other commands

There are of course several other options you might use. You can get an overview of all the internal cli functions through \?.

Disabling the tee and pager commands described in this blogentry can be done with \t or notee, \n or \nopager.

The post DBA 101: Sometimes forgotten functionality in the MySQL client appeared first on MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com