How to externalize the Postgresql column encryption key with Spring Boot JPA?

Question

One of my Postgresql entities has an encrypted column that I want decrypted when I read it with Spring Boot JPA. The encrypted entity fiels is decrypted with an hardcoded password :

@ColumnTransformer(
    read = "pgp_sym_decrypt(secretField::bytea, 'secr3t'),
    write = "pgp_sym_decrypt(?, 'secr3t')
)
private String secretField;

I want to externalize the 'secr3t' password, so it is not hardcoded anymore. But I cannot achieve this because :

Spring Boot annotations can only contain constant strings (no @Value("${encrypt.key}") possible to read it from application.properties)
I found the line current_setting('encrypt.key') to write in read = "pgp_sym_decrypt(secretField::bytea, current_setting('encrypt.key')), but it tells me org.postgresql.util.PSQLException: ERROR: unrecognized configuration parameter "encrypt.key" even if I declare encrypt.key in application.properties. I read that this parameter should be written in the server-side postgresql.conf configuration file, but it feels very weird not to have the password on the client side instead.

Is there a way to decrypt Postgresql columns with Spring Boot JPA without hardcoding the password ? I really thought there would be a line in application.properties that I could write for this, since client-side key is possible, it is just... hardcoded by JPA constant values restriction.

Nothing that I found refers to Spring Boot JPA decryption on https://www.postgresql.org/docs/8.3/pgcrypto.html or on the spring boot documentation. Everybody just seem to make examples with a hard-coded key.

Related questions :

Annotation can only contain static information (that has nothing to do with Spring Boot that is how it is defined in Java). Also, the password should really be on the database side and not the client-side. So what you think is weird is actually the way to handle it. Or don't use @ColumnTransformer but use an @AttributeConverter or user-type which does the conversion. — M. Deinum
– M. Deinum, Commented Sep 17, 2020 at 5:28
The @AttributeConverter approach is interesting, but I will need to manually decrypt the data, right ? In this case what is the way to know the initialisation verctor used by Postgres for an encryption ? Is there an example of manual Postgres decryption (whatever the language is) ? — Vulpo
– Vulpo, Commented Sep 17, 2020 at 9:56

Samuel Armbrust · Accepted Answer · 2021-04-23 14:46:37Z

1

There is an available answer here. The problem here is that it stores the data in the application.properties. In a production system, this solution is not the best one as it will change depending on the customer.

But you can use the same structure and define your secret.key in another way you can change depending on your customer-specific key (e.g. fetching from a secure store). Remember that, if you have more than one customer, it would be good to have separate keys for each one.

Maybe change the setKey() method visibility to public would be interesting, once working in a multitenant environment will not allow you to define this key in the constructor. I still need to test more this approach of making it public because it can also impact the segregation of duties of the application.

answered Apr 23, 2021 at 14:46

Samuel Armbrust

714 bronze badges

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

Collectives™ on Stack Overflow

How to externalize the Postgresql column encryption key with Spring Boot JPA?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related