Thom Brown <thombrown@xxxxxxxxx> writes: > On 14 July 2010 20:23, Anthony Presley <anthony@xxxxxxxxxxxxxx> wrote: >> select >> e.id >> from >> employee e ,app_user au >> where >> au.id=user_id and >> au.corporation_id=$1 and >> e.ssn is not null and >> e.ssn!=' ' and >> e.ssn!='' and >> e.deleted='N'and >> bytea2text(DECRYPT(decode(e.ssn,'hex'), text2bytea(cast(e.id as text)), >> 'bf'))=$2 >> >> How would I create an index based on the results of the decrypt and >> bytea2text function to improve this select statement? > Would the following work?: > CREATE INDEX idx_employee_functional ON employee > (bytea2text(DECRYPT(DECODE(ssn,'hex'), text2bytea(CAST(id AS > text)),'bf')) That would work as far as speeding up the query goes. However, as Bill Moran points out nearby, the query reveals a totally incompetent security design. There is no value to speak of in encrypting a data value and then storing the decryption key right beside it. Perhaps the excuse is to not have the SSN in cleartext on disk, nevermind whether a halfway competent attacker could get it back --- but even with that barely-useful goal, you do *not* want an index like this, because all the index entries will be cleartext SSNs. What you really need is to take two steps back and figure out why you want to encrypt this data and what threats you intend to protect against. It's probably possible to make a credibly-secure design that runs faster than this does, but there's no point at all in improving the performance of a fundamentally broken design. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
