Postgres Hibernate set session variables for row level security

Question

I am having trouble finding information about this issue I am running into. I am interested in implementing row level security on my Postgres db and I am looking for a way to be able to set postgres session variables automatically through some form of an interceptor. Now, I know that with hibernate you are able to do row-level-security using @Filter and @FilterDef, however I would like to additionally set policies on my DB.

A very simple way of doing this would be to execute the SQL statement SET variable=value prior to every query, though I have not been able to find any information on this.

This is being used on a spring-boot application and every request is expected to will have access to a request-specific value of the variable.

Answer 1

Since your application uses spring, you could try accomplishing this in one of a few ways:

Spring AOP

In this approach, you write an advice that you ask spring to apply to specific methods. If your methods use the @Transactional annotation, you could have the advice be applied to those immediately after the transaction has started.

Extended TransactionManager Implementation

Lets assume your transaction is using JpaTransactionManager.

public class SecurityPolicyInjectingJpaTransactionManager extends JpaTransactionManager {
  @Autowired
  private EntityManager entityManager;

  // constructors

  @Override
  protected void prepareSynchronization(DefaultTransactionStatus status, TransactionDefinition definition) {
    super.prepareSynchronization(status, definition);
    if (status.isNewTransaction()) {
      // Use entityManager to execute your database policy param/values

      // I would suggest you also register an after-completion callback synchronization
      // This after-completion would clear all the policy param/values
      // regardless of whether the transaction succeeded or failed 
      // since this happens just before it gets returned to the connection pool
    }
  }
}

Now simply configure your JPA environment to use your custom JpaTransactionManager class.

---

There are likely others, but these are the two that come to mind that I've explored.