Application users in row-level policies: how to set session variables

Question

I am trying to write row-level policies which check user rights before executing queries. In this context, I need to be able to set a session variable, containing the user identifier, when a connection is fetched from the connection pool and unset/release that same session variable when the connection is given back to the pool. The following blog might give a good background to explain what I am trying to do: https://blog.2ndquadrant.com/application-users-vs-row-level-security/

In my setup, I have the following layers:

1. Hikari: HikariDataSource
2. Spring: TransactionAwareDataSourceProxy (using Hikari data source), DataSourceTransactionManager (using Hikary data source)
3. jOOQ: DataSourceConnectionProvider (using Spring data source), DefaultDSLContext

The first two layers are internal only. That is, all queries in the application are done via jOOQ, the final layer.

So, I need to be able to implement a callback on connection take from pool and give back to pool.

I have been looking through documentation of Hikari and Spring, but couldn't find anything useful. This is probably my first question: does Hikari and/or Spring provide such callbacks?

The next step in my investigation was jOOQ. I stumbled upon ExecuteListener. This is my follow-up question: would ExecuteListener be a good application for this? This does not implement the actual point when connection is taken and given back, but an execution context is probably good enough in my case (as all my queries are done in jOOQ)? If so, should I implement the start and end functions?

Answer 1

This can be solved at various levels in your current stack. Here are two suggestions:

Solving this in a DataSource / Connection proxy

Most connection pools implement the following semantics:

• DataSource.getConnection(): "Take from pool"
• Connection.close(): "Give back to pool"

So, you could proxy those two APIs and implement your desired semantics inside of those relevant methods. This is a bit more work, but it will work with any JDBC client, including jOOQ (i.e. it will also work when you do not use jOOQ)

Solving this in jOOQ

If all your database interactions go through jOOQ, you can solve this more easily with jOOQ directly, using the ConnectionProvider SPI, which contains two methods that have exactly the semantics you require:

• ConnectionProvider.acquire()
• ConnectionProvider.release(Connection)

So, instead of using jOOQ's out-of-the-box DataSourceConnectionProvider, you could do this:

public class MyConnectionProvider implements ConnectionProvider {

    // Configure spring to inject your data source here
    @Autowire
    DataSource ds;

    @Override
    public Connection acquire() {
        try {
            Connection c = ds.getConnection();
            // Do your stuff here
            return c;
        }
        catch (SQLException e) {
            throw new DataAccessException("Something failed", e);
        }
    }

    @Override
    public void release(Connection c) {
        try {
            // Do your stuff here
            c.close();
        }
        catch (SQLException e) {
            throw new DataAccessException("Something failed", e);
        }
    }
}

You could do this with ExecuteListener, too, but the above approach is certainly simpler.