PostgreSQL : encryption with pgcrypto

[ROS Didier]

Hi
   Regarding the encryption of data by pgcrypto, I would like to 
know the recommendations for the management of the key.
   Is it possible to store it off the PostgreSQL server?
   Is there the equivalent of Oracle "wallet" ?

   Thanks in advance

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







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: increasing HA

[ROS Didier]

Hi
   I have made a lot of PostgreSQL High Availability tests (more 
than 20 by solution) and the two following products respond well to the need :

(1)Repmgr (2ndQuadrant)

(2)Pglookout (aiven)

About PAF, the product is hard to install and set up . It need a linux cluster 
and a system engineers team to use it.

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


De : thomas.p...@gmail.com [mailto:thomas.p...@gmail.com]
Envoyé : mardi 4 septembre 2018 16:59
À : pgsql-general@lists.postgresql.org
Objet : increasing HA

Hello,

I am looking after some advice about solution allowing to increase High 
availability?

Here is a bit of context :

I have an Master-Slave architecture
 - 1 master
 - 2 asynchronous slaves using replication slot
 - backup is made with barman using replication slot
 - Wal archiving is done towards barman server

I think 3 axes could be improved ( I am totaly novice with these):

- using of a proxy
 I found HAproxy.
 Could you advice any others solutions to explore or share your experience?
- using an automatick failover
 I found PAF
 Could you advice any others solutions to explore or share your experience?
- using a tool for fencing a failing node
Ideally, I aimagine to disable network traffic in I/O to prevent client 
connecting and exchange between backup server failling server + on postgesql 
server disable automatic restart of the service.
Could you share you experience about it?

- Maybe an other axe to explore ?

Thank you

Thomas



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.

Why the index is not used ?

[ROS Didier]

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);


(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');
pgp_sym_decrypt

-

(0 rows)



Time: 52659.571 ms (00:52.660)


? The execution time is very long and I get no result (!?)

QUERY PLAN

---

Seq Scan on cartedecredit  (cost=0.00..3646.00 rows=1 width=32) (actual 
time=61219.989..61219.989 rows=0 loops=1)

   Filter: (cc = pgp_sym_encrypt('test value 32'::text, 'motdepasse'::text))

   Rows Removed by Filter: 10

Planning time: 0.157 ms

Execution time: 61220.035 ms

(5 rows)



? My index is not used.

QUESTIONS :
-  why I get no result ?

-why the index is not used?

Thanks in advance

Best Regards
Didier



[cid:image002.png@01D14E0E.8515EB90]


Didier ROS
Expertise SGBD
DS IT/IT DMA/Solutions Groupe EDF/Expertise Applicative - SGBD







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 Didier]

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<mailto:didier@edf.fr>
support-postgres-nive...@edf.fr<mailto:support-postgres-nive...@edf.fr>
support-oracle-nive...@edf.fr<mailto: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<http://x.id>, pgp_sym_encrypt('test value ' || x.id<http://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 q

RE: Why the index is not used ?

[ROS Didier]

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 ?

[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 ?

[ROS Didier]

Hi Vlad

Your remark is very interesting. You want to say that it's better to run SQL 
queries on unpersonalized data, and then retrieve the encrypted data for those 
records.
OK, I take this recommendation into account and I will forward it to my 
company's projects.

Nevertheless, you say that it is possible, in spite of everything, to use 
indexes on the encrypted data by using deterministic algorithms.
Can you tell me some examples of these algorithms?

Thanks in advance

Best Regards

[cid:image002.png@01D14E0E.8515EB90]


Didier ROS
Expertise SGBD



De : greatvo...@gmail.com [mailto:greatvo...@gmail.com]
Envoyé : dimanche 7 octobre 2018 21:33
À : ROS Didier 
Cc : fola...@peoplecall.com; 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 ?

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



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 Didier]

Hi Virendra 

You think that outside encryption of the database is the best solution  
 ?
   How do you manage the encryption key ?
Can you give me some examples of this kind of solution.

Best Regards
Didier ROS

-Message d'origine-
De : virendra.ku...@guycarp.com [mailto:virendra.ku...@guycarp.com] 
Envoyé : dimanche 7 octobre 2018 20:41
À : ROS Didier ; fola...@peoplecall.com
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 ?

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.



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 son

RE: Why the index is not used ?

[ROS Didier]

Hi Vlad
Sorry for this delay, but apparently the subject is of interest to many people 
in the community. I received a lot of comments and answers.
I wrote my answers in the body of your message below

