Spring boot set postgresql connection variable before each query

Question

I am using Spring boot, Spring data, Postgresql, and hikariCP for managing my application entities and queries. I am querying the db using Spring boot repositories and the queries/named functions inside the repositories aren't always annotated with @Transactional.

In my db I have a table Employee that has row-level security on it. The row-level security uses a session variable like this:

create policy employee_rls on Employee for select to public using (manager_id = CAST(current_setting('user_id') as integer));

How can I make it so that before each connection to the db from my application, I will set a session variable on the connection and clear it after the connection closes? The connection variable should come from an @autowired service called UserService and it should happen even for none transactional queries.

Answer 1

You want to look into creating a custom DataSource. See this for one solution that uses a ThreadLocal to achieve multitenancy. You basically set a thread local with the user id you want to initialize the connection and the custom datasource performs the SET when the connection is obtained from the datasource. If the thread local is not set and a connection is obtained your custom datasource it would clear the user id via RESET.

You say the value of the user id is obtained from UserService. You could inject this service into the custom datasource. You didn't mention how this is to be used.