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.

There are several ways to accomplish this but they all vary depending on the other frameworks you may be using. Tell me, is your application using Spring or is it written to run on an application server like WildFly? — Naros
– Naros, Commented Sep 16, 2019 at 19:00
@NTL Can you please answer : stackoverflow.com/questions/67043502/… — Sushma Kundapur
– Sushma Kundapur, Commented Apr 11, 2021 at 10:03

Naros · Accepted Answer · 2019-09-16 19:14:40Z

2

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.

answered Sep 16, 2019 at 19:14

Naros

21.3k4 gold badges50 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

NTL Over a year ago

I will look into this approach today and tomorrow, thank you.

NTL Over a year ago

This seems to work for the most part, however I am running into a new problem. I have defined a @Repository that extends PagingAndSortingRepository and in there, I have the following signature defined: Boolean existsTemplateByNameIgnoreCase(String name);. If I call this first, then the SecurityPolicyInjectingJpaTransactionManager gets ignored and my logic in prepareSynchronization does not get executed.

NTL Over a year ago

Update on my previous comment. The problem is solved by annotating the Boolean existsTemplateByNameIgnoreCase(String name); method with @Transactional. My question here is, is this something that can be done for every query without specifying the annotation?

Naros Over a year ago

Traditionally repository calls are wrapped either by a service method or a controller method that is annotated with a @Transactional. You never want to annotate the repository methods this way as a single atomic operation may involve multiple repositories. For example, to create an order I may need to fetch information from the ProductRepository, create the order and then save it using the OrderRepository. This entire operation is a single atomic action that should all be associated to a single database transaction.

Collectives™ on Stack Overflow

Postgres Hibernate set session variables for row level security

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related