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
1 | CREATE EXTENSION pgcrypto; |
add some data
1 2 3 4 5 6 7 8 | 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
1 2 3 4 5 6 7 | 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:
1 | SELECT id FROM users ORDER BY pgp_sym_decrypt(email::bytea, 'longsecretencryptionkey' ) LIMIT 1 |