How to join encrypted and plain sight columns in Spring Data JPA and Hibernate?

Question

I have two tables in a database (PostgreSQL), both of which have an id field that is a primary key, a common example_id and other fields.

A { id, example_id, first_name, last_name } <- Here example_id is always encrypted
B { id, example_id, qty, beds } <- Here example_id is always in plain sight

When I insert and read data from A, I use the @ColumnTransformer annotation to use the pgcrypto built-in function to encode and decode the example_id field.

@ColumnTransformer(
  forColumn = "example_id",
  read = "pgp_sym_decrypt(example_id, 'my_key')",
  write = "pgp_sym_encrypt(?, 'my_key')"
)
private String exampleId;

The problem now is this: how do I perform a JOIN between the two tables on the example_id field? Is there a JPA annotation to encrypt or decrypt a column on the fly before performing the match? Can it be done at the DB level? Or do I need to create a custom JPA query or move the logic to the application level?

Thanks in advance

Answer 1

In the end, I solved it by using a simple OneToOne relationship, and the underlying framework automatically performs the reading and writing of the encrypted value. Instead of encrypting the plaintext and comparing the cyphers, it automatically reads and decrypts the encrypted ones in the i-th record and performs the comparison.

However, to make it work, I had to correct the management of the cryptographic key using the current_setting function of the underlying database because it's not possible to inject the environment key dynamically and easily into JPA annotations. Additionally, I had to specify the schema in which the pgcrypto functions were installed.

@ColumnTransformer(
  forColumn = "example_id",
  read = "public.pgp_sym_decrypt(example_id, current_setting('encryption.key'))",
  write = "public.pgp_sym_encrypt(?, current_setting('encryption.key'))"
)
private String exampleId;

To do this, I had to access the postgresql.conf file and add an encryption.key property so that the current_setting function finds the key at the database level.

encryption.key = 'my_key'

However, be careful because the key is stored in plaintext in the database. It would be better to retrieve it from an external system through a Function or a Stored Procedure.