Encrypt column in PostgreSQL

PostgreSQL has a nice encryption (and hashing) module called pgcrypto which is easy to use. Using a provided key, you can quickly encrypt a column which contains sensitive information.

While the setup is fast and the usage is simple, there could be some disadvantages in some contexts:

  • be careful how you send the encryption key to the database server (if public, use SSL for transport, else keep it in a private network at least)
  • data is encrypted/decrypted in the database, so the transport is in plain (watch out for memory dump attacks)
  • some queries are very slow, as the decrypt operation is performed on the entire table if you want to sort or filter by encrypted columns

In a docker container

docker run --rm -tid -p 5432:5432 --name postgres postgres:9.6-alpine

install the module

docker exec -ti postgres sh -c 'apk add postgresql-contrib'

create the extension

CREATE EXTENSION pgcrypto;

add some data

CREATE TABLE public.users (
id serial primary key,
email varchar not null unique
);

INSERT INTO users (email) values
(pgp_sym_encrypt('adoe@domain.tld', 'longsecretencryptionkey')),
(pgp_sym_encrypt('bdoe@domain.tld', 'longsecretencryptionkey'));

perform various queries

SELECT * from users;

SELECT pgp_sym_decrypt(email::bytea, 'longsecretencryptionkey') FROM users WHERE pgp_sym_decrypt(email::bytea, 'longsecretencryptionkey') LIKE 'a%';

SELECT pgp_sym_decrypt(email::bytea, 'longsecretencryptionkey') FROM users ORDER BY pgp_sym_decrypt(email::bytea, 'longsecretencryptionkey') ASC;

SELECT pgp_sym_decrypt(email::bytea, 'longsecretencryptionkey') FROM users ORDER BY pgp_sym_decrypt(email::bytea, 'longsecretencryptionkey') DESC;

On a table with 10k rows, the following query needs 3.7 seconds, so it really depends on your use case if the module can help you or not:

SELECT id FROM users ORDER BY pgp_sym_decrypt(email::bytea, 'longsecretencryptionkey') LIMIT 1

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.