14

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"
  1. 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

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

2 Answers2

28

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.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • 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. – zeagord Feb 24 '17 at 12:22
  • works like a charm..the database field should be of type bytea – ramnar Dec 06 '18 at 12:17
  • instead of hardcoding the symmetric key in the code it can be read from postgresql.conf properties file with entry encrypt.key = 'mySecretKey' – ramnar Dec 06 '18 at 12:19
  • 5
    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 – mdzh Mar 18 '20 at 08:49
  • 2
    How would you do that with a custom Hibernate Type ? – Eltomon Jul 09 '20 at 10:47
  • 1
    Asking once again, can anyone provide an example for using encryption key from application.properties ? – Tomas Lukac Aug 26 '21 at 07:03
  • I am using your solution but I still get the result of `\\xc30d04.....`. When I go to the database and execute `select pgp_sym_decrypt((select value from mytable where id = 1), 'my-secret');`, I get the expected result. – Tomas Lukac Aug 30 '21 at 12:28
  • 1
    @VladMihalcea What about if postgresql.conf is not accessible? for example like services in Heroku? what is the solution for this? – 0x01Brain Oct 14 '21 at 10:36
  • You can use session variables for that. Check out this article for more details: https://vladmihalcea.com/postgresql-audit-logging-triggers/ – Vlad Mihalcea Oct 14 '21 at 10:43
  • @VladMihalcea I am using in production `@ColumnTransformer` in kubernetes pod its working fine, but on develop I am using heroku – 0x01Brain Oct 14 '21 at 10:49
  • @VladMihalcea so for my case I need something that will be dynamic by checking current spring boot active profile to change the key – 0x01Brain Oct 14 '21 at 10:49
  • @0x01Brain I have posted an example doing it in the session here: https://stackoverflow.com/questions/52077348/is-it-possible-to-variabilize-the-password-in-hibernate-columntransformer-for-p/73815778#73815778 – max Sep 22 '22 at 13:50
  • @mdzh See this example: https://stackoverflow.com/a/73815778/767434 – max Dec 14 '22 at 16:00
0

While this works like a charm, This cannot be considered as a product generic solution and this does not come handy as working on multiple databases will be a night mare. May be other alternatives like vaulting or usage of jasypt would be a good start!!!

Santhosh Ponnam
  • 51
  • 1
  • 2