Monday, February 1, 2016

On the Subject of Database Encryption, Part 2

Hey everyone - We had some fun last week working on encryption in MariaDB.  This week, we will do something similar for another popular database platform: PostgreSQL.  This time, we will do things a bit differently.  Last time, we worked through examples of symmetric cryptography.  This week, we will take a look at using asymmetric (public key) cryptography to encrypt fields in the database.

In case you need a refresher on asymmetric cryptography, have a look at last week's entry.  With MariaDB / MySQL, you have to step up to the paid enterprise version to get public key crypto in your database (please let me know if I am wrong on this).  PostgreSQL has this functionality freely available, so we will use it for our demonstration.

Our test platform is CentOS 7 with PostgreSQL 9.2.  Make sure you have your distribution's postgresql-contrib package installed:
  • On CentOS (and probably RHEL / Fedora as well), this is postgresql-contrib
  • On Debian / Ubuntu, this is also postgresql-contrib
  • On Windows, this should come with the PostgreSQL download
Postgresql-contrib is a collection of extensions to postgres that provide some useful functionality.  We need one in particular: pgcrypto.  Pgcrypto provides crypto functions for postgres.

You will also need plpython installed.  Plpython is the Python procedural language for Postgres.  In CentOS / RHEL / Fedora, you will need to install postgresql-plpython.

Before we dive into the database, we need to generate a public and private key pair to use to encrypt and decrypt our data.  We will use GPG (we used this utility last week) to generate the keys:

gpg --gen-key

Follow the prompts, and we will be ready to export the keys.  For the encryption, RSA / RSA 2048-bit will suffice.  I did not specify a passphrase for the keys to make things simpler.  You can make the key length longer if you need to.  You might run into issues when gpg needs to harness entropy to create your keys.  I was SSHed into the machine I was using for this, so I could not move the mouse or type in another window.  I ended up needing to use rng-tools (specifically rngd) to generate enough entropy for the key generation process to complete.  On CentOS / RHEL / Fedora, you can install these tools with the following command:
sudo yum install rng-tools

If you are SSHed into the box you are generating the keys on, open a new session and run the following:
sudo rngd -r /dev/urandom

rngd feeds random data into the kernel's entropy pool where gpg is trying to get entropy from.
-r tells rngd to use the device we specify as the source of randomness (we will use /dev/urandom in this case).

You should see something similar to the following when you are done:

Once our keys are set up, we need to export them so that the database can use them.  First, we will export the public key:
gpg -a --export [Public Key ID] > public.key

-a tells GPG to "armor" the keys which essentially Base64 encodes the keys so that they can be more easily transported.  Substitute your key ID (in our case, it is 7B470FB0).

We do the same for the private key:
gpg -a --export-secret-keys [private key ID] > secret.key

In our example, the commands would look like this:

gpg -a --export 7B470FB0 > public.key
gpg -a --export-secret-keys C7D7EFB7 > secret.key

We need to protect the private key so only users in the postgres group can access them.  We will put them in the postgres user's home directory (/var/lib/pgsql).

chown postgres:postgres public.key
chown postgres:postgres secret.key
sudo chmod 440 secret.key
sudo mv public.key /var/lib/pgsql/
sudo mv secret.key /var/lib/pgsql/ 

Since we are running CentOS, we need to change the SELinux context for the keys so that the database user (postgres) can access them:

sudo chcon -v -t postgres_db_t /var/lib/pgsql/public.key
sudo chcon -v -t postgres_db_t /var/lib/pgsql/secret.key

