Search code examples
databasepostgresqlencryptionaespgcrypto

Database column encryption postgres

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 ?

Solution

  • 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)