Synchronize from CSV to LDAP directory

This tutorial explains you can synchronize a CSV file to an LDAP directory. The idea is to use a database source service based on a HSQLDB instance (database on files or in memory).

This HSQLDB instance will be previously populated with data contained in the CSV file.

First, get a CSV file, or create one for this tutorial. This file will be called tutorial.csv and contains the following sample data:

uid;sn;givenName;cn;mail
jdoe;Doe;John;John Doe;jdoe@example.com

LSC provides a wrapper to manage the packaged HSQLDB database, see HSQLDB presentation to get more details.

Start HSQLB with the following command:

$ bin/hsqldb --start

You can then check the status:

$ bin/hsqldb --status
HSQLDB Server is running (PID 7020)

Load the data with this command:

$ bin/hsqldb --import tutorial.csv 
1 lines imported into table csvdata

Check imported data:

$ bin/hsqldb --show
UID   SN   GIVENNAME  CN        MAIL
----  ---  ---------  --------  ----------------
jdoe  Doe  John       John Doe  jdoe@example.com

First of all, configure your database connection:

    <databaseConnection>
      <name>src-jdbc</name>
      <url>jdbc:hsqldb:hsql://localhost/lscdb</url>
      <username>sa</username>
      <password></password>
      <driver>org.hsqldb.jdbcDriver</driver>
    </databaseConnection>

Then configure your service by referencing the previous connection (attribute reference of the connection node):

<databaseSourceService>
  <name>user-src</name>
  <connection reference="src-jdbc"/>
  <requestNameForList>getInetOrgPersonList</requestNameForList>
  <requestNameForObject>getInetOrgPerson</requestNameForObject>
  <requestNameForClean>getInetOrgPersonClean</requestNameForClean>
</databaseSourceService>

We now need to use IBatis to get data from HSQLDB. First, create or update the SQL map configuration:

$ vi etc/sql-map-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
    "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
  <transactionManager type="JDBC">
    <dataSource type="SIMPLE">
      <property value="${driver}" name="JDBC.Driver" />
      <property value="${url}" name="JDBC.ConnectionURL" />
      <property value="${username}" name="JDBC.Username"/>
      <property value="${password}" name="JDBC.Password"/>
      <property value="15" name="Pool.MaximumActiveConnections"/>
      <property value="15" name="Pool.MaximumIdleConnections"/>
      <property value="1000" name="Pool.MaximumWait"/>
    </dataSource>
  </transactionManager>
  <sqlMap url="file://${lsc.config}/sql-map-config.d/InetOrgPerson.xml"/>
</sqlMapConfig>

Then declare SQL queries:

$ vi etc/sql-map-config.d/InetOrgPerson.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
 
<sqlMap namespace="InetOrgPerson">
 
        <select id="getInetOrgPerson" resultClass="java.util.HashMap" parameterClass="java.util.Map">
                SELECT
                pers."UID" AS uid,
                pers."SN" AS sn,
                pers."GIVENNAME" AS givenname,
                pers."CN" AS cn,
                pers."MAIL" AS mail
                FROM csvdata pers
                WHERE pers."UID" LIKE #uid#
        </select>
 
        <select id="getInetOrgPersonList" resultClass="java.util.HashMap">
                SELECT pers."UID" AS uid
                FROM csvdata pers
        </select>
 
        <select id="getInetOrgPersonClean" resultClass="java.util.HashMap" parameterClass="java.util.Map">
                SELECT
                pers."UID" AS UID
                FROM csvdata pers
                WHERE pers."UID" LIKE #uid#
        </select>
 
</sqlMap>

First of all, configure your ldap connection:

    <ldapConnection>
      <name>dst-ldap</name>
      <url>ldap://localhost:33389/dc=lsc-project,dc=org</url>
      <username>cn=Directory Manager</username>
      <password>secret</password>
      <authentication>SIMPLE</authentication>
      <referral>IGNORE</referral>
      <derefAliases>NEVER</derefAliases>
      <version>VERSION_3</version>
      <pageSize>-1</pageSize>
      <factory>com.sun.jndi.ldap.LdapCtxFactory</factory>
      <tlsActivated>false</tlsActivated>
      <saslMutualAuthentication>false</saslMutualAuthentication>
    </ldapConnection>

