Pass additional information (e.g. a JWT token) to the database

Hibernate ORM
1

Hi guys,

Here is my scenario:

  • the backend uses a Postgresql database via JPA, and Hibernate
  • the backend also has a JWT token, obtained via an OAuth session

Now I want to send the JWT token and the query as a result of the JPA call to the database (or a proxy of the database running in a separate process). The database (or its proxy) should validate the JWT token, and send the username plus the query to the database.

However, from a backend perspective, I want to use JPA.

Does someone have a suggestion how to implement this? Specifically how the database or its proxy an obtain the JWT token, or how the Java JPA code can send the token with, or as part of, the generated JPA / SQL query.

Best,

– Jaap Gordijn

2

What sort of proxy are we talking about here? Also, why can’t you just validate that token on the backend?

3

What I want to achieve is the following:

  • access control done (as much as possible) by the DBMS (e.g. with role level security).
  • the database can have more than one back-end and we do not trust the back-end. Hence access control will be done by the database. The back-end only provides the REST services, without any access control, and the request is passed via JPA to the database, along with a one time access token.
  • we want to implement access control as much as possible in the DBMS, and for the things not possible, the DMBS will delegate this to a proxy (between back-end and database). The proxy will be seperate process and has to be developed.
4

I don’t really understand how you can’t trust your own backend but can trust this proxy that you haven’t developed yet.
You do understand that every of your backends will have to be able to communicate with that proxy through whatever protocol you come up with?

If you really want something like that, I would recommend you to look into “security definer” functions: PostgreSQL: Documentation: 16: CREATE FUNCTION

You will essentially connect to the database with a user that has no permissions except calling your customer security definer function. You pass your token to that security definer function and check the validity of it in that function. If it is valid, you switch the user from within the function.
Don’t forget to reset the current user before returning the connection to the connection pool though. You can do that with a custom ConnectionProvider: Hibernate ORM User Guide

5

Thanks for your suggestions. I will look into this .

To explain a bit more: the backend hosts plugins of third parties. These are not trusted. But the database, or a proxy that represents the database, is trusted and allows only access to data the user is allowed to. The idea is that processing and checking of a signed username, who accessed the data, is done by a trusted component, and the database only returns the data the user is entitled to see.