Aller au contenu

Extension pgcrypto

Objet

Ce document présente l’extension pgcrypto.

Elle permet de stocker des données chiffrées au sein de champs de tables PostgreSQL.

Il ne s’agit pas de protéger les fichiers de la base sur le disque (ce qui devrait plutôt être fait au niveau du système d’exploitation) ou ses sauvegardes (chiffrement lors de leur création).

Les données restent cryptées si on ne possède pas la clé. Il faut la fournir au sein d’une requête, donc le superutilisateur pourra toujours se débrouiller pour y avoir accès.

Installation

pgcrypto fait partie des contrib fournis avec les sources de postgresql et inclus dans les paquets des distributions comme postgresql-contrib-9.6 ou postgresql96-contrib-9.6, en général toujours installés.

Il suffit donc d’ajouter l’extension dans la base de données voulue :

$ psql -h localhost -U postgres -c "CREATE EXTENSION pgcrypto;" mabase

Principe de fonctionnement

pgcrypto utilise des fonctions de chiffrement et de déchiffrement des informations. Comme il existe plusieurs algorithmes utilisables, chaque couple de fonction (chiffrer/déchiffrer) porte un nom particulier. Se reporter à la documentation officielle ( http://docs.postgresqlfr.org/current/pgcrypto.html) pour une liste exhaustive.

Les données chiffrées seront stockées dans des champs de type bytea (type de donnée binaire).

Ci-après, nous allons utiliser une table test sur la base mabase:

$ psql -h localhost -U postgres base

mabase=# create table test (encrypted bytea);
CREATE TABLE

Pour l’exemple, nous allons utiliser les fonctions de chiffrement de type PGP, avec un (trop) simple mot de passe. L’utilisation avec des clés PGP complètes sera traitée plus bas.

On chiffre le texte « mon secret » avec la clé « motdepasse » :

mabase=# insert into test(encrypted)
        values (pgp_sym_encrypt('mon secret','motdepasse'));

INSERT 0 1

Pour déchiffrer les données, il faudra spécifier à nouveau le mot de passe :

mabase=# select pgp_sym_decrypt(encrypted,'motdepasse') from test;
pgp_sym_decrypt
-----------------
mon secret
(1 ligne)

Toute autre tentative avec un mot de passe différent échoue :

mabase=# select pgp_sym_decrypt(encrypted,'pirate') from test;
ERROR:  Wrong key or corrupt data

Les données sont bien stockées sous forme binaire et chiffrées :

mabase=# select * from test;
encrypted
-----------------------------------------------
\303\015\004\007\003\002Di\234\225\177F\356\...
(1 ligne)

La chaîne binaire chiffrée ci-dessus a été raccourcie pour un affichage correct dans la documentation présente. Elle est en effet assez longue:

mabase=# select length(encrypted) from test;
length
--------
      76
(1 ligne)

Chaque ligne peut être chiffrée avec un mot de passe différent :

mabase=# drop table test;
DROP TABLE

mabase=# create table test (id serial primary key, encrypted bytea);
NOTICE:  CREATE TABLE will create implicit sequence
"test_id_seq" for serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit
index "test_pkey" for table "test"
CREATE TABLE

mabase=# insert into test (encrypted)
          values (pgp_sym_encrypt('mon secret','motdepasse'));
INSERT 0 1

mabase=# insert into test (encrypted)
          values (pgp_sym_encrypt('mon secret','motdepasse2'));
INSERT 0 1

mabase=# insert into test (encrypted)
        values (pgp_sym_encrypt('mon secret','motdepasse3'));
INSERT 0 1

On doit alors spécifier le bon mot de passe pour déchiffrer chaque ligne séparément :

mabase=# select pgp_sym_decrypt(encrypted,'motdepasse2')
          from test where id=2;

pgp_sym_decrypt
-----------------
mon secret
(1 ligne)

En effet ici, une requête avec un mot de passe unique ne fonctionne pas:

mabase=# select pgp_sym_decrypt(encrypted,'motdepasse2') from test;
ERROR:  Wrong key or corrupt data

Cela n’est possible que si le mot de passe est identique pour tous :

mabase=# update test set encrypted =
          (pgp_sym_encrypt('mon secret','motdepasse'))
          where id in (2,3);
UPDATE 2

mabase=# select pgp_sym_decrypt(encrypted,'motdepasse') from test;
pgp_sym_decrypt
-----------------
mon secret
mon secret
mon secret
(3 lignes)

Chiffrement asymétrique avec PGP

Certaines données sensibles ne doivent être lues que par les utilisateurs autorisés. Ces informations peuvent être par exemple des numéros de cartes de crédit, des numéros de sécurité sociale ou des numéros de comptes bancaires, etc.

Un des modes de chiffrement les plus efficaces et faciles à utiliser est fourni dans le module pgcrypto avec des fonctions de chiffrement PGP. Il existe deux types de chiffrement PGP, que vous pouvez utiliser.

  • symétrique : les clés pour chiffrer et lire sont les mêmes, comme dans l’exemple ci-dessus.
  • asymétrique : seule la clé publique peut chiffrer les données et seule la clé privée peut les lire.

Dans le cas d’une base de données contenant des informations sensibles, il est toujours possible que la base de données tombe entre de mauvaises mains. Dans ce cas, il est probablement préférable d’utiliser l’approche clé publique / clé privée.

Cela garantit que vous pouvez chiffrer les données avec une clé publique que vous stockez dans la base de données, voire même dans un trigger ou directement au niveau de l’application. Mais les utilisateurs qui ont besoin de lire cette information sécurisée auront eux besoin de la clé privée de déchiffrement. Ainsi, une personne peut toujours voler la base de données, même avec la clé publique, elle ne pourra pas obtenir l’information.

Cela va sans dire : la clé privée doit rester secrète !

Fabrication de clés PGP

Avant de pouvoir utiliser le chiffrement PGP, nous avons besoin de créer les clés.

Si vous êtes sur un système d’exploitation Unix vous avez déjà probablement l’outil en ligne de commande appelé gpg (dans les distributions le paquet se nomme gnupg2 en général). Vous pouvez le télécharger une version Windows à partir de http://www.gnupg.org/download/. Téléchargez-le et installez-le (par défaut dans C:\Program Files\GNU\GnuPG.). Vous pouvez aussi simplement extraire le dossier et le lancer depuis ce répertoire.

Les commandes qui suivent sont identiques sur Linux / Unix / Mac OSX ou Windows :

gpg --gen-key

Suivez les indications. Si vous n’avez pas besoin d’une sécurité accrue lors du déchiffrement, vous pouvez simplement taper sur la touche Entrée lorsqu’il vous demande la password phrase, c’est ce que nous avons fait ici.

gpg --list-secret-keys

Cette commande affiche la liste des clés disponibles dont celle qui vient d’être générée. Cela va ressembler à cela :

sec   1024R/98DD1B67 2012-04-26
uid                  PgCrypto (Asymetric encryption) <pgcrypto@domain.com>
ssb   1024R/7FE3D0AF 2012-04-26

Où 1024R est le nombre d’octet utilisé pour le chiffrement et 7FE3D0AF la clé privée et 98DD1B67 la clé publique.

Extraction de la clé publique nécessaire au chiffrement des données :

gpg -a --export 7FE3D0AF > public.key

Extraction de la clé privée nécessaire au déchiffrement des données pour leur lecture :

gpg -a --export-secret-keys 98DD1B67 > secret.key

D’une manière générale, l’option -a est une abréviation de armor, armure en français. Par défaut, la clé est constituée de données binaires, l’option -a la convertit en texte pour qu’elle puisse être utilisée dans les requêtes SQL et faire l’objet de copier/coller. Lorsque les clés seront utilisées, elles devront être transformées en binaire avec l’emploi de la fonction dearmor() de pgcrypto.

Chiffrement/Déchiffrement des données avec les clés PGP

Maintenant que nous avons nos clés publiques et privées, nous pouvons commencer à chiffrer des données. Voici les fonctions du module pgcrypto qui seront utilisées ici :

  • pgp_pub_encrypt : c’est la fonction utilisée pour chiffrer les données à l’aide d’une clé publique. Il y a aussi pgp_pub_encrypt_bytea pour le chiffrement des données binaires, pgp_sym_encrypt / pgp_sym_encrypt_bytea pour l’utilisation d’une clé symétrique.
  • pgp_pub_decrypt : c’est la fonction utilisée pour déchiffrer les données à l’aide de la clé privée. Il y aussi pgp_pub_decrypt_bytea pour déchiffrer les données binaires, pgp_sym_decrypt / pgp_sym_decrypt_bytea pour déchiffrer les données chiffrées symétriquement.
  • dearmor - utilisé pour reconvertir à leur format natif les clés générées avec l’option -a pour la passer à des fonctions de chiffrement/déchiffrement.
  • pgp_key_id : il est possible d’utiliser plusieurs clés pour chiffrer les données dans la base, notamment pour quelles soient accessibles à des personnes et pas par d’autres. Cette fonction vous indique la clé qui a été utilisé pour chiffrer un morceau de données de sorte que vous puissiez en déduire les clés a utiliser.

Il existe bien d’autre fonctions du module pgcrypto. Pour plus d’informations, consultez la documentation http://docs.postgresql.fr/current/pgcrypto.html.

Chiffrement des données

Nous créons une table pour le stockage des numéros de cartes de crédit et d’une autre pour le stockage de notre clé publique. Elle n’est pas secrète et cela évitera de la copier dans chaque ordre SQL :

CREATE TABLE cartedecredit(card_id SERIAL PRIMARY KEY, username varchar(100), cc bytea);

CREATE TABLE pgp_public_key(keyid text, pubkey bytea);
INSERT INTO pgp_public_key 
VALUES ('7FE3D0AF', dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v1.4.11 (GNU/Linux)

mQENBE+ZU1YBCADsZNW46rfz2ENaJWtsdzwg6CGU+ahYE9O/Z9GTXcuqaou/bL72
qBMeEbigTdxj9Q12vtshNkyAV5jXxAIMfsNIzlLG5bB+ky3uTZBr5CeKKlL4a8AK
pgPSrHL6Rbvu9fia+KwsKE06++xR3v2KVTCPIiNHQE/13lJRtHlV7KFzPqsPExh8
eG9CrQKAvjxNc5wsL/dPye2EylI37WoHkFuCBTVZn8UEiPlvFHf9OVocYD/viRtA
AjEDdiNMR3eeauc568Fnb8VDnKoVA31yZT6LjwaVyGSTnK9/SP5dDIjlPRbFq3An
NsJRPAAYDMVr5MkdQQn299D6Mj7jXt6xuZnrABEBAAG0NVBnQ3J5cHRvIChBc3lt
ZXRyaWMgZW5jcnlwdGlvbikgPHBnY3J5cHRvQGRvbWFpbi5jb20+iQE+BBMBAgAo
BQJPmVNWAhsDBQkSzAMABgsJCAcDAgYVCAIJCgsEFgIDAQIeAQIXgAAKCRBK5aQP
9iu7Rk6nCAC+tlyoHHy6PIlJ5iaxox1tKfOE4Le/UbOCuvsj4YA5EB32sOVqeFMG
ptDVmF3FNFLs3HMc4zruIAJMNwEeAUpD8yCFf5jyrlnk9NZc/3u7inhvEOlDlrLM
k9Je8BSeTSzOPOGDxDpYVgQgndQ5asKrdP5jWKHQ82YL+D6bp+YXP+FVs0hkBxci
geX/yo33m85YrP1ktnNRuXxVX4TSjnRbOJvuvrfp4J3FuL52Fxzx4etgjE2wm2/W
NGhPeg3Alfs3QI15eBZ0Q6Ue2IVzabQ97B7pltgYCbwlLbZLevUdbF2YhzhzVqbA
/m+5Gh0aEXDQ5BSN6bkN22nTEWmYhsG1uQENBE+ZU1YBCAC/eEJ0hfpD1VsTRN7/
oNrXQ4AYCJcVgZwy/OeFtFQiygx4aK7PIrvZDf1Kc7zg9KRHNXL2cJeZmxZAKpk/
bQf6ljBzl0r0Z5gCrk68IddSpkXyUZzPu8fwBicR3uPmrY8iKWwZ+VKyyvnxnvyW
CKxLkgR49Tbzt0Csa5X2hervRYBw1kyR1rCJETrRz3BFCdW3J4YkasA9hRWh6Amb
/AQkFClWRVPXupEobGBMZYDWZwbfiJ6+rFt30zJjfEPDvSpNqsfbnuEKY5Uda8Gw
DE/K656OaqwRXb7OxpdpwlWQKWkpVA512bHnRHCMVUD5r+SrBnMu7vft4qQ1zBkG
nvZ3ABEBAAGJASUEGAECAA8FAk+ZU1YCGwwFCRLMAwAACgkQSuWkD/Yru0byKAgA
ltZxezOfMwMRP71GOYEqr0fQFPRREexO4UGleHJBGu/srjmN3cRxhLEpRauP9fiC
6A7VOD+D1z3IoM1pJGDB3cGiA1beUnfnRGir3c7/b76UXQRcub1yB9mSuo93Uu3h
WVUX/hHcopiEY+ZahS7O8UJdgRnJPBV9ubv9e2KN0WCs4y466AQN6PpkxncT9Clz
+rdPxXHv7PfHm/yW4WNTzjzuMQnBrwf9fWvcrT33+8j0uTFr3+WC6UtBPBwY3vPU
5tnWEglQYq0+8xlQTwUHPZFS+iB3rD5kVGCAc7xSCE334JlOGXUSpMsN+HITuB4+
J5/xYqZL6ODTgQ/csZ9iOg==
=m525
-----END PGP PUBLIC KEY BLOCK-----'));

Ajout d’informations chiffrées par la clé publique, ici on insère un numéro de carte bancaire :

INSERT INTO cartedecredit (username, cc) 
VALUES ('gilles', pgp_pub_encrypt('123456123456789', (SELECT pubkey FROM pgp_public_key WHERE keyid='7FE3D0AF')));

Lecture des informations sans la clé privée :

SELECT username, cc FROM cartedecredit;

Nous voyons les données cryptées dans la colonne de cc :

 username |              cc
----------+---------------------------------------------------------------------
 gilles   | \xc1c04c034cb4ed2621bdd7410107ff6d746f8bcb7a599f9cc90601679a7dc53972
          | b94aecba3fe0d14b8bda393add1c6d0e6c9c998499b3625a18029e7a4e89d82e5a13c4dd474809cf
          | d5f7b3375f25799ee5ec3db5fba0970ba32f492228478552ffc6741bd3bf001848453b0b4b877862
          | 152f21a3d45970dfe09110a2b1ad45a186fd28ddfbdf51eb0ea3ead4ecf71733f1367a1370c35dd7
          | 2307fe38d5a59609f7ae1bff8ec3edd79d9bfbcbba10ff785fad2fad8226de06c1f032a2f5140390
          | daf679fa46c80f258d7f08d40647e56f9f857539087a2cb5874c2e44f27e7be87eab8d008e05c0a1
          | edfafbc8d75111ad5efc4f0a1821cb822b63b09b71c2d9ddb50b91e4a71a37e7758e47f1e77bd240
          | 01825e00b63dca3cb9dfbdee2bce10ce42d90589d217cd8d6db28acc7c2920c1f6c2ed518af1fa6a
          | 2fc2771af1c5f4b9520093ea82558c7aacd952f341407cbf
(1 ligne)

Informations sur les clés de chiffrement

Maintenant, nous pouvons utiliser pgp_keyid pour vérifier la clé publique utilisée pour déchiffrer nos données.

SELECT pgp_key_id((SELECT pubkey FROM pgp_public_key WHERE keyid='7FE3D0AF'));

Ce qui donne par exemple : 

    pgp_key_id
------------------
 01568B2E7FE3D0AF

Les huit derniers caractères correspondant à notre clé publique.

Pour vérifier que les données ont bien été chiffrées par notre clé publique :

SELECT username, pgp_key_id(cc) AS keyweused FROM cartedecredit;

 username |    keyweused
----------+------------------
 gilles   | 01568B2E7FE3D0AF

Déchiffrement des données

Pour déchiffrer les données, il suffit d’utiliser maintenant la clé privée correspondant à la clé publique utilisée pour le chiffrement. La clé privée, elle, ne doit pas être stockée dans la base, il faut donc la fournir dans le SQL :

SELECT username, pgp_pub_decrypt(cc, (SELECT dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v1.4.11 (GNU/Linux)

lQHYBE+ZarsBBACd93EPaQeqGYmiOkiCOd6yRoZoeTCWYKcHkHVj5WactaCcI6C7
rKII+NoOWPo28mef3aecJlW4cPofY0S3f1XRnqAt+M8RAnIMp8xYDcvGDVXCgPVf
SjURsqVss4yja42FqbxxUvD5ncpTODOu9VUIR1H5ToCjCjxea/+u3JSBCQARAQAB
AAP+I91bsot+nXyZ1pbUApkf+irv9kVZpjLAiYOfW3RohTjpg7nFEwchbNcPLwW9
nX8QzueU1+uON8eSylCP9/d1pLxFs+qVyWrMnWdzmj6Nr8FLO4rKEKkwyEfWNlg7
PDsbc8uU/kK8U+ecZdwmXzWvG+uK/aLwyzGb7oIsYusplrcCAMgXlkC86IE7+C2E
mmqcxQI0b/Zvgnf7wQZHk5fXlfgu+PbuXlQsKwBwInW0WDY1j/iNse868OxATCr1
V51iNY8CAMoapnVqbYVHl0+e4U8fK5drQS5WWieCa03t28AJy7sRSL9IO0bHPTnT
UmljrQLIw6DGeln1B0QH9TXeHTSSg+cB/ixaMD9BL3GNEO/5DIiPGK1OaoL/PAij
bd8xNiSgoLHC/n25uRWY4rpNxsBCFJuwr1pfaEh5HwqAhwKhSoglKTGgdbQ1UGdD
cnlwdG8gKEFzeW1ldHJpYyBlbmNyeXB0aW9uKSA8cGdjcnlwdG9AZG9tYWluLmNv
bT6IuAQTAQIAIgUCT5lquwIbAwYLCQgHAwIGFQgCCQoLBBYCAwECHgECF4AACgkQ
8vTaAJjdG2dv4QP/UmzEwe86F6EMtq1bsttMxFRkeTpuzL5iYlav+bNZ8c9bjeNd
X+x6+FNUvrr3iI1ETBliiRzVdu1exV7M7bFJ/KWQQKrzg6STMu0qd4HMTQC1uOVG
L6dh1wXrKLgaCdxX2LnL603WFpefUBiHUcB92uwN64pKzI6Z1NdQLPHr2b+dAdgE
T5lquwEEALszt4JPuBqi6Z3rYYiTivGsDEMlQYGP0zevuboH90wuzLgxKu1dYzGj
jPpmhcQeXZPhHsCj1y+mLYoQQXB8UEK3KeA2VEdJ9QlACoeYDztfxlGpPfnbZMu/
1+yi6G1oViErZjyzLEbd/5ebalUgn8mTkift+U3ZkFGUmH09b7ONABEBAAEAA/0f
JYOq0si25dQoyjj4GFRN0WpY4aH8hImIAlzMbvbeXgsBumyCAb+3SpbyMoyfDM5V
BGexdZEgBG4fQoUgyg4wQqCNssoBOXrlAcm7pAxhdTj3XE4tgjoMmHnFvgxv7KIt
pPdhwxlWhLeJBg4Kmc2xbeItmDMZ9a1aJzZzan6/oQIA2uZfcY9vd+Wi/3xcil+L
o++3VgdgeNg5kXZjrf1GfMRHcycu2v4luoXQfNhwQa8aKMPKTURaCdxvRu8CfqN4
xwIA2u4JbIABn3CJ1AY/A7szu/gdfPei9E7+TollFkytwCI7OAjNvT+/JEXN8AMi
etMfFjO1GLVG/2zkQyE/XyVlCwH/RZoZxlZwGCW5aLF+am6CUaIwtv3BhR4/NMdW
O10vWJB3QSyRAhQfEPylm61t3cYnln0GYdcKU55r9X5bfC5qdZd5iJ8EGAECAAkF
Ak+ZarsCGwwACgkQ8vTaAJjdG2dwOAQAh/gmo9AaUDLJ6R1/UiWa25lygJMhK2lS
02HhCYjD5k8MyQIiPhrXWsnvcGZSp2PMpBwfIxDKcC6+vPgn8gM6EkjSE94Ec4sR
e9fddHj6cIX3PVa3/Jo6SAjrzzfiXVesYnVyrkYA5lN3hCQ9yrE4eA1Fd4nzxwhL
D3CZIvHfd8M=
=wkn6
-----END PGP PRIVATE KEY BLOCK-----'))) FROM cartedecredit;

et le résultat obtenu est alors beaucoup plus lisible pour le détenteur de la clé privé :

 username | pgp_pub_decrypt
----------+-----------------
 gilles   | 123456123456789

Indexation des champs chiffrés

Le problème posé par les champs chiffrés survient lorsque l’on veut faire une recherche sur ce type de champ :

CREATE TABLE
test_pgcrypto=# INSERT INTO test SELECT pgp_sym_encrypt('test value ' || x.id, 'motdepasse') FROM generate_series(1,100000) AS x(id);
INSERT 0 100000
test_pgcrypto=# SELECT pgp_sym_decrypt(encrypted, 'motdepasse') FROM test WHERE pgp_sym_decrypt(encrypted, 'motdepasse')='test value 32';
 pgp_sym_decrypt 
-----------------
 test value 32
(1 ligne)

test_pgcrypto=# EXPLAIN ANALYZE SELECT pgp_sym_decrypt(encrypted, 'motdepasse') FROM test WHERE pgp_sym_decrypt(encrypted, 'motdepasse')='test value 32';
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..2890.25 rows=500 width=82) (actual time=81666.407..146498.976 rows=1 loops=1)
   Filter: (pgp_sym_decrypt(encrypted, 'motdepasse'::text) = 'test value 32'::text)
   Rows Removed by Filter: 99999
 Total runtime: 146499.004 ms

La requête met 146 secondes pour retourner le résultat alors qu’il n’y a que 100 000 enregistrements.

Pour accélérer cette recherche, on peut imaginer d’indexer les champs chiffrés, et c’est techniquement parfaitement possible. Par exemple :

CREATE INDEX
test_pgcrypto=# EXPLAIN ANALYZE SELECT pgp_sym_decrypt(encrypted, 'motdepasse') FROM test WHERE pgp_sym_decrypt(encrypted, 'motdepasse')='test value 32';
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_encrypted_idx on test  (cost=0.00..8.45 rows=1 width=82) (actual time=1.044..1.046 rows=1 loops=1)
   Index Cond: (pgp_sym_decrypt(encrypted, 'motdepasse'::text) = 'test value 32'::text)
 Total runtime: 1.063 ms

On voit qu’ici la requête utilise l’index ce qui nous permet d’avoir le résultat en 1 milliseconde environ, le gain en terme de performance est donc énorme.

De même pour le chiffrement asymétrique :

test_pgcrypto=# TRUNCATE cartedecredit;
TRUNCATE TABLE
test_pgcrypto=# INSERT INTO cartedecredit SELECT x.id,'Nom'||x.id,pgp_pub_encrypt('123456-'||x.id, (SELECT pubkey FROM pgp_public_key WHERE keyid='46CAA552')) FROM generate_series(1,100000) AS x(id);
INSERT 0 100000
test_pgcrypto=# EXPLAIN ANALYZE SELECT username,cc FROM cartedecredit WHERE pgp_pub_decrypt(cc, (SELECT dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v1.4.11 (GNU/Linux)

lQHYBFBkJUcBBAC0pCcEl8uULu736J5vXAb5MVTc1fQiO4xu7hSsEHujaOt3vwq5
LVDr8hfNdt0xAi/+egwCFc8ZArf5JKTESgqhaqh+KWe0+HUXK4kohB9LXyDs8xYw
8T6qFQWSQ2Hmt3MFFG7JlVrOKzVa9s3UsinojmIZAZKMl5t+2Fy0xT/Z9wARAQAB
AAP7BhytlUrtjBgwQhph6rKr4+nfZHl4xwQbVcWId7WUJ+nVnznLM9GD+rHyqVHI
[...]
UwCIAw43Y1KPtnTajzmRL+hDmSOYthOgUYilBBgBAgAPBQJQZCVHAhsMBQkSzAMA
AAoJEBiLdlUQS1fc/BUD/1olpo3KuwElCtc1kO9WHDxDYTYXzB0ZlHVa8zXnXcz+
8brpJIBy1/+4kec/MMkUrgrFIHRBzpuguTX47DRxZO+Ayxo6+JQgY5ajDmHfyUA9
nDER0afeQFBxB35oP5udSI01qVNtFp+Aed/gVBCqkAkL4o+wgdobnCPF6fMj8Pqa
=T+W3
-----END PGP PRIVATE KEY BLOCK-----')))='123456-32';
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Seq Scan on cartedecredit  (cost=0.01..4716.01 rows=500 width=216) (actual time=1677022.090..2049419.118 rows=1 loops=1)
   Filter: (pgp_pub_decrypt(cc, $0) = '123456-32'::text)
   Rows Removed by Filter: 99999
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
 Total runtime: 2049419.148 ms

test_pgcrypto=# CREATE INDEX cartedecredit_cc_idx ON cartedecredit(pgp_pub_decrypt(cc, '\x9501d8045064254[...]da1b9c23c5e9f323f0fa9a'));
CREATE INDEX
test_pgcrypto=# EXPLAIN ANALYZE SELECT username,cc FROM cartedecredit WHERE pgp_pub_decrypt(cc, '\x9501d8045064254[...]da1b9c23c5e9f323f0fa9a'::bytea)='123456-32';
                                                     QUERY PLAN                                                                                                      
----------------------------------------------------------------------------------------------------------------------------
 Index Scan using cartedecredit_cc_idx on cartedecredit  (cost=0.00..8.42 rows=1 width=216) (actual time=0.084..0.085 rows=1
 loops=1)
   Index Cond: (pgp_pub_decrypt(cc, '\x9501d8045064254[...]da1b9c23c5e9f323f0fa9a'::bytea) = '123456-32'::text)
 Total runtime: 0.108 ms
(3 lignes)

La recherche ne prend qu’un dixième de milliseconde ce qui est extrêmement rapide. On voit ici tout le gain de performance qu’il est possible d’avoir avec un index.

Cependant, en créant ces index, la sécurité des données est dévoilée à tout le monde, y compris aux détenteurs d’un dump ou de personnes ayant accès à la base sans droit de superutilisateur :

 tablename |     indexname      |                                               indexdef         
------------+-----------+--------------------+------------+---------------------------------------------------------------------------
 test      | test_encrypted_idx | CREATE INDEX test_encrypted_idx ON test USING btree (pgp_sym_decrypt(encrypted, 'motdepasse'::text))

Le mot de passe est en clair et tout le monde a la possibilité de le voir. Le même problème se pose pour le chiffrement asymétrique : la clé de déchiffrement est offerte à la vue de tous.

De plus si l’attaquant a accès aux fichiers de la base de données il pourra voir les valeurs déchiffrées en clair dans les fichiers d’index !

On pourrait aussi avoir l’idée d’indexer le champ sans le déchiffrer et de faire la requête en chiffrant la chaîne recherchée, par exemple :

test_pgcrypto=# CREATE INDEX test_encrypted_idx ON test(encrypted);
CREATE INDEX
test_pgcrypto=# EXPLAIN ANALYZE SELECT * FROM test WHERE encrypted=pgp_sym_encrypt('test value 32','motdepasse')::bytea;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..2889.00 rows=1 width=82) (actual time=142368.445..142368.445 rows=0 loops=1)
   Filter: (encrypted = pgp_sym_encrypt('test value 32'::text, 'motdepasse'::text))
   Rows Removed by Filter: 100000
 Total runtime: 142368.469 ms

Ceci permettrait de ne pas exposer le mot de passe ou la clé de chiffrement dans la définition de l’index. Malheureusement, les fonctions de chiffrement comme pgp_sym_encrypt() sont volatiles. À paramètres identiques, elles ne ramènent jamais la même valeur. L’indexation est donc impossible de cette manière.

Le seul moyen de créer des index directement sur les champs chiffrés et de faire la requête en chiffrant la chaîne recherchée revient à utiliser des fonctions de chiffrage/déchiffrage immuables (ces derniers renvoient toujours la même chose à paramètres égaux). Ceci est uniquement possible avec les fonctions crypt et decrypt de chiffrement brut ou raw.

test_pgcrypto=# DROP INDEX cartedecredit_cc_idx;
test_pgcrypto=# TRUNCATE cartedecredit;
test_pgcrypto=# INSERT INTO cartedecredit select x.id, 'Nom'||x.id, encrypt(('12345-'||x.id)::bytea,'macle', 'bf') FROM generate_series(1,100000) AS x(id);
INSERT 0 100000
test_pgcrypto=# EXPLAIN ANALYZE SELECT card_id,username,encode(decrypt(cc, 'macle', 'bf'),'escape') cc FROM cartedecredit WHERE cc=encrypt('12345-32', 'macle', 'bf');
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Seq Scan on cartedecredit  (cost=0.00..1985.01 rows=1 width=28) (actual time=0.081..19.588 rows=1 loops=1)
   Filter: (cc = '\x0375f32c846d3e8b4dac21d62e7645c9'::bytea)
   Rows Removed by Filter: 99999
 Total runtime: 19.610 ms
(4 lignes)

test_pgcrypto=# CREATE INDEX cartedecredit_cc_idx ON cartedecredit(cc);
CREATE INDEX
test_pgcrypto=# SELECT card_id,username,encode(decrypt(cc, 'macle', 'bf'),'escape') cc FROM cartedecredit WHERE cc=encrypt('12345-32', 'macle', 'bf');
 card_id | username |    cc
---------+----------+-----------
      32 | Nom32    | 12345-32
(1 ligne)

test_pgcrypto=# EXPLAIN ANALYZE SELECT card_id,username,encode(decrypt(cc, 'macle', 'bf'),'escape') cc FROM cartedecredit WHERE cc=encrypt('12345-32', 'macle', 'bf');
                                                             QUERY PLAN                                                     

----------------------------------------------------------------------------------------------------------------------------
 Index Scan using cartedecredit_cc_idx on cartedecredit  (cost=0.00..8.45 rows=1 width=28) (actual time=0.084..0.086 rows=1 
loops=1)
   Index Cond: (cc = '\x0375f32c846d3e8b4dac21d62e7645c9'::bytea)
 Total runtime: 0.110 ms
(3 lignes)

Voici la liste des fonctions permettant de faire du chiffrement brut :

  • encrypt(data bytea, key bytea, type text) returns bytea
  • decrypt(data bytea, key bytea, type text) returns bytea
  • encrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea
  • decrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea

Ces fonctions exécutent directement un calcul des données ; elles n’ont pas de fonctionnalités avancées de chiffrement PGP et ont donc les inconvénients suivants :

  • Elles utilisent directement la clé de l’utilisateur comme clé de calcul.
  • Elles ne fournissent pas une vérification de l’intégrité pour savoir si les données chiffrées ont été modifiées.
  • Elles s’attendent à ce que les utilisateurs gèrent eux-même tous les paramètres du chiffrement.
  • Elles ne gèrent pas le texte.

En plus de ces contraintes, elles ne permettent que des comparaisons avec l’opérateur =.

Donc, avec l’introduction du chiffrement PGP, l’utilisation des fonctions de chiffrement brut n’est pas encouragée.

Note

Mais la question est peut-être : a-t-on besoin de chercher dans des données chiffrées directement et peut-on accéder à cette information à partir d’autres champs non secrets indexés ? Indexer le contenu d’un mot de passe par exemple n’a pas d’intérêt si la recherche se fait d’abord sur le nom d’utilisateur qui lui sera indexé.

Conclusion

pgcrypto permet de stocker des valeurs chiffrées dans PostgreSQL de manière assez aisée.

Cependant, il y a des limites à son utilisation pour assurer la confidentialité des données:

  • Il faut avoir toute confiance en son administrateur système et/ou DBA.
  • On doit utiliser une connexion à la base locale ou en SSL pour que les données ne circulent pas en clair.
  • L’indexation n’est pas possible, à moins d’utiliser des fonctions cryptographique de moindre qualité ou fonctionnalité.

Quant à la mise en place de pgcrypto au sein d’une application déjà existante, elle n’est pas très aisée dans la mesure où elle impose un changement de type de données dans la plupart des cas, étant donné que les valeurs stockées ne peuvent l’être que dans des chaînes binaires de type bytea.