Best Regards
Didier

De : greatvo...@gmail.com [mailto:greatvo...@gmail.com]
Envoyé : samedi 6 octobre 2018 18:51
À : 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 ?

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.
<<
I tested the solution of the functional index. It works very well, but the data 
is no longer encrypted. This is not the right solution
>>
(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.
>>
you're right about the missing parameter  'compress-algo=2, 
cipher-algo=aes256'. I agree with you.
(1) I have tested your proposal :
DROP TABLE cartedecredit;
CREATE TABLE cartedecredit(card_id SERIAL PRIMARY KEY, username VARCHAR(100), 
cc bytea);
INSERT INTO cartedecredit(username,cc)  SELECT 'individu ' || x.id, 
decode('test value ' || x.id,'escape') FROM generate_series(1,10) AS x(id);

è I inserted unencrypted data into the bytea column
postgres=# select * from cartedecredit limit 5 ;
card_id |  username   |  cc
-+-+--
   1 | individu 1  | \x746573742076616c75652031
   2 | individu 2  | \x746573742076616c75652032
   3 | individu 3  | \x746573742076616c75652033
   4 | individu 4  | \x746573742076616c75652034
   5 | individu 5  | \x746573742076616c75652035
CREATE INDEX idx_cartedecredit_cc02 ON cartedecredit(cc);
SELECT encode(cc,'escape') FROM cartedecredit WHERE cc=decode('test value 
32','escape');
 QUERY PLAN

Index Only Scan using idx_cartedecredit_cc02 on cartedecredit  (cost=0.42..8.44 
rows=1 width=32) (actual time=0.033..0.034 rows=1 loops=1)
   Index Cond: (cc = '\x746573742076616c7565203332'::bytea)
   Heap Fetches: 1
Planning time: 0.130 ms
Execution time: 0.059 ms
(5 rows)

è It works but the data is not encrypted. everyone can have access to the data
(2) 2nd test :
DROP TABLE cartedecredit;
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);
postgres=# select * from cartedecredit limit 5 ;
>>
card_id |  username   | 
  cc
-+-+-
---
   1 | individu 1  | 
\xc30d0409030296304d007bf50ed768d2480153cd4a4e2d240249f94b31ec168391515ea80947f97970f7a4e058bff648f752df194498dd480c3b8a5c0d2942f90c6dde21a6b9bf4e9fd7986c6f986e3783
647e7a6205b48c03
   2 | individu 2  | 
\xc30d0409030257b50bc0e6bcd8d270d248010984b60126af01ba922da27e2e78c33110f223f0210cf34da77243277305254cba374708d447fc7d653dd9e00ff9a96803a2c47ee95269534f2c24fab1c9dc
31f7909ca7adeaf0
   3 | individu 3  | 
\xc30d0409030

RE: Why the index is not used ?

[ROS Didier]

Hi Phil

Thank you for this recommendation, but I posted on this public list 
only generic examples that have nothing to do with the works done in my company.
These examples serve me only to discuss about the subject of data 
encryption and performance
My answers to your remarks :

>>
Why do you need to search by credit card number?
<<
 Again, this is just an example. I just want to find a solution to query a 
column containing encrypted data with good performance.

>>
one option is to use an encryption function that doesn't salt the data
<<
I am interested. Can you give some examples of these encryption function that 
doesn't salt the data.

Best Regards
Didier ROS
-Message d'origine-
De : phil_tnlcz_endec...@chezphil.org [mailto:phil_tnlcz_endec...@chezphil.org] 
Envoyé : dimanche 7 octobre 2018 21:17
À : ROS Didier ; pgsql-general@lists.postgresql.org
Objet : 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...







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 Didier]

Hi Vlad
   OK, I take into account your remark about the need to do 
research on encrypted data.
My answers to your remarks :
>>
you can use a deterministic algorithm for it (without salt)
<<
Can you give me on of these deterministic algorithms(without salt) ?

Best Regards

Didier
De : greatvo...@gmail.com [mailto:greatvo...@gmail.com]
Envoyé : dimanche 7 octobre 2018 21:33
À : ROS Didier 
Cc : fola...@peoplecall.com; 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 ?

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



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 Didier]

Hi Tomas

Thank you for your answer and recommendation which is very interesting. 
I'm going to study the PCI DSS document right now.
-   Here are my answer to your question :
>>
What is your threat model?
<<
we want to prevent access to sensitive data for everyone except those who have 
the encryption key.
in case of files theft, backups theft, dumps theft, we do not want anyone to 
access sensitive data.

