Session hooks

A JdbcSessionHook bean prepares the database session of a connection for the work about to run on it. It is the way to run every statement as the current user (a role and the user's claims, for row-level security), to set a tenant, or a search path.

Why

A pool hands the same connections to every caller, so a setting made for one user would still be there for the next. Doing it by hand means a statement before every query and another after, in every place that touches the database, and a leak the first time one is forgotten. A hook is called by the starter at the two moments that matter, and a connection it could not reset is closed instead of reused.

Example

@Component()
class CurrentUserSession extends JdbcSessionHook {
  CurrentUserSession(this.users);

  final CurrentUser users;

  // Right after a transaction began: transaction scope, gone at its end.
  @override
  Future<void> afterBegin(ConnectionHolder transaction) async {
    await transaction.connection.query(
      "select set_config('app.user_id', ?, true)",
      [users.id],
    );
  }

  // Around one unit of work outside a transaction, on a pooled connection.
  @override
  Future<void> beforeWork(DataSource dataSource, JdbcConnection c) async {
    await c.query("select set_config('app.user_id', ?, false)", [users.id]);
  }

  @override
  Future<void> afterWork(DataSource dataSource, JdbcConnection c) =>
      c.execute('reset app.user_id');
}

A policy of the database can then read current_setting('app.user_id').

The three moments

Method When
afterBegin(transaction) Once for each transaction, right after it began and before its first statement. Every DataSourceTransactionManager of the data source calls it. What it sets with transaction scope (set local, set_config(…, true)) is gone when the transaction ends.
beforeWork(dataSource, connection) Before each call of JdbcTemplate, JdbcClient or a repository outside a transaction, on the pooled connection it got. For a stream, until the stream ends.
afterWork(dataSource, connection) After that work, before the connection goes back to the pool: take off what beforeWork set.

Each does nothing unless overridden.

The rules

A statement the database refuses for a missing privilege or a row-level security policy (PostgreSQL's 42501) is a PermissionDeniedDataAccessException; see Errors.

Used by

spring.supabase.db.rls: user of spring_boot_starter_supabase is a session hook: it sets the role authenticated or anon and the claims of the user's token.

In code

The jdbcSessionHookRegistrar bean gives the hooks to every DataSource bean (DataSource.sessionHooks). A data source made by hand takes them the same way:

dataSource.sessionHooks.add(hook);

In Spring (Java)

Spring has no such bean. The nearest are DataSourceTransactionManager.prepareTransactionalConnection, to override, and DataSource adapters such as UserCredentialsDataSourceAdapter.

See also