6

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 ?

Valentin Rochet
  • 137
  • 1
  • 11

1 Answers1

2

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:

@Dependent
@Unremovable
public class TransactionObserver {

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

    @Inject
    EntityManager em;

    public void interceptTransaction(@Observes @Initialized(TransactionScoped.class) Transaction tx) {
        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;
max
  • 1,134
  • 10
  • 16