How to encrypt a column in Postgres using Hibernate @ColumnTransformer

Question

I am trying to encrypt a column in my prostrgres DB. The column name is "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 retrieve the entity, I am getting the encrypted data like below. How do I get the decrypted value programmatically? But I get the actual value If i execute a postgres select query.

  "test": "\\xc30d04070302474627ea0994ea657bd24401aaa5543862d57524a407e5dbe2ee0f6f0f33ea4f4474f5bc801dca5d32956d41a975505b12ac000f124177bdc2f4507cbfd724d716aaa513ba46f004dfefd3b2b32eb6"

2. When I am trying to persist the entity, I am getting the below error.

ERROR: column "test" is of type bytea but expression is of type character varying

Answer 1

You need to use pgp_sym_encrypt for write and pgp_sym_decrypt for read. 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;

Because hard-coding the encryption key in the mapping does not sound like a very good idea, we will use the PostgreSQL support for user-defined settings instead.

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

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

The example is on GitHub and works like a charm.