{"id":8999,"date":"2022-05-13T08:59:15","date_gmt":"2022-05-13T07:59:15","guid":{"rendered":"https:\/\/blog.capdata.fr\/?p=8999"},"modified":"2022-05-13T08:59:15","modified_gmt":"2022-05-13T07:59:15","slug":"transparent-data-encryption-pour-postgresql","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/","title":{"rendered":"Transparent Data Encryption pour PostgreSQL"},"content":{"rendered":"<a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F8999&#038;text=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F8999&#038;title=Transparent%20Data%20Encryption%20pour%20PostgreSQL\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=Transparent%20Data%20Encryption%20pour%20PostgreSQL&#038;body=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20:%20https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F8999\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" \/><\/a><p>TDE ou Transparent Data Encryption est un produit mis \u00e0 disposition par <a href=\"https:\/\/www.cybertec-postgresql.com\/de\/?gclid=CjwKCAjw9-KTBhBcEiwAr19ig9H3AwlgEA-YrRcL1aHsZsZsiIMgsh2fW_KZPvLgL2YCzsyjvSDGjBoCd18QAvD_BwE\">Cybertec<\/a>. Il permet de faire de l&#8217;encryption de donn\u00e9es de mani\u00e8re automatique dans Postgres. Il crypte les donn\u00e9es des tables, des indexes, les tablespaces, les fichiers temporaires, et tous les objets d\u00e9pendants de la base de donn\u00e9es.<\/p>\n<p>L\u2019encryption est r\u00e9alis\u00e9e sur des blocs de 8K entre le disque et la m\u00e9moire.<\/p>\n<p>Il utilise toutes les optimisations possibles fournies par Intel pour fonctionner de mani\u00e8re efficace.<\/p>\n<h1>Installation :<\/h1>\n<p>Pour pouvoir disposer de cet outil, il vous faut disposer de la version de postgres recompil\u00e9e par Cybertech.<\/p>\n<p>Les sources sont disponibles \u00e0 l\u2019adresse suivante : https:\/\/download.cybertec-postgresql.com\/postgresql-12.3_TDE_1.0.tar.gz<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\n\r\nwget https:\/\/download.cybertec-postgresql.com\/postgresql-12.3_TDE_1.0.tar.gz\r\n\r\n<\/pre>\n<p>Une fois les sources t\u00e9l\u00e9charg\u00e9es, vous pouvez les d\u00e9compresser.<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\n\r\ntar xvfz postgresql-12.3_TDE_1.0.tar.gz\r\n\r\n<\/pre>\n<p>L\u2019\u00e9tape suivante est l\u2019\u00e9tape de compilation. Pour que votre code puisse \u00eatre compil\u00e9, vous allez avoir besoin d\u2019un certain nombre de pr\u00e9requis. Ces paquets pr\u00e9 requis sont les m\u00eames que les paquets n\u00e9cessaires pour compiler le code de postgresql.<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\n\r\n.\/configure --prefix=\/usr\/local\/pg12tde --with-openssl --with-perl \\ --with-python --with-ldap\r\n\r\n<\/pre>\n<p>Une fois la compilation r\u00e9alis\u00e9e, il faut installer les sources.<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\n\r\nmake install\r\n\r\ncd contrib\r\n\r\nmake install\r\n\r\n<\/pre>\n<p>A la suite de l\u2019installation des sources, vous allez donc vous retrouver avec un dossier <em>pg12tde<\/em> qui contiendra les sources de postgresql.<\/p>\n<p>Charge \u00e0 vous de cr\u00e9er l\u2019utilisateur postgres :<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\n\r\naddgroup postgres\r\nadduser postgres\r\n\r\n<\/pre>\n<p>Et de cr\u00e9er le r\u00e9pertoire o\u00f9 vous souhaitez stocker vos donn\u00e9es. Attention, ne d\u00e9marrez pas votre instance d\u00e8s \u00e0 pr\u00e9sent.<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\n\r\nmkdir -p \/usr\/local\/pg12tde\/data\r\nchown postgres \/usr\/local\/pg12tde\/data\r\n\r\n<\/pre>\n<p>Avant de r\u00e9aliser l\u2019initialisation de la base de donn\u00e9es, vous devez d\u00e9finir l\u2019encryptage de votre base de donn\u00e9es. Pour l\u2019exemple, nous avons utilis\u00e9 une m\u00e9thode tr\u00e8s simple : Un fichier de script qui renvoie directement la cl\u00e9 d\u2019encryptage de nos fichiers de donn\u00e9es. Il se pr\u00e9sente sous la forme suivante : <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_dfCz4jicII.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9000\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_dfCz4jicII-300x31.png\" alt=\"\" width=\"658\" height=\"68\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_dfCz4jicII-300x31.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_dfCz4jicII.png 452w\" sizes=\"auto, (max-width: 658px) 100vw, 658px\" \/><\/a><\/p>\n<p><strong>Attention\u00a0:<\/strong> Il s\u2019agit ici d\u2019un exemple simpliste afin de d\u00e9montrer le fonctionnement de TDE. Dans un environnement de production, il faudra favoriser un serveur de cl\u00e9 KMS type Hashicorp ou autre.<\/p>\n<p>Une fois le fichier de cl\u00e9 clairement identifi\u00e9, vous pouvez passer en utilisateur postgres et faire le initdb pour cr\u00e9er votre base de donn\u00e9es. C\u2019est dans cette commande la que vous allez pouvoir passer votre cl\u00e9 encod\u00e9e \u00e0 l\u2019aide de l\u2019option -K. <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_DRhv0GZF5w.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9001\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_DRhv0GZF5w-300x190.png\" alt=\"\" width=\"662\" height=\"419\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_DRhv0GZF5w-300x190.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_DRhv0GZF5w.png 752w\" sizes=\"auto, (max-width: 662px) 100vw, 662px\" \/><\/a><\/p>\n<p>Vous pouvez v\u00e9rifier qu\u2019il a bien prit le fichier d\u2019encryption en allant jeter un oeil au postgresql.conf <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_bnzhsGwCzY.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9002\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_bnzhsGwCzY-300x28.png\" alt=\"\" width=\"664\" height=\"62\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_bnzhsGwCzY-300x28.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_bnzhsGwCzY.png 694w\" sizes=\"auto, (max-width: 664px) 100vw, 664px\" \/><\/a><\/p>\n<p>Une fois votre initdb termin\u00e9, vous n\u2019avez plus qu\u2019\u00e0 lancer l\u2019instance, et \u00e0 vous connecter pour profiter d\u2019un PG encrypt\u00e9.<\/p>\n<p>Pour l\u2019exemple, nous allons cr\u00e9er une base de donn\u00e9es fictive, une table fictive, et des donn\u00e9es fictives, et nous allons v\u00e9rifier l\u2019\u00e9tat du fichier de donn\u00e9es, afin de voir si nous pouvons le d\u00e9crypter. <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_OJQS4HLTWY.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9003\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_OJQS4HLTWY-300x127.png\" alt=\"\" width=\"661\" height=\"280\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_OJQS4HLTWY-300x127.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_OJQS4HLTWY.png 536w\" sizes=\"auto, (max-width: 661px) 100vw, 661px\" \/><\/a><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_HMc3kMKa0D.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9004\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_HMc3kMKa0D-300x36.png\" alt=\"\" width=\"658\" height=\"79\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_HMc3kMKa0D-300x36.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_HMc3kMKa0D.png 539w\" sizes=\"auto, (max-width: 658px) 100vw, 658px\" \/><\/a><\/p>\n<p>Pour cela il suffit d\u2019aller voir dans le r\u00e9pertoire data de l\u2019instance, et de chercher \u00e0 ouvrir l\u2019un des fichiers. Vous devriez obtenir un r\u00e9sultat tel que ci-dessous : <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_AO1pw3NRO4.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9005\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_AO1pw3NRO4-300x167.png\" alt=\"\" width=\"661\" height=\"368\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_AO1pw3NRO4-300x167.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_AO1pw3NRO4-1024x569.png 1024w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_AO1pw3NRO4-768x427.png 768w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_AO1pw3NRO4-1536x853.png 1536w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_AO1pw3NRO4.png 1903w\" sizes=\"auto, (max-width: 661px) 100vw, 661px\" \/><\/a><\/p>\n<p>On voit donc bien que nos fichiers sont crypt\u00e9s.<\/p>\n<p>Attention cependant : l\u2019encryption n\u2019est pas effective sur les dumps de donn\u00e9es. En effet, extraire des donn\u00e9es avec pg_dump ne rentre pas dans la couverture de TDE, contrairement \u00e0 pg_basebackup.<\/p>\n<p>Nous avons test\u00e9 de r\u00e9importer un pg_basebackup depuis une base crypt\u00e9e avec TDE vers une base vanilla et cette derni\u00e8re n\u2019est pas parvenue \u00e0 lire le fichier de backup.<\/p>\n<h1>Au niveau des performances :<\/h1>\n<p>L\u2019\u00e9diteur parle d\u2019un gap de 30% de performance au maximum avec l\u2019utilisation de TDE par rapport \u00e0 une version vanilla de postgres.<\/p>\n<p>Nous avons r\u00e9alis\u00e9 des tests avec pgbench, et voici les r\u00e9sultats.<\/p>\n<p>La volum\u00e9trie de la base choisie est la suivante : <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_GZuJcePMoO.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9006\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_GZuJcePMoO-300x10.png\" alt=\"\" width=\"660\" height=\"22\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_GZuJcePMoO-300x10.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_GZuJcePMoO-768x24.png 768w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/putty_GZuJcePMoO.png 817w\" sizes=\"auto, (max-width: 660px) 100vw, 660px\" \/><\/a><\/p>\n<p>Cette volum\u00e9trie \u00e0 \u00e9t\u00e9 choisie par rapport \u00e0 la taille de la m\u00e9moire sur la machine o\u00f9 les tests ont \u00e9t\u00e9 r\u00e9alis\u00e9s. Il s\u2019agissait ici de forcer des lectures et \u00e9critures physiques afin de r\u00e9ellement constater l\u2019impact de TDE sur les performances.<\/p>\n<p>Avec TDE, j&#8217;ai r\u00e9alis\u00e9 un premier tir avec une g\u00e9n\u00e9ration de 1000 requ\u00eates sur 10 threads diff\u00e9rents qui appellent la base en m\u00eame temps. La capture d&#8217;\u00e9cran qui suit montre le r\u00e9sultats de pgbench en terme\u00a0 de temps de r\u00e9ponse de la simulation : <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9007\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/1-300x84.png\" alt=\"\" width=\"664\" height=\"186\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/1-300x84.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/1.png 715w\" sizes=\"auto, (max-width: 664px) 100vw, 664px\" \/><\/a><\/p>\n<p>Pendant que la simulation \u00e9tait en cours, nous surveillons \u00e9galement l&#8217;activit\u00e9 du serveur gr\u00e2ce \u00e0 une commande VMstat afin de voir en temps r\u00e9el la consommation de pgbench en terme de ressources: <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9008\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/2-300x83.png\" alt=\"\" width=\"665\" height=\"184\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/2-300x83.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/2.png 654w\" sizes=\"auto, (max-width: 665px) 100vw, 665px\" \/><\/a><\/p>\n<p>Afin de pouvoir faire une moyenne de temps d&#8217;ex\u00e9cution, nous avons lanc\u00e9 une deuxi\u00e8me fois pgbench mais cette fois-ci sur un plus gros \u00e9chantillon de requ\u00eates afin de mettre encore plus la base de donn\u00e9es sous tension. Il s&#8217;agissait ici d&#8217;un test de 10.000 requ\u00eates sur 10 threads (100.000 requ\u00eates)\u00a0 : <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/3.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9009\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/3-300x68.png\" alt=\"\" width=\"662\" height=\"150\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/3-300x68.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/3-768x175.png 768w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/3.png 858w\" sizes=\"auto, (max-width: 662px) 100vw, 662px\" \/><\/a><\/p>\n<p>Et comme pour l&#8217;essai pr\u00e9c\u00e9dent, nous surveillons l&#8217;\u00e9tat du serveur avec VMStat pour constater les pics d&#8217;utilisation de pgbench sur les ressources syst\u00e8me : <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/4.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9010\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/4-300x190.png\" alt=\"\" width=\"662\" height=\"419\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/4-300x190.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/4.png 668w\" sizes=\"auto, (max-width: 662px) 100vw, 662px\" \/><\/a><\/p>\n<p>Deuxi\u00e8me \u00e9tape de notre test, afin de comparer les r\u00e9sultats obtenus, nous avons r\u00e9aliser les m\u00eames test avec la m\u00eame volum\u00e9trie sur une installation classique de postgresql. <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/5.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9011\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/5-300x10.png\" alt=\"\" width=\"660\" height=\"22\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/5-300x10.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/5-768x24.png 768w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/5.png 817w\" sizes=\"auto, (max-width: 660px) 100vw, 660px\" \/><\/a><\/p>\n<p>Pour le plus petit \u00e9chantillon de requ\u00eates (les 1000 sur 10 threads) voici les temps de r\u00e9ponses que nous avons obtenus avec pgbench : <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/6.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9012\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/6-300x106.png\" alt=\"\" width=\"662\" height=\"234\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/6-300x106.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/6.png 547w\" sizes=\"auto, (max-width: 662px) 100vw, 662px\" \/><\/a><\/p>\n<p>Pareillement que pour la premi\u00e8re partie du test, nous avons v\u00e9rifier les consommations syst\u00e8me de pgbench : <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/7.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9013\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/7-300x121.png\" alt=\"\" width=\"662\" height=\"267\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/7-300x121.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/7.png 647w\" sizes=\"auto, (max-width: 662px) 100vw, 662px\" \/><\/a><\/p>\n<p>Et enfin, comme pour la premi\u00e8re partie, nous avons r\u00e9aliser le m\u00eame test avec une volum\u00e9tries de requ\u00eates envoy\u00e9es sup\u00e9rieures (100.000 requ\u00eates) : <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/8.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9014\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/8-300x108.png\" alt=\"\" width=\"664\" height=\"239\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/8-300x108.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/8.png 549w\" sizes=\"auto, (max-width: 664px) 100vw, 664px\" \/><\/a><\/p>\n<p>Et le r\u00e9sultat sur vmstat : <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/10.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9015\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/10-300x278.png\" alt=\"\" width=\"662\" height=\"613\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/10-300x278.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/10.png 646w\" sizes=\"auto, (max-width: 662px) 100vw, 662px\" \/><\/a><\/p>\n<p>On constate donc qu\u2019il y a bien une diff\u00e9rence, surtout sur les traitements d\u2019un grand nombre de donn\u00e9es. L\u2019instance vanilla a une moyenne de traitement \u00e0 169 ms tandis que l\u2019instance TDE a une moyenne \u00e0 207 ms. Ce qui repr\u00e9sente quand m\u00eame une augmentation du temps de traitement de l\u2019ordre de 22%. <em>(vous pouvez cliquer pour agrandir)<\/em><\/p>\n<p><a href=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/EXCEL_rOHJTItEP1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-9016\" src=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/EXCEL_rOHJTItEP1-300x181.png\" alt=\"\" width=\"663\" height=\"400\" srcset=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/EXCEL_rOHJTItEP1-300x181.png 300w, https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/04\/EXCEL_rOHJTItEP1.png 481w\" sizes=\"auto, (max-width: 663px) 100vw, 663px\" \/><\/a><\/p>\n<h1>Conclusion\u00a0:<\/h1>\n<p>TDE est un outil pratique pour l\u2019encryption des donn\u00e9es. Il est relativement facile \u00e0 configurer, pour peu que vous acceptiez d\u2019utiliser une version fournie par Cybertech que vous devez compiler vous-m\u00eame.<\/p>\n<p>Il faut cependant garder en m\u00e9moire que sur des traitements cons\u00e9quents, les performances peuvent \u00eatre impact\u00e9 de l\u2019ordre de 30%.<\/p>\n<a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F8999&#038;text=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F8999&#038;title=Transparent%20Data%20Encryption%20pour%20PostgreSQL\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=Transparent%20Data%20Encryption%20pour%20PostgreSQL&#038;body=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20:%20https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F8999\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" \/><\/a>","protected":false},"excerpt":{"rendered":"<p>TDE ou Transparent Data Encryption est un produit mis \u00e0 disposition par Cybertec. Il permet de faire de l&#8217;encryption de donn\u00e9es de mani\u00e8re automatique dans Postgres. Il crypte les donn\u00e9es des tables, des indexes, les tablespaces, les fichiers temporaires, et&hellip; <a href=\"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/\" class=\"more-link\">Continuer la lecture <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":41,"featured_media":9067,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[266],"tags":[394,401],"class_list":["post-8999","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-tde","tag-encryption"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Transparent Data Encryption pour PostgreSQL - Capdata TECH BLOG<\/title>\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\/transparent-data-encryption-pour-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Transparent Data Encryption pour PostgreSQL - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"TDE ou Transparent Data Encryption est un produit mis \u00e0 disposition par Cybertec. Il permet de faire de l&#8217;encryption de donn\u00e9es de mani\u00e8re automatique dans Postgres. Il crypte les donn\u00e9es des tables, des indexes, les tablespaces, les fichiers temporaires, et&hellip; Continuer la lecture &rarr;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2022-05-13T07:59:15+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/05\/safe-913452_960_720.webp\" \/>\n\t<meta property=\"og:image:width\" content=\"828\" \/>\n\t<meta property=\"og:image:height\" content=\"720\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/webp\" \/>\n<meta name=\"author\" content=\"Sarah FAVEERE\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sarah FAVEERE\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 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\/transparent-data-encryption-pour-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/\"},\"author\":{\"name\":\"Sarah FAVEERE\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/686f2452f7ec79115d31e41c230a9da2\"},\"headline\":\"Transparent Data Encryption pour PostgreSQL\",\"datePublished\":\"2022-05-13T07:59:15+00:00\",\"dateModified\":\"2022-05-13T07:59:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/\"},\"wordCount\":1214,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"#tde\",\"encryption\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/\",\"name\":\"Transparent Data Encryption pour PostgreSQL - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2022-05-13T07:59:15+00:00\",\"dateModified\":\"2022-05-13T07:59:15+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Transparent Data Encryption pour PostgreSQL\"}]},{\"@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\/686f2452f7ec79115d31e41c230a9da2\",\"name\":\"Sarah FAVEERE\",\"sameAs\":[\"http:\/\/blog.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/sfaveere\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Transparent Data Encryption pour PostgreSQL - Capdata TECH BLOG","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\/transparent-data-encryption-pour-postgresql\/","og_locale":"fr_FR","og_type":"article","og_title":"Transparent Data Encryption pour PostgreSQL - Capdata TECH BLOG","og_description":"TDE ou Transparent Data Encryption est un produit mis \u00e0 disposition par Cybertec. Il permet de faire de l&#8217;encryption de donn\u00e9es de mani\u00e8re automatique dans Postgres. Il crypte les donn\u00e9es des tables, des indexes, les tablespaces, les fichiers temporaires, et&hellip; Continuer la lecture &rarr;","og_url":"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2022-05-13T07:59:15+00:00","og_image":[{"width":828,"height":720,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/05\/safe-913452_960_720.webp","type":"image\/webp"}],"author":"Sarah FAVEERE","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Sarah FAVEERE","Dur\u00e9e de lecture estim\u00e9e":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/"},"author":{"name":"Sarah FAVEERE","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/686f2452f7ec79115d31e41c230a9da2"},"headline":"Transparent Data Encryption pour PostgreSQL","datePublished":"2022-05-13T07:59:15+00:00","dateModified":"2022-05-13T07:59:15+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/"},"wordCount":1214,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["#tde","encryption"],"articleSection":["PostgreSQL"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/","url":"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/","name":"Transparent Data Encryption pour PostgreSQL - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2022-05-13T07:59:15+00:00","dateModified":"2022-05-13T07:59:15+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/transparent-data-encryption-pour-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Transparent Data Encryption pour PostgreSQL"}]},{"@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\/686f2452f7ec79115d31e41c230a9da2","name":"Sarah FAVEERE","sameAs":["http:\/\/blog.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/sfaveere\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/8999","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\/41"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=8999"}],"version-history":[{"count":13,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/8999\/revisions"}],"predecessor-version":[{"id":9063,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/8999\/revisions\/9063"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/9067"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=8999"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=8999"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=8999"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}