Re: [SQL] How to index encrypted colums ?

Actually, my main advice to you is:
Please try to get a firmer grasp on the use and consequences of encryption. Currently, it seems as if you might not be
awareof the consequences of actually aiming to really encrypt data (capitalization according to RFC-principles):
 
* There is no tradeoff: You encrypt something, and then there is no way to speed up searching for actual values by
indexinganymore. Any attempt to do so is flawed security-wise by principle.
 
* You MUST NOT store encrypted data unencrypted anywhere else! Especially not in indexes.
* You MUST NOT store the private key in the same database (maybe in a storage area in an encrypted partition, but that
raisesthe question on how the partition is secured etc.pp. – security is about keeping it tiny and controlled, not
aboutspreading responsibility.)
 
* You SHOULD see to it that encrypted data is salted wherever possible.
This is but _very_ basic advice regarding security.
-Klaus

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Klaus Kaisersberger
Sent: Tuesday, October 17, 2017 8:33 PM
To: ROS Didier <didier.ros@edf.fr>; steve.midgley@mixrun.com
Cc: pgsql-sql-owner@postgresql.org; pgsql-sql@postgresql.org
Subject: Re: [SQL] How to index encrypted colums ?

But then again, what you asked was quite the opposite: You explicitly tried to build a *decrypted* index! Or did I just
imaginethe pgp_pub_decrypt() in your original question?
 
Huh? Confused I am.

From: mailto:pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of ROS Didier
Sent: Tuesday, October 17, 2017 5:18 PM
To: mailto:steve.midgley@mixrun.com
Cc: mailto:pgsql-sql-owner@postgresql.org; mailto:pgsql-sql@postgresql.org
Subject: Re: [SQL] How to index encrypted colums ?

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
encrypteddata 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.
 

>>
I suppose you could partially mitigate this by putting the index in a separate table space and putting that table space
ona 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
unencryptedinside Pg?
 
<<
Interesting recommendation, but it seems too complex for us.

Best Regards

Didier ROS 
DSP/CSP IT-DMA/Solutions Groupe EDF/Expertise Applicative
Expertise SGBD
32 Avenue Pablo Picasso
92000 NANTERRE 
Bureau : E2 565D (aile Nord-Est)
Tél. : 01.78.66.61.14
Tél. mobile : 06.49.51.11.88
Fax : 01.78.66.93.47
Mail : mailto:didier.ros@edf.fr
Mail du support :mailto:support-oracle-niveau3@edf.fr
Mail du support :mailto:%20support-postgres-niveau3@edf.fr
Site du support : https://sissi.edf.fr/web/expertise-sgbd/accueil 

De : mailto:steve.midgley.mixrun@gmail.com [mailto:steve.midgley.mixrun@gmail.com] 
Envoyé : mardi 17 octobre 2017 16:15
À : ROS Didier <mailto:didier.ros@edf.fr>
Cc : mailto:pgsql-sql-owner@postgresql.org; mailto:pgsql-sql@postgresql.org
Objet : Re: [SQL] How to index encrypted colums ?



On Oct 17, 2017 12:49 AM, "ROS Didier" <mailto: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
andI 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
encrypteddata 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
ona 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
unencryptedinside Pg? 
 

Steve 


Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à l'intention exclusive des destinataires
etles informations qui y figurent sont strictement confidentielles. Toute utilisation de ce Message non conforme à sa
destination,toute diffusion ou toute publication totale ou partielle, est interdite sauf autorisation expresse.
 
Si vous n'êtes pas le destinataire de ce Message, il vous est interdit de le copier, de le faire suivre, de le
divulguerou d'en utiliser tout ou partie. Si vous avez reçu ce Message par erreur, merci de le supprimer de votre
système,ainsi que toutes ses copies, et de n'en garder aucune trace sur quelque support que ce soit. Nous vous
remercionségalement d'en avertir immédiatement l'expéditeur par retour du message.
 
Il est impossible de garantir que les communications par messagerie électronique arrivent en temps utile, sont
sécuriséesou dénuées de toute erreur ou virus.
 
____________________________________________________
This message and any attachments (the 'Message') are intended solely for the addressees. The information contained in
thisMessage is confidential. Any use of information contained in this Message not in accord with its purpose, any
disseminationor disclosure, either whole or partial, is prohibited except formal approval.
 
If you are not the addressee, you may not copy, forward, disclose or use any part of it. If you have received this
messagein error, please delete it and all copies from your system and notify the sender immediately by return message.
 
E-mail communication cannot be guaranteed to be timely secure, error or virus-free.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql