5

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?

Glorfindel
2,2095 gold badges19 silver badges26 bronze badges
asked May 17, 2019 at 21:22

1 Answer 1

-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.
answered Aug 25, 2023 at 4:50

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.