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:


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


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?

1 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 $$
  result bytea;
  EXECUTE 'SELECT pg_temp.encrypt($1, ''aes-256-gcm'', ' || quote_literal(key) || '::bytea, ' || quote_literal(nonce) || '::bytea)'
  INTO result
  USING data;
  RETURN result;
$$ 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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.