pg_controldata: could not read file "/opt/postgres/9.2/data//global/pg_control": Success
*Hi All,* *archive_mode *is turned *on *unfortunately in my Postgres 9.2 database. Due to that disk space is full 100%. We are facing below problem when we try to start the database. *PANIC: could not read from control file:Success* Please help me how to handle this situation. *Log files contians the infomration as below:-* 2018-10-08 05:27:44.262 UTC,,,27688,,5bbaead0.6c28,1,,2018-10-08 05:27:44 UTC,,0,LOG,0,"database system was interrupted while in recovery at 2018-10-03 15:01:03 UTC",,"This probably means that some data is corrupted and you will have to use the last backup for recovery.",,,"" 2018-10-08 05:27:44.262 UTC,,,27688,,5bbaead0.6c28,2,,2018-10-08 05:27:44 UTC,,0,LOG,0,"database system was not properly shut down; automatic recovery in progress","" 2018-10-08 05:27:44.265 UTC,,,27688,,5bbaead0.6c28,3,,2018-10-08 05:27:44 UTC,,0,LOG,0,"redo starts at 93/775816B0","" 2018-10-08 05:27:44.514 UTC,,,27688,,5bbaead0.6c28,4,,2018-10-08 05:27:44 UTC,,0,FATAL,53100,"could not extend file ""base/77017/160045"": wrote only 4096 of 8192 bytes at block 278",,"Check free disk space.",,,"xlog redo insert(init): rel 1663/77017/160045; tid 278/1""" 2018-10-08 05:27:44.517 UTC,,,27686,,5bbaead0.6c26,1,,2018-10-08 05:27:44 UTC,,0,LOG,0,"startup process (PID 27688) exited with exit code 1","" 2018-10-08 05:27:44.517 UTC,,,27686,,5bbaead0.6c26,2,,2018-10-08 05:27:44 UTC,,0,LOG,0,"aborting startup due to startup process failure","" -- Regards, Raghavendra Rao J S V Mobile- 8861161425
Re: Why the index is not used ?
Hi Didier,
I’m sorry to tell you that you are probably doing something (ie
handling/storing credit cards) which would mean you have to comply with PCI DSS
requirements.
As such you should probably have a QSA (auditor) who you can run any proposed
solution by (so you know they will be comfortable with it when they do their
audit).
I think your current solution would be frowned upon because:
- cards are effectively stored in plaintext in the index.
- your encryption/decryption is being done in database, rather than by
something with that as its sole role.
People have already mentioned the former so I won’t go into it further
But for the second part if someone can do a
>> Select pgp_sym_decrypt(cc)
then you are one sql injection away from having your card data stolen. You do
have encryption, but in practice everything is available unencrypted so in
practice the encryption is more of a tick in a box than an actual defence
against bad things happening. In a properly segmented system even your DBA
should not be able to access decrypted card data.
You probably should look into doing something like:
- store the first 6 and last 4 digits of the card unencrypted.
- store the remaining card digits encrypted
- have the encryption/decryption done by a seperate service called by your
application code outside the db.
You haven’t gone into what your requirements re search are (or I missed them)
but while the above won’t give you a fast exact cc lookup in practice being
able to search using the first 6 and last 4 can get you a small enough subset
than can then be filtered after decrypting the middle.
We are straying a little off PostgreSQL topic here but if you and/or your
management aren’t already looking at PCI DSS compliance I’d strongly recommend
you do so. It can seem like a pain but it is much better to take that pain up
front rather than having to reengineer everything later. There are important
security aspects it helps make sure you cover but maybe some business aspects
(ie possible partners who won’t be able to deal with you without your
compliance sign off documentation).
The alternative, if storing cc data isn’t a core requirement, is to not store
the credit card data at all. That is generally the best solution if it meets
your needs, ie if you just want to accept payments then use a third party who
is PCI compliant to handle the cc part.
I hope that helps a little.
Paul
Sent from my iPhone
> On 8 Oct 2018, at 05:32, 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) !
>
>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
Re: Why the index is not used ?
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)) % 1;
$$ 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
Re: Why the index is not used ?
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
RE: Why the index is not used ?
Hello Didier, Your email is didier@edf.fr. Are you working at Electricite de France, and storing actual customers' credit card details? How many millions of them? Note that this mailing list is public; people looking for targets with poor security from which they can harvest credit card numbers might be reading it. And after you are hacked and all your customers' credit card details are made public, someone will find this thread. 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.. You should probably employ someone who knows what they are doing. Sorry for being so direct, but really... storing large quantities of credit card details is the text book example of something that has to be done correctly. if anyone has any proposals to put this in place, I'm interested. Why do you need to search by credit card number? If you really really need to do that, then one option is to use an encryption function that doesn't salt the data. Or you could store part of the number (last 4 digits?), or an unsalted hash of the number, unencrypted and indexed, and then you need only to sequentially decrypt (using the salted encryption) e.g. 1/1 of the card numbers. But there are complex security issues and tradeoffs involved here. You probably need to comply with regulations (e.g. "PCI standards") which will specify what is allowed and what isn't. And if you didn't already know that, you shouldn't be doing this. Good luck, I suppose. Phil. P.S. It seems that you were asking about this a year ago, and got the same answers...
Re: Why the index is not used ?
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 ?
You can consider outside DB encryption which is less of worry for performance
and data at rest will be encrypted.
Regards,
Virendra
-Original Message-
From: ROS Didier [mailto:didier@edf.fr]
Sent: Sunday, October 07, 2018 2:33 PM
To: fola...@peoplecall.com
Cc: pavel.steh...@gmail.com; pgsql-...@lists.postgresql.org;
pgsql-performa...@lists.postgresql.org; pgsql-general@lists.postgresql.org
Subject: RE: Why the index is not used ?
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.
This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.
If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.
RE: Why the index is not used ?
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 ?
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.
RE: Why the index is not used ?
Hi Paul
Thanks for the explanation. I think you are right.
I understand why the WHERE clause “cc=pgp_sym_encrypt('test
value 32', 'motdepasse');” does not bring anything back.
Best Regards
Didier ROS
De : p...@paulmcgarry.com [mailto:p...@paulmcgarry.com]
Envoyé : dimanche 7 octobre 2018 04:21
À : ROS Didier
Cc : pgsql-...@lists.postgresql.org; pgsql-performa...@lists.postgresql.org;
pgsql-general@lists.postgresql.org
Objet : Re: Why the index is not used ?
I haven’t looked up what pgp_sym_encrypt() does but assuming it does encryption
the way you should be for credit card data then it will be using a random salt
and the same input value won’t encrypt to the same output value so
WHERE cc=pgp_sym_encrypt('test value 32', 'motdepasse');
wouldn’t work because the value generated by the function when you are
searching on isn’t the same value as when you stored it.
Paul
On 6 Oct 2018, at 19:57, ROS Didier
mailto:didier@edf.fr>> wrote:
WHERE cc=pgp_sym_encrypt('test value 32', 'motdepasse');
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 ?
Hi Pavel
Thanks you for your answer. here is a procedure that works :
-CREATE TABLE cartedecredit(card_id SERIAL PRIMARY KEY, username
VARCHAR(100), cc bytea);
-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'));
-SELECT pgp_sym_decrypt(cc, 'motdepasse') FROM cartedecredit WHERE
pgp_sym_decrypt(cc, 'motdepasse','compress-algo=2, cipher-algo=aes256')='test
value 32';
pgp_sym_decrypt
-
test value 32
(1 row)
Time: 2.237 ms
- explain analyze SELECT pgp_sym_decrypt(cc, 'motdepasse') FROM
cartedecredit WHERE pgp_sym_decrypt(cc, 'motdepasse','compress-algo=2,
cipher-algo=aes256')='test value 32';
QUERY PLAN
---
Index Scan using idx_cartedecredit_cc02 on cartedecredit (cost=0.42..8.44
rows=1 width=32) (actual time=1.545..1.546 rows=1 loops=1)
Index Cond: (pgp_sym_decrypt(cc, 'motdepasse'::text, 'compress-algo=2,
cipher-algo=aes256'::text) = 'test value 32'::text)
Planning time: 0.330 ms
Execution time: 1.580 ms
(4 rows)
OK that works great.
Thank you for the recommendation
Best Regards
[cid:image002.png@01D14E0E.8515EB90]
Didier ROS
Expertise SGBD
DS IT/IT DMA/Solutions Groupe EDF/Expertise Applicative - SGBD
Nanterre Picasso - E2 565D (aile nord-est)
32 Avenue Pablo Picasso
92000 Nanterre
didier@edf.fr
support-postgres-nive...@edf.fr
support-oracle-nive...@edf.fr
Tél. : 01 78 66 61 14
Tél. mobile : 06 49 51 11 88
Lync : ros.did...@edf.fr
De : pavel.steh...@gmail.com [mailto:pavel.steh...@gmail.com]
Envoyé : samedi 6 octobre 2018 12:14
À : ROS Didier
Cc : pgsql-...@lists.postgresql.org; pgsql-performa...@lists.postgresql.org;
pgsql-general@lists.postgresql.org
Objet : Re: Why the index is not used ?
so 6. 10. 2018 v 11:57 odesílatel ROS Didier
mailto:didier@edf.fr>> napsal:
Hi
I would like to submit the following problem to the PostgreSQL community. In my
company, we have data encryption needs.
So I decided to use the following procedure :
(1)Creating a table with a bytea type column to store the encrypted data
CREATE TABLE cartedecredit(card_id SERIAL PRIMARY KEY, username VARCHAR(100),
cc bytea);
(2)inserting encrypted data
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);
(3)Querying the table
SELECT pgp_sym_decrypt(cc, 'motdepasse') FROM cartedecredit WHERE
pgp_sym_decrypt(cc, 'motdepasse')='test value 32';
pgp_sym_decrypt
-
test value 32
(1 row)
Time: 115735.035 ms (01:55.735)
-> the execution time is very long. So, I decide to create an index
(4)Creating an index on encrypted data
CREATE INDEX idx_cartedecredit_cc02 ON cartedecredit(cc);
this index cannot to help.
but functional index can cartedecredit(pgp_sym_decrypt(cc, 'motdepasse').
Unfortunately index file will be decrypted in this case.
CREATE INDEX ON
(5)Querying the table again
SELECT pgp_sym_decrypt(cc, 'motdepasse') FROM cartedecredit WHERE
pgp_sym_decrypt(cc, 'motdepasse')='test value 32';
pgp_sym_decrypt
-
test value 32
(1 row)
Time: 118558.485 ms (01:58.558) -> almost 2 minutes !!
postgres=# explain analyze SELECT pgp_sym_decrypt(cc, 'motdepasse') FROM
cartedecredit WHERE pgp_sym_decrypt(cc, 'motdepasse')='test value 32';
QUERY PLAN
--
Seq Scan on cartedecredit (cost=0.00..3647.25 rows=500 width=32) (actual
time=60711.787..102920.509 rows=1 loops=1)
Filter: (pgp_sym_decrypt(cc, 'motdepasse'::text) = 'test value 32'::text)
Rows Removed by Filter: 9
Planning time: 0.112 ms
Execution time: 102920.585 ms
(5 rows)
==> the index is not used in the execution plan. maybe because of the use of a
function in the WHERE clause. I decide to modify the SQL query
(6)Querying the table
SELECT pgp_sym_decrypt(cc, 'motdepasse') FROM cartedecredit WHERE
cc=pgp_sym_encrypt('test value 32', 'motdepasse');
it is strange - this should to use index, when there is usual index over cc
column.
What is result of explain analyze when you penalize seq scan by
set enable_seqscan to off
pgp_sym_decrypt
-
(0 rows)
Re: Text-indexing UTF-8 bytea, convert_from() immutability, null bytes...
Hi Andrew, Thanks for your great reply. Andrew Gierth wrote: "Phil" == Phil Endecott writes: Phil> As a hack I tried ALTER FUNCTION to make it immutable, A better approach is to wrap it in a function of your own which is declared immutable, rather than hacking the catalogs: create function from_utf8(bytea) returns text language plpgsql immutable as $$ begin return convert_from($1, 'UTF8'); end; $$; Thanks. I'm a bit surprised that it will allow me to declare a function immutable if it calls functions that aren't themselves immutable, but if it works... great. Phil> Anyway: given the problem of creating a text search index over Phil> bytea data that contains UTF-8 text, which may include oddities Phil> like null bytes, what would you do? You can search for 0x00 in a bytea using position() or LIKE. What do you want to do with values that contain null bytes? or values which you think are supposed to be valid utf8 text but are not? As long as it doesn't crash I don't really care; it would be better if the text search worked for the valid parts of the text but even that isn't essential. I think I will probably need to do some more preprocessing on the data when I load it, at least to remove the null bytes. Regards, Phil.
