When creating a Postgres Database backup, one convenient approach is to export the database to disk via the pg_dump command. This postgres export file can then be picked up by a regular filesystem backup.

A possible security risk with this approach is that your sensitive database data could be situated in multiple locations: in the database itself, at the location where export file is created on disk, and in the filesystem backup.

To mitigate this risk, it makes sense to encrypt your database backups when they are dumped to disk. Using a combination of RSA public/private key encryption and AES encryption, a database export backup can be created and encrypted with a public key. The corresponding private key could reside in a safe location (not on the server itself) and is used to decrypt the database backup. If an intruder gains access to your database backups, there would be no way for them to decrypt the data without the private key.

The OpenSSL command provides a nice tool for helping us encrypt and decrypt database backups in this way. The following steps will walk us through this process.

The first step needed to encrypt your database is to generate the RSA public/private keys. This can be done with the following command:

 # Generate a public private key pair
 openssl req -x509 -nodes -days 1000000 -newkey rsa:4096 -keyout backup_key.pem\
 -subj "/C=US/ST=Illinois/L=Chicago/O=IT/CN=www.example.com" \
 -out backup_key.pem.pub

You can enter in fake data for the -subj argument in the format above, as this is not used. (The observant user will note that this command is the same command that is run when registering an SSL certificate. The same files are generated, but we will use them for a different purpose.)

After running the command, you should see the following files which represent your public and private key pair.

backup_key.pem - private key 
backup_key.pem.pub - public key 

The public key should reside on the server that pg_dump is to run on, and the private key should be copied to some “safe place”.

The next step is to perform the export and encrypt the resulting export file. For good measure, we will also compress the export file using bzip2.

# Encrypt with public key  
pg_dump my_database | bzip2 | openssl smime -encrypt -aes256 -binary \
    -outform DEM -out my_database.sql.bz2.ssl backup_key.pem.pub

After running this command, you will be left with an encrypted, compressed database dump called my_database.sql.bz2.ssl.

As an additional note about the above command: RSA public/private key encryption can only encrypt small amounts of data, but the database dump is likely larger than what RSA encryption alone can handle. To solve this problem, this command instructs OpenSSL to generate an AES key to encrypt the data. AES encryption is much better suited for encrypting large data files. The AES key is then encrypted by using the generated RSA public key. This all happens automagically due to the “smime” parameter passed to OpenSSL.

To decrypt the database dump, copy it to the server where your private key is situated. Then run one of the following commands.

# Decrypt to a file using private key
openssl smime -decrypt -in my_database.sql.bz2.ssl -binary \
  -inform DEM -inkey backup_key.pem -out my_database.sql.bz2

# Decrypt to stdout using private key
openssl smime -decrypt -in my_database.sql.bz2.ssl -binary \
  -inform DEM -inkey backup_key.pem | bzcat 

That’s all there is to it!

We’ve automated the encryption process via the following shell script that can be run via a cron job. It’s a simple shell script that takes the database name as the first argument, dumps, compresses, and encrypts the database. It also only retains the most recent 30 days of backups. You can tweak this script to suite your needs.

$ cat backup_db_encrypted.sh
#!/bin/bash
# #######################
# Postgresql database backup script.
# - this runs out of the postgres user's crontab
# - this runs once per day
# - this takes a database name as the first argument
# - this compresses the dump with bzip2 compression
# - this encrypts the dump with aes 256 and  
#  
# To extract:
# You need the private key associated with the
# public key defined by the backup_public_key variable.
#
#   openssl smime -decrypt -in my_database.sql.sql.bz2.enc -binary -inform DEM -inkey private.pem | bzcat >  my_database.sql.sql
#
# #######################

# Database Name
database_name="$1"

backup_public_key="/iscape/etc/backup_key.pem.pub"

# Location to place backups.
backup_dir="/iscape/backups/database/"

# Numbers of days you want to keep copies of your databases
number_of_days=30

if [ -z ${database_name} ]
then
 echo "Please specify a database name as the first argument"
 exit 1
fi

# String to append to the name of the backup files
backup_date=`date +%Y-%m-%d-%H-%M-%S`

echo "Dumping ${database_name} to ${backup_dir}${database_name}\_${backup_date}.sql.bz2.enc"
pg_dump ${database_name}|bzip2|openssl smime -encrypt \
 -aes256 -binary -outform DEM \
 -out ${backup_dir}${database_name}\_${backup_date}.sql.bz2.enc \
 "${backup_public_key}"

find ${backup_dir} -type f -prune -mtime \
    +${number_of_days} -exec rm -f {} \;

When learning about and creating this script, the following articles were key to my understanding (no pun intended).

Encrypt large files with openssl

http://ashmek.weebly.com/home/encrypt-large-files-with-a-public-key-via-opensslhttp://www.agix.com.au/blog/?p=3737

Verify Key matches Cert

https://kb.wisc.edu/middleware/page.php?id=4064