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