Re: Index on a Decrypt / Bytea2Text Function

On Wed, 2010-07-14 at 15:56 -0400, Tom Lane wrote:
> 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
>

Yes, you are right ... the security here serves no purpose other than to
not have SSN's stored on disk in an un-encrypted way.  Unfortunately, we
need to be able to easily, and quickly, reverse the security, so that we
can get access to unencrypted data ... because our application does
export to payroll providers, and many of them still use SSN's as keys.
IE, storing the SSN in an encrypted manner (or using a one-way salt),
won't work.

IE, the reality is that our application has to be able to show / hide
the SSN, so someone breaking into the application (it is likely easier
to steal your manager's password than it would be to hack into the
server), would be able to access the data.

Even if we wanted to tackle *real* security here, I'm not sure how we'd
go about it.  Encrypting any data on a web app would require that the
encryption key and/or salt be stored in some combination of the
database, or app code, which is all vulnerable if someone breaks into
and/or steals the server.  There isn't a "client" piece, like you'd have
with Carbonite, etc...


--
Anthony