Arrow of time
Arrow of time

Encrypted PostgreSQL data types

Share Tweet Share

The last few nights I have been working on something very interesting: encrypted data types for PostgreSQL. The goal here …

The last few nights I have been working on something very interesting: encrypted data types for PostgreSQL. The goal here is to introduce transparent data encryption for applications which need to protect "data at rest", i.e. while the data is stored in the database within the file system. I didn't find any such mechanism (pgcrypto only offers security primitives which can be used on the application side) so I wrote pgenctypes.


The issue of database security is a complex one, simply because data needs to be accessible in order to do something with it. PostgreSQL already implements optional SSL support for the TCP connections to the database, but the data stored is usually unprotected.

The pgenctypes project works by having the client application specify the encryption key (per session), which is then used to transparently encrypt and decrypt certain fields in the table.

As an example, a database schema might include this table:

CREATE TABLE ccns (
user_id INTEGER NOT NULL REFERENCES users(id),
other_data TEXT,
ccn ENCTEXT NOT NULL
);

The intention here is to protect the "ccn" field (presumably, Credit Card Number) while leaving everything else as-is. To do this, the applications will set up an encryption key before doing any operations with this table, like this:

SELECT pgenctypes_set_key('z03ak7!637#51bH249u3');

SELECT * FROM ccns WHERE user_id=1001;
INSERT INTO ccns (user_id, ccn) VALUES (5005, '92929-234567-123553-123123');

This approach offers some interesting use cases:

  • Protection in "cloud" storage, where the storage is not in the client's complete control
  • Protection of "data at rest" for regulatory compliance
  • Protection of data to guard against hardware theft

The current state of the software is that its main features are pretty well defined and is ready for some exposure. On the other hand, only the "enctext" data type is currently implemented and there is no support for e.g. data type operators and indexing - but it's all coming soon.


#1 Re: Encrypted PostgreSQL data types

Added on 2011-11-23T23:37 by Robert Treat

Hmm, I don't think this will work very well for many of the cases that involve compliance; in fact the more I look at it, the more I think it's actually worse than any of the normal solutions one might employ. My concern here is based on needing the client to know the encryption key, needing the key to be transmitted over the wire, and needing the data transmistted as well. Even with SSL, that's likely to leak into places you don't want (like log files).

ISTM that either setting up client side encryption where the data is encrypted before sending into the db (only client knows the key), or setting up encryption routines on the db side (so client doesn't have the key to encrypt/decrypt, it uses generic functions), would both be better.

Would would make this really interesting would be if you had some way to set the key (maybe at compile time?), and then operators which would do the encrypt / decrypt for you transparently, so neither the db or the client had the key (which could be stored out of band).

#2 Re: Encrypted PostgreSQL data types

Added on 2011-11-27T19:21 by Ivan Voras

Yeah, the way I've done it is mostly useful for protecting offline data. The other things could also be worked out but... not until I need to :)


comments powered by Disqus