Manage encryption keys with PostgreSQL TDE

PostgreSQL TDE is a version of PostgreSQL which offers on-disk encryption for PostgreSQL. Currently we at CYBERTEC maintain this piece of software for public use. What we have noticed recently is that people kept asking about how to integrate PostgreSQL TDE with existing key management solutions. To help you manage encryption keys with PostgreSQL TDE, we have decided to outline the basic concepts and help people to implement key management strategies for PostgreSQL to secure their infrastructures.

PostgreSQL TDE: How encryption keys are handled

Running PostgreSQL TDE is easy. However, it requires some basic understanding of how we handle keys in general. The following image shows the basic architecture of how to manage encryption keys using our security-enabled version of PostgreSQL:

Let's focus on each of these parts separately.

Deploying a PostgreSQL instance

When a new PostgreSQL instance is created, initdb is executed behind the scenes. Most systems do that for you when the service is started for the first time. The purpose of initdb is to create the initial content of the data directory, deploy a config file and so on.

The point is: In the case of TDE (= Transparent Data Encryption), the data files on disk have to be encrypted. The natural question which arises is: How can we find the key? It's not a good idea to store the key directly in the data directory, so we have to be a little creative to handle keys properly.

When we developed PostgreSQL TDE, we decided on a simple plugin architecture. Key management can be quite diverse. Many different keystores exist and the requirements of every single company differ depending on use cases, philosophy, guidelines and sometimes even due to legal requirements.

When PostgreSQL TDE fetches the key, it uses a plugin which is an executable we have to point to:

initdb initializes a PostgreSQL database cluster.

Usage:

Options:

The -K option requires you to point to a script that returns the key as a string. Note that this key is never visible to anybody but the database server. It does not show up in a process table. It does not show up anywhere. The beauty of this design is that you can write any plugin for any key manager of your choice. For testing purposes, you can even call up the command line and make the administrator type in the key. In the real world, almost no-one does that - but even that approach is possible.

As stated: Provide a script or an executable that echoes the key to stdout. PostgreSQL will read it and use it to encrypt your data files. This script gives you total flexibility to integrate with every possible kind of key management.

Here is an example:

The files which belong to this database system will be owned by user "hs".

This user must also own the server process.

The database cluster will be initialized with locales

The default database encoding has been set accordingly, to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

Data encryption is enabled.

Success. You can now start the database server using:

The -K option is really the path to success here. In the simplest of all cases the script simply returns a constant:

But, as stated earlier, this is really just a showcase. DO NOT store the key on the same server - instead, integrate it with a proper key store and manage the key remotely. Otherwise, TDE would be pointless and simply a waste of resources.

What is really important to note here is that the key has to be in hex and it has to be 32 characters long. Otherwise initdb will error out.

For using secrets that humans can memorize there is a bundled pg_keytool utility that takes as input a passphrase and uses a key derivation function to form a correctly formatted output key. By using a key derivation function that takes a non-trivial amount of time to calculate, this adds an extra layer of security against dictionary attacks.

Once the instance has been deployed, we can actually start PostgreSQL normally.

Starting PostgreSQL

What initdb has done for us is to adjust the relevant values in postgresql.conf:

Every time the server starts up, it will run the same program to fetch the key. Remember, we need this key to decrypt those data files. The server has already taken care to ensure that nothing can go wrong - assuming that the program does indeed return the correct key. If the key cannot be provided, then the server cannot be started. There is NO BACKDOOR, no emergency key or anything of that kind. If you cannot provide the correct key your data is lost. That is the entire idea of TDE. We do not want backdoors.

Ensuring encryption is enabled

People keep asking: How can I figure out if my PostgreSQL instance is indeed encrypted? The way to do that is to control pg_controldata:

At the end of the listing, you can find the encryption details. Make sure that you use the RIGHT version of pg_controldata. Otherwise, you won't be able to find that information.

Popular keystores recommended for PostgreSQL TDE encryption keys

One of the main questions is: Which key store can one use for PostgreSQL? The answer is: Any of them. Here are some which are quite popular and widely used by many companies. Keep in mind that the list is not exhaustive:

• nitrokey.com
• YubiHSM
• HashiCorp Vault

A typical solution for a production system is to use a scheme where the database master key is generated randomly, then encrypted using a key from a key management system, and finally stored in the PostgreSQL data directory. This allows for keys to be rotated or even the key management system to be swapped out without having to re-encrypt the whole database.

Finally...

You can read more about the topic of encryption in our introduction to Transparent Layer Security (TLS).

Exciting news in the realm of GIS - we recently released a website that offers ready-to-use GIS data. If you are using PostGIS and OSM data, this is the place to go. We provide you with ready-made database dumps featuring the entire planet. Check out our new GIS site.

---

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

Some time ago, I posted some information about zheap, a storage engine for PostgreSQL. The idea behind zheap is to make UPDATE statements run faster in order to keep table bloat under control. Currently, PostgreSQL copies data on UPDATE and puts the copy of the row into the same data file. While this isn't a bad strategy, it can in some cases lead to table bloat. The idea behind zheap undo is to manage old copies of rows, which is similar to Oracle. Until now, the problem was that this “zheap undo” was not cleaned up. That's why we implemented a “discard worker” to handle the cleanup. By discarding zheap UNDO logs, the worker keeps UPDATE-related table bloat to a minimum.

Getting PostgreSQL and zheap

Before we dig into what the discard worker does, you need to install a zheap-enabled version of PostgreSQL. To make it easier for everybody, we created a container to allow users to give zheap a try out-of-the-box.

The original idea was to have zheap as an extension. However, it's too invasive, therefore you can't just install it on top of PostgreSQL, as you can with other extensions. zheap does need core patches.

Also: I want to point out that this is not a production-ready version, but rather a tech preview. We're happy to share it with the community to get feedback and maybe gather some bug reports.

Here is the freely available docker container for you to try out. Installation is simple and very straightforward:

To connect to the newly created instance, just run psql as you normally would. The default username and password are both postgres.

Username: postgres
Password: postgres

Testing zheap UNDO in PostgreSQL

Once your zheap-enabled version of PostgreSQL is up and running, we can inspect the process table:

The discard worker is launched automatically. Let's see what it does, and how the undo log is stored.

Before we create a table and fill it with data, we've got to adjust a variable:

This setting tells PostgreSQL which storage engine you want to use by default when creating a new table. Usually we want heaps. However, in this case zheap is the desired choice. We can add the zheap option to each CREATE TABLE. In many cases you might want to set it for the entire session as I just did:

The layout of the table doesn't really matter. We can use any layout.

Testing zheap UNDO with INSERT load

Let's load some data:

The important thing is that zheap is able to rollback in case of error. Therefore, a lot of undo has to be written: Let's take a look at the undo directories before we commit the transaction:

You can see that we've created a fairly large number of files in our base/undo directory. Let's commit and see what happens:

PostgreSQL will recycle (= delete) these logs when the new discard worker kicks in. Let's take a look and see:

The files are gone, which is what we expected.

Testing zheap UNDO with UPDATE load

The same will happen when we run UPDATE statements:

The important aspect is that there is actually a lot of WAL created. As you can see, we are talking about around 600 MB:

Now, let's commit the transaction:

What's interesting is what happens immediately after the commit statement:

So that's it - the zheap UNDO logs are gone. The cleanup is NOT part of a commit, but is actually done by the discard worker which is in charge of making sure that log is not killed too early. Keep in mind: You might not be the only transaction at work here, so the cleanup still has to be asynchronous (just like a vacuum is).

Finally...

Check out our Table Bloat Archive for more important information about zheap!

New GIS Site!

We also recently released a website that offers ready-to-use GIS data. If you use PostGIS and OSM data, this is the place to go. We provide you with ready-made database dumps featuring the entire planet. Check out our new GIS site.

PostgreSQL table partitioning is by far one of the most popular new PostgreSQL features. Many people look to partitioned tables as a way to improve performance and broaden scalability. However, partitioning comes with a little problem: How can you partition an existing table without locking up the database? The answer is: pg_rewrite can help you with PostgreSQL table partitioning. Here you will learn how to implement pg_rewrite to help you solve partitioning problems in the most elegant way possible.

Installing pg_rewrite

pg_rewrite is Open Source and can be quickly and easily downloaded from our Github profile. Cloning the repository works as shown in the next listing:

Once this is done, we can enter the directory:

To build the code, we have to make sure that the correct version of pg_config is in the path. In my case, I have a working binary in my home directory, so I can effortlessly build the code:

If pg_config is in the path, all you have to do is to run “make install” to compile and install the code:

The next thing to do is to adjust postgresql.conf file. pg_rewrite has to be loaded as a library when the server starts. Otherwise, it won’t work. Configuring PostgreSQL to load pg_rewrite works as follows:

The wal_level has to be adjusted to make sure that the WAL contains enough information for logical decoding to work. On top of that, you'll need a sufficient number of replication slots to safely run logical decoding. pg_rewrite will need one slot to operate.

After these changes are done, restart your server and verify that the variables are properly set:

Finally, you have to enable the pg_rewrite extension to ensure that the partition_table function is available:

Voilà, you have a working version of pg_rewrite. Let's take a look at how we can use it.

Creating a sample table

After installing pg_rewrite, we can create a table - which we'll want to partition later on:

For the sake of simplicity, the table contains a couple of random values. Some are greater than zero and some are less than zero. Let's verify that:

The data seem correct, so we can move to the next step:

PostgreSQL can only identify a row if there is a primary key. Otherwise, we'll have problems and the code won't work. So it's important to ensure that there are indeed primary keys.

Creating table partitions in PostgreSQL

Before we can partition the table, we have to come up with a partitioning scheme. We want to use that scheme for our database. In my example, all I'm doing is putting negative values into one table, and positive values into another. Here's how it works:

Make sure that the primary keys and constraints are identical. Otherwise, pg_rewrite will produce an error. Remember: we want to repartition the table - we don’t want anything else to happen.

PostgreSQL table partitioning with almost no locking

Now that all the ingredients are in place, we can rewrite the data:

t_number is the source table which has to be rewritten. t_part_number is the freshly partitioned table which we want to use as intermediate storage. t_old_number is the name of the original table which will be renamed. In case something goes wrong, the old table will still be found. The advantage is that nothing can go wrong. The downside is that more storage is needed. However, that is the case DURING repartitioning anyway so it does not matter if the source table is deleted or not. We need twice the space anyway.

The final structure will look as follows:

As you can see, the original table is still in place using up 42 MB of storage. Our two partitions will need the same amount of space.

pg_rewrite is a good method to turn existing tables into PostgreSQL partitions. It only needs a short lock, which makes it superior to the long and extensive table locking needed by the PostgreSQL core.

Finally ...

• GIS databases (PostGIS) are often very large and can make full use of partitioning. So we want to share some good news: We offer ready-made OSM data (OpenStreetMap) for PostgreSQL on our new GIS site. You will find ready made PostgreSQL dumps there which can be easily imported into your system.
• For information on how to migrate partitioned data, see this informative blogpost.
• And if you're interested in learning more about how to set PostgreSQL configuration parameters, see our blog here.