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"

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

I think you placed your function calls inversely. You should use something like read="pgp_sym_decrypt(test, '<key>')", write="pgp_sym_encrypt(?, '<key>')". (Because it is encrypted in the DB & you want decrypted in your application). — pozs
– pozs, Commented Feb 24, 2017 at 12:20
But I'm not sure if that's the right place to embed your key anyway. — pozs
– pozs, Commented Feb 24, 2017 at 12:20

Vlad Mihalcea · Accepted Answer · 2021-01-10 06:15:26Z

29

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.

edited Jan 10, 2021 at 6:15

answered Feb 24, 2017 at 11:52

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

zeagord Over a year ago

Thanks for answering. Same issue after the change. And also I am wondering, why I don't get the decrypted data via my rest API.

mdzh Over a year ago

That is good and all but is there a way to tell Hibernate to read the 'mySecretKey' from application.properties? We have the use case to store the key in the web app instead of postgres

Eltomon Over a year ago

How would you do that with a custom Hibernate Type ?

Tomas Lukac Over a year ago

Asking once again, can anyone provide an example for using encryption key from application.properties ?

0x01Brain Over a year ago

@VladMihalcea What about if postgresql.conf is not accessible? for example like services in Heroku? what is the solution for this?

|

Collectives™ on Stack Overflow

How to encrypt a column in Postgres using Hibernate @ColumnTransformer

1 Answer 1

13 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Linked

Related