Lately I've been dabbling in the world of security. While I'd more interested doing other things like building features and tackling research problems, security is something that should be part of every day thinking when designing solutions. One area of security focuses on databases.
While I've made the effort to doubly encrypt the postgres data at rest: One at the table column level, where certain fields are encrypted and two, at the file system level as a separate attached volume where postgres lives, these efforts would be useless if the database backups were stored as plain text. True, the encrypted fields would remain encrypted, but for peace of mind, let's encrypt the backups themselves!
Here I'll using GPG (
GNU Privacy Guard) encryption on a Centos 7 machine with a postgres database. While there is a lot of information about GPG on the web, I couldn't find a comprehensive article on how to do this. So here we go!
First let's install GPG
Since I'm using the postgres user to perform the automated backups with ident authentication, we need to switch to the postgres user (assuming we are already the root user):
#
become the postgres user
su
postgres
When generating GPG keys, it will ask for a passphrase using TTY. Unfortunately, GPG doesn't work well when running the terminal in an 'su session' just as we have done with the above command. To workaround this, we issue the following command:
#
workaround to generate gpg key in a su session as postgres
script
/dev/null
Redirecting the script to /dev/null causes screen to not try to write to the controlling terminal, so it doesn't hit the permission problem.
Now can generate the GPG keys for the postgres user. You will be asked for a passphrase - keep this some where safe.
bash-4.2$
gpg2 --gen-key
gpg
(GnuPG) 2.0.22; Copyright (C) 2013 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?
RSA
keys may be between 1024 and 4096 bits long.
What
keysize do you want? (2048)
Requested
keysize is 2048 bits
Please
specify how long the key should be valid.
0 = key does not expire
= key expires in n days
w = key expires in n weeks
m = key expires in n months
y = key expires in n years
Key
is valid for? (0)
Key
does not expire at all
Is
this correct? (y/N) y
GnuPG
needs to construct a user ID to identify your key.
Real
name: postgres
Email
address:
Comment:
You
selected this USER-ID:
"postgres"
Change
(N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You
need a Passphrase to protect your secret key.
We
need to generate a lot of random bytes. It is a good idea to perform
some
other action (type on the keyboard, move the mouse, utilize the
disks)
during the prime generation; this gives the random number
generator
a better chance to gain enough entropy.
We
need to generate a lot of random bytes. It is a good idea to perform
some
other action (type on the keyboard, move the mouse, utilize the
disks)
during the prime generation; this gives the random number
generator
a better chance to gain enough entropy.
The important thing to take note of is the 'Real name' which I've specified as 'postgres'. We will use this 'Real name' later when we perform the encryption.
At this stage, it seemed to just hang without any idea if it was doing anything at all. In my first attempt, I had let it sit for over an hour and still nothing. Turns out Entropy takes a long time if there's no system activity. So let's introduce 'random activity' in another terminal:
yum install
rng-tools
rngd
-r /dev/urandom
After running the rngd command, you notice almost immediately in the other terminal, that the GPG key gen has complated. Now you can kill the rngd process that's still running in the background.
ps -aux | grep rngd
root 25652
0.0 0.0 13216
368 ? Ss 14:37
0:00 rngd -r /dev/urandom
root 25665
0.0 0.0 112704 976 pts/0
S+ 14:37 0:00 grep --color=auto rngd
kill -9 25652
To troubleshoot entropy availability, you can monitor entropy availability here which should sit at around 1450 when idle. When being consumed, it should be much lower:
watch
cat /proc/sys/kernel/random/entropy_avail
Now that we have our GPG keys, we are ready to encrypt files. Here I've created a script to execute the postgres backups, compression and encryption all in one step:
pg_dump
-U postgres db_name | gzip > /backups/db_backup_$(date
+%Y-%m-%d).psql.gz
gpg
-e –r postgres /home/backups/patient_lookup_$(date +%Y-%m-%d).psql.gz
rm
-rf /home/backups/patient_lookup_$(date +%Y-%m-%d).psql.gz
chmod
0600 -R /backups/*.gpg
The first line using pg_dump generates a compressed GZ backup file.
The second line then takes the GZ file and encrypts it, creating a new GPG file. The -e argument tells GPG to encrypt and the -r argument specifies the recipient which in this case is the postgres user that we specified earlier when generating the GPG keys.
Since GPG creates a new file, we remove the GZ file in the third line.
Then we only allow read/write permissions for the postgres user on the fourth line.
You can run the script on a cron job to routinely do your backups.
Of course, before you put this into production, you should check to ensure you can successfully decrypt the backups.
su postgres
script /dev/null
gpg postgres_backup.gpg
If this helped you please like! Thx
References:
