1

I've done quite a bit of digging and research on implementations of client side per-tenant encryption but am a bit stumped on how I should proceed with our application. For some background we've got a dropwizard application that has many custom DAOs that stores information in our postgres database using jooq. There are many tables, custom functions, and custom user defined data types for our database. Our requirement is that some sensitive data for our customers needs to be encrypted (certain columns in the database tables). The data is partitioned by rows per tenant.

I understand I've got two options of where to put the encryption of the data.

  1. In our application
  2. In our postgres database

Here's what I've found when exploring the two options above:

While putting the encryption in our application was my original plan having to edit every DAO will likely cause many problems and take an obscene amount of time.

So I've looked at the alternative of having the database handle the encryption using pgcrypto with column-side encryption. However, like a mentioned earlier because of all of our custom data types for columns I'm not able to store any encrypted data for these custom data type columns since the encrypted data is returned as a bytea. Pruning docs and other stack overflow posts I was not able to find a solution to this other than changing the data type of these columns to accept bytea vs their custom types which I'd like to avoid if possible.

After this I'm a bit stumped on how best to proceed next as both options I've laid out here would take a large chunk of time and possibly break a lot of our app.

Some other ideas I've had:

  1. I've looked into jooq or liquibase doing the encryption but couldn't find anything there.
  2. Instead of updating the data types of columns just create a new encrypted version of the database and use views for plaintext representations of tables.

My big question: Given the background information here, are there any suggestions or alternatives to implement client side encryption? (With being the least disruptive to our current architecture)

Thanks! :)

jban
  • 23
  • 4
  • Encrypting in the application is way more secure than sending unencrypted data to the database to be processed by `pgcrypto` functions. – Laurenz Albe Sep 15 '21 at 04:09
  • Yeah I agree after reading up on it there seem to be far more risks performing encryption in the database but updating our application code doesn't seem viable at this point. – jban Sep 16 '21 at 00:15

1 Answers1

0

You can easily use a jOOQ Converter<String, String> that implements your encryption/decryption transparently on all desired columns, if you attach it to those columns using the code generator.

Example:

public class EncryptionConverter extends AbstractConverter<String, String> {
    public EncryptionConverter() {
        super(String.class, String.class);
    }

    @Override
    public String from(String databaseObject) {
        return EncryptUtil.decrypt(databaseObject);
    }

    @Override
    public String to(String userObject) {
        return EncryptUtil.encrypt(userObject);
    }
}

More details here:

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Thanks for this seems promising! We do use converters already for our custom types. Is it possible to store the encrypted cipher text using this method without changing the custom types of the columns? I'm guessing we'd still have to change them. – jban Sep 21 '21 at 22:09
  • @jban: Why do you not want to use "custom types"? I mean, you can attach converters every single time you interact with the column: https://www.jooq.org/doc/latest/manual/sql-execution/fetching/ad-hoc-converter. But that seems tedious and error prone. Why not just attach it to the columns for good? – Lukas Eder Sep 22 '21 at 07:56
  • That's just how our db was designed initially but we are already attaching converters to certain columns in our code generator. Anyhow I'll give this a shot and appreciate your answer! – jban Sep 30 '21 at 16:44