Hi :wave:! We are building a feature where we are ...

# exposed

[André Danielsson]

Hi 👋!
We are building a feature where we are supposed to encrypt some specific column values and one detail is that we are supposed to have different encryption keys depending on the tenant that is doing the request.

We have found two approaches that we are evaluating.

Approach 1:
We write a class implementing ColumnType that will be responsible for encryption and decryption when constructing the queries and returning results. We will need to create multiple instances, one per tenant, so that the appropriate encryption key is used. A snippet demonstrating this idea can be found here: https://pl.kotl.in/9azT-Jlr2

What we found is that this solution works when only using DSL but we are also using DAO to define relations and Entities are constructed by the Exposed library (using reflection?). To my knowledge there is no way of constructing Entities, passing our Table instances to it. This creates a problem for us as the codebase is using many DAOs today. Does anyone know if there is a way to get around that in any way and we can provide our own "factories" for creating Entity instances?

Also, I sense that this approach is a bit “hacky” to depend on the tenant inside the DB layer. So this made us look into a second approach.

Approach 2:
We handle encryption/decryption in a repository. To make sure we can’t mix up what is encrypted and what is not we are introducing a new type for encrypted text and then writing a ColumnType for it to wrap/unwrap the type. We then move the responsibility for encryption/decryption to the repository. We will have different repository instances scoped to tenants so that they can fetch the appropriate encryption key. This is an example how it would look: https://pl.kotl.in/E0ewX3hZ9

This approach will introduce more boilerplate but in this case I think it's not too bad as we will get type safety and it will be impossible to confuse encrypted text with a String.


1. Which would you say is the best approach?
2. Is passing scoped repositories into the ColumnTypes the wrong abstraction and is it better to keep them as simple as possible?

[André Danielsson]

I have now realized that neither of these approaches will work that well. When encrypting data with AES, the random IV will alter the encrypted text so joins and queries will not be possible. This is a major limitations for us so we will try to push for actually hosting multiple databases, one per tenant and use cloud provider services to encrypt them with the customer's key.