Application Layer Encryption Part 4: Database Integration

Overview

In previous posts we shared how we built self-service encryption infrastructure in the cloud, how we decoupled encryption keys from services and attached them to data instead, and how we encrypt the proto messages.

That last post ended with a number we're pretty proud of: 18 Terabytes of data encrypted and decrypted every day. But protobuf messages are just a part of that number.
In this post, we’ll discuss encrypting data as it's written to and read from the database, without asking engineers to do the encrypting.
We applied the same principle that has guided this entire series: engineers declare what should be encrypted, and the framework takes care of how to do it.

To make it easy and familiar, we wanted to introduce database encryption in a way that feels natural to the problem domain.
At Block, both jOOQ and Hibernate are the most popular ORMs being used.
With Hibernate you’d usually write your own model classes representing the database tables, and use code annotations to specify column values and other relations and properties.
jOOQ on the other hand, generates model classes based on a given database schema.
The rest of this post will go into more detail on how we integrated application layer encryption into these ORMs at Block.

Hibernate

Hibernate exposes an events system that lets users implement listener hooks for specific events in an entity’s lifecycle.
That was the mechanism that was used to inspect every entity as it’s being written and read from the database.
To encrypt, we implemented the PreInsertEventListener and PreUpdateEventListener interfaces, and to decrypt we implemented the PostLoadEventListener interface.
The business logic of the implementing class is relatively simple: when invoked, check if the entity that’s involved in the event happening is supposed to be encrypted/decrypted, and encrypt/decrypt accordingly.

kotlin
1override fun onPreInsert(event: PreInsertEvent) = encryptSupportedColumnFields(event.entity)

The first thing the encryptSupportedColumnFields needs to do is check which fields in the entity should be encrypted, if any.
Marking a field as encrypted is a single annotation on the entity:

kotlin
1@EncryptedField(keyName = "customer_pii_key", type = NonDeterministic)
2@Column(name = "encrypted_customer_ssn")
3var customerSsn: ByteArray = byteArrayOf()

The next thing the encryptSupportedColumnFields function does is get the encryption key for that column, encrypt it, and then substitute the ciphertext into the entity state that Hibernate is about to persist.

The opposite happens for decryption.

One last feature that we introduced specifically for Hibernate was the EncryptedFieldContext annotation.
By adding this annotation on a function and specifying the column name, we could include the values of other columns when encrypting using Tink’s AEAD primitive.

The end result from the user’s perspective looks very simple and natural.
An example Hibernate model class would look something like this:

kotlin
1@Entity
2@Table(name = "example_table")
3class DbExampleRecord constructor() : DbUnsharded<DbExampleRecord> {
4  @javax.persistence.Id @GeneratedValue override lateinit var id: Id<DbExampleRecord>
5
6  @Column(nullable = false) lateinit var name: String
7
8  @Column(nullable = false) var aliases: Int = 0
9
10  @Column(nullable = true)
11  @EncryptedField(keyName = "column_3_key", type = EncryptedFieldType.NonDeterministic)
12  var column3: String? = null
13
14  @Column(nullable = true)
15  @EncryptedField(keyName = "column_4_key", type = EncryptedFieldType.Indexable)
16  var column4: ByteArray? = null
17
18  @EncryptedFieldContext("column3")
19  fun column3Context(): Map<String, String> {
20    val context = mutableMapOf("constantKey" to "constantValue", "aliases" to "$aliases")
21    return context
22  }
23
24  constructor(name: String, aliases: Int, column3: String? = null, column4: ByteArray? = null) : this() {
25    this.name = name
26    this.aliases = aliases
27    this.column3 = column3
28    this.column4 = column4
29  }
30}

The above example models a table with 5 columns: id, name, aliases, column3, and column4. The last 2 columns are encrypted.
column3 is encrypted by an AEAD key called column_3_key, and column4 is encrypted using a Deterministic AEAD key called column_4_key.
column3 also has an encryption context function called column3Context which returns a map of values that’d be serialized into a byte array and then used as the AAD when encrypting and decrypting this field.

There are a few more details we needed to handle which make this a little more involved than the above.
For example, we had to implement the PostLoadEventListener and PostUpdateEventListener interfaces to decrypt the fields we just encrypted - because we substitute ciphertext into the entity right before it's persisted, the entity object would otherwise be left holding encrypted bytes after a save.
If the code keeps using that entity, it should see plaintext, not ciphertext.
We also had to extend and replace Hibernate’s DefaultFlushEntityEventListener to ensure that unchanged encrypted entities are not marked dirty.
When entities contain fields that are encrypted with AEAD (non-deterministic), other event listeners replace their fields with plaintext versions when they are loaded from the database. This causes them to be persisted even when they're unchanged.
The flush listener adds an extra check after the default dirty check to remove any properties that were marked dirty, but should not have been.
Lastly, because we also had to make some performance considerations to make sure this is usable.
Encryption is a relatively slow process, and when combined with the process of searching for the key name in a class annotation which involves code reflection, it can result in a significant performance degradation.
The next thing we had to incorporate into this solution was a caching mechanism that’d store a map of entity class names, and their encrypted fields and associated encryption key names.

