Encrypting PostgreSQL database columns in Spring Boot JPA entities

chris — Wed, 24 Jul 2024 08:20:54 +0000

A simple tutorial on how to encrypt single columns in a PostgreSQL database using JPA entities in a Spring Boot project. Let's start!

1. Storing the encryption key

We will use a property in the application.yml file to store the encryption key.

⚠ This is not the best practice in a production environment, so consider looking for a more secure way.

Create a property called encryptionSecretKey in the application.yml file under security-properties > security and assign it a random string:



security-properties:
  security:
    encryptionSecretKey: 1234567890123456

You can use a different property instead, but make sure to adapt the code from the next step.

2. Making the key available to the database

We have to create a temporary parameter in the database to store the key, in order to access it in the next step. The set_config() function from PostgreSQL is what we need.

Create a property called connectionInitSql in the application.yml file under spring > datasource > hikari and assign it the following query:



spring:
  datasource:
    hikari:
      connectionInitSql: select set_config('encryption.key', '${security-properties.encryptionSecretKey}', false)

This code will run the function set_config( setting_name, new_value, is_local) each time a new database connection is created. The third parameter of the function is set to false, so the encryption key will apply to the current database session instead of the current transaction only.

3. Adding the `pgcrypto` extension

The database needs to have the pgcrypto extension added to work properly. Execute this query on a database console:



-- Create pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

4. Encrypting the actual data

We have an entity called UMUser with an email and a login field that need to be encrypted. We just need to add the annotations @ColumnTransformer and @Column to each one:



/** An user. */
@Entity
@Table(name = "um_user")
public class UMUser {

  @ColumnTransformer(
      read = "pgp_sym_decrypt(" + "login, " + "current_setting('encryption.key')" + ")",
      write = "pgp_sym_encrypt(" + "?::text," + "current_setting('encryption.key')" + ")")
  @NotNull
  @Column(name = "login", nullable = false, columnDefinition = "bytea")
  private String login;

  @ColumnTransformer(
      read = "pgp_sym_decrypt(" + "email, " + "current_setting('encryption.key')" + ")",
      write = "pgp_sym_encrypt(" + "?::text," + "current_setting('encryption.key')" + ")")
  @Column(name = "email", columnDefinition = "bytea")
  private String email;

See how we use the pgp_sym_decrypt() and pgp_sym_encrypt() functions from PostgreSQL, which access the key to read and write the fields using current_setting(). The @Column annotation needs to include columnDefinition = "bytea", since the encrypted value is stored in bytes.

DEV Community: chris

Encrypting PostgreSQL database columns in Spring Boot JPA entities

1. Storing the encryption key

2. Making the key available to the database

3. Adding the pgcrypto extension

4. Encrypting the actual data

3. Adding the `pgcrypto` extension