Implement pgcrypto functionality

[mdedetrich]

We have a need to encrypt certain columns in our database, and we are currently reviewing whether to use db level encryption (with pgcrypto module, i.e. http://www.postgresql.org/docs/9.3/static/pgcrypto.html and http://www.postgresonline.com/journal/archives/165-Encrypting-data-with-pgcrypto.html) or application level encryption (in which case, its a general postgres problem).

How difficult would it be to provide fairly seamless encryption with slick-pg, i.e. using a column type such as def encryptedField = column[SHA512[String]]?

[tminglei]

Hi @mdedetrich it's not so easy.

slick-pg can be easily enhanced to support encrypt functions, but maybe you can't use it as your expected. They can be used freely in query's select clause and where clause, but not in insert/update/mutate statements.

Because in insert/update/mutate statements, it accepts normal types as input types (i.e. String, Long, or case class composed by String/Long, etc.), but not slick Column or case class composed by columns.

p.s. slick SqlFunction processed value will be converted to Column type or others.

---

BTW, you can't integrate a function into your declared types, to let it auto wrap the function for you when insert/update a real value to db. Because ... you know, this behavivor is similiar to a sql injection ... JDBC PreparedStatement implementation , which slick/slick-pg based on, will prevent you to do that.

[mdedetrich]

Well it may be possible to use O.DBType function to set what encryption to use. I assume the insert/update/mutate limitation is not a postgres limitation, but a slick one (and if so, should this be an issue?)

[tminglei]

Well, to some degree, you're right.

Currently, with slick we can't freely express sql like this:

update set col1 = encrypt(?) from table1 where id = ?

[mdedetrich]

Well (at least If I am understanding this correctly?), its an issue because you can't insert any encrypted data?

Should I make an issue with slick then in regards to how we can implement encryption?

[tminglei]

Well, if you encrypted the data with your app code before inserting to db, you can insert the encrypted data to db, no difference from any other normal data.

But if you want to construct a sql like this, using Slick type-safe API:

update set col1 = encrypt(?) from table1 where id = ?

then use it to insert your raw data to db, you'll fail.

That means, you'll have to use app level encryption, instead of db level encryption.

[mdedetrich]

Yup precisely, the somewhat unique issue we have here, is that we want to encrypt data that uses the postgres JSON data type. Using application layer encryption isn't that hard if you just make a custom EncryptedJSON type, and store the stuff as text.

It sounds like using pgcrypto encryption module is not practically possible with the current way that slick is set up (correct me if I am wrong?), in which case, its probably an issue that should be reported with slick

[tminglei]

Yeah, using pgcrypto encryption module is not practically possible with the current way.

And I think you should report to slick about this. But it should be a feature request, instead of a defect.