jOOQ

The business logic required to encrypt data via jOOQ is the same, but the implementation is slightly different.
Instead of hooking on event listeners, we had to inject our encryption/decryption functions statically via jOOQ’s code generator. And since we already had some experience with how to encrypt this type of data, and knew how to avoid the common pitfalls, we decided to open source this integration.
It’s published to Maven Central as app.cash.jooq:jooq-encryption.

From the user’s perspective, integrating encryption is simple.
The user has to do 2 things:

1. Configure jOOQ to use our custom code generator.

   1jooq {
   2  configurations {
   3    create("main") {
   4      jooqConfiguration.apply {
   5        generator.apply {
   6          name = "app.cash.jooq.jooq.EncryptionAwareJavaGenerator"
   7          // ...
   8        }
   9      }
   10    }
   11  }
   12}

   The EncryptionAwareJavaGenerator finds every column that's eligible for encryption — any VARBINARY column without an existing forced converter — and attaches a jOOQ Converter to it.
   The converter will check if the column name has an encryption key associated with it, and encrypt values on the way in and decrypt them on the way out.

2. Configure and initialize our encryption primitive with a map of column names to encryption primitives.

   kotlin
   1// Initialize the keys you need in your application's main/bootstrap section.
   2// Calling KeysetHandle.read() requires a KMS client as a parameter to safely read and decrypt key material. 
   3// NOTE: Never persist key material in plaintext.
   4val dataType1EncryptionKey = KeysetHandle.read(/* ... */).getPrimitive(Aead::class.java)
   5val dataType2EncryptionKey = KeysetHandle.read(/* ... */).getPrimitive(DeterministicAead::class.java)
   6
   7val jooqNonDeterministicKeyMap = mapOf(
   8    "myTable.someColumn" to dataType1EncryptionKey,
   9)
   10val jooqIndexableKeyMap = mapOf(
   11    "myTable.anotherColumn" to dataType2EncryptionKey,
   12)
   13
   14// Make sure this statement is executed before any other database interactions
   15RealJooqKeyPrimitive.initialize(jooqNonDeterministicKeyMap, jooqIndexableKeyMap)

Threat Model

The application layer database encryption libraries we created leverage 2 very important security features:

• AAD (Additional Authentication Data)
• Deterministic Encryption (DAEAD) Unfortunately, with added security comes added friction.
  We believe these are reasonable trade-offs.

AAD

The library we wrote leverages AEAD by binding the table and column names of the data being encrypted in its AAD.
Consider an attacker (or a very confused backfill script) with write access to the database, but no access to the keys. They cannot decrypt the data without the corresponding key - but without AAD, they could still move ciphertext around.
For example, they could copy the encrypted SSN from someone else's row into their own, or swap an encrypted value from a low-sensitivity column into a high-sensitivity one and wait for the application to decrypt it in the wrong context.
With the table and column in the AAD, a ciphertext transplanted across tables or columns is designed to fail to decrypt.

The downside to using the table and column in the AAD, is that renaming the table or column breaks the decryption operation.
To bypass this problem, instead of performing an ALTER TABLE/COLUMN migration statement, users needs to break this into operation into a couple of stages:

1. Create a new table/column
2. Backfill the existing data to the new table or column
3. Delete the old table/column

It’s no fun to do it, but if using careful data model planning I think it’s a reasonable tradeoff.

Deterministic Encryption

By design, good encryption is indistinguishable from random noise, which makes encrypted columns useless in a WHERE clause.
For most encrypted data like addresses, documents, free-form PII that's not a problem.
For situations where fetching a record by an encrypted value, we support an Indexable column type backed by Tink's Deterministic AEAD primitive.
Using DAEAD means that identical plaintext produces identical ciphertext, so exact-match equality queries work.
It's pretty minimal support (no LIKE, no ranges, no ordering — just =), but still.

Deterministic encryption provides weaker security compared to non deterministic encryption, and it’s a deliberate trade-off and should only be used on plaintext values with real entropy.
An encrypted phone number column is much weaker than it looks because it’s still possible to enumerate every valid phone number, encrypt each one, and build a lookup table.
Determinism also requires careful planning with key rotation — after rotating a DAEAD key, new writes use new key material and your equality queries can quietly start missing older rows.