Add custom database session variable to Spring Boot JPA queries?

Question

I am trying to set SET SESSION encrypt.key='some_key' to database queries or connection.

Thing is I have following column definition in my model class

@ColumnTransformer(forColumn = "first_name",
        read = "pgp_sym_decrypt(first_name, current_setting('encrypt.key'))",
        write = "pgp_sym_encrypt(?, current_setting('encrypt.key'))")
@Column(name = "first_name", columnDefinition = "bytea")
private String firstName;

Above works when we set encrypt.key in postgres.conf file directly but out requirement is to have encrypt.key configurable from our spring properties file.

Things I tried.

1. AttributeConverter annotation with custom Converter class which only works with JPA, and LIKE operations are not supported.
2. I tried ContextEventListener where I executed SET SESSION query at application startup but that only works for few requests
3. Next I tried CustomTransactionManager extends JpaTransactionManager where I was doing following

@Override

protected void prepareSynchronization(DefaultTransactionStatus status,TransactionDefinition definition) {
        super.prepareSynchronization(status, definition);
        if (status.isNewTransaction()) {
            final String query = "SET encrypt.key='" + encryptKey + "'";
            entityManager.createNativeQuery(query).executeUpdate();
        }
        log.info("Encrypt Key : {}", entityManager.createNativeQuery("SElECT current_setting('encrypt.key')").getSingleResult());
    }
}

Above does not work when I call normal JPA Repository methods and encrypt.key is not set as the CustomTransactionManager class in not called.

Any guidance in right direction would help me a lot

Answer 1

Since I created CustomTransactionManager extends JpaTransactionManager

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Primary;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.support.DefaultTransactionStatus;

import javax.persistence.EntityManager;

@Component
@Slf4j
@Primary
@Qualifier(value = "transactionManager")
public class CustomTransactionManager extends JpaTransactionManager {
    @Autowired
    private EntityManager entityManager;

    @Value("${database.encryption.key}")
    private String encryptKey;

    @Override
    protected void prepareSynchronization(DefaultTransactionStatus status, TransactionDefinition definition) {
        super.prepareSynchronization(status, definition);
        if (status.isNewTransaction()) {
            final String query = "SET SESSION encrypt.key='" + encryptKey + "'";
            entityManager.createNativeQuery(query).executeUpdate();
        }
    }
}

Above was not getting called when I used normal JPA Repository methods. For example,

public interface UserRepository extends JpaRepository<User, Long> {

    Optional<User> findByFirstName(String firstName);

}

Adding @Transactional on Repository class did override framework logic where a shared transaction was getting created behind-the-scenes for all repository beans. This resulted in my CustomTransactionManager to be called even with repository methods.

I initially thought that adding Transactional annotation was overkill but found out that it gets created automatically at framework level as well so manually adding it had no additional footprint on its own but code/query you write inside CustomTransactionManager class will add required request footprint.

So I ended up adding @Transactional annotation on all repository classes whose domain(table) had encrypted columns.

For my use-case, this was the most flexible solution to have column level encryption on Azure postgres datbase service with Spring boot because we can not add custom environment variables there from Azure Portal, and directly adding to postgres.conf file also not possible due it being a SAAS service.