Re: How to index encrypted colums ?

On Tue, Oct 17, 2017 at 9:26 AM, Klaus Kaisersberger <
point-of-entry(at)outlook(dot)com> wrote:

> What I meant was, that indexing an encrypted column with a plain,
> unencrypted index renders the whole effort of encryption void.
> Do _not_ do that! Otherwise, there would be no sense whatsoever in
> encrypting the column first.
>
> Just for completeness, though: pgp_pub_decrypt() accepts binary, which you
> can specify in one of many ways, e.g.
> * https://www.postgresql.org/docs/9.0/static/datatype-binary.html
> * dearmor(<ASCII-armored private key>)
>
> But once again: Do _not_ do this! It would completely negate your attempts
> at providing the security obviously mandated by your client!
> -Klaus
>
>
> My response was from my wrong email address so it went to Ros but not the
group. Resending to reduce confusion. In short - I think Klaus is making a
really strong, important point: indexes are not encrypted. You can't index
an encrypted column b/c the data in the encrypted column is basically
random noise, so the index can't function properly (there's no benefit to
sorting data according to random noise in an index). If you decrypt the
encrypted column and then index it, you are storing the unencrypted data in
the index in plaintext, which means your index is defeating the encryption
for an attacker.

<<my original message>>
I believe that Klaus is trying to tell you that if you create an index on
an encrypted column, you are storing the encrypted data in plaintext within
the index. An attacker can access the secure data via the index instead of
the column.

I suppose you could partially mitigate this by putting the index in a
separate table space and putting that table space on a virtual disk device
that is encrypted.

But if that meets your needs why not just put the whole table on that
encrypted table space and leave the column unencrypted inside Pg?