How to inject a variable inside @ColumnTransformer in Spring Boot dynamically?

Question

I'm working on a Spring Boot project using PostgresSQL DB. I need to encrypt/decrypt data then I follow this solution How to encrypt a column in Postgres using Hibernate @ColumnTransformer

As the example,

@ColumnTransformer(
    read =  "pgp_sym_decrypt(" +
            "    test, " +
            "    current_setting('encrypt.key')" +
            ")",
    write = "pgp_sym_encrypt( " +
            "    ?, " +
            "    current_setting('encrypt.key')" +
            ") "
)
@Column(columnDefinition = "bytea")
private String test;

He said that

encrypt.key is stored in the postgresql.conf configuration file

But I host the DB on AWS RDS and have no postgresql.conf . I want to read the key from variable from application.properties I try to directly inject it like,

read =  "pgp_sym_decrypt(" +
            "    test, " +
            "    '${psql.key}'"

But the it will consider '${psql.key}' as a string. How can I inject the variable inside it ?

Thanks for helps.

@krumpli not yet. What I have done so far is to directly put the string there. — Hikaru Shindo, Sep 12 '22 at 19:35

Arun · Answer 1 · 2023-08-14T07:57:18.513

Entity Class:

@ColumnTransformer(forColumn = "pan", read = "public.pgp_sym_decrypt(pan, current_setting('app.secret'), 'cipher-algo=aes256')", write = "public.pgp_sym_encrypt(?, current_setting('app.secret'), 'cipher-algo=aes256')")
@Column(name = "pan", columnDefinition = "bytea")
private String pan;

ConnectionProvider:

@Value("${spring.piiSecret}")
private String piiSecret;

@Override
public Connection getConnection(String tenantIdentifier) throws SQLException {
    log.info("Get connection for the Tenant - [{}]", tenantIdentifier);
    final var connection = getAnyConnection();
    connection.setSchema(tenantIdentifier);
    try (var sql = connection.createStatement()) {
        var piiQuery = MessageFormat.format("set app.secret = ''{0}'';", piiSecret);
        sql.execute(piiQuery);
    }
    return connection;
}

Explanation:

When a request is received on the server side, a new connection will be established with the database. At that moment, the value for the secret name used in the Entity class will be set. This approach makes everything dynamic. I hope this explanation is helpful.

How to inject a variable inside @ColumnTransformer in Spring Boot dynamically?

1 Answers1