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.

To run a sample CSV to LDAP connector out of the box, you can follow the quickstart.

Another way to synchronize from CSV to LDAP is to use Executable plugin and Perl wrappers for CSV.

Step 1: Populate the HSQLDB database from CSV

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

HSQLDB script

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

Start HSQLDB

Start HSQLB with the following command:

$ bin/hsqldb --start

You can then check the status:

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

Import CSV data

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

Step 2: Configure LSC

We just provide here the specific configuration items for this tutorial. For a complete overview of LSC configuration, please read the full documentation.

HSQLDB source connector

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>

LDAP destination connector

We suppose you have a running LDAP server on localhost. If not, you can use the sample LDAP directory from the quickstart.

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>

Set synchronization rules

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 configuration

Check your configuration:

$ bin/lsc -v

Launch synchronization

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

For further synchronizations, you will need to reimport fresh data from a CSV file into HSQLDB and launch the connector again.