javapostgresqlencryptionjooqpgcrypto

Client Side Encryption with Java and Postgres Database

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! :)

Solution

  • 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: