Encrypt private key in database

[humphd]

For a user's certificates, we're going to store both the certificate and private key. The latter we need to encrypt. We need a solution for doing this.

As I understand it, we have 3 options:

• do it in MySQL (i.e., at the database level). Much of what I read suggests not doing this, but I'm not an expert.
• do it in Prisma middleware, for example https://github.com/47ng/prisma-field-encryption
• do it in the server code before we write to the db

We should evaluate these options (and maybe others?) and implement a solution.

[cychu42]

For option 1, it seems possible to do automatic encryption on MySQL side by setting triggers to run AES_ENCRYPT() on each update and insert.
https://stackoverflow.com/questions/50348954/safely-auto-encrypt-mysql-column-using-a-trigger-with-a-key-string

Although I do wonder where would the encryption key be stored in such case. Some research suggests it's safer to store on a separate machine, then the question would be how would the database get the key.

[humphd]

Thank you for researching. I wonder if we should abandon this. We can add it post 1.0 if that's important to ITS.

[cychu42]

I think it would make sense to ask ITS how they want to handle it, before we do anything, especially option 1 is essentially leaving it up to ITS to encrypt data and possible store that key.

[humphd]

I think it would make sense to ask ITS how they want to handle it, before we do anything, especially option 1 is essentially leaving it up to ITS to encrypt data and possible store that key.

I asked ITS as you requested, and they agree to simplify it by keeping it in plain text in the DB for now. Closing.

[cychu42]

OK, thank you.