Stream encrypting/decrypting along with gzipping, etc. originally for database dumps

0-How-To.md

When backing up databases/table to files I needed to have them encrypted for security and compliance. But there were a few concerns:

1. I didn't want the file to be in plaintext, ever.
2. I didn't want that same user to be able to decrypt the file later in case the account was compromised.

So, Pub/Priv is great for #2. Unfortunately, Pub/Priv is not made for large files, therefor I decided follow this process:

1. Generate symetric key
2. Encrypt files in-line w/ symetric key using AES-256-CBC
3. Encrypt symetric key w/ public-key encryption (from a 4096-bit private key)

Info on types of encryption used:

• AES-256
• CBC
• Public-Key

Here are some setup things:

# Generate private key (I would place this in your /root/.ssh/ folder or some place safer)
openssl genrsa -out private_key.pem 4096

# Generate public key (this can go anywhere, just make sure the user that runs the encryption can read it. I use ~/.ssh/ of the user)
openssl rsa -in private_key.pem -out public_key.pem -outform PEM -pubout

# Public key used to encrypt symmetric key (make sure this points to where yours is)
CERTFILE=~/.ssh/public_key.pem

# Randomly generate the parts of the symmetric key and initialization vector
ENC_IV=$(openssl rand 32 -hex) # 32 bytes = (32*8) = 256-bit
ENC_KEY=$(openssl rand 32 -hex) # 32 bytes = (32*8) = 256-bit

## Command used to encrypt in-line
# "openssl" is self-explanatory
# "enc" is the sub-app inside openssl that does encryption
# "aes-256-cbc" is the type of cypher used
# "-e" means encrypt, use "-d" when decrypting
# "-iv ${ENC_IV}" is where I'm passing in the initialization vector we generated
# "-K ${ENC_KEY}" is where I'm passing in the symmetric key we generated
# more info: https://www.openssl.org/docs/manmaster/apps/enc.html
STREAM_ENCRYPT_COMMAND="openssl enc -aes-256-cbc -e -iv ${ENC_IV} -K ${ENC_KEY}"

# File name/location stuff
DEST_FOLDER=/data/backups/
OUTPUT_FULL_PATH=${DEST_FOLDER}/test_file

# Save the symmetric key and IV to a pub key encrypted file separated with a ":"
echo "${ENC_IV}:${ENC_KEY}" | openssl rsautl -encrypt -inkey ${CERTFILE} -pubin > ${OUTPUT_FULL_PATH}.key

Do your thing and encrypt it in-stream

<mysqldump, tar, curl, or whatever you do> | ${STREAM_ENCRYPT_COMMAND} > ${OUTPUT_FULL_PATH}.bin

You can also do more things in-stream like gzip it before encrypting it:

<mysqldump, tar, curl, or whatever you do> | gzip | ${STREAM_ENCRYPT_COMMAND} > ${OUTPUT_FULL_PATH}.gz.bin

This creates two files The key file: /data/backups/test_file.key The encrypted file: /data/backups/test_file.bin -or- /data/backups/test_file.gz.bin

To decrypt, you reverse the process:

1. Decrypt the key file using the private key
2. split the contents into symmetric key and IV
3. Use the symmetric key and IV to decrypt the bin file

• reverse the pipe order so if it was zipped then encrypted, it's decrypted and then unzipped
• swap the "-e" for "-d" to decrypt

CERTFILE=/root/.ssh/public_key.pem
SECRETS=$(cat /data/backups/test_file.key | openssl rsautl -decrypt -inkey ${CERTFILE})
ENC_IV=$(echo ${SECRETS} | cut -f1 -d':')
ENC_KEY=$(echo ${SECRETS} | cut -f2 -d':')
STREAM_DECRYPT_COMMAND="openssl enc -aes-256-cbc -d -iv ${ENC_IV} -K ${ENC_KEY}"

# Gzipped file:
cat /data/backups/test_file.gz.bin | ${STREAM_DECRYPT_COMMAND} | gunzip > /data/backups/test_file

# Just encrypted:
cat /data/backups/test_file.gz.bin | ${STREAM_DECRYPT_COMMAND} > /data/backups/test_file

I would suggest having your script that does the encryption to only be writeable by root or something like that so no one can just add logging statements to get the symmetric key.

In both mysql commands in the scripts I have it prompt for the password to the database for security reasons, but you could put it in the ~/.my.conf file or something.