-   I have tested the solution you proposed, it works great.

Best Regards

Didier ROS
-Message d'origine-
De : tomas.von...@2ndquadrant.com [mailto:tomas.von...@2ndquadrant.com]
Envoyé : dimanche 7 octobre 2018 22:08
À : ROS Didier ; fola...@peoplecall.com
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 ?

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




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 Didier]

Hi Paul

   Thank you very much for your feedback which is very informative.
   I understand that concerning the encryption of credit card 
numbers, it is imperative to respect the PCI DSS document. I am going to study 
it.
   However, I would like to say that I chose my example badly by 
using a table storing credit card numbers. In fact, my problem is more generic.
I want to implement a solution that encrypts “sensitive” data and can retrieve 
data with good performance (by using an index).
I find that the solution you propose is very interesting and I am going to test 
it.

Best Regards
Didier ROS

De : p...@paulmcgarry.com [mailto:p...@paulmcgarry.com]
Envoyé : lundi 8 octobre 2018 00:11
À : ROS Didier 
Cc : fola...@peoplecall.com; 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 ?

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 
mailto:didier@edf.fr>> 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> 
[mailto:fola...@peoplecall.com]
Envoyé : dimanche 7 octobre 2018 17:58
À : ROS Didier mailto:didier@edf.fr>>
Cc : pavel.steh...@gmail.com<mailto:pavel.steh...@gmail.com>; 
pgsql-...@lists.postgresql.org<mailto:pgsql-...@lists.postgresql.org>; 
pgsql-performa...@lists.postgresql.org<mailto:pgsql-performa...@lists.postgresql.org>;
 pgsql-general@lists.postgresql.org<mailto: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 
mailto:didier@edf.fr>> wrote:


-INSERT INTO cartedecredit(username,cc)  SELECT 'individu ' || 
x.id<http://x.id>, pgp_sym_encrypt('test value ' || x.id<http://x.id>, 
'motdepasse','compress-algo=2, ciphe

RE: Why the index is not used ?

[ROS Didier]

Dear Jean-Paul

Thank you very much for this link which is actually very interesting. I 
am going to study it carefully.
But my problem is more generic: 
How to set up the encryption of sensitive data and have good 
performance (using an index by example) ?. 
Apparently it is not obvious as that.

Best Regards

Didier ROS
-Message d'origine-
De : j...@argudo.org [mailto:j...@argudo.org] 
Envoyé : lundi 8 octobre 2018 10:44
À : pgsql-general@lists.postgresql.org
Objet : Re: Why the index is not used ?

Dear Didier,


Le lundi 08 octobre 2018 à 08:32 +0000, ROS Didier a écrit :
> Hi Virendra 
>   You think that outside encryption of the database is the best solution  
>  ?
>How do you manage the encryption key ?
>   Can you give me some examples of this kind of solution.
> Best Regards
> Didier ROS

If I understand your need well, you need to store credit card information into 
your database.

This is ruled by the Payment Card Industry Data Security Standard (aka PCI DSS).

I attend some years ago a real good presentation from Denish Patel, a well 
known community member.

I saw this talk in pgconf 2015 in Moscow: https://pgconf.ru/media2015c/patel.pdf

I recommend you read it, if you had not already? It shows code examples, etc.


My 2 cents...


-- 
Jean-Paul Argudo





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.

PostgreSQL 11 and security

[ROS Didier]

Hi
   I would like to know what's new in security with PostgreSQL 11

   Thanks in advance

Best Regards

Didier ROS












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: PostgreSQL 11 and security

[ROS Didier]

Hi Hilbert

Thank you for your answer. In fact, I need to update a security guide 
for PostgreSQL 11. 
And for that, I need to know what's new in the field of security for 
this version.

Best Regards
Didier ROS
-Message d'origine-
De : karsten.hilb...@gmx.net [mailto:karsten.hilb...@gmx.net] 
Envoyé : vendredi 19 octobre 2018 12:34
À : pgsql-general@lists.postgresql.org
Objet : Re: PostgreSQL 11 and security

On Fri, Oct 19, 2018 at 10:22:05AM +, ROS Didier wrote:

>I would like to know what's new in security with 
> PostgreSQL 11


https://www.postgresql.org/docs/current/static/release-11.html#id-1.11.6.5.5

sections "Permissions" and "Authentication"

