16

For a GDPR (EU General Data Protection Regulation) implementation, I need to encrypt some personal information. There are two ways we can encrypt the data:

  • Let the web application have the sole responsibility of encrypting and decrypting. The actual data in database is fully encrypted. This way, if the data is stolen, the data is safe (assuming my encryption is good).

  • Enable encryption on the database level and add the accessing web application as a trusted client. The encryption and decryption happens in database itself. This way, if the database credentials is known, the data is lost.

So which is the best way without compromising too much on performance?

  • 1
    Similar question here – David Magalhães Jul 8 at 14:28
  • 13
    Should your implementation ever be audited, "I'm using the industry standard in database XXX" will probably give you a lot less headache than "I hacked something together myself". – Guntram Blohm Jul 9 at 6:00
  • @GuntramBlohm I'm stealing that point for my answer, because that's a very good one. Proving your custom software secure is extremely hard, because writing secure software is hard; if you can point to someone else and say "they wrote it, these are their credentials, these are the people who've reviewed it already and found it secure", that's much simpler to deal with. – Nic Hartley Jul 9 at 17:30
  • If your goal is compliance and certification, encryption at the database level may be the better option. If your goal is true security for your users, however, encryption at the application level may be the better answer, because the keys may never reach the database (where the encrypted data is stored). If the database is on a separate machine, this setup can certainly be more secure. But usually, you want both, compliance and true security. – caw Jul 11 at 16:22
27

Letting your database handle the encryption/decryption is probably for the best:

  1. You don't need to write any encryption/decryption code and risk breaking your own security by accident. This also means, as Guntram Blohm pointed out, you won't have to prove your own security to be secure, if it comes down to it. And proving your custom software secure is as hard as you probably think it is, if not moreso.
  2. Using the stuff built-in to your database is typically easier, e.g. Postgres lets you pick which columns and tables to encrypt, and their design only briefly has the keys and decrypted data on the server. Other databases are probably similar, but that's the only one I'm familiar with.
  3. If you ever need to, say, generate a report based on the data, it'll be simpler to do that with an existing tool that supports encrypted databases than writing all the custom code yourself on the client-side.
  4. If you ever need to do any filtering on the data that isn't on exact, case-sensitive values, the database will handle doing it efficiently for you, e.g. maintaining indices but keeping them encrypted until they're needed, and even then only decrypting the specific values they need at any given time.

There is one point in favor of client-side encryption, which you've already mentioned. It's a big one, but I don't think it outweighs the benefits above:

  1. Your security will be slightly increased if you handle all encryption/decryption on the client side, because at no point does the database (and therefore any evil sysadmins/attackers in control of that server) know either the decryption key or the decrypted data. However, depending on the data and its usage, there are some cons to consider:
    • It's entirely possible you'll mess up your client-side encryption code and leak the data/key that way, either directly or through side channels.
    • You need to either trust an outsider or handle key-management yourself, which isn't trivial (and is very not trivial to support in code, if you want to avoid scheduled downtime every so often)
    • If you do mess up your code, you alone (well, your company alone -- you know what I mean) are responsible for fixing it and cleaning up the damage, and any fixes are just as likely to have bugs as your original code. Possibly more, given that you'll be stressed.
    • You'll either have to spend a lot of time performance tuning, or accept that your performance will be bad, or reduce security for easy performance gains (e.g. by short-circuiting a comparison or precalculating some constants or letting your compiler optimize the wrong bits). None of those are good things to do. The database server has already had a lot of people doing a lot of tuning; you'd be redoing all that effort from scratch.
    • For every single new dev, you'll need to either train them in good secure coding practices, or tell them not to touch the security-related bits of your code, neither of which is cost-effective or even generally that reliable.
    • If you need to implement fuzzy filtering client-side, you're basically going to have to do SELECT * FROM table; and manually decrypt and filter every single row, which is just... not going to go well. You'll be keeping the key in memory in a directly-web-accessible server for a long time, as a direct response to a request which can be sent by an attacker.

As Kevin points out, you also have the option of encrypting specific fields. For example, if you're storing a customer's username and credit card number, the latter definitely needs to be encrypted, but the former probably doesn't. You could include e.g. a CHECK constraint to ensure that you don't accidentally add an unencrypted credit card. Credit card numbers are a particularly good example of this, because you never need to do any sort of matching against them -- you only ever retrieve them from or store them to the database, so you can handle them efficiently from the client side.

However, note that good security is not one thing. Good security is doing everything right:

  • Analyzing your threat model to ensure you're guarding against the right threats
  • Using HTTPS for your whole website with valid, current certificates
  • Encrypting your database, one way or another
  • Encrypting the communication between your website and your database
  • Using SQL correctly to avoid SQL injection
  • Hardening your database server to protect it from attacks
  • Applying the principle of least privilege
  • And so much more.

However you handle your database, it's not the end of the road. Make sure you take care of everything.

  • Field-level encryption can work well if you're prepared to put in the effort of managing and rotating all of those keys yourself, or pay someone else to do it for you. The latter option may require an undesirable level of trust in the cloud provider, but is relatively easier to implement and (probably) more secure than home-grown code. Field-level encryption is appropriate to mitigate some kinds of insider risk, particularly if a large number of people need access to the database as a whole, but not the individual fields. – Kevin Jul 9 at 17:32
  • @Kevin I feel like if a lot of people need access to the production data, there are better ways to give them that, e.g. creating readonly replicas which entirely exclude the sensitive data. That's a good point, though. I'll add a paragraph about encrypting only some fields when I get a chance. Though I'd like to point out that even if someone else handles all of the encryption code, doing it in your webapp still requires you to roll some of your own crypto management code, which can be just as dangerous as rolling your own crypto. e.g. forgetting to call encrypt_ssn in even one place... – Nic Hartley Jul 9 at 17:42
  • 1
    You can use a CHECK constraint against "does this look like a properly-decorated encrypted blob" for that. Also, nobody said you can't do both DB-level and field-level crypto at once. In fact, I'd go for broke and throw in full-disk encryption too. (But, as always, it does depend on your threat model. Will someone actually walk away with that HDD?) – Kevin Jul 9 at 17:43
  • @Kevin I hope you don't mind me basically quoting you verbatim when I add that to this answer. Or, if you want, you can edit it in yourself; I won't be able to for another little bit (I need to dig into this some more, and you seem to already be experienced with it) – Nic Hartley Jul 9 at 17:55
  • If you use the database in built function to encrypt the data at rest (ie on disk) you should also look to encrypt the data in transit - ie ensure that the comms between your application and the DB is encrypted. – Qwerky Jul 9 at 20:52
3

Adding encryption on its database per-field or per-group-Of-field basis seems like the best trade off provided that an index field(s) is also created alongside with each field being encrypted.

You would solve several problems at once.

  1. Searching index would be just as fast
  2. Not all fields need to be encrypted
  3. Overhead of encryption is kept to a minimal with focus on GDPR.
  4. Different passwords for each field can be used for different end-users as privilege would allow

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.