Accueil > Divers (et d’été) > Informatique > Autour des produits Microsoft > Active directory > Charger le contenu de votre Active Directory dans une base Postgresql

Charger le contenu de votre Active Directory dans une base Postgresql

dimanche 13 décembre 2020, par Paul Courbis

Le langage SQL est plus courant que les requêtes LDIF et beaucoup plus puissant que les outils des clickodromes Microsoft.

Le script shell ci-dessous permet de charger l’intégralité d’un annuaire Active Directory dans une telle base de données.

  1. #!/bin/sh
  2.  
  3. # ImportActiveDirectory
  4. #
  5. # (c) 2020 Paul Courbis https://www.courbis.fr
  6. #
  7. # Pas de redistribution sans autorisation
  8.  
  9. # Ce script créé une table full_ad dans un serveur Postgres contenant
  10. # le contenu de l'Active Directory sous la forme de 4 champs :
  11. #
  12. # dn            DN de l'objet
  13. # codage        clear si la valeur est en clair
  14. #               base64 si la valeur est encodée en base64
  15. # key           nom de l'attribut
  16. # value         valeur de l'attribut
  17. #
  18. # Deux attributs fictifs sont rajoutés :
  19. #
  20. # father_node   DN de l'objet père (pour parcourir l'arbre)
  21. # self_node     DN de l'objet auquel le DN père est retiré
  22. #
  23. # Les données du précédent import (si elles existaient) sont conservées
  24. # dans la table full_ad_old ce qui permet des comparaisons par exemple
  25. # Au premier run, la table full_ad_old existera mais sera vide
  26.  
  27. # Dans un deuxieme temps une table ad_objects est crée dynamiquement
  28. #
  29. # La clef principale est object_dn qui contient le DN des objets
  30. # La table comporte autant de colonnes que d'attributs exitants
  31. # dans l'AD. Les champs sont mis à NULL si aucune valeur ne correspond
  32. # pour un DN
  33. #
  34. # Pour chaque champ on trouve en fait une ou deux colonnes :
  35. #
  36. # -une colonne xxxx qui contient la valeur telle que récupérée
  37. # -une colone multi_xxxx typée qui contient les données parsées
  38. #  sous forme multivaluée pour les champs qui le sont potentiellement
  39. #
  40. # Ainsi on va trouver un champ dn[1] pour chaque ligne qui contiendra
  41. # la première partie du DN de l'objet
  42.  
  43. # FIXME :
  44. # - reconnaître d'autres types que les entiers
  45. # - faire la distinction entre les vrais entiers et des entiers longs
  46. # - le type NUMERIC(20) est à améliorer
  47. # - décoder les données base64 subsistantes dans des blobs
  48. # - mettre les fichiers temporaires dans /tmp et les détruires en fin
  49. #
  50.  
  51. # Nécessite l'installation de :
  52. #
  53. # - gawk
  54. # - ldap-utils
  55. # - coreutils
  56. # - postgresql
  57.  
  58. . ./Common.sh
  59.  
  60. # Le fichier Common.sh doit contenir les déclaration de :
  61. # A défaut commenter la ligne ./Common.sh et renseigner et
  62. # décommenter les lignes ci dessous
  63.  
  64. # PATH          Path des commandes utilisées
  65. #export PATH="/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games:/opt/mssql-tools/bin/"
  66.  
  67. # LDAP_SRV      url du serveur LDAP ou LDAP-SSL ldap://xxxx.xxxx
  68. #export LDAP_SRV=ldaps://dc.acme.com
  69.  
  70. # ROOT_DN       DN de la racine de l'ad
  71. #export ROOT_DN="DC=acme,DC=local"
  72.  
  73. # LDAP_USER     DN de l'utilisateur habilité à requêter l'AD
  74. #export LDAP_USER="CN=Super admin,OU=Big Chiefs, DC=acme,DC=local"
  75.  
  76. # LDAP_PASS     Mot de passe de l'utilisateur habilité à requêter l'AD
  77. #export LDAP_PASS="Th4t1sMyP@ssw0rd!"
  78.  
  79. # PGDATABASE    Nom de la base Postgres (doit être créée en mode UTF-8)
  80. #export PGDATABASE="active_directory"
  81.  
  82. # PGHOST        Optionnel - Hôte du serveur Postgres
  83. #export PGHOST="127.0.0.1"
  84.  
  85. # PGPORT        Optionnel - Port de connexion
  86. #export PGPORT="5432"
  87.  
  88. # PGUSER        Optionnel - Utilisateur de connexion
  89. #export PGUSER="postgres"
  90.  
  91. # Le cas échéant mettre le mot de passe dans ~/.pgpass (voir man psql)
  92.  
  93.  
  94. # Raccourcis pour interroger la base
  95.  
  96. Postgres()
  97. {
  98.    echo "$@;" |
  99.           sed 's/; *;$/;/g' |
  100.           psql -A -t |
  101.           tr '|' '\t'
  102. }
  103.  
  104.  
  105. echo "Récuparation du ldif..."
  106.  
  107. ldapsearch -o ldif-wrap=no -LLL -H "$LDAP_SRV" -b "$ROOT_DN"  -D "$LDAP_USER" -w "$LDAP_PASS" 'objectclass=*' -E pr=2147483647/noprompt > full_ad.ldif
  108.  
  109. echo "Décodage..."
  110.  
  111. cat full_ad.ldif |
  112.     sed 's/#.*//g' |
  113.     grep -v '^$' |
  114.     sed 's/\(::*\) */\t\1\t/' |
  115.     awk -F '    ' '
  116.  
  117. BEGIN { DN=""; CONTENT="" }
  118.  
  119. # Decodage Base64
  120.  
  121. function decoder( valeur )
  122. {
  123.   cmd = "echo '"'"'"  valeur  "'"'"' | base64 -d"
  124.   cmd | getline result
  125.   close( cmd )
  126.   return result
  127. }
  128.  
  129. # Le DN sera décodé si nécessaire et stocké pour servir de clé à chaque attribut qui suit
  130.  
  131. /^dn\t::\t/                             { $3=decoder( $3 ); $2=":"; DN=$3 }
  132. /^dn\t:\t/                              { DN=$3 }
  133.  
  134. # Autres champs devant être décodés
  135.  
  136. /^cn\t::\t/                             { $3=decoder( $3 ); $2=":" }
  137. /^sn\t::\t/                             { $3=decoder( $3 ); $2=":" }
  138. /^givenName\t::\t/                      { $3=decoder( $3 ); $2=":" }
  139. /^title\t::\t/                          { $3=decoder( $3 ); $2=":" }
  140. /^distinguishedName\t::\t/              { $3=decoder( $3 ); $2=":" }
  141. /^displayName\t::\t/                    { $3=decoder( $3 ); $2=":" }
  142. /^memberOf\t::\t/                       { $3=decoder( $3 ); $2=":" }
  143. /^member\t::\t/                         { $3=decoder( $3 ); $2=":" }
  144. /^sAMAccountName\t::\t/                 { $3=decoder( $3 ); $2=":" }
  145. /^ou\t::\t/                             { $3=decoder( $3 ); $2=":" }
  146. /^description\t::\t/                    { $3=decoder( $3 ); $2=":" }
  147. /^showInAddressBook\t::\t/              { $3=decoder( $3 ); $2=":" }
  148. /^protocolSettings\t::\t/               { $3=decoder( $3 ); $2=":" }
  149. /^name\t::\t/                           { $3=decoder( $3 ); $2=":" }
  150. /^comment\t::\t/                        { $3=decoder( $3 ); $2=":" }
  151. /^altRecipientBL\t::\t/                 { $3=decoder( $3 ); $2=":" }
  152. /^department\t::\t/                     { $3=decoder( $3 ); $2=":" }
  153. /^mail\t::\t/                           { $3=decoder( $3 ); $2=":" }
  154. /^managedBy\t::\t/                      { $3=decoder( $3 ); $2=":" }
  155. /^managedObjects\t::\t/                 { $3=decoder( $3 ); $2=":" }
  156. /^publicDelegates\t::\t/                { $3=decoder( $3 ); $2=":" }
  157. /^msExchCoManagedByLink\t::\t/          { $3=decoder( $3 ); $2=":" }
  158. /^msExchDelegateListBL\t::\t/           { $3=decoder( $3 ); $2=":" }
  159. /^msExchCoManagedObjectsBL\t::\t/       { $3=decoder( $3 ); $2=":" }
  160. /^msExchDelegateListLink\t::\t/         { $3=decoder( $3 ); $2=":" }
  161. /^msExchOABGeneratingMailboxBL\t::\t/   { $3=decoder( $3 ); $2=":" }
  162. /^msExchUserBL\t::\t/                   { $3=decoder( $3 ); $2=":" }
  163.  
  164. # Pour tous les attributs on sort
  165. #
  166. # Le DN
  167. # Le codage
  168. # La clé (ie: le nom de cet attribut)
  169. # Sa valeur
  170.  
  171.         {
  172.            printf( "%s\t%s\t%s\t%s\n", DN, $2, $1, $3 );
  173.        }
  174. ' > full_ad.txt
  175.  
  176.  
  177. # Création des requêtes SQL d'import
  178.  
  179. cat full_ad.txt |
  180.    sed "s/'/''/g" |
  181.    sed 's/\(.*\)\t\(.*\)\t\(.*\)\t\(.*\)$/insert into full_ad_new values ( '"'"'\1'"'"', '"'"'\2'"'"', '"'"'\3'"'"', '"'"'\4'"'"' );/' > full_ad.sql
  182.  
  183. echo "Chargement en base..."
  184.  
  185. (
  186. cat << EOF
  187.  
  188. -- Creation de la table si nécessaire
  189.  
  190. create table if not exists full_ad
  191. (
  192.    dn           text,
  193.    codage       text,
  194.    key          text,
  195.    value        text
  196. ) with oids;
  197.  
  198. -- Destruction de la table d'inport si elle existe
  199. -- On importe dans une table temporaire pour minimiser
  200. -- le temps d'indisponibilité des données
  201.  
  202. drop table if exists full_ad_new;
  203.  
  204. -- Creation de la table d'inmport sur le modèle de la
  205. -- table de production
  206.  
  207. select * into full_ad_new from full_ad where 1=2;
  208. EOF
  209. ) | psql
  210.    
  211. # Importation des données
  212.  
  213. cat full_ad.sql | psql | grep -v INSERT
  214.  
  215. (
  216. cat << EOF
  217.  
  218. -- Mise en clair du type de codage du champ value
  219.  
  220. update full_ad_new set codage = 'clear' where codage = ':';
  221. update full_ad_new set codage = 'base64' where codage = '::';
  222.  
  223. -- Ajout d'un pseudo champ father_node pour permettre de parcourir l'arbre
  224.  
  225. drop table if exists fathers_tempo;
  226.  
  227. select distinct
  228.    dn,
  229.    codage,
  230.    'father_node',
  231.    substring( dn from position(',' in dn)+1 for length(dn) - position(',' in dn) )
  232. into
  233.    fathers_tempo
  234. from
  235.    full_ad_new
  236. where
  237.    position(',' in dn) > 0
  238.    and
  239.    codage = 'clear';
  240.  
  241. insert into full_ad_new (select * from fathers_tempo);
  242.  
  243. drop table if exists fathers_tempo;
  244.  
  245. -- AJout d'un pseudo champ self_node pour avoir le nom de l'objet
  246.  
  247. drop table if exists self_tempo;
  248.  
  249. select distinct
  250.    dn,
  251.    codage,
  252.    'self_node',
  253.    substring( dn from 1 for position(',' in dn)-1  )
  254. into
  255.    self_tempo
  256. from
  257.    full_ad_new
  258. where
  259.    position(',' in dn) > 0
  260.    and
  261.    codage = 'clear';
  262.  
  263. insert into full_ad_new (select * from self_tempo);
  264.  
  265. drop table if exists self_tempo;
  266.  
  267. -- Creation des nouveaux index
  268.  
  269.  
  270. drop index if exists full_ad_i1_new;
  271. drop index if exists full_ad_i2_new;
  272.  
  273. create index full_ad_i1_new on full_ad_new ( dn );
  274. create index full_ad_i2_new on full_ad_new ( dn, key );
  275.  
  276. vacuum FULL VERBOSE ANALYZE full_ad_new;
  277.  
  278. EOF
  279. ) | psql
  280.  
  281. #################################################################
  282. ################## Création table dénormalisée ##################
  283. #################################################################
  284.  
  285. Postgres "drop table if exists ad_objects_new;"
  286.  
  287. echo -n "Creation de la table pivot" >&2
  288.  
  289. (
  290.    echo "create table ad_objects_new"
  291.    echo "("
  292.    echo -n "   object_dn text"
  293.  
  294.    Postgres "select key, max(position ( ',' in value)) from full_ad_new group by key order by key;" | while read key multi
  295.    do
  296.       echo ","
  297.  
  298.       FIELD=`echo $key | tr '-' '_'`
  299.  
  300.       ISNUMERIC=`Postgres "select distinct REGEXP_REPLACE( REPLACE( value, '_', '' ), '^[0-9][0-9]*$', '__INTEGER__') from full_ad_new where key = '$key'" | grep -v '^__INTEGER__$' | wc -l`
  301.  
  302.       TYPE="text"
  303.       if [ "$ISNUMERIC" = "0" ]
  304.       then
  305.          TYPE="numeric(20)"
  306.       fi
  307.  
  308.       echo -n "   $FIELD        $TYPE default null"
  309.  
  310.       if [ "$multi" != 0 ]
  311.       then
  312.          echo ","
  313.          echo -n "   multi_$FIELD       text[] default null"
  314.       fi
  315.  
  316.       echo -n "." >&2
  317.    done
  318.    
  319.    echo
  320.    echo ") with oids;"
  321.  
  322. ) | psql
  323.  
  324. echo >&2
  325.  
  326. Postgres "insert into ad_objects_new ( object_dn ) (select distinct dn from full_ad_new );"
  327.  
  328. echo -n "Remplissage de la table pivot" >&2
  329.  
  330. Postgres "select key, max(position ( ',' in value)) from full_ad_new group by key order by key;" | while read key multi
  331. do
  332.    FIELD=`echo $key | tr '-' '_'`
  333.  
  334.    ISNUMERIC=`Postgres "select distinct REGEXP_REPLACE( REPLACE( value, '_', '' ), '^[0-9][0-9]*$', '__INTEGER__') from full_ad_new where key = '$key'" | grep -v '^__INTEGER__$' |wc -l`
  335.  
  336.    VALUE="f.value"
  337.    if [ "$ISNUMERIC" = "0" ]
  338.    then
  339.       VALUE="cast( f.value as numeric(20))"
  340.    fi
  341.  
  342.    Postgres "update ad_objects_new o set $FIELD = $VALUE from full_ad_new f where o.object_dn = f.dn and f.key = '$key';" | grep -v UPDATE
  343.  
  344.    if [ "$multi" != 0 ]
  345.    then
  346.       Postgres "update ad_objects_new o set multi_$FIELD = string_to_array(f.value, ',') from full_ad_new f where o.object_dn = f.dn and f.key = '$key';" | grep -v UPDATE
  347.    fi
  348.  
  349.    echo -n "." >&2
  350. done
  351.  
  352. echo "" >&2
  353.  
  354. Postgres "drop index if exists ad_objects_i1_new;"
  355. Postgres "create index ad_objects_i1_new on ad_objects_new (object_dn);"
  356. Postgres "vacuum FULL VERBOSE ANALYZE ad_objects_new;"
  357.  
  358. #################################################################
  359. #################################################################
  360. #################################################################
  361.  
  362. echo "Renommage des tables temporaires"
  363.  
  364. TABLES="full_ad ad_objects"
  365.  
  366. # Nettoyage des anciennes tables
  367.  
  368. for table in $TABLES
  369. do
  370.    Postgres "drop table if exists $table""_old"
  371. done
  372.  
  373. # Le renommage des tables est fait dans une transaction
  374.  
  375. (
  376.    echo "begin;"
  377.  
  378.    for table in $TABLES
  379.    do
  380.       echo "alter table if exists $table rename to $table""_old;"
  381.       echo "alter table if exists $table""_new rename to $table;"
  382.    done
  383.  
  384.    echo "commit;"
  385. ) | psql
  386.  
  387. # Renommage des index
  388.  
  389. for table in $TABLES
  390. do
  391.    echo '\d '"$table" |
  392.       psql |
  393.       sed '1,/^Indexes:/d' |
  394.       grep -v '^$' |
  395.       sed 's/^ *"//g' |
  396.       sed 's/".*//g' |
  397.       while read index
  398.       do
  399.          CUR=`echo "$index" | sed 's/_new$//g'`
  400.          OLD=`echo "$index" | sed 's/_new$/_old/g'`
  401.    
  402.          Postgres "drop index if exists $OLD"
  403.          Postgres "alter index if exists $CUR rename to $OLD"
  404.          Postgres "alter index if exists $index rename to $CUR"
  405.       done
  406.    done
  407.  
  408. echo "Terminé"

Télécharger

Un message, un commentaire ?

modération a priori

Ce forum est modéré a priori : votre contribution n’apparaîtra qu’après avoir été validée par un administrateur du site.

Qui êtes-vous ?
Votre message

Pour créer des paragraphes, laissez simplement des lignes vides.

Les spams donneront systématiquement lieu à dépôt de plainte. Les messages peu aimables ou comportant trop de fautes d'orthographe seront purement et simplement supprimés sans publication. Aucune obligation de publication ne pourra être opposée au webmaster, sauf éventuel droit de réponse dûment justifié.
ipv6 ready ipv6 test
Suivre ce site :
Recommander cette page :
Bookmark and Share
Traduire :

Divers (et d’été)


Dans la même rubrique





- Plan du site | Contact | Flux RSS 2.0