Subject: Re: Why the index is not used ?
To: ROS Didier <didier.ros@edf.fr>,
 "folarte@peoplecall.com" <folarte@peoplecall.com>
Cc: "pavel.stehule@gmail.com" <pavel.stehule@gmail.com>,
 "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>,
 "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <dd1702f0a1b74ce79ca1e380ea4e63ee@PCYINTPEXMU001.NEOPROD.EDF.FR>
 <CAFj8pRAQ1Ctv5dkO1UuehXfws83czzZXpyv=rkmb_86agPt-PQ@mail.gmail.com>
 <74090066a33740ea8fc442cc70f8afa2@PCYINTPEXMU001.NEOPROD.EDF.FR>
 <CA+bJJbxJuM4RQ=qMwQOF4Gep+y4w6a0Ym_ta7e_NBZH0p1sC2w@mail.gmail.com>
 <3a14f7f8fbda4e868b663b31e8c5fa16@PCYINTPEXMU001.NEOPROD.EDF.FR>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <88005798-8b90-3535-cc1c-a915a9e12d17@2ndquadrant.com>
Date: Sun, 7 Oct 2018 22:07:44 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.5.2
MIME-Version: 1.0
In-Reply-To: <3a14f7f8fbda4e868b663b31e8c5fa16@PCYINTPEXMU001.NEOPROD.EDF.FR>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi,

On 10/07/2018 08:32 PM, ROS Didier wrote:
> Hi Francisco
> 
> 	Thank you for your remark. 
> You're right, but it's the only procedure I found to make search on
> encrypted fields with good response times (using index) !
> 

Unfortunately, that kinda invalidates the whole purpose of in-database
encryption - you'll have encrypted on-disk data in one place, and then
plaintext right next to it. If you're dealing with credit card numbers,
then you presumably care about PCI DSS, and this is likely a direct
violation of that.

> Regarding access to the file system, our servers are in protected
network areas. few people can connect to it.
> 

Then why do you need encryption at all? If you assume access to the
filesystem / storage is protected, why do you bother with encryption?
What is your threat model?

> it's not the best solution, but we have data encryption needs and
> good performance needs too. I do not know how to do it except the
> specified procedure..
>
> if anyone has any proposals to put this in place, I'm interested.
> 

One thing you could do is hashing the value and then searching by the
hash. So aside from having the encrypted column you'll also have a short
hash, and you may use it in the query *together* with the original
condition. It does not need to be unique (in fact it should not be to
make it impossible to reverse the hash), but it needs to have enough
distinct values to make the index efficient. Say, 10k values should be
enough, because that means 0.01% selectivity.

So the function might look like this, for example:

  CREATE FUNCTION cchash(text) RETURNS int AS $$
    SELECT abs(hashtext($1)) % 10000;
  $$ LANGUAGE sql;

and then be used like this:

  CREATE INDEX idx_cartedecredit_cc02 ON cartedecredit(cchash(cc));

and in the query

  SELECT pgp_sym_decrypt(cc, 'motdepasse') FROM cartedecredit
   WHERE pgp_sym_decrypt(cc, 'motdepasse')='test value 32'
     AND cchash(cc) = cchash('test value 32');

Obviously, this does not really solve the issues with having to pass the
password to the query, making it visible in pg_stat_activity, various
logs etc.

Which is why people generally use FDE for the whole disk, which is
transparent and provides the same level of protection.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services