Bonjour.
A la suite de la sortie de la deuxième version du fichier Pwned Passwords
https://www.troyhunt.com/ive-just-launc ... version-2/
Je me suis dis que j'allais me faire le service de vérification de mot de passe moi même sans passer le service https://haveibeenpwned.com
Ceci me permettrait donc faire des requête offline.
J'ai donc récupéré le fichier contenant le hash(sha1) d'une liste de mot de passe avec le nombre d'occurrence d'utilisation : https://haveibeenpwned.com/Passwords
Ce fichier fait 31,6Go décompressé.
Je l'ai intégré dans une base Postgres à l'aide de la commande copy (https://www.postgresql.org/docs/9.2/sta ... -copy.html)
Le caractère séparateur étant ':'
L'insertion semble avoir bien fonctionné, mais je n'en suis même pas sur vue la volumétrie.
J'en arrive à un blocage. les requêtes sont interminables.
Je suis débutant en optimisation de base de données et j'aurais besoin d'un coup de main sur le type de donnée à choisir pour la table, et surtout sur l'index à appliquer ou sur la façon de faire la requête.
la table à la structure suivante :
CREATE TABLE public.password
(
id text,
"number" bigint
)
WITH (
OIDS=FALSE
);
Je vous remercie par avance, en espérant que cette question vous intéressera.
Seb
intégration d'un très grand fichier dans Postgres
-
- Messages : 2
- Enregistré le : dim. 25 févr. 2018 18:31
-
- Administrateur du site
- Messages : 298
- Enregistré le : mar. 1 sept. 2015 00:38
- Localisation : France
- Contact :
Re: intégration d'un très grand fichier dans Postgres
Bonjour Seb,
Merci pour cette question et désolé pour l'attente, il y a une approbation à effectuer par l'admin pour les sujets postés lorsqu'on vient de s'inscrire et je n'avais pas vu le message hier. Sur du clé-valeur il y a d'autres solutions qu'une base PostgreSQL mais c'est tout à fait possible malgré tout.
Réponse 1 sur les types de données :
Pour le champ texte, text est OK. Pour le nombre d'occurrences du mot de passe, bigint est OK même si int suffirait probablement (2 milliards quand même)
Pas besoin de guillemets, password, id et number ne sont pas des mots réservés par la norme SQL ou PostgreSQL : https://www.postgresql.org/docs/10/stat ... endix.html
Réponse 2 sur COPY :
La commande COPY est une bonne solution pour charger un fichier plat dans une table. Cette commande renvoie une confirmation immédiate du nombre de lignes copiées sous la forme "COPY compte" donc il est possible de savoir si c'est effectivement OK.
Après le chargement il est possible de voir un échantillon de la table avec :
ou encore compter le nombre de lignes avec
Réponse 3 sur les SELECT :
Quelle est la syntaxe de la requête interminable ? Sur une table avec 2 champs, les requêtes ne pourront pas être très complexes. La clause where va intégrer un test sur id et/ou number. A priori plutôt sur ID qui est le mot de passe à tester ?
Si le test est une égalité simple (select * from password where id = ...) alors un index B-Tree ou un index Hash seront OK.
Le temps de réponse doit ensuite être de quelques ms au maximum pour tester sur un id particulier.
Merci pour cette question et désolé pour l'attente, il y a une approbation à effectuer par l'admin pour les sujets postés lorsqu'on vient de s'inscrire et je n'avais pas vu le message hier. Sur du clé-valeur il y a d'autres solutions qu'une base PostgreSQL mais c'est tout à fait possible malgré tout.
Réponse 1 sur les types de données :
Pour le champ texte, text est OK. Pour le nombre d'occurrences du mot de passe, bigint est OK même si int suffirait probablement (2 milliards quand même)
Code : Tout sélectionner
CREATE TABLE password
(
id text,
number bigint
)
;
Réponse 2 sur COPY :
La commande COPY est une bonne solution pour charger un fichier plat dans une table. Cette commande renvoie une confirmation immédiate du nombre de lignes copiées sous la forme "COPY compte" donc il est possible de savoir si c'est effectivement OK.
Après le chargement il est possible de voir un échantillon de la table avec :
Code : Tout sélectionner
select * from nom_table limit 10;
Code : Tout sélectionner
select count(*) from nom_table
Quelle est la syntaxe de la requête interminable ? Sur une table avec 2 champs, les requêtes ne pourront pas être très complexes. La clause where va intégrer un test sur id et/ou number. A priori plutôt sur ID qui est le mot de passe à tester ?
Si le test est une égalité simple (select * from password where id = ...) alors un index B-Tree ou un index Hash seront OK.
Code : Tout sélectionner
create index on password(id)
Cdlt. Phil - pgphil.ovh
-
- Administrateur du site
- Messages : 298
- Enregistré le : mar. 1 sept. 2015 00:38
- Localisation : France
- Contact :
Re: intégration d'un très grand fichier dans Postgres
Démo sur un simple pc en utilisant le fichier v2 disponible au lien fourni https://haveibeenpwned.com/Passwords
Le chargement du fichier et la création de l'index (ici avec la méthode hash) prennent moins d'une heure.
Ensuite un petit essai avec le mot de passe toto donne 15320...comme prévu il est à présent possible d'affirmer en moins de 1ms que toto n'est pas un très bon mot de passe.
Code : Tout sélectionner
select version();
version
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11devel (Ubuntu 11~~devel~20180226.2331-1~418.git364de25.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 ligne)
CREATE TABLE password
(
id text,
number bigint
)
;
CREATE TABLE
copy password from '/path/to/pwned-passwords-2.0.txt' with delimiter ':';
COPY 501636842
Durée : 975988,430 ms (16:15,988)
\d+
Liste des relations
Schéma | Nom | Type | Propriétaire | Taille | Description
--------+----------+-------+--------------+--------+-------------
public | password | table | postgres | 39 GB |
(1 ligne)
create index password_h1 on password using hash(id);
CREATE INDEX
Durée : 2171269,934 ms (36:11,270)
\di+
Liste des relations
Schéma | Nom | Type | Propriétaire | Table | Taille | Description
--------+-------------+-------+--------------+----------+--------+-------------
public | password_h1 | index | postgres | password | 16 GB |
(1 ligne)
create extension pgcrypto;
CREATE EXTENSION
explain select number from password where id = upper(encode(digest('toto', 'sha1'), 'hex'));
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using password_h1 on password (cost=0.00..8.02 rows=1 width=8)
Index Cond: (id = '0B9C2625DC21EF05F6AD4DDF47C5F203837AA32C'::text)
select number from password where id = upper(encode(digest('toto', 'sha1'), 'hex'));
number
--------
15320
(1 ligne)
Temps : 0,402 ms
Ensuite un petit essai avec le mot de passe toto donne 15320...comme prévu il est à présent possible d'affirmer en moins de 1ms que toto n'est pas un très bon mot de passe.
Cdlt. Phil - pgphil.ovh
-
- Administrateur du site
- Messages : 298
- Enregistré le : mar. 1 sept. 2015 00:38
- Localisation : France
- Contact :
Re: intégration d'un très grand fichier dans Postgres
Merci encore une fois pour cette question qui a indirectement permis d'améliorer la version 11 actuellement en développement : https://git.postgresql.org/gitweb/?p=po ... 877dc8ecb5
Cdlt. Phil - pgphil.ovh
-
- Messages : 2
- Enregistré le : dim. 25 févr. 2018 18:31
Re: intégration d'un très grand fichier dans Postgres
Bonjour.
Veuillez m'excuser pour la lecture tardive.
Merci pour votre réponse, je vais lire tout ça attentivement.
Veuillez m'excuser pour la lecture tardive.
Merci pour votre réponse, je vais lire tout ça attentivement.