chcon changes the security context on a specified file.
  • -v tells chcon to be verbose
  • -t allows us to specify a type for the file, which in this case is postgres_db_t (this is the type for files that postgres uses and is defined in the default CentOS SELinux security policy)
  • /var/lib/pgsql/*.key are the keys that we want to change the SELinux context on

As I mentioned last week, key storage is a tough problem and one that should be considered carefully.  For this example, we will keep the key in the postgres user's home directory (/var/lib/pgsql).  However, if this machine or that user is compromised, the key will be compromised.  It would be best to keep the key on a separate machine or a key server.

We are done generating keys, and now we can work on the database.  I have already set up a database called example and a database user called dbuser.

Once you have a database setup, we need to enable pgcrypto for the database.  As the database superuser (usually postgres), switch to your database (\connect <your database name>), and run the following:


This will install the pgcrypto extension into the database.

The documentation for pgcrypto is here. We will be using pgp_pub_encrypt and pgp_pub_decrypt.  These functions take the respective keys (public for encrypt, secret for decrypt), so the logs will show our keys if we use them.  That is fine for the public key since it is public, but we need to protect the private key somehow.  We will do this by writing a function that reads the key we need and presents it in a format we can use.  In the logs, it will only show that we called the function (the content of the key will not be in there).

To make this happen, we need to install a 'trusted' language into the database.  Only trusted languages can access the file system, and we need to access the file system to read the private and public keys.  We will install plpythonu (PL/Python) into this database which allows us to use Python in Postgres:

Now for the function:

return open('/var/lib/pgsql/public.key').read()
language plpythonu;
REVOKE ALL ON FUNCTION retr_pub_key() FROM public;

That last line (REVOKE...) makes it so that other users cannot do anything with the function.

Now we need to create a similar function for the private key:

return open('/var/lib/pgsql/secret.key').read()
language plpythonu;
REVOKE ALL ON FUNCTION retr_priv_key() FROM public;

Let's verify that they work:

SELECT retr_pub_key();
SELECT retr_priv_key();

Looks good.  Now we are going to write functions to do the actual encryption and decryption.  We will use a few functions from pgcyrpto to do this: dearmor, pgp_pub_encrypt, and pgp_pub_decrypt:
  • dearmor takes the ASCII keys we made and turns them into bytes (more specifically a byte array or bytea) which are more suitable for encryption operations than the ASCII keys
  • pgp_pub_encrypt encrypts plain text with a specified key (our public key in this case) and returns the cipher text (as a byte array)
  • pgp_pub_decrypt decrypts a given cipher text using a specified key (our private key in this case) and returns the plain text
Here is the encryption function which we will call encrypt_via_pgp:

CREATE FUNCTION encrypt_via_pgp(plaintext text, ciphertext out bytea) AS $$
public_key_bytes bytea;
public_key_bytes := dearmor(retr_pub_key());
ciphertext := pgp_pub_encrypt(plaintext, public_key_bytes);

SECURITY DEFINER tells postgres that we want the function to execute with the permissions of the user that created it (as if you set the suid bit in Linux).  We want to do this because the function needs to call retr_pub_key or retr_priv_key which we only allow the postgres (superuser) to run.

Now we need to set the correct permissions so only the user(s) we want to be able to encrypt can do so:

REVOKE ALL ON FUNCTION encrypt_via_pgp(text) FROM public;
GRANT EXECUTE ON FUNCTION encrypt_via_pgp(text) TO dbuser;

Let's make a similar function for decryption:

CREATE FUNCTION decrypt_via_pgp(ciphertext bytea, plaintext out text) AS $$
private_key_bytes bytea;
private_key_bytes := dearmor(retr_priv_key());
-- If you are using a passphrase, you need to specify it in the function like this
-- pgp_pub_decrypt(ciphertext, private_key_bytes, "passphrase");
plaintext := pgp_pub_decrypt(ciphertext, private_key_bytes);

We want to set the same permissions on this function:

REVOKE ALL ON FUNCTION decrypt_via_pgp(bytea) FROM public;
GRANT EXECUTE ON FUNCTION decrypt_via_pgp(bytea) TO dbuser;

Great.  Now we should test the functions to see if they work as intended.

We will reconnect to the database server as our user dbuser and try to access our keys.  These calls should fail:

SELECT retr_pub_key();
SELECT retr_priv_key();

Looks like that works as intended.  This means that only the super user can retrieve the keys.

Now we will encrypt a random string 'Super sensitive stuff':

SELECT encrypt_via_pgp('Super sensitive stuff');

Looks good.  For it to be useful, we need to be able to decrypt it when necessary:

SELECT decrypt_via_pgp(encrypt_via_pgp('Super sensitive stuff'));

That also looks good.

Final Thoughts

That is really all there is to it.  Key storage is still an issue here, but this is an alternative to using symmetric crypto that we used last week.  Please let me know what you think.  Did I gloss over something you think needed more explanation?  Is there a better way to do this?

Thanks for reading!


  1. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article

  2. what about encrypting content of python function itself