{"id":10163,"date":"2023-08-02T11:02:56","date_gmt":"2023-08-02T10:02:56","guid":{"rendered":"https:\/\/blog.capdata.fr\/?p=10163"},"modified":"2023-09-29T15:34:15","modified_gmt":"2023-09-29T14:34:15","slug":"chiffrement-des-donnees-sous-postgresql","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/","title":{"rendered":"Chiffrement sous PostgreSQL : mais sous quelles conditions ?"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

\"\"<\/p>\n

Lorsque l’on parle s\u00e9curit\u00e9 des donn\u00e9es sur une instance PostgreSQL, nous avons le choix entre le chiffrement “at rest” avec TDE (cf cet article<\/a>), ou bien l’utilisation de l’extension, bien connue des DBA PostgreSQL, pgcrypto<\/strong>.<\/p>\n

Cette extension permet de disposer de fonctions de chiffrement via m\u00e9thode de hashage et dite de “salage” pour g\u00e9n\u00e9rer des valeurs al\u00e9atoires.<\/p>\n

Dans cet article, nous allons effectuer une \u00e9tude comparative sur les diff\u00e9rents algorithmes de chiffrement, en portant notre attention sur les temps d’ex\u00e9cution li\u00e9s au chiffrage, mais aussi les probl\u00e9matiques de stockage que cela peut engendrer.<\/p>\n

 <\/p>\n

Etat des lieux<\/h2>\n

 <\/p>\n

Afin d’effectuer les diff\u00e9rents tests, nous partons sur une petite configuration machine, \u00e0 savoir, une EC2 AWS t2.micro, avec 1 CPU et 1 Go de RAM.<\/p>\n

Cette VM h\u00e9berge une instance de bases de donn\u00e9es PostgreSQL version 13.<\/p>\n

 <\/p>\n

[postgres@~]$ cat \/proc\/cpuinfo | egrep -i 'model|Mhz|core'\r\nmodel : 79\r\nmodel name : Intel(R) Xeon(R) CPU E5-2686 v4 @ 2.30GHz\r\ncpu MHz : 2299.980\r\ncore id : 0\r\ncpu cores : 1<\/pre>\n
 <\/p>\n
[postgres@ ~]$ cat \/proc\/meminfo | grep -i Total\r\nMemTotal: 834212 kB\r\nSwapTotal: 0 kB\r\nVmallocTotal: 34359738367 kB\r\nHugePages_Total: 0<\/pre>\n
 <\/p>\n
Nous savons que ce n’est pas avec une telle configuration que nous pouvons nous attendre \u00e0 pas des performances impressionnantes, mais nous aurons au moins de quoi se faire une id\u00e9e sur les caract\u00e9ristiques du chiffrement.<\/p>\n
Pour notre jeu de donn\u00e9es, nous nous appuyons sur le site “DVD Rental” proposant une base de donn\u00e9es fictive de location de DVDs (oui oui cela doit encore exister cette cat\u00e9gorie de soci\u00e9t\u00e9 ! ) (DVD Rental sur ce site<\/a>)<\/p>\n
Les tables comportent des donn\u00e9es assez repr\u00e9sentatives de ce que l’on souhaite faire pour notre \u00e9tude.<\/p>\n
Les tables propos\u00e9es pour cette base sont les suivantes<\/p>\n
 <\/p>\n
(postgres@[local]:5433) [dvdrental]  select schemaname,tablename, pg_size_pretty(pg_relation_size(schemaname::varchar||'.'||tablename::varchar)) as Size_table, pg_size_pretty(pg_indexes_size(schemaname::varchar||'.'||tablename::varchar)) as Size_index from pg_tables where schemaname not in ('pg_catalog','information_schema') order by 2 desc;\r\n\r\n------------+---------------+------------+------------\r\npublic      | store         | 8192 bytes | 32 kB\r\npublic      | staff         | 8192 bytes | 16 kB\r\npublic      | rental        | 1200 kB    | 1120 kB\r\npublic      | payment       | 864 kB     | 920 kB\r\npublic      | language      | 8192 bytes | 16 kB\r\npublic      | inventory     | 200 kB     | 208 kB\r\npublic      | film_category | 48 kB      | 40 kB\r\npublic      | film_actor    | 240 kB     | 216 kB\r\npublic      | film          | 432 kB     | 200 kB\r\npublic      | customer      | 72 kB      | 112 kB\r\npublic      | country       | 8192 bytes | 16 kB\r\npublic      | city          | 40 kB      | 48 kB\r\npublic      | category      | 8192 bytes | 16 kB\r\npublic      | address       | 64 kB      | 64 kB\r\npublic      | actor         | 16 kB      | 32 kB<\/pre>\n
 <\/p>\n
Si l’on souhaite avoir la liste des clients et leurs informations personnelles, y compris leur adresse, nous formulons la requ\u00eate SQL suivante sur notre outil PostgreSQL client.<\/p>\n
 <\/p>\n
\n
(postgres@[local]:5433) [dvdrental]  select c.customer_id,c.first_name,c.last_name,c.email,c.create_date,a.address,a.district,a.phone from customer c inner join address a on (c.address_id=a.address_id);\r\n\r\ncustomer_id  | first_name  | last_name    | email                                    | create_date | address                                | district             | phone\r\n-------------+-------------+--------------+------------------------------------------+-------------+----------------------------------------+----------------------+--------------\r\n524          | Jared       | Ely          | jared.ely@sakilacustomer.org             | 2006-02-14  | 1003 Qinhuangdao Street                | West Java            | 35533115997\r\n1            | Mary        | Smith        | mary.smith@sakilacustomer.org            | 2006-02-14  | 1913 Hanoi Way                         | Nagasaki             | 28303384290\r\n2            | Patricia    | Johnson      | patricia.johnson@sakilacustomer.org      | 2006-02-14  | 1121 Loja Avenue                       | California           | 838635286649\r\n3            | Linda       | Williams     | linda.williams@sakilacustomer.org        | 2006-02-14  | 692 Joliet Street                      | Attika               | 448477190408\r\n4            | Barbara     | Jones        | barbara.jones@sakilacustomer.org         | 2006-02-14  | 1566 Inegl Manor                       | Mandalay             | 705814003527\r\n5            | Elizabeth   | Brown        | elizabeth.brown@sakilacustomer.org       | 2006-02-14  | 53 Idfu Parkway                        | Nantou               | 10655648674\r\n6            | Jennifer    | Davis        | jennifer.davis@sakilacustomer.org        | 2006-02-14  | 1795 Santiago de Compostela Way        | Texas                | 860452626434\r\n7            | Maria       | Miller       | maria.miller@sakilacustomer.org          | 2006-02-14  | 900 Santiago de Compostela Parkway     | Central Serbia       | 716571220373\r\n8            | Susan       | Wilson       | susan.wilson@sakilacustomer.org          | 2006-02-14  | 478 Joliet Way                         | Hamilton             | 657282285970\r\n9            | Margaret    | Moore        | margaret.moore@sakilacustomer.org        | 2006-02-14  | 613 Korolev Drive                      | Masqat               | 380657522649\r\n10           | Dorothy     | Taylor       | dorothy.taylor@sakilacustomer.org        | 2006-02-14  | 1531 Sal Drive                         | Esfahan              | 648856936185\r\n11           | Lisa        | Anderson     | lisa.anderson@sakilacustomer.org         | 2006-02-14  | 1542 Tarlac Parkway                    | Kanagawa             | 635297277345\r\n12           | Nancy       | Thomas       | nancy.thomas@sakilacustomer.org          | 2006-02-14  | 808 Bhopal Manor                       | Haryana              | 465887807014\r\n13           | Karen       | Jackson      | karen.jackson@sakilacustomer.org         | 2006-02-14  | 270 Amroha Parkway                     | Osmaniye             | 695479687538\r\n14           | Betty       | White        | betty.white@sakilacustomer.org           | 2006-02-14  | 770 Bydgoszcz Avenue                   | California           | 517338314235\r\n15           | Helen       | Harris       | helen.harris@sakilacustomer.org          | 2006-02-14  | 419 Iligan Lane                        | Madhya Pradesh       | 990911107354\r\n16           | Sandra      | Martin       | sandra.martin@sakilacustomer.org         | 2006-02-14  | 360 Toulouse Parkway                   | England              | 949312333307\r\n17           | Donna       | Thompson     | donna.thompson@sakilacustomer.org        | 2006-02-14  | 270 Toulon Boulevard                   | Kalmykia             | 407752414682\r\n .......\r\n\r\n(599 rows)\r\n\r\nTime: 5.196 ms <\/pre>\n<\/div>\n
<\/div>\n
Les donn\u00e9es nous sont renvoy\u00e9es en clair avec nom, pr\u00e9nom, adresse et t\u00e9l\u00e9phone des personnes.<\/div>\n
La question que l’on sera amen\u00e9 \u00e0 se poser est, combien cela va ma couter de “s\u00e9curiser” ces donn\u00e9es afin que ces champs ne soient pas lisibles via un simple “SELECT ” sans algorithme de chiffrage ?<\/div>\n
<\/div>\n
\n
Mise en place<\/h2>\n
 <\/p>\n
Comme \u00e9voqu\u00e9 ci-dessus, nous utiliserons l’extension “pgcrypto” pour r\u00e9aliser nos diff\u00e9rents tests de chiffrement.<\/p>\n
Nous avons la version 1.3 de pgcrypto sur une instance PostgreSQL 13<\/p>\n
 <\/p>\n
(postgres@[local]:5433) [dvdrental]  \\dx\r\nName      | Version | Schema     | Description\r\n----------+---------+------------+------------------------------\r\npgcrypto  | 1.3     | public     | cryptographic functions<\/pre>\n<\/div>\n
<\/div>\n
Les donn\u00e9es que nous nous proposons de traiter seront extraits de la requ\u00eate avec jointure interne entre la table des clients (customer) et la table des adresses clients (address).<\/p>\n
Le plan d’ex\u00e9cution de cette requ\u00eate est assez simple. 2 lectures s\u00e9quentielles sont effectu\u00e9es directement sur les tables heap customer et address dans la mesure ou aucune clause where n’est indiqu\u00e9e.<\/p>\n
(postgres@[local]:5433) [dvdrental]  explain (analyze, verbose) select c.customer_id,c.first_name,c.last_name,c.email,c.create_date,a.address,a.district,a.phone from customer c inner join address a on (c.address_id=a.address_id);\r\n\r\nQUERY PLAN\r\n---------------------------------------------------------------------------------------------------------------------------------------------------\r\nHash Join (cost=21.57..38.14 rows=599 width=94) (actual time=0.978..2.295 rows=599 loops=1)\r\nOutput: c.customer_id, c.first_name, c.last_name, c.email, c.create_date, a.address, a.district, a.phone\r\nInner Unique: true\r\nHash Cond: (c.address_id = a.address_id)\r\n- Seq Scan on public.customer c (cost=0.00..14.99 rows=599 width=55) (actual time=0.009..0.433 rows=599 loops=1)\r\nOutput: c.customer_id, c.store_id, c.first_name, c.last_name, c.email, c.address_id, c.activebool, c.create_date, c.last_update, c.active\r\n- Hash (cost=14.03..14.03 rows=603 width=45) (actual time=0.958..0.961 rows=603 loops=1)\r\nOutput: a.address, a.district, a.phone, a.address_id\r\nBuckets: 1024 Batches: 1 Memory Usage: 56kB\r\n- Seq Scan on public.address a (cost=0.00..14.03 rows=603 width=45) (actual time=0.007..0.477 rows=603 loops=1)\r\nOutput: a.address, a.district, a.phone, a.address_id\r\nPlanning Time: 0.271 ms\r\nExecution Time: 2.705 ms\r\n(13 rows)\r\n\r\nTime: 3.476 ms<\/pre>\n
Pour cette simple requ\u00eate, nous n’avons besoin que de 3 millisecondes pour trier les donn\u00e9es, avec un co\u00fbt de 38, et ramener les 599 lignes.<\/p>\n
 <\/p>\n
Chiffrement classique<\/h3>\n
 <\/p>\n
Le chiffrement classique consiste \u00e0 utiliser les fonctions simples “encrypt \/ decrypt”.
\nCes 2 fonctions utilisent une cl\u00e9 de chiffrement que l’on passe \u00e0 chacun des champs crypt\u00e9s lors des ordres INSERT et SELECT.<\/p>\n
Tout d’abord on cr\u00e9e les 2 tables vides, copies des tables “customer” et “address”<\/p>\n
 <\/p>\n
CREATE TABLE IF NOT EXISTS public.customer_encrypt\r\n(\r\ncustomer_id integer,\r\nstore_id smallint ,\r\nfirst_name bytea ,\r\nlast_name bytea ,\r\nemail bytea,\r\naddress_id smallint,\r\nactivebool boolean,\r\ncreate_date date,\r\nlast_update timestamp without time zone,\r\nactive integer,\r\nCONSTRAINT customer_pkey_crypt PRIMARY KEY (customer_id)\r\n)\r\nTABLESPACE pg_default;<\/pre>\n
 <\/p>\n
CREATE TABLE IF NOT EXISTS public.address_encrypt\r\n(\r\naddress_id integer,\r\naddress bytea,\r\naddress2 character varying(50),\r\ndistrict character varying(20),\r\ncity_id smallint,\r\npostal_code character varying(10),\r\nphone bytea,\r\nlast_update timestamp without time zone,\r\nCONSTRAINT address_key_crypt PRIMARY KEY (address_id)\r\n)\r\nTABLESPACE pg_default;<\/pre>\n
 <\/p>\n
Puis on y ins\u00e8re les donn\u00e9es \u00e0 partir des tables sources “customer” et “address”. Pour cela, nous utilisons la fonction “encrypt” avec la cl\u00e9 “capdata2023” et l’algorithme aes.<\/p>\n
 <\/p>\n
insert into public.customer_encrypt\r\nselect customer_id,\r\nstore_id,\r\nencrypt(c.first_name::bytea,'capdata2023','aes'),\r\nencrypt(c.last_name::bytea,'capdata2023','aes'),\r\nencrypt(c.email::bytea,'capdata2023','aes'),\r\naddress_id,\r\nactivebool,\r\ncreate_date,\r\nlast_update,\r\nactive\r\nfrom customer c;\r\nINSERT 0 599\r\nTime: 5.297 ms<\/pre>\n
 <\/p>\n
insert into public.address_encrypt\r\nselect address_id,\r\nencrypt(address::bytea,'capdata2023','aes'),\r\naddress2,\r\ndistrict,\r\ncity_id,\r\npostal_code,\r\nencrypt(phone::bytea,'capdata2023','aes'),\r\nlast_update\r\nfrom address;\r\nINSERT 0 603\r\nTime: 4.616 ms<\/pre>\n
 <\/p>\n
Les donn\u00e9es dans les tables sont bien chiffr\u00e9es comme le montre le simple SELECT suivant<\/p>\n
(postgres@[local]:5433) [dvdrental]  select * from customer_encrypt limit 10;\r\n\r\ncustomer_id  | store_id | first_name                         | last_name                          | email                                                                                              | address_id | activebool | create_date | last_update | active\r\n-------------+----------+------------------------------------+------------------------------------+----------------------------------------------------------------------------------------------------+------------+------------+-------------+-------------------------+--------\r\n524          | 1        | \\x18a7f8f4ba98111a01f63aef09773202 | \\xdf4bcb77c652d6291c34ec7788cbe881 | \\xa536a4f46d1bcc00f7183b46b68da712d70a0f419c1e58d778a4e9a3771828d7                                 | 530 | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1\r\n1            | 1        | \\x4f49ec91c5f38f8d3f751e42966f5695 | \\x7c78e265d6e86fc17cdc78ebf1b41dd3 | \\xac61ff9165a035a68a821c3959f5867a218fed8e350853e14d0c0a7dcf49b6d5                                 | 5   | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1\r\n2            | 1        | \\x541dec9d2a1688ff55fe0730e184548b | \\x15200a194b068c59b0431271cf6f3c12 | \\x7c9b39f5896ac24d8bedc08427adbfe1d5c22379d66dfd2881d3bdabe9b5cae60c329724424be9c6bcfe08cffc356c3c | 6   | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1\r\n3            | 1        | \\x4f1dd6b1d35f3024eb7c6cbf5c600269 | \\x47d3a708ba137f5b9a0b873df5a7e340 | \\x9297172b1a299f013db248a00d7414c7ea1378fbefc143e3f1f632c431ecf8597b5307f415eb82f672b15449c93c297e | 7   | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1\r\n4            | 2        | \\x84554c6f4c26d2f9c0b832b311263883 | \\x6eff33a47393acf9d178afa9553ffae4 | \\xe912cb073583fb399c92379d3529b18cf39754828ed28e02e31e3b8c9b523d2c02a18bfebc210bab193d8489cdd11914 | 8   | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1\r\n5            | 1        | \\xca5601a262cbc84669558ba7199f5960 | \\x99fc3b33675cb5de00c92447f6f7e8ed | \\xae7ea0ea5204c1df2e596af38b53a2da84cce415ae4ba5645df6dba7826e868a26eb59af5e79a7f8a4d55abc1382499b | 9   | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1\r\n6            | 2        | \\xb5f9f526337e730b25f4c4f7dab8eb91 | \\x52bba94e13cc36be9f1094c1bae22c47 | \\x50c4a57f06048c579897fce47603945db4d7143f6deaa5d801c447f23edde371565e24c4bc53e272e0ebf32ace80fbd5 | 10  | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1\r\n7            | 1        | \\xac6e41d9fb3a3eaefd0775e5b646a730 | \\xb8ba78ad82aff0a50717301716e59013 | \\xad3cec70e819240d7f7ddd6066364332f0c6b504d15b6627516deb13fe591bd2                                 | 11  | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1\r\n8            | 2        | \\x8aeb1a151900c8c2b3ac52c3a9c78a42 | \\xea0c5c0e6546ffc8da1ef4f04f5addea | \\x5eac14927a3c464e8cd88122d02271333e7235e8d212e1c2722a6b3804ba1672                                 | 12  | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1\r\n9            | 2        | \\x9248a51a98b10f1c0499f37604e2caa3 | \\x254b523f8e750e100218947a2f838585 | \\x706c681ae5692ba107cb51c3f54b1ef2699c1e463b68b8fbc48f8f9f15c86a45d456498740e41097f99f46c80837dcbf | 13  | t | 2006-02-14 | 2013-05-26 14:49:45.738 | 1<\/pre>\n
<\/div>\n
<\/div>\n
Pour d\u00e9chiffrer les donn\u00e9es de notre requ\u00eate SQL avec jointure entre les clients et leurs adresses, nous devons appeler la fonction “decrypt” pour chaque champ, avec la cl\u00e9 de chiffrage associ\u00e9e. Afin de lire correctement la valeur, utiliser la fonction “convert_from” pour transformer l’information de fa\u00e7on lisible.<\/p>\n
Ce qui donne la requ\u00eate suivante :<\/p>\n
 <\/p>\n
\r\n\r\n(postgres@[local]:5433) [dvdrental]  select c.customer_id,\r\nconvert_from(decrypt(c.first_name,'capdata2023','aes'),'UTF8') as first_name,\r\nconvert_from(decrypt(c.last_name,'capdata2023','aes'),'UTF8') as last_name,\r\nconvert_from(decrypt(c.email,'capdata2023','aes'),'UTF8') as email,\r\nc.create_date,\r\nconvert_from(decrypt(a.address,'capdata2023','aes'),'UTF8') as adresse,\r\na.district,\r\nconvert_from(decrypt(a.phone,'capdata2023','aes'),'UTF8') as telephone\r\nfrom customer_encrypt c\r\ninner join address_encrypt a\r\non (c.address_id=a.address_id);\r\n\r\ncustomer_id  | first_name  | last_name    | email                                    | create_date | adresse                                | district             | telephone\r\n-------------+-------------+--------------+------------------------------------------+-------------+----------------------------------------+----------------------+--------------\r\n524          | Jared       | Ely          | jared.ely@sakilacustomer.org             | 2006-02-14  | 1003 Qinhuangdao Street                | West Java            | 35533115997\r\n1            | Mary        | Smith        | mary.smith@sakilacustomer.org            | 2006-02-14  | 1913 Hanoi Way                         | Nagasaki             | 28303384290\r\n2            | Patricia    | Johnson      | patricia.johnson@sakilacustomer.org      | 2006-02-14  | 1121 Loja Avenue                       | California | 838635286649\r\n3            | Linda       | Williams     | linda.williams@sakilacustomer.org        | 2006-02-14  | 692 Joliet Street                      | Attika | 448477190408\r\n4            | Barbara     | Jones        | barbara.jones@sakilacustomer.org         | 2006-02-14  | 1566 Inegl Manor                       | Mandalay | 705814003527\r\n5            | Elizabeth   | Brown        | elizabeth.brown@sakilacustomer.org       | 2006-02-14  | 53 Idfu Parkway                        | Nantou | 10655648674\r\n6            | Jennifer    | Davis        | jennifer.davis@sakilacustomer.org        | 2006-02-14  | 1795 Santiago de Compostela Way        | Texas | 860452626434\r\n7            | Maria       | Miller       | maria.miller@sakilacustomer.org          | 2006-02-14  | 900 Santiago de Compostela Parkway     | Central Serbia | 716571220373\r\n8            | Susan       | Wilson       | susan.wilson@sakilacustomer.org          | 2006-02-14  | 478 Joliet Way                         | Hamilton | 657282285970\r\n9            | Margaret    | Moore        | margaret.moore@sakilacustomer.org        | 2006-02-14  | 613 Korolev Drive                      | Masqat | 380657522649\r\n10           | Dorothy     | Taylor       | dorothy.taylor@sakilacustomer.org        | 2006-02-14  | 1531 Sal Drive                         | Esfahan | 648856936185\r\n11           | Lisa        | Anderson     | lisa.anderson@sakilacustomer.org         | 2006-02-14  | 1542 Tarlac Parkway                    | Kanagawa | 635297277345\r\n12           | Nancy       | Thomas       | nancy.thomas@sakilacustomer.org          | 2006-02-14  | 808 Bhopal Manor                       | Haryana | 465887807014\r\n13           | Karen       | Jackson      | karen.jackson@sakilacustomer.org         | 2006-02-14  | 270 Amroha Parkway                     | Osmaniye | 695479687538\r\n14           | Betty       | White        | betty.white@sakilacustomer.org           | 2006-02-14  | 770 Bydgoszcz Avenue                   | California | 517338314235\r\n15           | Helen       | Harris       | helen.harris@sakilacustomer.org          | 2006-02-14  | 419 Iligan Lane                        | Madhya Pradesh | 990911107354\r\n16           | Sandra      | Martin       | sandra.martin@sakilacustomer.org         | 2006-02-14  | 360 Toulouse Parkway                   | England | 949312333307\r\n17           | Donna       | Thompson     | donna.thompson@sakilacustomer.org        | 2006-02-14  | 270 Toulon Boulevard                   | Kalmykia | 407752414682\r\n\r\n ...\r\n\r\n(599 rows)\r\n\r\nTime: 5.064 ms<\/pre>\n
<\/div>\n
<\/pre>\n
<\/div>\n
Le temps d’ex\u00e9cution de cette requ\u00eate est d’un peu plus de 5 millisecondes, nous sommes donc tr\u00e8s proches de ce qui a \u00e9t\u00e9 relev\u00e9 sur les tables originales.<\/div>\n
<\/div>\n
Chiffrement PGP sym\u00e9trique<\/h3>\n
<\/div>\n
\n
Le chiffrement PGP (Pretty Good Privacy) utilise des standards li\u00e9s \u00e0 OpenPGP (RFC 2440).<\/p>\n<\/div>\n
Le proc\u00e9d\u00e9 consiste \u00e0 utiliser un message chiffr\u00e9 PGP en deux parties.
\n– Un paquet est envoy\u00e9 avec la cl\u00e9 de session (cl\u00e9 sym\u00e9trique ou bien une cl\u00e9 publique)
\n– Paquet contenant les donn\u00e9es chiffr\u00e9es avec la cl\u00e9 de session.
\nAvec un chiffrement \u00e0 cl\u00e9 sym\u00e9trique, le mot de passe est envoy\u00e9 crypt\u00e9 avec l\u2019algorithme String2key (S2K).<\/div>\n
<\/div>\n
Si une cl\u00e9 de session choisie par l’utilisateur, elle sera \u00e9galement chiffr\u00e9e suivant le m\u00eame algorithme.<\/div>\n
<\/div>\n
Enfin les donn\u00e9es sont format\u00e9es avec un hachage SHA1, et sont pr\u00e9fix\u00e9es avec un bloc d\u2019octets pris au hasard.
\nPuis elles sont chiffr\u00e9es avec la cl\u00e9 de session.<\/div>\n
<\/div>\n
L’\u00e9tape de cr\u00e9ation de tables de tests et insertion des donn\u00e9es \u00e0 partir des tables sources “customer” et “address” est \u00e9galement n\u00e9cessaire pour notre exemple.<\/div>\n
<\/div>\n
\n
CREATE TABLE IF NOT EXISTS public.customer_pgp_sym\r\n(\r\ncustomer_id integer,\r\nstore_id smallint , \r\nfirst_name bytea , \r\nlast_name bytea , \r\nemail bytea, \r\naddress_id smallint, \r\nactivebool boolean, \r\ncreate_date date, \r\nlast_update timestamp without time zone, \r\nactive integer,\r\nCONSTRAINT customer_pkey_sym PRIMARY KEY (customer_id) \r\n)\r\nTABLESPACE pg_default;<\/pre>\n<\/div>\n
<\/div>\n
<\/div>\n
\n
CREATE TABLE IF NOT EXISTS public.address_pgp_sym\r\n(\r\naddress_id integer, \r\naddress bytea,\r\naddress2 character varying(50),\r\ndistrict character varying(20),\r\ncity_id smallint,\r\npostal_code character varying(10), \r\nphone bytea,\r\nlast_update timestamp without time zone,\r\nCONSTRAINT address_key_sym PRIMARY KEY (address_id)\r\n)\r\nTABLESPACE pg_default;<\/pre>\n<\/div>\n
<\/div>\n
Insertion des donn\u00e9es. Nous utilisons un algorithme “aes256” avec compression et cl\u00e9 de session.<\/div>\n
La cl\u00e9 de chiffrement est toujours ‘capdata2023’<\/div>\n
<\/div>\n
\n
insert into public.customer_pgp_sym_256\r\nselect customer_id,\r\nstore_id,\r\npgp_sym_encrypt(c.first_name,'capdata2023','cipher-algo=aes256, compress-algo=1, sess-key=1'),\r\npgp_sym_encrypt(c.last_name,'capdata2023','cipher-algo=aes256, compress-algo=1, sess-key=1'),\r\npgp_sym_encrypt(c.email,'capdata2023','cipher-algo=aes256, compress-algo=1, sess-key=1'),\r\naddress_id,\r\nactivebool,\r\ncreate_date,\r\nlast_update,\r\nactive\r\nfrom customer c;\r\n\r\nINSERT 0 599\r\nTime: 1822.944 ms (00:01.823)<\/pre>\n<\/div>\n
<\/div>\n
\n
insert into public.address_pgp_sym_256\r\nselect address_id,\r\npgp_sym_encrypt(address,'capdata2023','cipher-algo=aes256, compress-algo=1, sess-key=1'),\r\naddress2,\r\ndistrict,\r\ncity_id,\r\npostal_code,\r\npgp_sym_encrypt(phone,'capdata2023','cipher-algo=aes256, compress-algo=1, sess-key=1'),\r\nlast_update\r\nfrom address;\r\n\r\nINSERT 0 603\r\nTime: 1244.807 ms (00:01.245)<\/pre>\n<\/div>\n
Nous d\u00e9passons la seconde \u00e0 chaque op\u00e9ration d’insertions dans les nouvelles tables !!<\/p>\n
Si l’on souhaite d\u00e9crypter les champs , nous utilisons la fonction “pgp_sym_decrypt” et notre cl\u00e9 de chiffrement.<\/p>\n
 <\/p>\n
\r\n\r\nselect c.customer_id,\r\npgp_sym_decrypt(c.first_name,'capdata2023') as first_name,\r\npgp_sym_decrypt(c.last_name,'capdata2023') as last_name,\r\npgp_sym_decrypt(c.email,'capdata2023') as email,\r\nc.create_date,\r\npgp_sym_decrypt(a.address,'capdata2023') as adresse,\r\na.district,\r\npgp_sym_decrypt(a.phone,'capdata2023') as telephone\r\nfrom customer_pgp_sym_256 c\r\ninner join address_pgp_sym_256 a\r\non (c.address_id=a.address_id);\r\n\r\ncustomer_id  | first_name  | last_name    | email                                    | create_date | adresse                                | district             | telephone\r\n-------------+-------------+--------------+------------------------------------------+-------------+----------------------------------------+----------------------+--------------\r\n524          | Jared       | Ely          | jared.ely@sakilacustomer.org             | 2006-02-14  | 1003 Qinhuangdao Street                | West Java            | 35533115997\r\n1            | Mary        | Smith        | mary.smith@sakilacustomer.org            | 2006-02-14  | 1913 Hanoi Way                         | Nagasaki             | 28303384290\r\n2            | Patricia    | Johnson      | patricia.johnson@sakilacustomer.org      | 2006-02-14  | 1121 Loja Avenue                       | California           | 838635286649\r\n3            | Linda       | Williams     | linda.williams@sakilacustomer.org        | 2006-02-14  | 692 Joliet Street                      | Attika               | 448477190408\r\n4            | Barbara     | Jones        | barbara.jones@sakilacustomer.org         | 2006-02-14  | 1566 Inegl Manor                       | Mandalay             | 705814003527\r\n5            | Elizabeth   | Brown        | elizabeth.brown@sakilacustomer.org       | 2006-02-14  | 53 Idfu Parkway                        | Nantou               | 10655648674\r\n6            | Jennifer    | Davis        | jennifer.davis@sakilacustomer.org        | 2006-02-14  | 1795 Santiago de Compostela Way        | Texas                | 860452626434\r\n7            | Maria       | Miller       | maria.miller@sakilacustomer.org          | 2006-02-14  | 900 Santiago de Compostela Parkway     | Central Serbia       | 716571220373\r\n8            | Susan       | Wilson       | susan.wilson@sakilacustomer.org          | 2006-02-14  | 478 Joliet Way                         | Hamilton             | 657282285970\r\n9            | Margaret    | Moore        | margaret.moore@sakilacustomer.org        | 2006-02-14  | 613 Korolev Drive                      | Masqat               | 380657522649\r\n10           | Dorothy     | Taylor       | dorothy.taylor@sakilacustomer.org        | 2006-02-14  | 1531 Sal Drive                         | Esfahan              | 648856936185\r\n11           | Lisa        | Anderson     | lisa.anderson@sakilacustomer.org         | 2006-02-14  | 1542 Tarlac Parkway                    | Kanagawa             | 635297277345\r\n12           | Nancy       | Thomas       | nancy.thomas@sakilacustomer.org          | 2006-02-14  | 808 Bhopal Manor                       | Haryana              | 465887807014\r\n13           | Karen       | Jackson      | karen.jackson@sakilacustomer.org         | 2006-02-14  | 270 Amroha Parkway                     | Osmaniye             | 695479687538\r\n14           | Betty       | White        | betty.white@sakilacustomer.org           | 2006-02-14  | 770 Bydgoszcz Avenue                   | California           | 517338314235\r\n15           | Helen       | Harris       | helen.harris@sakilacustomer.org          | 2006-02-14  | 419 Iligan Lane                        | Madhya Pradesh       | 990911107354\r\n16           | Sandra      | Martin       | sandra.martin@sakilacustomer.org         | 2006-02-14  | 360 Toulouse Parkway                   | England              | 949312333307\r\n\r\n\r\n .....\r\n\r\n(599 rows)\r\n\r\nTime: 2887.341 ms (00:02.887)<\/pre>\n
<\/div>\n
Ici, nous d\u00e9passons les 2 secondes pour renvoyer les donn\u00e9es d\u00e9chiffr\u00e9es.\u00a0 Dans le plan d’ex\u00e9cution de cette requ\u00eate, on peut voir un co\u00fbt global multipli\u00e9 par 3 quasiment, et un temps global sur l’op\u00e9ration de jointure entre les 2 tables de plus de 2800 millisecondes.<\/div>\n
<\/div>\n
<\/div>\n
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nHash Join (cost=37.57..84.63 rows=599 width=177) (actual time=7.387..2886.456 rows=599 loops=1)\r\nOutput: c.customer_id, pgp_sym_decrypt(c.first_name, 'capdata2023'::text), pgp_sym_decrypt(c.last_name, 'capdata2023'::text), pgp_sym_decrypt(c.email, 'capdata2023'::text), c.create_date, pgp_sym_decrypt(a.address, 'capdata2023'::text), a.district, pgp_sym_decrypt(a.phone, 'capdata2023'::text)\r\nInner Unique: true\r\nHash Cond: (c.address_id = a.address_id)\r\n- Seq Scan on public.customer_pgp_sym_256 c (cost=0.00..37.99 rows=599 width=370) (actual time=0.007..0.833 rows=599 loops=1)\r\nOutput: c.customer_id, c.store_id, c.first_name, c.last_name, c.email, c.address_id, c.activebool, c.create_date, c.last_update, c.active\r\n- Hash (cost=30.03..30.03 rows=603 width=253) (actual time=1.186..1.189 rows=603 loops=1)\r\nOutput: a.address, a.district, a.phone, a.address_id\r\nBuckets: 1024 Batches: 1 Memory Usage: 179kB\r\n- Seq Scan on public.address_pgp_sym_256 a (cost=0.00..30.03 rows=603 width=253) (actual time=0.006..0.516 rows=603 loops=1)\r\nOutput: a.address, a.district, a.phone, a.address_id\r\nPlanning Time: 0.200 ms\r\nExecution Time: 2887.108 ms\r\n(13 rows)\r\n\r\nTime: 2887.897 ms (00:02.888)<\/pre>\n
 <\/p>\n
Chiffrement PGP avec pair de cl\u00e9s publique\/priv\u00e9e<\/h3>\n
 <\/p>\n
C\u2019est la m\u00e9thode la plus complexe \u00e0 mettre en place, mais aussi la plus optimale en termes de s\u00e9curit\u00e9.
\nLe proc\u00e9d\u00e9 s\u2019appuie \u00e9galement sur OpenPGP, mais utilise, en plus, une cl\u00e9 publique c\u00f4t\u00e9 serveur de bases de donn\u00e9es. Cette cl\u00e9 publique est utilis\u00e9e pour chiffrer la donn\u00e9e.
\nC\u2019est au d\u00e9chiffrage que la cl\u00e9 priv\u00e9e est envoy\u00e9e depuis l\u2019application pour contr\u00f4le, et qui sera utilis\u00e9e pour lire les champs enregistr\u00e9s.<\/p>\n
 <\/p>\n
Gestion des cl\u00e9s RSA<\/h4>\n
Avant d\u2019utiliser les cl\u00e9s publiques et cl\u00e9s priv\u00e9es, il faut les d\u00e9clarer sur la machine h\u00e9bergeant la base de donn\u00e9es.<\/p>\n
Pour l\u2019installation, nous utilisons l\u2019utilitaire \u00ab gpg<\/strong> \u00bb. Lancer la cr\u00e9ation de cl\u00e9s via l\u2019option \u00ab full-generate-keys<\/strong> \u00bb pour choisir les options de cl\u00e9s. Nous choisissons des cl\u00e9s RSA sign\u00e9es, avec un codage sur 2048 bits. La p\u00e9riode de validit\u00e9 des cl\u00e9s est de 2 ans.<\/p>\n
Si vous lancez cette commande avec un compte “non root”, ex\u00e9cuter l’option –pinentry-mode=loopback<\/strong>, sinon vous n’aurez pas les permissions de
\nmodifications des cl\u00e9s \u00e9ventuellement d\u00e9j\u00e0 cr\u00e9\u00e9es.<\/p>\n
[postgres@ ~]$ gpg --full-generate-key --pinentry-mode=loopback\r\ngpg (GnuPG) 2.2.20; Copyright (C) 2020 Free Software Foundation, Inc.\r\nThis is free software: you are free to change and redistribute it.\r\nThere is NO WARRANTY, to the extent permitted by law.\r\n\r\nPlease select what kind of key you want:\r\n(1) RSA and RSA (default)\r\n(2) DSA and Elgamal\r\n(3) DSA (sign only)\r\n(4) RSA (sign only)\r\n(14) Existing key from card\r\nYour selection? 1\r\nRSA keys may be between 1024 and 4096 bits long.\r\nWhat keysize do you want? (2048) 2048\r\nRequested keysize is 2048 bits\r\nPlease specify how long the key should be valid.\r\n0 = key does not expire\r\n = key expires in n days\r\nw = key expires in n weeks\r\nm = key expires in n months\r\ny = key expires in n years\r\nKey is valid for? (0) 2y\r\nKey expires at Wed 09 Jul 2025 02:28:18 PM UTC\r\nIs this correct? (y\/N) y\r\n\r\nGnuPG needs to construct a user ID to identify your key.\r\n\r\nReal name: capdata\r\nEmail address: \r\nComment: capdata gpg PG test\r\nYou selected this USER-ID:\r\n"capdata (capdata gpg PG test) "\r\n\r\nChange (N)ame, (C)omment, (E)mail or (O)kay\/(Q)uit? O\r\n\r\n\r\npub rsa2048 2023-07-10 [SC] [expires: 2025-07-09]\r\n50B8DAB80C7E568169DCF9A7A38D290FAA943D45\r\nuid capdata (capdata gpg PG test) \r\nsub rsa2048 2023-07-10 [E] [expires: 2025-07-09]<\/pre>\n
 <\/p>\n
 <\/p>\n
V\u00e9rifications<\/p>\n
[postgres@~]$ gpg --list-keys --keyid-format short\r\ngpg: checking the trustdb\r\ngpg: marginals needed: 3 completes needed: 1 trust model: pgp\r\ngpg: depth: 0 valid: 1 signed: 0 trust: 0-, 0q, 0n, 0m, 0f, 1u\r\ngpg: next trustdb check due at 2025-07-09\r\n\/var\/lib\/pgsql\/.gnupg\/pubring.kbx\r\n---------------------------------\r\npub rsa2048\/AA943D45 2023-07-10 [SC] [expires: 2025-07-09]\r\n50B8DAB80C7E568169DCF9A7A38D290FAA943D45\r\nuid [ultimate] capdata (capdata gpg PG test) \r\nsub rsa2048\/1756306E 2023-07-10 [E] [expires: 2025-07-09]<\/pre>\n
 <\/p>\n
et<\/p>\n
[postgres@~]$ gpg --list-secret-keys --keyid-format short\r\n\/var\/lib\/pgsql\/.gnupg\/pubring.kbx\r\n---------------------------------\r\nsec rsa2048\/AA943D45 2023-07-10 [SC] [expires: 2025-07-09]\r\n50B8DAB80C7E568169DCF9A7A38D290FAA943D45\r\nuid [ultimate] capdata (capdata gpg PG test) \r\nssb rsa2048\/1756306E 2023-07-10 [E] [expires: 2025-07-09]<\/pre>\n
 <\/p>\n
Exporter la cl\u00e9 publique et la cl\u00e9 priv\u00e9e<\/p>\n
 <\/p>\n
[postgres@~]$ gpg --armor --export 'capdata'\r\n-----BEGIN PGP PUBLIC KEY BLOCK-----\r\n\r\nmQENBGSsFaoBCADMenKoGzxL7hZ1gkVHUrY4UXA34ckuZn6yOeSwb\/mrY\/HdXVtz\r\nMO+cCCmJyp0rS3WoWAM+2bcstpAOgJaRzjZMbbKx9P0BocwbahwMbEgGY9J10l6S\r\nKT79khDmLrkLuXxiDl3IzsqatqMYIphrdNMZmUYIo5YegX\/zcrgY2b3+xRdiPLnk\r\nWOmh\/hJqzHd9GnTGCDa5jkUrme0DhBHJOBuAyh1abWGHHCrYlrs2guA1iYUMiq3P\r\nRD8dkIP5vEZO9XBPWRe0S41Et8kuocn0AlABW0VcA0GenMeviLk\/xME2Cpnme9Fr\r\n0FDUxVQYi6vFXuf530G5f9QQnIYsQL26DVa1ABEBAAG0OmNhcGRhdGEgKGNhcGRh\r\ndGEgZ3BnIFBHIHRlc3QpIDxlcmFtaUBjYXBkYXRhLW9zbW96aXVtLmNvbT6JAVQE\r\nEwEIAD4WIQRQuNq4DH5WgWnc+aejjSkPqpQ9RQUCZKwVqgIbAwUJA8JnAAULCQgH\r\nAgYVCgkICwIEFgIDAQIeAQIXgAAKCRCjjSkPqpQ9ReqCB\/97Fk6gWWYSfHQoQrH4\r\nN7Rlf1tNN0M5N7gmO6qZQVZzR5qiV1y3ahAIBPyIcQla9Nb3ry1NE5QayZ1FyEnu\r\nvTTVF2CWq0yXtes3Sv7Q2DrzoiENVwOeGSxqsx\/IqfY8iFL6m3hXwXC51JNraLFh\r\nsTP617LKvfSETr+UFpkctdAfgmxlzJ7cUHF+m0lr7OsN9e5XZ8S9CwInFX6GJPDS\r\nj\/CpUr4l\/fdZa5H\/1pc+gFBDWoaZYquqoYXM2YHOkPp9RZ12uejbRaIn\/SlYKutE.......\r\n......\r\nmkx5lbcQlsaWj8PKM56mCgKF\r\n=U1rR\r\n-----END PGP PUBLIC KEY BLOCK-----<\/pre>\n
 <\/p>\n
 <\/p>\n
et<\/p>\n
[postgres@~]$ gpg --armor --export-secret-key 'capdata'\r\n-----BEGIN PGP PRIVATE KEY BLOCK-----\r\n\r\nlQOYBGSsFaoBCADMenKoGzxL7hZ1gkVHUrY4UXA34ckuZn6yOeSwb\/mrY\/HdXVtz\r\nMO+cCCmJyp0rS3WoWAM+2bcstpAOgJaRzjZMbbKx9P0BocwbahwMbEgGY9J10l6S\r\nKT79khDmLrkLuXxiDl3IzsqatqMYIphrdNMZmUYIo5YegX\/zcrgY2b3+xRdiPLnk\r\nWOmh\/hJqzHd9GnTGCDa5jkUrme0DhBHJOBuAyh1abWGHHCrYlrs2guA1iYUMiq3P\r\nRD8dkIP5vEZO9XBPWRe0S41Et8kuocn0AlABW0VcA0GenMeviLk\/xME2Cpnme9Fr\r\n0FDUxVQYi6vFXuf530G5f9QQnIYsQL26DVa1ABEBAAEAB\/4poUVeJdtfDxxZ9LmD\r\nlZqdOTFaYzjZHkttoD1H0ahYZUr8+VCQ0XX7A6tnTw20HpMYAME6Zst1Cj8mgLYG\r\n\/d+OrGfM9Nac4jLCoxYOTm5UhLa4v6l64vRc3kPcBUet1Cf3c7rS0w0rNgNa+tIi\r\n0IBZDhxUzm9WCyIAb8r83jnhGCSTaAeCBOqHnXE+JgUOdf15k1oVxYZdS73mpoNT\r\naLXmmJbC7JFC74j40oP4brbUzzWo0mZo0R394ZG0booBJM2BDH4ydrSvGWbsreSF\r\njo31xkHqsLOPHDlJvdVnbWVSuyjk0oL2bKWgXTz9oT1YxiaN32WRgM6cMXvXHZka\r\nTxhhBADa3SQQjK5+QXRYcTZjnMZ+E15AIk\/DklYC1\/Q\/TYtKKD3w7oaoc7M8sPsq\r\nw7gzTMc9kAKb7NlG4x+v4+Ab5RuV08osS7ZPu3H3gZyPgjjyjwZEKG0pnMJdDfwr\r\neWqhIdc35gd8FGiNHgoeFVS9pAA5TBB3W+mgR3x9Jdj\/Q8htlQQA7yxwQ3\/1bYyj\r\nejd1Hvihq4YtvMyA7pJSkv5ptqyA\/qiM6jkjH4WVL4Qew+IrmHOCfVyEjebIQgF......\r\n......\r\n5YLh06oFmkx5lbcQlsaWj8PKM56mCgKF\r\n=fYKN\r\n-----END PGP PRIVATE KEY BLOCK-----<\/pre>\n
 <\/p>\n
 <\/p>\n
La suite consistera \u00e0 cr\u00e9er les tables pour accueillir ces donn\u00e9es chiffr\u00e9es, et ins\u00e9rer les donn\u00e9es \u00e0 partir des donn\u00e9es sources.<\/p>\n
 <\/p>\n
CREATE TABLE IF NOT EXISTS public.customer_pgp_pub\r\n(\r\ncustomer_id integer,\r\nstore_id smallint , \r\nfirst_name bytea , \r\nlast_name bytea , \r\nemail bytea, \r\naddress_id smallint, \r\nactivebool boolean, \r\ncreate_date date, \r\nlast_update timestamp without time zone, \r\nactive integer,\r\nCONSTRAINT customer_pkey_pub PRIMARY KEY (customer_id) \r\n)\r\nTABLESPACE pg_default;\r\n\r\n\r\n\r\nCREATE TABLE IF NOT EXISTS public.address_pgp_pub\r\n(\r\naddress_id integer, \r\naddress bytea,\r\naddress2 character varying(50),\r\ndistrict character varying(20),\r\ncity_id smallint,\r\npostal_code character varying(10), \r\nphone bytea,\r\nlast_update timestamp without time zone,\r\nCONSTRAINT address_key_pub PRIMARY KEY (address_id)\r\n)\r\nTABLESPACE pg_default;<\/pre>\n
 <\/p>\n
Lors de l’\u00e9tape d’insertion de donn\u00e9es, nous devons renseigner la cl\u00e9 publique, et utiliser, sur chaque champ, la fonction “pgp_pub_encrypt” !<\/p>\n
 <\/p>\n
insert into public.customer_pgp_pub\r\nselect customer_id,\r\nstore_id,\r\npgp_pub_encrypt(c.first_name, keys.pubkey),\r\npgp_pub_encrypt(c.last_name,keys.pubkey),\r\npgp_pub_encrypt(c.email,keys.pubkey),\r\naddress_id,\r\nactivebool,\r\ncreate_date,\r\nlast_update,\r\nactive\r\nfrom customer c\r\ncross join (select dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----\r\n\r\nmQENBGSsFaoBCADMenKoGzxL7hZ1gkVHUrY4UXA34ckuZn6yOeSwb\/mrY\/HdXVtz\r\nMO+cCCmJyp0rS3WoWAM+2bcstpAOgJaRzjZMbbKx9P0BocwbahwMbEgGY9J10l6S\r\nKT79khDmLrkLuXxiDl3IzsqatqMYIphrdNMZmUYIo5YegX\/zcrgY2b3+xRdiPLnk\r\nWOmh\/hJqzHd9GnTGCDa5jkUrme0DhBHJOBuAyh1abWGHHCrYlrs2guA1iYUMiq3P\r\nRD8dkIP5vEZO9XBPWRe0S41Et8kuocn0AlABW0VcA0GenMeviLk\/xME2Cpnme9Fr\r\n0FDUxVQYi6vFXuf530G5f9QQnIYsQL26DVa1ABEBAAG0OmNhcGRhdGEgKGNhcGRh\r\ndGEgZ3BnIFBHIHRlc3QpIDxlcmFtaUBjYXBkYXRhLW9zbW96aXVtLmNvbT6JAVQE\r\nEwEIAD4WIQRQuNq4DH5WgWnc+aejjSkPqpQ9RQUCZKwVqgIbAwUJA8JnAAULCQgH\r\nAgYVCgkICwIEFgIDAQIeAQIXgAAKCRCjjSkPqpQ9ReqCB\/97Fk6gWWYSfHQoQrH4.....\r\n....\r\nvrti5S7AxozOn3jUfUawgKGHRhY2\/Sm06+dmThnV3O3jRqAJcerTcFyL5YLh06oF\r\nmkx5lbcQlsaWj8PKM56mCgKF\r\n=U1rR\r\n-----END PGP PUBLIC KEY BLOCK-----') As pubkey) \r\nAs keys;\r\n\r\n\r\nINSERT 0 599\r\nTime: 159.320 ms<\/pre>\n
 <\/p>\n
insert into public.address_pgp_pub\r\nselect address_id,\r\npgp_pub_encrypt(address,keys.pubkey),\r\naddress2,\r\ndistrict,\r\ncity_id,\r\npostal_code,\r\npgp_pub_encrypt(phone,keys.pubkey),\r\nlast_update\r\nfrom address\r\ncross join (select dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----\r\n\r\nmQENBGSsFaoBCADMenKoGzxL7hZ1gkVHUrY4UXA34ckuZn6yOeSwb\/mrY\/HdXVtz\r\nMO+cCCmJyp0rS3WoWAM+2bcstpAOgJaRzjZMbbKx9P0BocwbahwMbEgGY9J10l6S\r\nKT79khDmLrkLuXxiDl3IzsqatqMYIphrdNMZmUYIo5YegX\/zcrgY2b3+xRdiPLnk\r\nWOmh\/hJqzHd9GnTGCDa5jkUrme0DhBHJOBuAyh1abWGHHCrYlrs2guA1iYUMiq3P\r\nRD8dkIP5vEZO9XBPWRe0S41Et8kuocn0AlABW0VcA0GenMeviLk\/xME2Cpnme9Fr\r\n0FDUxVQYi6vFXuf530G5f9QQnIYsQL26DVa1ABEBAAG0OmNhcGRhdGEgKGNhcGRh\r\ndGEgZ3BnIFBHIHRlc3QpIDxlcmFtaUBjYXBkYXRhLW9zbW96aXVtLmNvbT6JAVQE\r\nEwEIAD4WIQRQuNq4DH5WgWnc+aejjSkPqpQ9RQUCZKwVqgIbAwUJA8JnAAULCQgH\r\nAgYVCgkICwIEFgIDAQIeAQIXgAAKCRCjjSkPqpQ9ReqCB\/97Fk6gWWYSfHQoQrH4\r\nN7Rlf1tNN0M5N7gmO6qZQVZzR5qiV1y3ahAIBPyIcQla9Nb3ry1NE5QayZ1FyEnu\r\n....\r\nvrti5S7AxozOn3jUfUawgKGHRhY2\/Sm06+dmThnV3O3jRqAJcerTcFyL5YLh06oF\r\nmkx5lbcQlsaWj8PKM56mCgKF\r\n\r\n=U1rR\r\n\r\n-----END PGP PUBLIC KEY BLOCK-----') As pubkey)&amp;amp;amp;nbsp;\r\n\r\n As keys;\r\n\r\n\r\nINSERT 0 599\r\nTime: 159.320 ms<\/pre>\n
<\/div>\n
<\/div>\n
Les insertions sont plut\u00f4t rapides vis-\u00e0-vis du PGP sym\u00e9triques avec le chiffrage en aes256.<\/div>\n
La restitution des donn\u00e9es d\u00e9chiffr\u00e9es est permise gr\u00e2ce \u00e0 la cl\u00e9 priv\u00e9e renseign\u00e9e.<\/div>\n
<\/div>\n
<\/div>\n
\n
select c.customer_id,\r\npgp_pub_decrypt(c.first_name,keys.pubkey) as first_name,\r\npgp_pub_decrypt(c.last_name,keys.pubkey) as last_name,\r\npgp_pub_decrypt(c.email,keys.pubkey) as email,\r\nc.create_date,\r\npgp_pub_decrypt(a.address,keys.pubkey) as adresse,\r\na.district,\r\npgp_pub_decrypt(a.phone,keys.pubkey) as telephone\r\nfrom customer_pgp_pub c\r\ninner join address_pgp_pub a\r\non (c.address_id=a.address_id)\r\ncross join (SELECT dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----\r\n\r\nlQOYBGSsFaoBCADMenKoGzxL7hZ1gkVHUrY4UXA34ckuZn6yOeSwb\/mrY\/HdXVtz\r\nMO+cCCmJyp0rS3WoWAM+2bcstpAOgJaRzjZMbbKx9P0BocwbahwMbEgGY9J10l6S\r\nKT79khDmLrkLuXxiDl3IzsqatqMYIphrdNMZmUYIo5YegX\/zcrgY2b3+xRdiPLnk\r\nWOmh\/hJqzHd9GnTGCDa5jkUrme0DhBHJOBuAyh1abWGHHCrYlrs2guA1iYUMiq3P\r\nRD8dkIP5vEZO9XBPWRe0S41Et8kuocn0AlABW0VcA0GenMeviLk\/xME2Cpnme9Fr\r\n0FDUxVQYi6vFXuf530G5f9QQnIYsQL26DVa1ABEBAAEAB\/4poUVeJdtfDxxZ9LmD\r\nlZqdOTFaYzjZHkttoD1H0ahYZUr8+VCQ0XX7A6tnTw20HpMYAME6Zst1Cj8mgLYG\r\n\/d+OrGfM9Nac4jLCoxYOTm5UhLa4v6l64vRc3kPcBUet1Cf3c7rS0w0rNgNa+tIi\r\n0IBZDhxUzm9WCyIAb8r83jnhGCSTaAeCBOqHnXE+JgUOdf15k1oVxYZdS73mpoNT\r\naLXmmJbC7JFC74j40oP4brbUzzWo0mZo0R394ZG0booBJM2BDH4ydrSvGWbsreSF\r\njo31xkHqsLOPHDlJvdVnbWVSuyjk0oL2bKWgXTz9oT1YxiaN32WRgM6cMXvXHZka\r\nTxhhBADa3SQQjK5+QXRYcTZjnMZ+E15AIk\/DklYC1\/Q\/TYtKKD3w7oaoc7M8sPsq\r\nw7gzTMc9kAKb7NlG4x+v4+Ab5RuV08osS7ZPu3H3gZyPgjjyjwZEKG0pnMJdDfwr.....\r\n.....\r\n\r\nNd51J9eivrti5S7AxozOn3jUfUawgKGHRhY2\/Sm06+dmThnV3O3jRqAJcerTcFyL\r\n5YLh06oFmkx5lbcQlsaWj8PKM56mCgKF\r\n=fYKN\r\n-----END PGP PRIVATE KEY BLOCK-----') As pubkey) As keys;\r\n\r\n\r\ncustomer_id  | first_name  | last_name    | email                                    | create_date | adresse                                | district             | telephone\r\n-------------+-------------+--------------+------------------------------------------+-------------+----------------------------------------+----------------------+--------------\r\n524          | Jared       | Ely          | jared.ely@sakilacustomer.org              | 2006-02-14 | 1003 Qinhuangdao Street                | West Java            | 35533115997\r\n1            | Mary        | Smith        | mary.smith@sakilacustomer.org             | 2006-02-14 | 1913 Hanoi Way                         | Nagasaki             | 28303384290\r\n2            | Patricia    | Johnson      | patricia.johnson@sakilacustomer.org       | 2006-02-14 | 1121 Loja Avenue                       | California           | 838635286649\r\n3            | Linda       | Williams     | linda.williams@sakilacustomer.org         | 2006-02-14 | 692 Joliet Street                      | Attika               | 448477190408\r\n4            | Barbara     | Jones        | barbara.jones@sakilacustomer.org          | 2006-02-14 | 1566 Inegl Manor                       | Mandalay             | 705814003527\r\n5            | Elizabeth   | Brown        | elizabeth.brown@sakilacustomer.org        | 2006-02-14 | 53 Idfu Parkway                        | Nantou               | 10655648674\r\n6            | Jennifer    | Davis        | jennifer.davis@sakilacustomer.org         | 2006-02-14 | 1795 Santiago de Compostela Way        | Texas                | 860452626434\r\n7            | Maria       | Miller       | maria.miller@sakilacustomer.org           | 2006-02-14 | 900 Santiago de Compostela Parkway     | Central Serbia       | 716571220373\r\n8            | Susan       | Wilson       | susan.wilson@sakilacustomer.org           | 2006-02-14 | 478 Joliet Way                         | Hamilton             | 657282285970\r\n9            | Margaret    | Moore        | margaret.moore@sakilacustomer.org         | 2006-02-14 | 613 Korolev Drive                      | Masqat               | 380657522649\r\n10           | Dorothy     | Taylor       | dorothy.taylor@sakilacustomer.org         | 2006-02-14 | 1531 Sal Drive                         | Esfahan              | 648856936185\r\n11           | Lisa        | Anderson     | lisa.anderson@sakilacustomer.org          | 2006-02-14 | 1542 Tarlac Parkway                    | Kanagawa             | 635297277345\r\n12           | Nancy       | Thomas       | nancy.thomas@sakilacustomer.org           | 2006-02-14 | 808 Bhopal Manor                       | Haryana              | 465887807014\r\n13           | Karen       | Jackson      | karen.jackson@sakilacustomer.org          | 2006-02-14 | 270 Amroha Parkway                     | Osmaniye             | 695479687538\r\n14           | Betty       | White        | betty.white@sakilacustomer.org            | 2006-02-14 | 770 Bydgoszcz Avenue                   | California           | 517338314235\r\n15           | Helen       | Harris       | helen.harris@sakilacustomer.org           | 2006-02-14 | 419 Iligan Lane                        | Madhya Pradesh       | 990911107354\r\n16           | Sandra      | Martin       | sandra.martin@sakilacustomer.org          | 2006-02-14 | 360 Toulouse Parkway                   | England              | 949312333307\r\n......\r\n\r\n\r\n(599 rows)\r\n\r\nTime: 11351.879 ms (00:11.352)<\/pre>\n<\/div>\n
Des donn\u00e9es relev\u00e9es en 11 secondes. Concernant le plan d’ex\u00e9cution, , le co\u00fbt global de notre jointure double vis \u00e0 vis du chiffrement sym\u00e9trique (on passe \u00e0 175), et un temps d’ex\u00e9cution bien plus long (11400 millisecondes pour la jointure).<\/p>\n
 <\/p>\n
Hash Join (cost=74.57..175.63 rows=599 width=177) (actual time=21.263..11498.855 rows=599 loops=1)\r\nOutput: c.customer_id, pgp_pub_decrypt(c.first_name, \\************************************\r\nInner Unique: true\r\nHash Cond: (c.address_id = a.address_id)\r\n- Seq Scan on public.customer_pgp_pub c (cost=0.00..91.99 rows=599 width=1030) (actual time=0.006..1.294 rows=599 loops=1)\r\nOutput: c.customer_id, c.store_id, c.first_name, c.last_name, c.email, c.address_id, c.activebool, c.create_date, c.last_update, c.active\r\n- Hash (cost=67.03..67.03 rows=603 width=695) (actual time=1.806..1.809 rows=603 loops=1)\r\nOutput: a.address, a.district, a.phone, a.address_id\r\nBuckets: 1024 Batches: 1 Memory Usage: 440kB\r\n- Seq Scan on public.address_pgp_pub a (cost=0.00..67.03 rows=603 width=695) (actual time=0.004..0.556 rows=603 loops=1)\r\nOutput: a.address, a.district, a.phone, a.address_id\r\nPlanning Time: 0.536 ms\r\nExecution Time: 11499.672 ms\r\n(13 rows)\r\n\r\nTime: 11501.474 ms (00:11.501)<\/pre>\n
 <\/p>\n
Interpr\u00e9tation des r\u00e9sultats.<\/h3>\n
 <\/p>\n
Une fois ces tests effectu\u00e9s, nous pouvons en tirer des conclusions.<\/p>\n
stockage<\/h4>\n
Concernant l’espace disque de chacune de ces tables, nous voyons de grandes diff\u00e9rences<\/p>\n
 <\/p>\n
(postgres@[local]:5433) [dvdrental] select tablename , pg_size_pretty(pg_table_size(tablename::varchar)) as SizeMo from pg_tables where tablename like 'custo%' or tablename like 'addre%' order by tablename,2;\r\ntablename             | sizemo\r\n----------------------+--------\r\naddress               | 88 kB\r\naddress_encrypt       | 104 kB\r\naddress_pgp_pub       | 520 kB\r\naddress_pgp_sym_256   | 224 kB\r\ncustomer              | 96 kB\r\ncustomer_encrypt      | 120 kB\r\ncustomer_pgp_pub      | 720 kB\r\ncustomer_pgp_sym_256  | 288 kB\r\n(10 rows)<\/pre>\n
 <\/p>\n
-> Le chiffrement simple demande donc 20% de stockage en plus par rapport aux tables d’origine.<\/p>\n
-> Avec le chiffrement sym\u00e9trique et compression algo aes256, cela double le volume disque vis-\u00e0-vis du chiffrement classique.<\/p>\n
-> Enfin le chiffrement avec cl\u00e9 publique n\u00e9cessite des tables 5 \u00e0 6 fois plus volumineuses que celles d’origine.<\/p>\n
 <\/p>\n
Les temps d’ex\u00e9cution<\/h4>\n
Comme nous avons pu le constater au cours de ce test, le chiffrement engendre des temps de r\u00e9ponses bien plus importants sur notre requ\u00eate test.<\/p>\n
 <\/p>\n
-> Pour le chiffrement simple, un temps d’ex\u00e9cution en insertion et s\u00e9lection quasi identique. Mais l’\u00e9chantillon de donn\u00e9es n’est pas forc\u00e9ment le plus repr\u00e9sentatif car celui-ci est plut\u00f4t restreint.<\/p>\n
-> Pour le chiffrement sym\u00e9trique avec compression algo aes256, \u00a0temps d’ex\u00e9cution de l’insertion et de la s\u00e9lection beaucoup plus longs que le chiffrement classique. Nous passons de 5 millisecondes au SELECT et INSERT \u00e0 plus de 1000 millisecondes pour l’INSERT et plus de 2800 millisecondes pour le SELECT.<\/p>\n
-> Pour le chiffrement avec cl\u00e9 publique\/cl\u00e9 priv\u00e9e, \u00e0 l’insertion, c’est plut\u00f4t rapide (quelques centaines de millisecondes).
\nAu select, nous d\u00e9passons les 10 secondes, pour une requ\u00eate qui met 5 millisecondes sans aucun chiffrage.<\/p>\n
 <\/p>\n
Nous devons prendre en compte le fait que pour notre test, nous travaillons sur une VM extr\u00eamement sous dimensionn\u00e9e (ec2 type t2 micro).
\nCependant, nous n’avons s\u00e9lectionn\u00e9 que quelques centaines de lignes dans nos tables, ce qui repr\u00e9sente un \u00e9chantillon faible.<\/p>\n
Qu’en serait-il sur des tables de plus 100 millions de lignes ? La manipulation de donn\u00e9es chiffr\u00e9es volumineuses exigerait, sans aucun doute, plus de ressources en terme de CPU et RAM.<\/p>\n
 <\/p>\n
 <\/p>\n
\ud83d\ude42<\/p>\n
Emmanuel Rami<\/p>\n
 <\/p>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"
Lorsque l’on parle s\u00e9curit\u00e9 des donn\u00e9es sur une instance PostgreSQL, nous avons le choix entre le chiffrement “at rest” avec TDE (cf cet article), ou bien l’utilisation de l’extension, bien connue des DBA PostgreSQL, pgcrypto. Cette extension permet de disposer… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":32,"featured_media":10164,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[266],"tags":[458,459],"class_list":["post-10163","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-chiffrement","tag-pgcrypto"],"yoast_head":"\nChiffrement sous PostgreSQL : mais sous quelles conditions ? - Capdata TECH BLOG<\/title>\n<meta name=\"description\" content=\"Op\u00e9rations de chiffrement des donn\u00e9es sous PostgreSQL avec pgcrypto , combien cela va me couter en temps et en stockage ?\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Chiffrement sous PostgreSQL : mais sous quelles conditions ? - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"Op\u00e9rations de chiffrement des donn\u00e9es sous PostgreSQL avec pgcrypto , combien cela va me couter en temps et en stockage ?\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2023-08-02T10:02:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-09-29T14:34:15+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/07\/chiffrement.png\" \/>\n\t<meta property=\"og:image:width\" content=\"366\" \/>\n\t<meta property=\"og:image:height\" content=\"252\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Emmanuel RAMI\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Emmanuel RAMI\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"23 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/\"},\"author\":{\"name\":\"Emmanuel RAMI\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/797b9b6698fa35f7ce3e9a70a8b102ae\"},\"headline\":\"Chiffrement sous PostgreSQL : mais sous quelles conditions ?\",\"datePublished\":\"2023-08-02T10:02:56+00:00\",\"dateModified\":\"2023-09-29T14:34:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/\"},\"wordCount\":5656,\"commentCount\":3,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"chiffrement\",\"pgcrypto\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/\",\"name\":\"Chiffrement sous PostgreSQL : mais sous quelles conditions ? - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2023-08-02T10:02:56+00:00\",\"dateModified\":\"2023-09-29T14:34:15+00:00\",\"description\":\"Op\u00e9rations de chiffrement des donn\u00e9es sous PostgreSQL avec pgcrypto , combien cela va me couter en temps et en stockage ?\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Chiffrement sous PostgreSQL : mais sous quelles conditions ?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/797b9b6698fa35f7ce3e9a70a8b102ae\",\"name\":\"Emmanuel RAMI\",\"sameAs\":[\"https:\/\/blog.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/erami\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Chiffrement sous PostgreSQL : mais sous quelles conditions ? - Capdata TECH BLOG","description":"Op\u00e9rations de chiffrement des donn\u00e9es sous PostgreSQL avec pgcrypto , combien cela va me couter en temps et en stockage ?","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/","og_locale":"fr_FR","og_type":"article","og_title":"Chiffrement sous PostgreSQL : mais sous quelles conditions ? - Capdata TECH BLOG","og_description":"Op\u00e9rations de chiffrement des donn\u00e9es sous PostgreSQL avec pgcrypto , combien cela va me couter en temps et en stockage ?","og_url":"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2023-08-02T10:02:56+00:00","article_modified_time":"2023-09-29T14:34:15+00:00","og_image":[{"width":366,"height":252,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/07\/chiffrement.png","type":"image\/png"}],"author":"Emmanuel RAMI","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Emmanuel RAMI","Dur\u00e9e de lecture estim\u00e9e":"23 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/"},"author":{"name":"Emmanuel RAMI","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/797b9b6698fa35f7ce3e9a70a8b102ae"},"headline":"Chiffrement sous PostgreSQL : mais sous quelles conditions ?","datePublished":"2023-08-02T10:02:56+00:00","dateModified":"2023-09-29T14:34:15+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/"},"wordCount":5656,"commentCount":3,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["chiffrement","pgcrypto"],"articleSection":["PostgreSQL"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/","url":"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/","name":"Chiffrement sous PostgreSQL : mais sous quelles conditions ? - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2023-08-02T10:02:56+00:00","dateModified":"2023-09-29T14:34:15+00:00","description":"Op\u00e9rations de chiffrement des donn\u00e9es sous PostgreSQL avec pgcrypto , combien cela va me couter en temps et en stockage ?","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/chiffrement-des-donnees-sous-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Chiffrement sous PostgreSQL : mais sous quelles conditions ?"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/797b9b6698fa35f7ce3e9a70a8b102ae","name":"Emmanuel RAMI","sameAs":["https:\/\/blog.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/erami\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/10163","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/32"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=10163"}],"version-history":[{"count":65,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/10163\/revisions"}],"predecessor-version":[{"id":10295,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/10163\/revisions\/10295"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/10164"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=10163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=10163"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=10163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}