Thread: [SQL] How to index encrypted colums ?
Hi
I would to create an index on an encrypted column :
CREATE INDEX cartedecredit_cc_idx ON cartedecredit(pgp_pub_decrypt(cc, '--------'));
How can I do to indicate the private key '--------' in the sql query ?
Thanks in advance
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
Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à l'intention exclusive des destinataires et les 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 divulguer ou 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ées ou 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 this Message is confidential. Any use of information contained in this Message not in accord with its purpose, any dissemination or 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 message in 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.
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.
>>
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.
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 : didier.ros@edf.fr
Mail du support : support-oracle-niveau3@edf.fr
Mail du support : support-postgres-niveau3@edf.fr
Site du support : https://sissi.edf.fr/web/expertise-sgbd/accueil
De : steve.midgley.mixrun@gmail.com [mailto:steve.midgley.mixrun@gmail.com]
Envoyé : mardi 17 octobre 2017 16:15
À : ROS Didier <didier.ros@edf.fr>
Cc : pgsql-sql-owner@postgresql.org; pgsql-sql@postgresql.org
Objet : Re: [SQL] How to index encrypted colums ?
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
Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à l'intention exclusive des destinataires et les 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 divulguer ou 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ées ou 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 this Message is confidential. Any use of information contained in this Message not in accord with its purpose, any dissemination or 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 message in 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.
But then again, what you asked was quite the opposite: You explicitly tried to build a *decrypted* index! Or did I just imagine the pgp_pub_decrypt() in your original question?
Huh? Confused I am.
From: 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: steve.midgley@mixrun.com
Cc: pgsql-sql-owner@postgresql.org; 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 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.
>>
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.
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 : didier.ros@edf.fr
Mail du support : support-oracle-niveau3@edf.fr
Mail du support : support-postgres-niveau3@edf.fr
Site du support : https://sissi.edf.fr/web/expertise-sgbd/accueil
De : steve.midgley.mixrun@gmail.com [mailto:steve.midgley.mixrun@gmail.com]
Envoyé : mardi 17 octobre 2017 16:15
À : ROS Didier <didier.ros@edf.fr>
Cc : pgsql-sql-owner@postgresql.org; pgsql-sql@postgresql.org
Objet : Re: [SQL] How to index encrypted colums ?
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
Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à l'intention exclusive des destinataires et les 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 divulguer ou 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ées ou 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 this Message is confidential. Any use of information contained in this Message not in accord with its purpose, any dissemination or 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 message in 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.