How to encrypt a column in Postgres using Hibernate @ColumnTransformer

Question

I am trying to encrypt a column in my prostrgres database. The column name is a "test" of type "bytea".

My enity code is below,

@ColumnTransformer( forColumn="test", read="pgp_sym_encrypt(test::bytea, 'mySecretKey')", write="pgp_sym_decrypt(?, 'mySecretKey')") private String test; 

When I tried to get the object, I get encrypted data, as shown below. How to get decrypted value programmatically? But I get the actual value. If I execute a postgres select query.

  "test": "\\xc30d04070302474627ea0994ea657bd24401aaa5543862d57524a407e5dbe2ee0f6f0f33ea4f4474f5bc801dca5d32956d41a975505b12ac000f124177bdc2f4507cbfd724d716aaa513ba46f004dfefd3b2b32eb6" 

2. When I try to save the object, I get the following error.

ERROR: the "test" column is of type bytea, but the expression is of type differing character

Answer 1

You need to use pgp_sym_encrypt for writing and pgp_sym_decrypt for reading. You did the opposite.

 @ColumnTransformer( read = "pgp_sym_decrypt(" + " test, " + " current_setting('encrypt.key')" + ")", write = "pgp_sym_encrypt( " + " ?, " + " current_setting('encrypt.key')" + ") " ) @Column(columnDefinition = "bytea") private String test; 

Since hard-coding an encryption key in a mapping is not a good idea, we will use PostgreSQL support for user settings instead.

So, the encrypt.key key is stored in the postgresql.conf configuration file:

 encrypt.key = 'Wow! So much security.' 

In fact, I liked your question so much that I even wrote an article about it . GitHub example and works like a charm.