How to encrypt a column using aes-256-gcm encryption in PostgreSQL?

Question

As a database point person, I am required to encrypt SSN numbers wherever they are in a table. We use PostgreSQL. My application counterparts require that we use the AES-256-GCM encryption algorithm. However, I am not too sure how to apply that in postgresql.

After googling around, I am able to encrypt using either one of these two AES methods:

1.

UPDATE ssnTable
 SET encrypted_ssn = encrypt(encrypted_ssn::bytea, 'mykey', 'aes')`

2.

UPDATE ssnTable
 SET encrypted_ssn = pgp_sym_encrypt(encrypted_ssn, 'mykey', 'compress-algo=1, cipher-algo=aes256')

None of the above specifically uses the 'aes-256-gcm' algorithm, which is what I'm being asked to use. Any idea on how to use this algorithm on the column, in PostgreSQL?

Answer 1

-- Create a custom function for AES-GCM encryption using OpenSSL
CREATE OR REPLACE FUNCTION aes256_gcm_encrypt(data bytea, key text, nonce text)
RETURNS bytea AS $$
DECLARE
 result bytea;
BEGIN
 EXECUTE 'SELECT pg_temp.encrypt(1,ドル ''aes-256-gcm'', ' || quote_literal(key) || '::bytea, ' || quote_literal(nonce) || '::bytea)'
 INTO result
 USING data;
 RETURN result;
END;
$$ LANGUAGE plpgsql;
-- Usage: Encrypt a column using AES-256-GCM
UPDATE ssnTable
SET encrypted_ssn = aes256_gcm_encrypt(encrypted_ssn, 'encryption_key', 'nonce');
-- If needed, you can also create a decryption function
-- But remember to handle exceptions and security aspects properly.