On Sat, Nov 14, 2009 at 5:08 PM, John R Pierce <pierce@xxxxxxxxxxxx> wrote: > Naoko Reeves wrote: >> >> I have a encrypted column use encrypt function. >> >> Querying against this column is almost not acceptable – returning 12 rows >> took 25,908 ms. >> >> The query was simply Select decrypt(phn_phone_enc) FROM phn WHERE >> decrypt(phn_phone_enc,’xxx’,’xxx’) LIKE ‘123%’ >> >> So I built index like: CREATE INDEX idx_phn_phone_dec ON phn >> (decrypt(phn_phone_enc, ‘xxx’, ‘xxx’)) >> >> This returns 12 rows in 68 ms. >> >> Would this be the solution for the fast encrypted field search or does >> this raise the security issue? >> > > very ugly for all the reasons given by the others. > > if all your LIKE searches are specifically for the first 3 chars of the > phn_phone field, I dunno, but maybe you could have a seperate field (and > index) which consists of just the first three characters encrypted. > > this would, however, allow people with access to the database but without > the cipher key to group your data by this prefixm, and potentially the > knowlege of that grouping could be used as an aid in cracking the keys. I'm not good enough with encryption to know for sure, but I suspect that doing this would give a _determined_ attacker enough information to break into your data (just for starters, you reduce the pool of numbers that would have to be guessed by a few orders of magnitude, and you give away geographical location). By determined I mean someone with a complete copy of your database, time, cpu power, and a bad attitude :-). I'm not saying not to do it, but the risks should be known. If you are just looking to block casual access to the data, it's probably ok. Even a single digit would reduce the brute force search by 90% if the numbers distribute well. That said, I'm betting you can't really budget 25 seconds of cpu time for a common lookup search. So you either have to compromise security or re-think the way you are doing lookups... merlin merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
