Sunday, November 6, 2016

The Elephant in the Locker Room: Instance-encrypted PostgreSQL meets Vault

Recently, Cybertec has released a patched PostgreSQL 9.6.0 with instance level encryption. And since I'm frequently asked if PostgreSQL supports this, I had to try it out immediately.

Well, it seems to work as advertised, but as always when dealing with encryption, the pesky question is - where to put the key?

One method to provide the key, is to put a passphrase into a environment variable named PGENCRYPTIONKEY, which is a bit of a misnomer, because the actual key is derived
by SHA256 from the passphrase. But the documentation mentions a second way to provide
the key.

Once the cluster is initialized with initdb, you can provide a command that retrieves the key at startup,
but no example for this is given in the release announcement. So, here is how it works:

As key repository, I chose HashiCorp's Vault, which is "a tool for managing secrets" as they say.

First, fire up vault. I use development mode here which is the easiest way but of course not recommended for production systems.

vault server -dev

Keep the output of this command, you'll need it later!

Second, get the SHA256 hash of the passphrase the database cluster was initialized with. Mine was 'sekrit'. :-)

echo -n sekrit | sha256sum

7e35aaaea6b83ab43c9271737060ce485dc285837d9b4b98b6458408886ac7b1  -

This is the secret to store in Vault. Don't forget the '-n' option for echo!

vault write secret/pg_key value=encryptionkey=7e35aaaea6b83ab43c9271737060ce485dc285837d9b4b98b6458408886ac7b1

The documentation says, that the value returned by the key retrieval command must be 'encryptionkey=...', so this was written as the secret to the vault, not only the key. 'pg_key' is an arbitrary name under which the secret can be retrieved later.

Now, we need a command to retrieve the secret from the Vault. Using the REST interface this becomes:

curl -s -X GET -H "X-Vault-Token:$VAULT_TOKEN" http://localhost:8200/v1/secret/pg_key | jq '.data.value' | tr -d '"'

This is the raw response:


  "request_id": "1d485f2a-893f-67e7-6257-12b3b401f916",
  "lease_id": "",
  "renewable": false,
  "lease_duration": 2592000,
  "data": {
    "value": "encryptionkey=7e35aaaea6b83ab43c9271737060ce485dc285837d9b4b98b6458408886ac7b1"
  "wrap_info": null,
  "warnings": null,
  "auth": null

curl does the actual REST call, jq parses the value of interest out of the JSON response and tr removes the superfluous quotes around it. At the end we get the desired output:


Looks OK, so this can be put into postgresql.conf.

pgcrypto.keysetup_command='curl -s -X GET -H "X-Vault-Token:$VAULT_TOKEN" http://localhost:8200/v1/secret/pg_key | jq ''.data.value'' | tr -d ''"'''

And with that in place, every time the PostgreSQL server starts, the key is pulled out of vault and all is set.

But what is the point of using a secret manager like vault instead of other mechanisms? Well, vault needs authentication and authorization to provide it's services.

Actually the REST interface requires a authorization token  to work: -H "X-Vault-Token:$VAULT_TOKEN". vault writes a root token to stdout, that's why the output at startup is important. vault will not reveal that token ever again after first startup!

Unseal Key (hex)   : d6426284cb1b9756d8a108f17a2508a261963acf529cfbbd6b12b8cdc90da643
Unseal Key (base64): 1kJihMsbl1bYoQjxeiUIomGWOs9SnPu9axK4zckNpkM=
Root Token: 60a42d59-1e2b-76fb-329f-c32f3b77291e

In production you would of course never use the root token directly, but get additional tokens with limited rights and lifetime! HTTPS instead of HTTP is the protocol of choice for the REST API.

Also note the unseal key. A vault in production mode will emit several unseal keys of which you need a subset to unseal the secret storage.

So in a production setup, vault allows to:
  1. Manage all your secrets in a central service
  2. Restrict access to the secrets by scope and time
  3. Protect all secrets in encrypted storage
  4. Seal the vault immediately if you suspect your system is compromised
  5. Unseal the secrets only by consent of several unseal key holders
  6. And much more, see the documentation for details
I'm not affiliated with HashiCorp in any way and when I took first look at vault I was not impressed. But the more I dive into it's documentation and try things, this gradually changes. And it can not only manage secrets for PostgreSQL, but use PostgreSQL as a storage backend too!


  1. Thanks for this interesting example.
    Wouldn't you also need some sort of periodic task to renew the lease?
    Otherwise doesn't the Vault remove the secret in 30 days and the database becomes un-decryptable?

    1. Hm, the documentation says that vault _can_ revoke the data after the lease has expired. It is not clear that it always _will_.

      But anyway, you're right. Either the lease has to be renewed regularly via the 'renew' API call or the secret has to be registered again after the lease has expired.

    2. Another way to store long living secrets is to encrypt/decrypt them via the Vault 'transit' backend (which stores keys without a lease time) and actually store the encrypted secret somewhere else, e.g. Consul.

  2. This does not protect your data if your data ends up in the swap space. Only full disk encryption fully protects your PostgreSQL data. So why use instance encryption when you need and use full disk encryption?

    1. This largely depends on the attack profile you want to minimize, doesn't it?

      However, I could not find evidence of locking physical memory in the patch.

      Anyway, this is the first release of this patch and far from production readiness, I'd say. Before this ends up in the main source tree, it usually has to pass muster anyway...