How to intercept JDBC queries with Hibernate/Spring/Tomcat?

Question

I'm trying to implement the solution outlined in this answer. The short of it is: I want to set the role for each database connection in order to provide better data separation for different customers. This requires intercepting JDBC queries or transactions, setting the user before the query runs and resetting it afterwards. This is mainly done to comply with some regulatory requirements.

Currently I'm using Tomcat and Tomcat's JDBC pool connecting to a PostgreSQL database. The application is built with Spring and Hibernate. So far I couldn't find any point for intercepting the queries.

I tried JDBC interceptors for Tomcat's built in pool but they have to be global and I need to access data from my Web appliation in order to correlate requests to database users. As far as I see, Hibernate's interceptors work only on entities which is too high level for this use case.

What I need is something like the following:

class ConnectionPoolCallback {
  void onConnectionRetrieved(Connection conn) {
    conn.execute("SET ROLE " + getRole()); // getRole is some magic
  }
  void onConnectionReturned(Connection conn) {
    conn.execute("RESET ROLE");
  }
}

And now I need a place to register this callback... Does anybody have any idea how to implement something like this?

Answer 1

Option 1:

As Adam mentioned, use Hibernate 4's multi-tenant support. Read the docs on Hibernate multi-tenancy and then implement the MultiTenantConnectionProvider and CurrentTenantIdentifierResolver interfaces.

In the getConnection method, call SET ROLE as you've done above. Although it's at the Hibernate level, this hook is pretty close in functionality to what you asked for in your question.

Option 2:

I tried JDBC interceptors for Tomcat's built in pool but they have to be global and I need to access data from my Web appliation in order to correlate requests to database users.

If you can reconfigure your app to define the connection pool as a Spring bean rather than obtain it from Tomcat, you can probably add your own hook by proxying the data source:

<!-- I like c3p0, but use whatever pool you want -->
<bean id="actualDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="jdbcUrl" value="${db.url}"/>
        <property name="user" value="${db.user}" />
        .....

<!-- uses the actual data source.  name it "dataSource".  i believe the Spring tx
     stuff looks for a bean named "dataSource". -->
<bean id="dataSource" class="com.musiKk.RoleSettingDSProxy">
    <property name="actualDataSource"><ref bean="actualDataSource" /></property>
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource"><ref bean="dataSource" /></property>
....

And then build com.musiKk.RoleSettingDSProxy like this:

public class RoleSettingDSProxy implements DataSource {
    private DataSource actualDataSource;

    public Connection getConnection() throws SQLException {
        Connection con = actualDataSource.getConnection();

        // do your thing here. reference a thread local set by
        // a servlet filter to get the current tenant and set the role

        return con;
    }

    public void setActualDataSource(DataSource actualDataSource) {
        this.actualDataSource = actualDataSource;
    }

Note that I haven't actually tried option 2, it's just an idea. I can't immediately think of any reason why it wouldn't work, but it may unravel on you for some reason if you try to implement it.

Answer 2

Hibernate 4 has multitenancy support. For plain sql you will need datasource routing which I believe spring has now or is an addon.

I would not mess ( ie extend) the pool library.