Index on a Decrypt / Bytea2Text Function

On 14 July 2010 20:23, Anthony Presley <anthony@resolution.com> wrote:

Hi all,

We tend to do a lot of lookups on our database that look something like:

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

The analyze here looks like:

explain analyze select e.id from employee e ,app_user au where

au.id=user_id and au.corporation_id=41197 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'))='188622250';

QUERY
PLAN
--------------------------------------------------------------------------
 Nested Loop  (cost=0.00..19282.05 rows=122 width=8) (actual
time=24.591..192.435 rows=1 loops=1)
  ->  Index Scan using emp_del on employee e  (cost=0.00..18625.99
rows=122 width=16) (actual time=24.556..192.398 rows=1 loops=1)
        Index Cond: (deleted = 'N'::bpchar)
        Filter: ((ssn IS NOT NULL) AND (ssn <> ' '::text) AND (ssn <>
''::text) AND (bytea2text(decrypt(decode(ssn, 'hex'::text),
text2bytea((id)::text), 'bf'::text)) = '188622250'::text))
  ->  Index Scan using app_user_pkey on app_user au  (cost=0.00..5.36
rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=1)
        Index Cond: (au.id = e.user_id)
        Filter: (au.corporation_id = 41197)
 Total runtime: 192.565 ms
(8 rows)

It would appear that almost 100% of this time is taken up by doing the
bytea2text and decrypt() functions.

How would I create an index based on the results of the decrypt and
bytea2text function to improve this select statement?

Thanks!

--
Anthony

Would the following work?:

CREATE INDEX idx_employee_functional ON employee
(bytea2text(DECRYPT(DECODE(ssn,'hex'), text2bytea(CAST(id AS
text)),'bf'))

Thom

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

Hi all,

We tend to do a lot of lookups on our database that look something like:

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

The analyze here looks like:

explain analyze select e.id from employee e ,app_user au where

au.id=user_id and au.corporation_id=41197 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'))='188622250';

QUERY
PLAN
--------------------------------------------------------------------------
Nested Loop (cost=0.00..19282.05 rows=122 width=8) (actual
time=24.591..192.435 rows=1 loops=1)
-> Index Scan using emp_del on employee e (cost=0.00..18625.99
rows=122 width=16) (actual time=24.556..192.398 rows=1 loops=1)
Index Cond: (deleted = 'N'::bpchar)
Filter: ((ssn IS NOT NULL) AND (ssn <> ' '::text) AND (ssn <>
''::text) AND (bytea2text(decrypt(decode(ssn, 'hex'::text),
text2bytea((id)::text), 'bf'::text)) = '188622250'::text))
-> Index Scan using app_user_pkey on app_user au (cost=0.00..5.36
rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=1)
Index Cond: (au.id = e.user_id)
Filter: (au.corporation_id = 41197)
Total runtime: 192.565 ms
(8 rows)

It would appear that almost 100% of this time is taken up by doing the
bytea2text and decrypt() functions.

How would I create an index based on the results of the decrypt and
bytea2text function to improve this select statement?

The best way would be to unencrypt the column and use a normal index.

Since you're simply using a value in another column as the key anyway,
your design has created all the performance headaches of encryption
with no actual security.

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

On 14 July 2010 20:32, Bill Moran <wmoran@potentialtech.com> wrote:

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

Hi all,

We tend to do a lot of lookups on our database that look something like:

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

The analyze here looks like:

explain analyze select e.id from employee e ,app_user au where

au.id=user_id and au.corporation_id=41197 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'))='188622250';

QUERY
PLAN
--------------------------------------------------------------------------
 Nested Loop  (cost=0.00..19282.05 rows=122 width=8) (actual
time=24.591..192.435 rows=1 loops=1)
   ->  Index Scan using emp_del on employee e  (cost=0.00..18625.99
rows=122 width=16) (actual time=24.556..192.398 rows=1 loops=1)
         Index Cond: (deleted = 'N'::bpchar)
         Filter: ((ssn IS NOT NULL) AND (ssn <> ' '::text) AND (ssn <>
''::text) AND (bytea2text(decrypt(decode(ssn, 'hex'::text),
text2bytea((id)::text), 'bf'::text)) = '188622250'::text))
   ->  Index Scan using app_user_pkey on app_user au  (cost=0.00..5.36
rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=1)
         Index Cond: (au.id = e.user_id)
         Filter: (au.corporation_id = 41197)
 Total runtime: 192.565 ms
(8 rows)

It would appear that almost 100% of this time is taken up by doing the
bytea2text and decrypt() functions.

How would I create an index based on the results of the decrypt and
bytea2text function to improve this select statement?

The best way would be to unencrypt the column and use a normal index.

Since you're simply using a value in another column as the key anyway,
your design has created all the performance headaches of encryption
with no actual security.

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

--

Yes, I immediately thought about what's actually happening as soon as
I sent the last message. Forget the functional index.

Thom

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

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

On Wed, 2010-07-14 at 20:32 +0100, Thom Brown wrote:

On 14 July 2010 20:23, Anthony Presley <anthony@resolution.com> wrote:

Hi all,

We tend to do a lot of lookups on our database that look something like:

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

The analyze here looks like:

explain analyze select e.id from employee e ,app_user au where

au.id=user_id and au.corporation_id=41197 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'))='188622250';

QUERY
PLAN
--------------------------------------------------------------------------
Nested Loop (cost=0.00..19282.05 rows=122 width=8) (actual
time=24.591..192.435 rows=1 loops=1)
-> Index Scan using emp_del on employee e (cost=0.00..18625.99
rows=122 width=16) (actual time=24.556..192.398 rows=1 loops=1)
Index Cond: (deleted = 'N'::bpchar)
Filter: ((ssn IS NOT NULL) AND (ssn <> ' '::text) AND (ssn <>
''::text) AND (bytea2text(decrypt(decode(ssn, 'hex'::text),
text2bytea((id)::text), 'bf'::text)) = '188622250'::text))
-> Index Scan using app_user_pkey on app_user au (cost=0.00..5.36
rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=1)
Index Cond: (au.id = e.user_id)
Filter: (au.corporation_id = 41197)
Total runtime: 192.565 ms
(8 rows)

It would appear that almost 100% of this time is taken up by doing the
bytea2text and decrypt() functions.

How would I create an index based on the results of the decrypt and
bytea2text function to improve this select statement?

Thanks!

--
Anthony

Would the following work?:

CREATE INDEX idx_employee_functional ON employee
(bytea2text(DECRYPT(DECODE(ssn,'hex'), text2bytea(CAST(id AS
text)),'bf'))

Thom

Unfortunately, that doesn't work:

# CREATE INDEX idx_employee_functional ON employee
(bytea2text(DECRYPT(DECODE(ssn,'hex'), text2bytea(CAST(id AS
text)),'bf')));
ERROR: functions in index expression must be marked IMMUTABLE

Guess we'll need to come up with something else.

--
Anthony

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/