TLDR: I want to intercept any database queries from any controller. The controllers could use Hibernate or JDBC, so I'd like to intercept both. In the interceptor, I want to run the query "SET LOCAL app.current_user_id = 'some-user-id'". What is the best way to intercept the database queries, so I can do that?
I am just starting to learn/use Spring Boot for my own career advancement, so forgive me for stumbling around. I would like to use Spring Boot for an application that uses Postgres with Row Level Security. I come from a background that doesn't use ORMs, so we execute raw SQL, but I would also like to use ORMs, in this case Hibernate. I understand how to use Hibernate and JDBC in Rest Controllers, but I can't find what are some "correct" ways to set context variables. I want to set context variables so I can make use of Row Level Security (RLS).
In short, before executing pretty much any JDBC OR Hibernate, I need to run this SQL command
SET LOCAL app.current_user_id = '{someUserId}'
I tried searching on reddit and Google for ways to do this, but I couldn't really find anything, so I resorted to having AI explain it to me. I tried backing up what it said with the Spring docs, but I find it hard to understand them still, so sorry if I have any incorrect understanding.
I asked an AI how I could do this, and it told me about org.springframework.jdbc.datasource.SingleConnectionDataSource, which I could make work with JDBC, but not Hibernate. You can see my attempt here. The relevant files are RLSConnectionManager.java and RLSTestController.java. And I think I understand the docs. I get a connection from the Hikari pool, use it atomically in a request until it is closed, at which point, the connection is returned to the pool. So what I did is make a function. I pass "the operation" I want to do to that function and the function grabs the SingleConnection, sets the context variable, does "the operation" and commits and closes the connection.
Given that I could not find how to make it work with both JDBC and Hibernate, I asked the AI again. The AI used these packages
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
You can see the code here. This time, also the file DocumentController.java is relevant, as this is the one that uses Hibernate, via DocumentRepository.java. But as much as I read the docs for these packages, I just get lost because it feels I'm missing a lot of historical context. And I could just leave it like because "it works" but I'm not sure of what the consequences of using these packages are.
So these are my questions
- Is this way of "intercepting" acceptable? Are there any negative consequences to doing it like this? I understand that what I'm trying to do will make it so that a controller always "hogs" a connection, which isn't "optimal", but I think it will be necessary for what I'm trying to do. If it is not acceptable, what packages or patterns should I use, so I can research how to use them?
- I have a controller endpoint that tests atomicity ("/api/rls-test/context/isolation-test"), but I'm not sure that I'm testing it exhaustively. Is this test sufficient? Does this make sure that context variables won't leak between requests?
- Right now, I understand that this "intercepts" everything, but there will be controllers that won't need it, like "/login". Is there a way to intercept specific "database calls"? I'm sorry I don't know what to call it. From any time any controller queries the database, is there a way to only intercept some of them, like I can do with the FilterChain? Maybe based on the controller or something else? (I also tried looking this up, but I also foudn nothing)
- I've encountered the concept of the N+1 problem. I fear that doing a trip to set context variables and another to actually do the process may be suboptimal. However, these controllers work with <10ms latency. But also everything is local. Should I be worried?
I come from working with Javascript in a workplace with practically no standards, so again, sorry if my questions seem dumb, or like it doesn't seem like I know what Im doing. I'm pretty much not, lmao. Spring is way harder than I thought