And then, bugs have been fixed, the security implications of which are not 
necessarily fully known.

Other than that, your question may need to become more specific.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B




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.

migration of a logical replication configuration

[ROS Didier]

Hi
   I would like to know the impact of migrating from 10 to 11 the 
source PostgreSQL cluster of a logical replication configuration?
should we also migrate the target PostgreSQL cluster?
   Or is it possible to setup logical replication from a PostgreSQL 
11 instance to a Postgresql instance 10 ?

   Thanks in advance

Best regards
Didier ROS





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: vaccum in background

[ROS Didier]

Hi
It is possible to use background workers with the pg_background extension.

Best Regards
Didier ROS
EDF

De : sonams1...@gmail.com [mailto:sonams1...@gmail.com]
Envoyé : mercredi 28 août 2019 08:59
À : pgsql-general 
Objet :

Is there any option to run reindex or vaccum in background?
Every time the session gets logged off in between.



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.

JSONB maximal length ?

[ROS Didier]

Hi
   By inserting data in a JSONB type column I got the following error message:
>>
ERROR:  string too long to represent as jsonb string
DETAIL:  Due to an implementation restriction, jsonb strings cannot exceed 
268435455 bytes.
<<
could anyone confirm that there is a size limit for JSONB type fields ?

Thanks in advance

Best Regards
Didier ROS
EDF



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: JSONB maximal length ?

[ROS Didier]

Hi Pavel

   I don’t know what is the value 255MB ?
  all I know is that I want to insert large pdf documents. I am prevented 
because of this limit.

Best Regards
Didier ROS
EDF
De : pavel.steh...@gmail.com [mailto:pavel.steh...@gmail.com]
Envoyé : samedi 28 septembre 2019 18:26
À : ROS Didier 
Cc : pgsql-gene...@postgresql.org
Objet : Re: JSONB maximal length ?



so 28. 9. 2019 v 18:12 odesílatel ROS Didier 
mailto:didier@edf.fr>> napsal:
Hi
   By inserting data in a JSONB type column I got the following error message:
>>
ERROR:  string too long to represent as jsonb string
DETAIL:  Due to an implementation restriction, jsonb strings cannot exceed 
268435455 bytes.
<<
could anyone confirm that there is a size limit for JSONB type fields ?

you hit check

static size_t
checkStringLen(size_t len)
{
<-->if (len > JENTRY_OFFLENMASK)
<--><-->ereport(ERROR,
<--><--><--><-->(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
<--><--><--><--> errmsg("string too long to represent as jsonb string"),
<--><--><--><--> errdetail("Due to an implementation restriction, jsonb strings 
cannot exceed %d bytes.",
<--><--><--><--><--><-->   JENTRY_OFFLENMASK)));

<-->return len;
}

what is 255MB

Regards

Pavel


Thanks in advance

Best Regards
Didier ROS
EDF

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.



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.

multiple configurations with repmgr

[ROS Didier]

Hi
   We are going to use repmgr  with one node for the primary, one 
node for the standby and one node for the witness.
   It works fine with one project.

The problem is  that we want to have several other projects. Each one with its 
own primary node, its own standby node and the same witness node.
   Question : is it possible to do that ? One witness node which 
surveys several other nodes.

   Thanks in advance

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






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.

repmgr and SSH

[ROS Didier]

Hi
   I would like to setup a repmgr configuration with one primary 
node, one standby node and one witness node.
   Regarding SSH configuration, the documentation is not clear, I 
think.
Do we need to setup SSH between the three nodes or only between primary and 
standby nodes ?

   Thanks in advance

Best Regards
[cid:image002.png@01D14E0E.8515EB90]


Didier ROS
Expertise SGBD
DS IT/IT DMA/Solutions Groupe EDF/Expertise Applicative - SGBD







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.

repmgr and STONIT - SPLIT BAIN

[ROS Didier]

Hi
   We are going to implement repmgr according to the following 
configuration :

-Primary node on the site A

-Standby node on the site B

-Witness node on the same site A

Do we need to use LOCATION repmgr parameter in this case to avoid Split Brain 
and permit STONIT ?

   Thanks in advance for your advice

Best Regards
[cid:image002.png@01D14E0E.8515EB90]


Didier ROS
Expertise SGBD
DS IT/IT DMA/Solutions Groupe EDF/Expertise Applicative - SGBD







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: repmgr and SSH

[ROS Didier]

