Thom Brown <thombrown@gmail.com> writes:
> On 14 July 2010 20:23, Anthony Presley <anthony@resolution.com> 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