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 pgcrypto 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 Cancel reply