Hi Ian
If we setup SSH between all the nodes (master, standby and witness) 
there is a big security problem when repmgr monitors several configurations :
For instance this architecture :
- configuration A : primary A, standby A
- configuration B : primary B, standby B
- Configuration C : primary C, standby C
- witness : monitors the three configurations

If I can connect on primary A with the "postgres" user, I can login on 
the witness, and then I can connect to all the nodes (primary B, primary C, 
standby B, standby C).

Question : is it OK if I setup SSH between only the primary and the 
standby nodes of each configuration ?

Thanks in advance

Best Regards
Didier ROS
Expertise SGBD
DS IT/IT DMA/Solutions Groupe EDF/Expertise Applicative - SGBD

-Message d'origine-
De : ian.barw...@2ndquadrant.com [mailto:ian.barw...@2ndquadrant.com] 
Envoyé : mardi 15 janvier 2019 12:53
À : ROS Didier ; pgsql-general@lists.postgresql.org
Objet : Re: repmgr and SSH

On 1/14/19 6:29 PM, ROS Didier wrote:
> Hi
> 
> I would like to setup a repmgr configuration with one primary node, one 
> standby node and one witness node.
> 
>     Regarding SSH configuration, the documentation is not clear, 
> I think.
> 
> Do we need to setup SSH between the three nodes or only between primary and 
> standby nodes ?

Between all three, though outbound SSH connections from the witness node are 
not essential.

We'll clarify the documentation.


Regards

Ian Barwick


-- 
  Ian Barwick   http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services



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: Postgres Automated Failover

[ROS Didier]

Hi
For PostgreSQL Automatic Failover , we are using repmgr too.

Best Regards

Didier ROS
Expertise SGBD
DS IT/IT DMA/Solutions Groupe EDF/Expertise Applicative - SGBD

-Message d'origine-
De : f.pa...@portavita.eu [mailto:f.pa...@portavita.eu] 
Envoyé : jeudi 17 janvier 2019 15:29
À : pgsql-general@lists.postgresql.org
Objet : Re: Postgres Automated Failover

Hi,

In my opinion repmgr it's worth a look. 

https://repmgr.org/

regards,

fabio pardi


On 17/01/2019 14:32, AI Rumman wrote:
> Hi,
> 
> I am planning to use Postgresql with TimescaleDb extension. I have to design 
> a system similar to AWS RDS which supports automated failover, transparent 
> minor version upgrades etc.
> In early days, I worked with PgPool II to enable heartbeat between the 
> Postgres servers. Is there any new tool to achieve the same by which I can 
> design a transparent failover system for Postgres?
> 
> Also is there any multi-master Postgresql solution? Is Postgres-XC production 
> ready?
> 
> Any suggestions is appreciated. 
> 
> Thanks.




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.

repmgr and automatic failover

[ROS Didier]

Hi
It seems that after an automatic failover by repmgr, it is possible to start 
the old primary instance. ( SPLIT BRAIN Risk) .
For example:
the case when we make a reboot of the primary server, so there is an automatic 
failover made by repmgr and in this situation, we end up with 2 primary 
instances.
Is it possible to get this with repmgr ?  can anyone affirm or deny this fact?

Thanks in advance

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)







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: Table Export & Import

[ROS Didier]

Hi
One solution could be to use intel technology: FPGA : 
https://www.intel.fr/content/www/fr/fr/products/programmable.html
the principle is to add an PCI electronic card on the server with CPUs and RAM.
this greatly speeds up the loading of the data into the database.

Best Regards

[cid:image002.png@01D14E0E.8515EB90]
[Certification-DALIBO]


Didier ROS
Expertise SGBD
EDF - DTEO - DSIT - IT DMA
Département Solutions Groupe
Groupe Performance Applicative
32 avenue Pablo Picasso
92000 NANTERRE

didier@edf.fr
Tél. : +33 6 49 51 11 88
[cid:image003.png@01D4BE20.1EAF68B0][cid:image004.png@01D4BE20.1EAF68B0]



De : satcs...@gmail.com [mailto:satcs...@gmail.com]
Envoyé : lundi 1 avril 2019 08:10
À : pgsql-gene...@postgresql.org >> PG-General Mailing List 

Objet : Table Export & Import

Hi Team,

We have a requirement to copy a table from one database server to another 
database server. We are looking for a solution to achieve this with lesser 
downtime on Prod. Can you help us with this?

Table Size: 160GB
Postgresql Server Version: 9.5





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.