Tuesday, August 21, 2018

Encrypting postgres backups

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


yum install gnupg2


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:

No comments:

Post a Comment