3

We've got a web app that needs to store sensitive data entered by the user. Currently we're exploring PostgreSQL in AWS. I'm aware of pgcryto and that we can hash contents of certain columns (as not everything needs to be encrypted). However, we need to also be able to search through these columns and perform sorting. These two seem to be limitations once we encrypt the data.

What are my choices at the moment if we must also support sorting and searching? Keeping in mind that the solution must also be performant.

5
  • You mean that you have an encrypted column, then you will search on it ( + order by the result), right ?
    – Luan Huynh
    Feb 22, 2017 at 9:47
  • @LuanHuynh I should add - I'm personally not the DBA working on this but as per my understanding, yes, we will be encrypting the contents at the db-level for 3 columns out of 10, but would still need to be able to perform search / sort etc on those encrypted columns without compromising security Feb 22, 2017 at 9:50
  • APP sends /receives plain-text from DB. DB will encrypt plain-text or decrypt data by pgcrypto, right ?
    – Luan Huynh
    Feb 22, 2017 at 10:08
  • 1
    @LuanHuynh I presume, yes. Is that an industry standard thing to do? Since we're using AWS, I'm assuming we'll be protected from a man-in-middle attack of some sort. I'm currently trying to understand and look for a solution, I'm not so much after pgcrypto if it can't do what we want. Feb 22, 2017 at 10:10
  • See similar question in stackoverflow.com. What's the best way to store and yet still index encrypted customer data?
    – JohnMudd
    May 8, 2019 at 21:10

1 Answer 1

2

You don't want to encrypt specific columns, what you want to do is encrypt the filesystem that the database is written to, and any backups of your database that you make. AWS offers facilities for doing both of those ( RDS Encrypted resources and S3 server-side encryption ) or you can do it yourself by using LVM volumes that are mounted using ecryptfs.

You fundamentally cannot search or sort an encrypted column without decrypting the contents. If your encryption is effective then any sorting or indexing process will see it as gibberish and if it's not effective then why are you bothering with it?

The other consideration you should be taking into account is the threat model and risk models associated with your data. If you are dealing with certain classes of data you will need to manage your exposure by following mandated security protocols. HIPAA for medical information, PCI DSS for payment information. Detailed guides for implementing those protocols are available, and if you are covered by one of them; follow it.

Encryption and data protection is not an area in which you should be creative; it's hard to get right and will bite you hard if you get it wrong.

5
  • 1
    But if the filesystem is encrypted, employees logged into the mounted filesystem can still see and read the contents, no? What I'm trying to do is protect user data even from the DBAs working on the system. Is that going too far? My content is just normal notes entered by users but I treat them as private and secure and something no one should have direct access to. Feb 22, 2017 at 23:18
  • If it is supposed to be secure; why would you allow people to log in to the host the database server is running on? Also, column level encryption will not help you solve that problem since the application needs to have the key to access that column at some point, and a malicious employee with access will be able to use the application; especially if they have legitimate reasons to access that data at times.
    – Larry
    Feb 23, 2017 at 21:45
  • 1
    It wouldn't typically be people logging in, but legitimate members of the staff working on the database / application. Yes the key would of course need to be sent over to the database in case pgcryto is used (normally the key would be set as an environment variable and not physically saved anywhere so it's inaccessible to hackers). The rogue employee case could be a worry anywhere, but that isn't my concern. My aim is to provide users with the peace of mind that their data is as secure as it can be. Feb 23, 2017 at 21:49
  • Also, RDS sadly is a slightly more expensive option for the time being. What I guess I need to hear is whether there are any immediate drawbacks with pgcrypto? We have successfully been able to encrypt and decrypt the information (using a key saved as an environment variable) and have been able to perform sorting / search by running queries such as: SELECT decode(md5('...'), 'hex') as unencrypted, ... WHERE unencrypted LIKE '%searching%'; Feb 23, 2017 at 21:52
  • Each block you put in front of the attacker helps. If by encrypting the column, I can prevent some script kiddie from seeing sensitive data if he breaches me, then I'd take that. Now I can think about dealing with a superuser attack. Mind you, that's the most difficult to deal with! So guess what, moot point! Jun 16, 2018 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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