Database column encryption postgres

Question

How to encrypt column in postgres database using pgcrypto addon ?

I am using postgres 9.3 and i need to encrypt one of my column , does postgres also support Aes encryption or by any mean i can achieve it ?

Answer 1

The above is not encryption as encryption is reversible which means if you encrypt some secret text or value, you should be able to know what that secret value or text was unlike hashing where you want to verify if the user-provided value matches the hashed value or not.

This is how you would encrypt column data using pgcrypto module.

create extension if not exists pgcrypto; -- this will install the module if not installed 

CREATE TABLE agents (
id serial primary key,
name varchar not null
);
 
INSERT INTO agents (name) values
(pgp_sym_encrypt('Johny Smith', 'longsecretencryptionkey')),
(pgp_sym_encrypt('Bob Marley', 'longsecretencryptionkey'));

longsecretencryptionkey 

is your encryption key. You can generate encryption key from here encryption key generator and choose the bit of your choice. The recommendation would be to choose min 256 bit.

Remember to keep encryption key somewhere safe saved somewhere. If you lose your encryption, you will not be able to decrypt anymore. This is very crucial to understand.

This is how you would query them

SELECT pgp_sym_decrypt(name::bytea, 'longsecretencryptionkey') FROM users WHERE pgp_sym_decrypt(name::bytea, 'longsecretencryptionkey') ILIKE 'johny%'; -- querying for agents whose name start with johny

You can checkout this blog article which helped me https://blog.andreiavram.ro/encrypt-postgresql-column/

Answer 2

Yes, Postgres pgcrypto module does support AES. All details with examples can be found here. As for the sample usage:

-- add extension
CREATE EXTENSION pgcrypto;

-- sample DDL
CREATE TABLE test_encrypt(
  value TEXT
);
INSERT INTO test_encrypt VALUES ('testvalue');

-- encrypt value
WITH encrypted_data AS (
  SELECT crypt('PasswordToEncrypt0',gen_salt('md5')) as hashed_value
)
UPDATE test_encrypt SET value = (SELECT hashed_value FROM encrypted_data);

Validate password:

SELECT (value = crypt('PasswordToEncrypt0', value)) AS match FROM test_encrypt;

Returns:

 match 
-------
 t
(1 row)