Then configure LDAP destination service:

      <ldapDestinationService>
        <name>user-dst</name>
        <connection reference="dst-ldap"/>
        <baseDn>ou=Sample,dc=lsc-project,dc=org</baseDn>
        <pivotAttributes>
          <string>uid</string>
        </pivotAttributes>
        <fetchedAttributes>
          <string>cn</string>
          <string>sn</string>
          <string>gn</string>
          <string>uid</string>
          <string>userPassword</string>
          <string>objectClass</string>
          <string>mail</string>
        </fetchedAttributes>
        <getAllFilter>(objectClass=inetOrgPerson)</getAllFilter>
        <getOneFilter>(&amp;(objectClass=inetOrgPerson)(uid={uid}))</getOneFilter>
      </ldapDestinationService>

As usual, define also how the synchronized objects are going to be identified and how you want to force or leave current attributes:

<propertiesBasedSyncOptions>
  <mainIdentifier>"uid=" + srcBean.getDatasetFirstValueById("uid") + ",ou=Sample,dc=lsc-project,dc=org"</mainIdentifier>
  <defaultDelimiter>;</defaultDelimiter>
  <defaultPolicy>FORCE</defaultPolicy>
  <dataset>
    <name>objectClass</name>
    <policy>FORCE</policy>
    <forceValues>
      <string>"inetOrgPerson"</string>
      <string>"organizationalPerson"</string>
      <string>"person"</string>
      <string>"top"</string>
    </forceValues>
    <delimiter>,</delimiter>
  </dataset>
  <dataset>
    <name>userPassword</name>
    <policy>KEEP</policy>
    <createValues>
      <string>"changethis"</string>
    </createValues>
  </dataset>
</propertiesBasedSyncOptions>

Here the rules are quite simple:

• Create the objectClass attribute (class inetOrgPerson)
• Create the password with the default value “changethis”
• Copy all other attributes from source to destination (uid, cn, sn, givenname and mail)

Check your configuration:

$ bin/lsc -v

Finally launch the synchronization:

$ bin/lsc -s all -c all

You should see the following result:

avr. 23 22:38:35 - DEBUG - Loading XML configuration from: /home/clement/tmp/lsc-2.0-SNAPSHOT/bin/../etc/lsc.xml
avr. 23 22:38:35 - INFO  - Logging configuration successfully loaded from /home/clement/tmp/lsc-2.0-SNAPSHOT/bin/../etc/logback.xml 
avr. 23 22:38:35 - INFO  - LSC configuration successfully loaded from /home/clement/tmp/lsc-2.0-SNAPSHOT/bin/../etc/
avr. 23 22:38:35 - INFO  - Connecting to LDAP server ldap://localhost:33389/dc=lsc-project,dc=org as cn=Directory Manager
avr. 23 22:38:36 - INFO  - Starting sync for user
avr. 23 22:38:36 - INFO  - # Adding new object uid=jdoe,ou=Sample,dc=lsc-project,dc=org for user
dn: uid=jdoe,ou=Sample,dc=lsc-project,dc=org
changetype: add
uid: jdoe
mail: jdoe@example.com
sn: Doe
cn: John Doe
userPassword: changethis
objectClass: organizationalPerson
objectClass: person
objectClass: inetOrgPerson
objectClass: top

avr. 23 22:38:36 - INFO  - All entries: 1, to modify entries: 1, modified entries: 1, errors: 0
avr. 23 22:38:36 - INFO  - Starting clean for user
avr. 23 22:38:36 - INFO  - All entries: 1, to modify entries: 0, successfully modified entries: 0, errors: 0