Re: Index on a Decrypt / Bytea2Text Function

In response to Anthony Presley <anthony@resolution.com>:

> 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...

You need to do more research on this.  I understand that bosses are
arbitrarily requiring "encrypt the SSN" without understanding what they're
asking for, but doing it half-assed like this is irresponsible to the
point of being criminal.

As Tom says, first identify what attack vectors you're protecting from.

If you just want to protect the data if the server if physically stolen,
disk encryption of the partition where PG has it's data files is
probably your best bet, and on most OSen is pretty easy to set up.  The
only headache there is someone has to manually enter the disk passphrase
any time the system is rebooted.

If you want to protect from other attack vectors, such as SQL injections,
it's a little trickier, but still doable.  The complexity depends on
the rules of your access model.

In the simplest case, you generate a shared secret that every SSN is
encrypted with.  You don't keep the shared secret anywhere in the DB.
But when you create users that need access to the SSNs, you create a
copy of the shared secret that's encrypted with their password.  Now,
when they log in, they can decrypt the shared secret and get access to
the SSNs.

It even makes your application look better, because when they go to access
the SSNs, a screen pops up saying "please enter your password again to
verify that your session hasn't been hijacked" ... which you can tout
as a benefit to the users.

If your access rules are more complex (which it doesn't seem that they
are, but I only know so much) the implementation gets more complex, with
hierarchies of shared secrets and interesting grant/revoke routines --
maybe even PKI -- but at it's core, it's the same setup as what I just
described with additional layers.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/