1-backup.sh

#!/bin/bash

if [ $# -eq 0 ];then
    echo "No arguments supplied. Usage: 'script.sh DATABASE_NAME [TABLE_NAME]'."
fi

# Public key used to encrypt symmetric key
CERTFILE=/home/backupman/.ssh/public_key.pem
# Generate the parts of the symmetric key
ENC_IV=$(openssl rand 32 -hex)
ENC_KEY=$(openssl rand 32 -hex)
# Command used to encrypt in-line
STREAM_ENCRYPT_COMMAND="openssl enc -aes-256-cbc -e -iv ${ENC_IV} -K ${ENC_KEY}"
# DB connection info
HOST='dbserver.dns'
PORT=3306
ACCOUNT='backupman'
# Filename Stuff
DEST_FOLDER="/data/backups/${HOST}"
DATE_FORMAT='%Y%m%d%H%M%Z'
# Incoming Parameters from user
DATABASE_NAME=$1
TABLE_NAME=$2

# Create the base for the output files
if [ -z "${TABLE_NAME}" ]; then
    OUTPUT_FULL_PATH=${DEST_FOLDER}/${DATABASE_NAME}.$(date +"${DATE_FORMAT}")
else
    OUTPUT_FULL_PATH=${DEST_FOLDER}/${DATABASE_NAME}.${TABLE_NAME}.$(date +"${DATE_FORMAT}")
fi

# Save the symmetric key to a pub key encrypted file
echo "${ENC_IV}:${ENC_KEY}" | openssl rsautl -encrypt -inkey ${CERTFILE} -pubin > ${OUTPUT_FULL_PATH}.key

# Output the backup encrypting it with the symmetric key
if [ -z "${TABLE_NAME}" ]; then
    echo "No table argument supplied. Backuping up database ${DATABASE_NAME} to ${OUTPUT_FULL_PATH}"
    # I don't lock tables here because AWS RDS instances don't like locking :-(
    mysqldump -h "${HOST}" -P ${PORT} --lock-tables=false -p -u "${ACCOUNT}" ${DATABASE_NAME} | gzip | ${STREAM_ENCRYPT_COMMAND} > ${OUTPUT_FULL_PATH}.bin
else
    echo "Backing up ${DATABASE_NAME}.${TABLE_NAME} to ${OUTPUT_FULL_PATH}"
    mysqldump -h "${HOST}" -P ${PORT} -p -u "${ACCOUNT}" ${DATABASE_NAME} ${TABLE_NAME} | gzip | ${STREAM_ENCRYPT_COMMAND} > ${OUTPUT_FULL_PATH}.bin
fi

# Set the file permissions so the right people can see/use them
chown $(whoami):mybackupgroup ${OUTPUT_FULL_PATH}.*
chmod 770 ${OUTPUT_FULL_PATH}.*

2-restore.sh

#!/bin/bash

if [ $# -eq 0 ];then
    echo "No arguments supplied. Usage: 'script.sh INFILE [DATABASE_NAME]'."
fi

# Just the base w/o extension
INFILE=$1
# What db it is going into
RESTOREDB=$2

# DB connection info
HOST='dbserver.dns'
PORT=3306
ACCOUNT='restoreman'

CERTFILE=/root/.ssh/private_key.pem
# Decrypt the key file
SECRETS=$(cat ${INFILE}.key | openssl rsautl -decrypt -inkey ${CERTFILE})
# Split SECRETS on the ":"
ENC_IV=$(echo ${SECRETS} | cut -f1 -d':')
ENC_KEY=$(echo ${SECRETS} | cut -f2 -d':')

# If a RESTOREDB is passed as the second parameter it will play it to that db in mysql, otherwise it just decrypts the file.
if [ -z "${RESTOREDB}" ]; then
    RESTORELOC="${INFILE}"
    cat ${INFILE}.bin | ${STREAM_DECRYPT_COMMAND} | gunzip > ${INFILE}
else
    RESTORELOC="the database ${RESTOREDB}"
    cat ${INFILE}.bin | ${STREAM_DECRYPT_COMMAND} | gunzip | mysql -h "${HOST}" -P ${PORT} -p -u "${ACCOUNT}" ${RESTOREDB}
fi
echo "Your file has been decrypted and restored to ${INFILE}"