Hi,
We have a situation where HIPAA data that needs to be encrypted.
Since we have lots of users, and a number of users who access the data
of different people, we cannot simply encrypt the disk and call it
good - it's not fine-grained enough.
So far, we've been encrypting each row, and that actually works out
fairly well, but now that we need to do searching and sorting, things
have naturally become a bit more difficult...
I've been testing a few different solutions with our data, which
shouldn't exceed 10,000 rows or thereabouts, in terms of what needs to
be encrypted/decrypted/searched and sorted.
I prototyped something like this:
http://www.doc.ic.ac.uk/teaching/distinguished-projects/2009/w.harrower.pdf
in Ruby (we're using Rails), and the performance is pretty good (well,
insertion is pretty slow, but that's ok for us), and also allows us to
search for substrings.
However, that did nothing about sorting. So the next idea was to do
something like this: save a list of names (the data we're storing that
must be encrypted) and database row id's as a Ruby list, and encrypt
that encrypt(marshal([list ... of ... names])). The advantage over
having to decrypt single rows is that it seems to be a lot faster to
decrypt one big chunk of data rather than lots of little things.
Searching through a list of N thousand names is actually fairly quick
in Ruby, as is sorting. So... this would probably work, but it's
pretty gross as a solution in that we're going to have to manually
keep a lot of data synced, and it feels awfully strange to be doing
everything in the application.
However, I can't think of a way to create an "index" like that in
Postgres, either. Am I overlooking something? The trick, I think, is
to keep the encrypt/decrypt operations to a minimum even if that
requires encrypting/decrypting a lot of data at once. Perhaps
something like decrypting to a temporary table, running the queries I
need, and then dumping and encrypting the table back to its binary
field?
Thoughts?
Thank you,
--
David N. Welton
http://www.dedasys.com/