Is it possible to variabilize the password in Hibernate @ColumnTransformer for pgcrypto?

Question

I'm working on a postgresql base that use pgcrypto to encrypt some columns of the base.

Java side, I use JPA to "map" my classes to my database.

Currently, to encrypt and decrypt fields in java I use @ColumnTransformer. Example :

@Column(name = "my_column", nullable = false)
@ColumnTransformer(read = "pgp_sym_decrypt(my_column, "my_password")", write = "pgp_sym_encrypt(?, "my_password")")
private String myColumn;

This works fine. But I would like to variabilize my password. My application will be deployed on several servers and the password will be different for each one.

I tried a lot of things, I searched on internet but I didn't find anything. So I'm starting to think... is this just possible with this annotation ? Or should I encrypt/decrypt in another way ? In the repository maybe ?

PS here are a few examples of what I tried :

@ColumnTransformer(read = "pgp_sym_decrypt(my_column, ${application.security.pgcryptoPassword})", write = "pgp_sym_encrypt(?, ${application.security.pgcryptoPassword})")

with application.security.pgcryptoPassword defined in my configuration file

@Value("${application.security.pgcryptoPassword}")
private static final String pgcryptoPassword;
private static final String readCreator = "pgp_sym_decrypt(creation_aladdin_par, " + pgcryptoPassword + ")";
@ColumnTransformer(read = readCreator, write = "pgp_sym_encrypt(?, my_password)")

EDIT

I tried 2 things :

private static final String pgcryptoPassword = "my_password";

@ColumnTransformer(read = "pgp_sym_decrypt(my_column, '" + pgcryptoPassword + "')", write = "pgp_sym_encrypt(?, '" + pgcryptoPassword  + "')")
private String myColumn;

and

@Value("${application.security.pgcryptoPassword}")
private static final String pgcryptoPassword;
@ColumnTransformer(read = "pgp_sym_decrypt(my_column, '" + pgcryptoPassword + "')", write = "pgp_sym_encrypt(?, '" + pgcryptoPassword  + "')")
private String myColumn;

First works, second I get the error :
The value for annotation attribute ColumnTransformer.read must be a constant expression
From what I found on internet about this error, I think there is no way to variabilize my password in a configuration file and use it in the @ColumnTransformer annotation.

Do you agree with me or do you have another solution ?

Savanna

An annotation, any annotation, requires static info, yes. – Progestin

Have you found a solution? – Kemppe

Did you get the solution? – Cryptanalysis

Hey. I'm not sure cause it was quite a time ago, but I think we couldn't find a proper solution, so we did the decrypt part in the SQL requests. We had to do it in every request... not super clean – Savanna

look this issue a solution seems to be found – Sorce

Answer 1

You cannot use other than static parameters in annotations.

What we did is to set the password in the current jdbc session (Transaction Interceptor)

SET env_key = 'my-super-secret-pwd';

and than read the password via postgresql function

current_setting('enc_key')

In Quarkus the transaction interceptor looks like that:

@ApplicationScoped
@Unremovable
public class TransactionObserver {

    @ConfigProperty(name = "my.encryption.key")
    String envEncryptionKey;

    @Inject
    EntityManager em;

    public void onBeginTransaction(@Observes @Initialized(TransactionScoped.class) Object event) {
        addEncryptionKeyToSession();
    }

    private void addEncryptionKeyToSession() {
        em.createNativeQuery("SET enc_key = '" + envEncryptionKey + "';").executeUpdate();
    }
}

and in the entity you do something like that:

@ColumnTransformer(read = "pgp_sym_decrypt(NAME, current_setting('enc_key'))", write = "pgp_sym_encrypt(?, current_setting('enc_key'))")
@Column(name = "NAME", columnDefinition = "bytea", unique = false, nullable = true)
public String name;

Homogeny

Can you pls. share snippet for Springboot ? – Epithelioma

@SriramR Sorry we don't use this clumsy Spring stuff ;-) But I guess you do it with the "@TransactionalEventListener" - see baeldung.com/spring-events – Homogeny

Answer 2

You can take advantage of HikariCP connectionInitSql property.

my-key: 1234

spring:
  datasource:
    hikari:
      connection-init-sql: select set_config('encryption.key', '${my-key}', false)

Then each time getConnection() is called init query is invoked and available for @ColumnTransformer using current_setting function like this:

@Entity
public class Organization {

  @Id
  private UUID id;

  @ColumnTransformer(
      read = """
          pgp_sym_decrypt(
            access_token,
            current_setting('encryption.key')
          )
          """,
      write = """
          pgp_sym_encrypt(
            ?,
            current_setting('encryption.key')
          )
          """
  )
  @Column(columnDefinition = "bytea")
  private String accessToken;
}

Pebble