Today I decided to encrypt all my Postgresql Database Dumps. 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:
openssl req -x509 -nodes -days 1000000 –newkey rsa:4096 –keyout backup_key.pem -subj "/C=US/ST=Houston/L=Texas/O=IT/CN=some-site.com" -out backup_key.pem.pub
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 gzip.
/usr/bin/pg_dump production -U postgres -h 127.0.0.1 | gzip | openssl smime -encrypt -aes256 -binary -outform DEM -out prod_`date +%Y%m%d%H%M`.gz.enc backup_key.pem.pub
After running this command, you will be left with an encrypted, compressed database dump called prod_DATE.gz.enc
To decrypt the database dump, copy it to the server where your private key is situated. Then run one of the following commands:
openssl smime -decrypt -in prod_DATE.gz.enc -binary -inform DEM –inkey backup_key.pem -out production.gz
Thats it.
P.S. I have 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 7 days of backups:
#!/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="/opt/db_backups/backup_key.pem.pub"
# Location to place backups.
backup_dir="/opt/db_backups/pg_dump"
# Numbers of days you want to keep copies of your databases
number_of_days=7
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`
echo "Dumping ${database_name} to ${backup_dir}${database_name}_${backup_date}.gz.enc"
pg_dump ${database_name} -U postgres -h 127.0.0.1 | gzip | openssl smime -encrypt -aes256 -binary -outform DEM \
-out ${backup_dir}/${database_name}\_${backup_date}.gz.enc ${backup_public_key}
find ${backup_dir} -type f -prune -mtime \
+${number_of_days} -exec rm -f {} \;
Stay tuned!
Leave a Reply