Re: Why the index is not used ?

[Vladimir Ryabtsev]

Hello Didier,

(3), (5) to find the match, you decrypt the whole table, apparently this
take quite a long time.
Index cannot help here because indexes work on exact match of type and
value, but you compare mapped value, not indexed. Functional index should
help, but like it was said, it against the idea of encrypted storage.

(6) I never used pgp_sym_encrypt() but I see that in INSERT INTO you
supplied additional parameter 'compress-algo=2, cipher-algo=aes256' while
in (6) you did not. Probably this is the reason.

In general matching indexed bytea column should use index, you can ensure
in this populating the column unencrypted and using 'test value 32'::bytea
for match.
In you case I believe pgp_sym_encrypt() is not marked as STABLE or
IMMUTABLE that's why it will be evaluated for each row (very inefficient)
and cannot use index. From documentation:

"Since an index scan will evaluate the comparison value only once, not once
at each row, it is not valid to use a VOLATILE function in an index scan
condition."
https://www.postgresql.org/docs/10/static/xfunc-volatility.html

If you cannot add STABLE/IMMUTABLE to pgp_sym_encrypt() (which apparently
should be there), you can encrypt searched value as a separate operation
and then search in the table using basic value match.

Vlad

Re: Why the index is not used ?

[Vladimir Ryabtsev]

Didier,

you was given a few things to check in another my message on the same day.
You have not provided any feedback.
It is up to you how to implement your system, but you can with no doubt
consider your database as not encrypted with your approach. You (or
probably your management) have no understanding from which risks you
protect your data.

Regards,
Vlad


вс, 7 окт. 2018 г. в 11:33, ROS Didier :

> 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) !
>
> Regarding access to the file system, our servers are in protected
> network areas. few people can connect to it.
>
> 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.
>
> Thanks in advance
>
> Best Regards
> Didier ROS
>
> -Message d'origine-
> De : fola...@peoplecall.com [mailto:fola...@peoplecall.com]
> Envoyé : dimanche 7 octobre 2018 17:58
> À : ROS Didier 
> Cc : pavel.steh...@gmail.com; pgsql-...@lists.postgresql.org;
> pgsql-performa...@lists.postgresql.org; pgsql-general@lists.postgresql.org
> Objet : Re: Why the index is not used ?
>
> ROS:
>
> On Sun, Oct 7, 2018 at 3:13 PM, ROS Didier  wrote:
> 
> > -INSERT INTO cartedecredit(username,cc)  SELECT 'individu ' ||
> x.id, pgp_sym_encrypt('test value ' || x.id,
> 'motdepasse','compress-algo=2, cipher-algo=aes256') FROM
> generate_series(1,10) AS x(id);
> > -CREATE INDEX idx_cartedecredit_cc02 ON
> cartedecredit(pgp_sym_decrypt(cc, 'motdepasse','compress-algo=2,
> cipher-algo=aes256'));
>
> If my french is not too rusty you are encrypting a credit-card, and then
> storing an UNENCRYPTED copy in the index. So, getting it from the server is
> trivial for anyone with filesystem access.
>
> Francisco Olarte.
>
>
>
> 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.
>

Re: Why the index is not used ?

[Vladimir Ryabtsev]

Additionally it is not clear why you want to search in table on encrypted
data. Usually you match user with it's unpersonalized data (such as login,
user ID) and then decrypt personalized data. If you need to store user
identifying data encrypted as well (e.g. bank account number) you can use a
deterministic algorithm for it (without salt) because it is guaranteed to
be unique and you don't need to have different encrypted data for two same
input strings.

Vlad