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),
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 * 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.