Re: [SQL] How to index encrypted colums ? - Mailing list pgsql-sql

From Steve Midgley
Subject Re: [SQL] How to index encrypted colums ?
Date
Msg-id CAJexoSLgBnxF3iX3m3EihB5LdX2uvMe8igco+r3Ws+9P+RyLJg@mail.gmail.com
Whole thread Raw
In response to Re: [SQL] How to index encrypted colums ?  (ROS Didier <didier.ros@edf.fr>)
List pgsql-sql


On Oct 17, 2017 12:49 AM, "ROS Didier" <didier.ros@edf.fr> wrote:
Hi Klaus

        We need encryption because we need to protect sensitive data. I need to create an index on encrypted columns and I don't know how to insert private key.
Example:
CREATE INDEX cartedecredit_cc_idx ON cartedecredit(pgp_pub_decrypt(cc, '--------'));

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? 

Steve 

pgsql-sql by date:

Previous
From: ROS Didier
Date:
Subject: Re: [SQL] How to get the list of tables with encrypted columns
Next
From: ROS Didier
Date:
Subject: Re: [SQL] How to index encrypted colums ?