Re: How to index encrypted colums ?

2017-10-17 17:17 GMT+02:00 ROS Didier <didier(dot)ros(at)edf(dot)fr>:

> Hi
>
> Here is my answers to your remarks :
>
> *>> *
>
> *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 am not sure about this. If I index the field without deciphering it and
> make the query by encrypting the search string. This permits not to expose
> the password or the encryption key in the definition of the index.
>
> To do this, I need to use the encrypt () and decrypt () raw encryption
> functions which are immutable. With identical parameters, they always
> return the same value and can be used to index an encrypted column.
>
>
>

No need to encrypt/decrypt anything in that case. Just index the already
ciphered content of the column and be done with it.

*>> *
>
> *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?*
>
> *<< *
>
> Interesting recommendation, but it seems too complex for us.
>
>
>

--
Guillaume.