Setting up LDAP with back-sql

Note: The following is part of a series of steps to setup an email server using Exim 4.x, with imap and webmail access. It will use winbind to get user information from an NT server. If you found this page via a search engine it may not cover what you need or you may need to start at the beginning to understand everything I have done.

(Note: These instructions reference software that is now possibly much newer with many new or different configuration options. This page is being left up for reference.)

Quick Steps:

General:

  1. Download OpenLDAP.
  2. Unpack.
  3. Configure, make, and install.
  4. Building via RPM New
  5. Edit slapd.conf.
  6. Edit ldap.conf.
  7. Start LDAP.
  8. Logging for debugging purposes.
  9. Ports for the firewall.
  10. Working with LDAP notes.

Back-sql specific:

  1. Play with the test database.
  2. Understanding the ldap_* tables.
  3. Simple example setup.
  4. Setting up the MUA for the test database.
  5. Some LDAP Attributes.
  6. SQL to LDAP Misc.

Samba specific:

  1. Has been removed (Dealt with Samba-TNG).

Here's how I did it. Part of this assumes you want to use a database as a backend, but you can skip that if you want. I also have instructions for setting up Samba-TNG's and/or Samba 3.0 alpha's LDAP backend in this. For those who want backsql, just ignore the "samba-tng" and/or "samba 3.0 alpha" parts and vice-versa.

For Samba-TNG and Samba this is also a good site to read:
http://www.unav.es/cti/ldap-smb/ldap-smb-howto.html


General:

1) Download OpenLDAP

If you want backsql support you will need to compile the program yourself. If you don't want backsql support, then the RPM should do fine. For compiling LDAP support into a program you will also need the openldap-devel RPM installed as well.

To compile it yourself, I recommend getting the latest stable version:
http://www.openldap.org/

I started using 2.0.27 (20021018) stable with MySQL and 2.1.22 with PostgreSQL.

2) Unpack

gunzip name_of.tgz
tar -xvf name_of.tar

3) Configure, Make, and Install

Cd into the openldap-* directory. Note that if you want to use OpenLDAP without the sql backend you will also need the db4 rpms, including db4-devel, installed. There may be other packages you need depending on what you want support for. For back-sql you'll need to install the unixODBC-devel*.rpm or libiodbc-devel*.rpm as mentioned here.

Run ./configure with options as needed. I used (note the last one is for back-sql):
./configure --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin \
--sysconfdir=/etc --datadir=/usr/share --localstatedir=/var \
--mandir=/usr/share/man --infodir=/usr/share/info --enable-sql

Use:
./configure --help
to see all the options.

Then:
make depend
make

When you are ready to install:
make install

4) Building an RPM w/ back-sql

For those of you interested in compiling your own RPMs, here is what I did:

  • Installed RedHat's source rpm (latest):
    rpm -ivh openldap-[version].src.rpm.
  • cd to /usr/src/redhat/SPECS/
  • Changed the openldap.spec file as shown below to build with back-sql support.
  • Installed the necessary packages for compiling. Try a:
    rpmbuild -bp --nobuild openldap.spec
    to see if it says any packages are missing.
  • Ran rpmbuild -bi openldap.spec to test compiling.
  • Ran rpmbuild -bb openldap.spec to build the binaries (or rpmbuild -ba openldap.spec to build all).

Note: I am running rpm 4.1.1-1.8x. See rpm.org for the latest.

To download my rpm or view my full spec file, go here.

Here are the spec file changes I made to RedHat's v2.0.27-2.8.0 (in red):

%define migtools_ver 44
%define db_version 4.1.24.NC
%define backend gdbm
Summary: The configuration files, libraries, and documentation for OpenLDAP.
Name: openldap
Version: 2.0.27
Release: 2.8.0sql
License: OpenLDAP
#...
 
%package servers
#...
%description servers
OpenLDAP is an open-source suite of LDAP (Lightweight Directory Access
Protocol) applications and development tools. LDAP is a set of
protocols for accessing directory services (usually phone book style
information, but other information is possible) over the Internet,
similar to the way DNS (Domain Name System) information is propagated
over the Internet. This package contains the slapd and slurpd servers,
migration scripts, and related files. Built with back-sql support.
 
%package clients
#...
 
%configure \
        --with-slapd --with-slurpd --without-ldapd \
        --with-threads=posix --enable-static \
        \
        --enable-local --enable-cldap --disable-rlookups \
        \
        --with-tls \
        --with-cyrus-sasl \
        \
        --enable-wrappers \
        \
        --enable-passwd \
        --enable-shell \
        --enable-cleartext \
        --enable-crypt \
        --enable-spasswd \
        --enable-modules \
        --enable-sql \
        \
        --libexecdir=%{_sbindir} \
        --localstatedir=/%{_var}/run \
#...

If you are using RH8 you will need to update libtool to v1.5 (which in turn requires autoconf >= v2.54 to be built) to build openldap 2.1.22. Here are the changes I made to the spec file from openldap-2.1.22-8.src.rpm:

 

#...
Name: openldap
Version: 2.1.22
Release: 8sql
#...
 
%description servers
OpenLDAP is an open-source suite of LDAP (Lightweight Directory Access
Protocol) applications and development tools. LDAP is a set of
protocols for accessing directory services (usually phone book style
information, but other information is possible) over the Internet,
similar to the way DNS (Domain Name System) information is propagated
over the Internet. This package contains the slapd and slurpd servers,
migration scripts, and related files. Built with back-sql support.
 
#...
CFLAGS="$CPPFLAGS $RPM_OPT_FLAGS -D_REENTRANT -fPIC"; export CFLAGS
%configure \
	--with-slapd --with-slurpd --without-ldapd \
	--with-threads=posix --enable-static --enable-dynamic \
	\
	--enable-local --enable-cldap --enable-rlookups \
	\
	--with-tls \
	--with-cyrus-sasl \
	\
	--enable-wrappers \
	\
	--enable-passwd \
	\
	--enable-cleartext \
	--enable-crypt \
	--enable-spasswd \
	--enable-modules \
  --enable-sql \
	\
	--libexecdir=%{_sbindir} \
	--localstatedir=/%{_var}/run \
	$@
 
#...
 
# Build the servers with Kerberos support (for password checking, mainly).
CPPFLAGS="$OPENSSL_CPPFLAGS -I${dbdir}/include -I/usr/kerberos/include" ; export CPPFLAGS
LDFLAGS="$OPENSSL_LDFLAGS -L${dbdir}/%{_lib} -L/usr/kerberos/lib" ; export LDFLAGS
pushd build-servers
 
#...
 
%files servers
%defattr(-,root,root)
%doc README.migration TOOLS.migration
%doc $RPM_SOURCE_DIR/README.upgrading $RPM_SOURCE_DIR/guide.html
%doc servers/slapd/back-sql/docs
%doc servers/slapd/back-sql/rdbms_depend/*
# ...

Note that the RedHat openldap package takes a while to build and a lot of disk space.

Once the binary rpm is built you can install it from the RPMS/i386 directory with:
rpm -ivh [package_name]*

5) Edit slapd.conf

First you will need to generate the encrypted password to place in the config file. Some folks use MD5, I'm using crypt. Generate it by running:
slappasswd -h {crypt}
and type in the password. It will spit out the string you need.

Note: I am only showing the bare minimum for what is needed for the back-sql sample database and/or samba-tng and/or samba 3.0 alpha.

Slapd.conf for OpenLDAP v2.1.22 (PostgreSQL, no samba stuff):

# $OpenLDAP: pkg/ldap/servers/slapd/slapd.conf,v 1.23.2.8 2003/05/24 23:19:14 ku
#
# See slapd.conf(5) for details on configuration options.
# This file should NOT be world readable.
#
include         /etc/openldap/schema/core.schema
include         /etc/openldap/schema/cosine.schema
include         /etc/openldap/schema/inetorgperson.schema
 
# Allow LDAPv2 client connections.  This is NOT the default.
allow bind_v2
 
# Do not enable referrals until AFTER you have a working directory
# service AND an understanding of referrals.
#referral       ldap://root.openldap.org
 
pidfile         /var/run/slapd.pid
argsfile        /var/run/slapd.args
defaultsearchbase       dc=cpm-inc,dc=com
# Timeout in seconds, 0 = never
idletimeout     0
threads         32
# Debuging level, 0 = none
#loglevel       64
 
access to * by * read
 
#######################################################################
# sql database definitions
#######################################################################
database        sql
suffix          "ou=contacts,dc=cpm-inc,dc=com"
# Only need if not using the ldbm/bdb stuff below
#rootdn         "cn=manager,dc=cpm-inc,dc=com"
#rootpw         {crypt}yZx0a4mg
dbname          contacts
dbuser          ldapac
dbpasswd        ldapac
lastmod off
# new to OpenLDAP v2.1.x
has_ldapinfo_dn_ru      no
# PostgreSQL
insentry_query  "insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values ((select max(id)+1 from ldap_entries),?,?,?,?)"
upper_func      "upper"
strcast_func    "text"
concat_pattern  "?||?"
 
#######################################################################
# ldbm and/or bdb database definitions
#######################################################################
 
database        ldbm
suffix          "dc=example,dc=com"
rootdn          "cn=manager,dc=example,dc=com""
rootpw          {crypt}yZx0a4mg
# The database directory MUST exist prior to running slapd AND
# should only be accessible by the slapd and slap tools.
# Mode 700 recommended.
directory       /var/lib/ldap
# Indices to maintain for this database
index objectClass                       eq,pres
index ou,cn,mail,surname,givenname      eq,pres,sub
index uidNumber,gidNumber,loginShell    eq,pres
index uid,memberUid                     eq,pres,sub
index nisMapName,nisMapEntry            eq,pres,sub
 
# Set the userPassword so that it can be changed
# by the entry owning it if they are authenticated.
# Others should not be able to see it, except the
# admin entry below
# Note: include lmPassword and ntPassword if using a samba/tng schema
access to dn="(.*,)?dc=example,dc=com" attr=userPassword
        by self write
        by dn="cn=manager,dc=example,dc=com" write
access  to dn="(.*,)?dc=example,dc=com"
        by dn="cn=manager,dc=example,dc=com"         write
        by self         write
        by *            read
 
# Replicas of this database
#replogfile /var/lib/ldap/openldap-master-replog
#replica host=ldap-1.example.com:389 tls=yes
#     bindmethod=sasl saslmech=GSSAPI
#     authcId=host/ldap-master.example.com@EXAMPLE.COM

For OpenLDAP v2.1.x see also man slapd-sql.

Older slapd.conf for OpenLDAP v2.0.27 (MySQL):

# $OpenLDAP: pkg/ldap/servers/slapd/slapd.conf,v 1.8.8.7 2001/09/27 20:00:31 kur
#
# See slapd.conf(5) for details on configuration options.
# This file should NOT be world readable.
#
include         /etc/openldap/schema/core.schema
include         /etc/openldap/schema/cosine.schema
include         /etc/openldap/schema/inetorgperson.schema
include         /etc/openldap/schema/nis.schema
#
# For horde/imp/turba. Comes with horde.
# Copy the file there to the location specified here.
include         /etc/openldap/schema/horde.schema
#
# NOTE: you cannot have both TNG & 3.0 uncommented
# as there are conflicts. (from what I saw)
#
# For samba-tng. This is found in (cvs dir)/tng/ldap.
# Copy the file there to the location specified here.
#include         /etc/openldap/schema/sambatng.schema-v3
#
# For samba 3.0 alpha. This is found in
# (cvs dir)/samba/examples/LDAP. Copy the file there
# to the location specified here.
include         /etc/openldap/schema/samba.schema
 
pidfile         /var/run/slapd.pid
argsfile        /var/run/slapd.args
defaultsearchbase       dc=example,dc=com
# Timeout in seconds, 0 = never
idletimeout     0
threads         32
# Debuging level, 0 = none
loglevel        32
 
# Define global ACLs to disable default read access.
# Allow all to search
access to * by * read
# Note ACLs defined in the database definitions
# override this. If you don't have the above folks
# must know what base your LDAP uses to see anything
 
#######################################################################
# sql database definitions
#######################################################################
 
database        sql
# This following is for the sample database as it installs
suffix          "o=sql,c=RU"
rootdn          "cn=root,o=sql,2c=RU"
rootpw          {crypt}yZx0a4mg
dbname          test
dbuser          nobody
dbpasswd
subtree_cond    "ldap_entries.dn LIKE CONCAT('%',?)"
insentry_query  "INSERT INTO ldap_entries (dn,oc_map_id,parent,keval) VALUES (?,?,?,?)"
 
#######################################################################
# ldbm database definitions
#######################################################################
 
database        ldbm
# The following is for ldap in general, especially if running a mix of
# back-sql and ldbm
suffix          "dc=example,dc=com"
rootdn          "cn=manager,dc=example,dc=com"
rootpw          {crypt}yZx0a4mg
# The database directory MUST exist prior to running slapd AND
# should only be accessible by the slapd user. Mode 700 recommended.
directory       /var/openldap-ldbm
# Indices to maintain
index objectClass eq
lastmod on
# Set the userPassword so that it can be changed
# by the entry owning it if they are authenticated.
# Others should not be able to see it, except the
# admin entry below
# Note: include lmPassword and ntPassword only if using a samba/tng schema
access to dn="(.*,)?dc=example,dc=com" attr=userPassword,lmPassword,ntPassword
        by self write
        by dn="cn=manager,dc=example,dc=com" write
access  to dn="(.*,)?dc=example,dc=com"
        by dn="cn=manager,dc=example,dc=com"         write
        by self         write
        by *            read

Further reading:
http://yolinux.com/TUTORIALS/LinuxTutorialLDAP-SLAPD-LDIF-V2-config.html

6) Edit ldap.conf

Note: I am only showing the bare minimum and what is needed for the test database or for samba-tng/samba 3.0 alpha.

# $OpenLDAP: pkg/ldap/libraries/libldap/ldap.conf,v 1.4.8.6 2000/09/05 17:54:38
#
# LDAP Defaults
 
 
# See ldap.conf(5) for details
# This file should be world readable but not world writable.
 
HOST    127.0.0.1
# Note: I think you can use only one of the following at a time
# You can modify the test database information
# so that it falls under the same base as samba-tng
# by modifying information in the tables.
#
# for the test database
BASE    o=sql,c=RU
# for samba and other examples
#BASE    dc=example,dc=com

7) Start LDAP

service ldap start

8) Logging for debugging purposes

If you want to enable logging for test purposes, run slapd manually:
/path/to/slapd -s level
or add this to your slapd.conf file:
loglevel level

Where level is:

1 trace function calls
2 debug packet handling
4 heavy trace debugging
8 connection management
16 print out packets sent and received
32 search filter processing
64 configuration file processing
128 access control list processing
256 stats log connections/operations/results
512 stats log entries sent
1024 print communication with shell backends
2048 entry parsing

To make it go to a separate file edit
/etc/syslog.conf
and add
local4.* ~/var/log/ldap.log
and then restart syslog
/etc/rc.d/init.d/syslog restart

Note: This can be a big performance hit. I recommend using it only when you need to. Rem it out and restart syslog when you are done.

You can also use the -d (debug) switch, which can sometimes tell you more than -s (syslog):
/path/to/slapd -d level

If you are using an rpm from RedHat and it is not logging, check /etc/init.d/ldap file to make sure it does not have -l daemon in the startup. If it does you can either remove it or change the syslog.conf file.

9) Ports for the Firewall

LDAP runs on port 389/tcp by default and LDAP over SSL is 636/tcp.

10) Working with LDAP notes

Searching:
Show everything from tree base down:
ldapsearch -x -b 'dc=example,dc=com' 'objectclass=*'

Show things with an objectclass of sambaAccount starting at level o=samba,dc=example,dc=com:
ldapsearch -x -b 'o=samba,dc=example,dc=com' 'objectclass=sambaAccount'

Search everything from tree base down with login as cn=manager and prompt for password:
ldapsearch -x -b 'dc=example,dc=com' \
'objectclass=*' -D 'cn=manager,dc=example,dc=com' -W

Adding using an ldif file:
Create your ldif file with what you want to add. For the very first addition you are setting up the base of your LDAP tree. It would include the base definition and an account with managing/root rights and look something like:

dn: dc=example,dc=com
objectClass: top
objectClass: dcObject
objectClass: organization
dc: example
o: Example Company
description: The Example Company       
 
dn: cn=manager, dc=example, dc=com
objectClass: organizationalRole
objectClass: simpleSecurityObject
cn: admin
description: LDAP administrator
userPassword: {crypt}xYz03aBc

To generate the {crypt} password, or MD5 if you chose, for the above just run: slappasswd -h {crypt}

To import your initial entries run:
su -m slapd_user
slapadd -l base.ldif -f /etc/openldap/slapd.conf
exit

Sometimes you need to kick it in the pants (service ldap restart) for the changes to take affect. If you get a message about "database does not support the necessary operations" and you have the sql section enabled, rem it out and restart ldap.

After you have your initial entries in you can add more. Remember to also include any higher level branches that have not been created already. For example if I want to add some users under ou=users,o=new,dc=example,dc=com and o=new and ou=users does not exist I must also include the definition for those branches. Ex:

dn: o=new,dc=example,dc=com
o: new
objectClass: organization
 
dn: ou=users,o=new,dc=example,dc=com
ou: users
objectClass: organizationalUnit
 
dn: uid=Administrator,ou=users,o=new,dc=example,dc=com
cn: Administrator
objectClass: sambaAccount
objectClass: posixAccount
uid: Administrator
pwdLastSet: 0
logonTime: 0
logoffTime: 0
kickoffTime: 0
pwdCanChange: 0
pwdMustChange: 0
rid: 500
...

Once your ldif file is created you can import it. After the initial entries it is better to import with (this will prompt for the password):
Tip: add -n to the options to show what would be done without doing it.
ldapadd -D "cn=manager,dc=example,dc=com" -H \
ldap://127.0.0.1 -v -W -x -f file_name.ldif

If the import fails you must figure out what did import and remove those entries from the ldif file before trying to import again. After fixing the problem, of course.

Modifying using an ldif file:
An ldif setup for modifying one entry (with replace, add, and delete examples) looks like:

dn: uid=newuser,ou=users,o=new,dc=example,dc=com
changetype: modify
replace: mail
mail: newuser@example.com
-
add: title
title: Flunky
-
delete: description
-

To modify those entries in the ldif file created above run:
Tip: add -n to the options to show what would be done without doing it.
ldapmodify -D "cn=manager,dc=example,dc=com" -H \
ldap://127.0.0.1 -r -v -W -x -f file_name.ldif

Removing:
An ldif file setup for deleting one entry looks like:
dn: uid=newuser,ou=users,o=new,dc=example,dc=com
changetype: delete

To remove those entries in the ldif file created above run:
Tip: add -n to the options to show what would be done without doing it.
ldapmodify -D "cn=manager,dc=example,dc=com" -H \
ldap://127.0.0.1 -v -W -x -f file_name.ldif


Back-sql specific:

If you prefer to use Postgresql, here is another how-to for it.

Note: The SQL database must be running before slapd starts.
If the SQL server is running on the same machine you may need to:
chckconfig sql_server_init_file off
Edit the chkconfig line of /etc/init.d/sql_server_init_file so that the first number is lower than the number in the chkconfig line of /etc/init.d/ldap
chkconfig sql_server_init_file on

1) Play with the Test Database

If this is your first time doing this, playing with the test provided with back_sql is a great way to figure out how it works. Be sure to also read concept and install in (openldap source)/servers/slapd/back-sql/docs. Of course I assume you have your SQL server installed and running. My steps for mySQL are here.

In (openldap source)/servers/slapd/back-sql/rdbms_depend/(database type) there are some scripts. (The PostgreSQL scripts where not included until 2.1.x. If using my rpm, see /usr/share/docs/openldap-servers-version.) The testdb_*.sql are for creating a simple test with some data. Load them (see your database server's documentation, I used Webmin) in this order: testdb_create.sql, testdb_data.sql, backsql_create.sql, and finally testdb_metadata.sql. I recommend doing this in the default 'test' database. When you start using your database all you need to run is the backsql_create.sql to create the necessary ldap_* tables.

If you configured "sql database definitions" in slapd.conf as shown above everything is now ready. Start ldap with:
/etc/rc.d/init.d/ldap start

To see if it is reading the test database and see everything:
ldapsearch -LLL -s sub -b "o=sql,c=RU" "(objectClass=*)"

A nice free LDAP browser for Windows is Softerra's LDAP Browser:
http://www.softerra.com/products/ldapbrowser.php

The first thing you will notice in the persons database is that the name is all in one column. Most folks separate this out into at least first and last. Let's split the name column into FirstName and LastName. (See your database's documentation for how to do this. I used Webmin.) Then, in table ldap_attr_mappings change:

id oc_map_id name sel_expr from_tbls join_where add_proc delete_proc param_order expect_return
1 1 cn persons.name persons       3 0
3 1 sn persons.name persons       3 0

to:

id oc_map_id name sel_expr from_tbls join_where add_proc delete_proc param_order expect_return
1 1 cn CONCAT(persons.FirstName, ' ', persons.LastName) persons       3 0
3 1 sn persons.LastName persons       3 0

and restart ldap:
/etc/rc.d/init.d/ldap restart

Note: With PostgreSQL it is much easier if you keep all table and field names lowercase.

Basically you need to restart ldap if you make a change to one of the ldap_* tables. Most everything else is dynamic, except...

Take a look at the values in the ldap_entries table. Notice such as "cn=Mitya Kovalev,o=sql,c=RU". The way this database is currently setup you would need to add/update/delete entries here when you modified the persons table. The following is from the concept document:
"...This table defines mappings between DNs of entries in your LDAP tree, and values of primary keys for corresponding relational data. It has recursive structure (parent column references id column of the same table), which allows you to add any tree structure(s) to your flat relational data. Having id of objectclass mapping, we can determine table and column for primary key, and keyval stores value of it, thus defining exact tuple corresponding to LDAP entry with this DN."

That same document (in 2.0.27, but not found in 2.1.x) gives an example to do this dynamically using a VIEW. However, MySQL does not support this.

Update: I have decided to switch to PostgreSQL because it supports VIEW and I do not want to maintain data in two places. See the notes further down, in Simple Example Setup, for what I did different to make VIEW ldap_entries act like a manually created version of TABLE ldap_entries discussed here.

Next, let's add an email address field. I called it EMailAddress. In that add some bogus email addresses for the folks listed. Now in table ldap_attr_mappings add:

id oc_map_id name sel_expr from_tbls join_where add_proc delete_proc param_order expect_return
<n> 1 mail persons.EMailAddress persons       3 0

Where <n> is the next id number available.

2) Understanding the ldap_* Tables

This is the best I can describe it...

The ldap_oc_mappings table is where you put your object classes. Columns:
id = a unique id that you will refer to in other ldap_* tables
name = The value for objectClass
keytbl = the table where entities for the objectClass are held. Ex: inetOrgPerson is for identifying people, so it uses the persons table in the test data.
keycol = the table's primary key column name
create_proc = the SQL code when an LDAP create is called
delete_proc = the SQL code when an LDAP delete is called
expect_return = what to expect when the query is successful (ie not an error)

The ldap_attr_mappings table is where you put your field definitions (table colum = ldap type). Columns:
id = a unique id that you will refer to in other ldap_* tables
oc_map_id = refers back to the id of the relevant objectClass in the ldap_oc_mappings table
name = the ldap attribute name
sel_expr = the SELECT xxxx part of the SQL statement
from_tbls = the FROM xxxx part of the SQL statement
join_where = the WHERE ... xx.xx=yy.yy ... part of the SQL statement if applicable. A null is allowed if you are not doing a join.
add_proc = the SQL code when an LDAP create is called
delete_proc = the SQL code when an LDAP delete is called
param_order = (I have no idea, but 3 seems to be the default in the test data.)
expect_return = what to expect when the query is successful (ie not an error)

The ldap_entries table is where you define the mappings between the DN entries in the LDAP tree. Columns:
id = a unique id
dn = the dn for an entry
oc_map_id = refers back to the id of the relevant objectClass in the ldap_oc_mappings table
parent = what level in the LDAP tree this is located at, starting with 0 (zero)
keyval = refers back to the id of the relevant row of the table the data is contained in. These rows are identified by a number that is a primary key.
Note: If you database server supports VIEW, see the concepts in (openldap-*)/servers/slapd/back-sql/docs.

The ldap_entry_objectclass... something to do with multiclassing?

The ldap_referrals... something to do with referrals?

3) Simple Example Setup

In this case I want to use both the back-sql and ldbm databases. The back-sql for our contact information and ldbm for other things, like samba users. My root is:
dc=example,dc=com
My contacts, using back-sql, will be under:
ou=contacts,dc=example,dc=com
My other LDAP stuff, such as samba users and machines, using ldbm will be under:
ou=samba,dc=example,dc=com

If you have not setup your LDAP base do the following:

  • For now, edit your slapd.conf so that the "sql database definition" is rem'd out, the "ldbm database definition" is similar to what is shown in #4 above, and stop LDAP (service ldap stop).
  • Generate the administrative user's password by using (note: MD5 is an option as well):
    slappasswd -h {crypt}
  • Next, create your base.ldif file, which will have your base and administrative user defined. Example:
    dn: dc=example,dc=com
    objectClass: top
    objectClass: dcObject
    objectClass: organization
    dc: example
    o: Example Company
    description: The Example Company
     
    dn: cn=manager, dc=example, dc=com
    objectClass: organizationalRole
    objectClass: simpleSecurityObject
    cn: manager
    description: LDAP administrator
    userPassword: {crypt}xYz03aBc
  • Double-check that the directory specified for the database in slapd.conf is owned by the user slapd is run as. If you need to change it run something like:
    chown slapd_user.slapd_users_group /var/openldap-ldbm
  • To import the ldif file run:
    su -m slapd_user
    slapadd -l base.ldif -f /etc/openldap/slapd.conf

With the base setup, but with the "sql database definitions" still rem'd out, you can now add any branches you need. If you recall I want two branches - contacts and samba. To do this with LDAP running (service ldap start):

  • Create another ldif file, similar to:
    dn: ou=contacts,dc=example,dc=com
    ou: contacts
    objectclass: organizationalUnit                
    dn: ou=samba,dc=example,dc=com
    ou: samba
    objectclass: organizationalUnit
  • Then import it with (you will be prompted for the administrative user's password):
    ldapadd -D "cn=manager,dc=example,dc=com" -H \
    ldap://127.0.0.1 -v -W -x -f file_name.ldif

For now I'll skip other things that might go in the ldbm file and continue with the back-sql part.

My slapd.conf looks exactly like the above in General #4, but the "sql database definitions" are now: Vfy with PostgreSQL

#######################################################################
# sql database definitions
#######################################################################
 
database        sql
suffix          "ou=contacts,dc=example,dc=com"
# You only need these if normal ldap backends are defined and hold the "root"
#rootdn          "cn=manager,dc=example,dc=com"
#rootpw          {crypt}yZx0a4mg
dbname          contacts
dbuser          ldapac
dbpasswd        youwish
index           cn,sn,givenName,o
index           objectclass pres,eq
index           default none
lastmod off
# new to OpenLDAP v2.1.x
has_ldapinfo_dn_ru      no
# PostgreSQL
insentry_query  "insert into ldap_entries (id,dn,oc_map_id,parent,keyval) values (select max(id)+1 from ldap_entries),?,?,?,?)"
upper_func      "upper"
strcast_func    "text"
concat_pattern  "?||?"
# MySQL & OpenLDAP v2.0.27 (may need to be changed)
#subtree_cond    "ldap_entries.dn LIKE CONCAT('%',?)"
#insentry_query  "INSERT INTO ldap_entries (dn,oc_map_id,parent,keval) VALUES (?,?,?,?)"

Tip: I've found the "sql database definitions" must go before the "ldbm database definitions". Also that you must specify any sub-branches the data is under in "suffix". In my case the back-sql stuff will fall under the branch 'ou=contacts'.

Don't restart LDAP yet, but instead start setting up your database tables. There are many ways to do this, therefore I won't go into specific detail. With MySQL there is a database called "test" which is a good place to play. We will assume you are working there since this is an example, but you can create your own database and work there as well. Clean out (drop) the database of tables created with the back-sql test database scripts if you used them.

First (re)run the backsql_create.sql to create the necessary ldap_* tables in the database. You did keep that script didn't you? Note that with PostgreSQL I had to get them from a newer version of OpenLDAP and then remove the creation of ldap_entries, which I chose to create with a view (not applicable with MySQL).

Now, since we are using organizationalUnit(s) for the branch, created a table:
Name: orgunit
Field 1: name = id, type = tinyint, # of char = 11, nulls = no, primary = yes, auto-increment = yes
Field 2: name = name, type = varchar, # of char = 50, nulls = no

In that table I placed the following data:

id name
1 contacts

Next I created a table for the (contact) information. Technically I exported our current list to a CVS file and imported it after creating the table and it's fields, but for this example we'll only use a few fields and manually enter some data. Let's create a table:
Name: contacts
Field 1: name = id, type = tinyint, # of char = 11, nulls = no, primary = yes
Field 2: name = FirstName, type = varchar, # of char = 50
Field 3: name = LastName, type = varchar, # of char = 50
Field 4: name = Company, type = varchar, # of char = 100
Field 5: name = EMailAddress, type = varchar, # of char = 255

Now, let's enter some sample information:

id FirstName LastName Company EMailAddress
1 My Name Example Co. myname@example.com
2 Your Name Example Co. yourname@example.com
3 Nobody Nowhere Here nobody@here.com

To use what we have done so far with LDAP, we now need to add some information into some of the ldap_* tables. Those are created with backsql_create.sql.

First in ldap_oc_mappings we need to enter some data to define a few objectClass(es). In this example we need one objectClass for the branch (ou=contacts) and one for the people in contacts. (note: I'm not allowing adding and deleting via LDAP so that part is blank.) My data looks like:

id name keytbl keycol create_proc delete_proc expect_return
1 organizationalUnit orgunit id     0
2 inetOrgPerson contacts id     0

Next in ldap_attr_mappings you need to map LDAP types to fields in the orgunit and contacts tables. Using the sample information the data looks like:

id oc_map_id name sel_expr from_tbls join_where add_proc delete_proc param_order expect_return
1 1 ou orgunit.name orgunit       3 0
1 2 cn CONCAT(contacts.FirstName, ' ', contacts.LastName) contacts       3 0
2 2 givenName contacts.FirstName contacts       3 0
3 2 sn contacts.LastName contacts       3 0
4 2 o contacts.Company contacts       3 0
5 2 mail contacts.EMailAddress contacts       3 0

Note: inetOrgPerson scheme requires a value for 'cn' and 'sn'. Because we have some contacts that only have values in "Company", my real 'cn' sel_expr is:
MySQL:
IF(LENGTH(LastName)>0,IF(LENGTH(FirstName)>0,IF(LENGTH(MInitial)>0,CONCAT(FirstName, ' ', MInitial, ' ', LastName),CONCAT(FirstName, ' ', LastName)),LastName),IF(LENGTH(FirstName)>0,FirstName,Company))
PostgreSQL (shortned due to varchar(255) length):
CASE WHEN LENGTH(lastname)>0 THEN CASE WHEN LENGTH(firstname)>0 THEN CASE WHEN LENGTH(minitial)>0 THEN firstname||' '||minitial||' '||lastname ELSE firstname||' '||lastname END ELSE lastname END ELSE company END
and my real 'sn' sel_expr is:
MySQL:
IF(LENGTH(LastName)>0,LastName,IF(LENGTH(FirstName)>0,FirstName,Company))
PostgreSQL:
CASE WHEN LENGTH(lastname)>0 THEN lastname ELSE company END

A "gotcha" with Mozilla/Thunderbird is the way it returns for attributes that go by multiple names, such as 'o' and 'company' for the "Company" field. If you decide to use 'company' it will not return the value since 'o' is defined first, even though 'o' returns nothing. In this case you might want to define both 'o' and 'company', if you are not using 'o' for something else. See "nsAbLDAPProperties.cpp" for how things are defined. My biggest problem with this but is that it defines 'postofficebox' before 'streetaddress' for the "WorkAddress" field. This causes the street address, which we need for snail mail, to not show up (if there is no PO address). So, for 'postofficebox' I used:
PostgreSQL:
CASE WHEN LENGTH(poaddress)>0 THEN poaddress ELSE CASE WHEN LENGTH(address2)>0 THEN address1||' '||address2 ELSE address1 END END
MySQL:
IF(LENGTH(PoAddress)>0,PoAddress,IF(LENGTH(Address2)>0,CONCAT(Address1, ' ', Address2),Address1))

Finially we need to edit ldap_entries. MySQL does not support VIEW, so if you are using it you will need to manually enter (and maintain!) the data. When I was testing MySQL I exported our current list with a custom query to a file and imported, but for this example we'll manually enter the data. And, no, I couldn't figure out a good way to maintain both the table with the contacts information and the ldap_entries table using MS Access (brain fried). Notes on creating a VIEW with PostgreSQL to be ldap_entries follow after the sample data.

The problem with the example that comes with back-sql is that I think there is too much room for a possible duplicate dn, which cannot be. So instead of using "cn=FirstName LastName", I decided to use "uid=id". Using the sample information the data looks like:

id dn oc_map_id parent keyval
1 ou=contacts,dc=example,dc=com 1 0 1
2 uid=1,ou=contacts,dc=example,dc=com 2 1 1
3 uid=2,ou=contacts,dc=example,dc=com 2 1 2
4 uid=3,ou=contacts,dc=example,dc=com 2 1 3

PostgreSQL specific: A view can be created using something like:
CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval) AS SELECT id, 'uid='||id||',ou=contacts,dc=example,dc=com', 2, 1, id FROM "tblContacts";

By using a VIEW to create ldap_entries I cannot manually set id 1 as shown above. So I'm faking it by having a bogus entry with id 1 in the contacts table and using some CASE statements. Example:

CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval) AS
SELECT id,
 CASE WHEN id=1 THEN 'ou=contacts,dc=example,dc=com'
      ELSE 'uid='||id||',ou=contacts,dc=example,dc=com'
 END,
 CASE WHEN id=1 THEN 1
      ELSE 2
 END,
 CASE WHEN id=1 THEN 0
      ELSE 1
 END,
 id FROM "contacts";

Because of this and because I exported the Access primary key, when creating a file to import our existing contacts, I had to modify my Access export query to add 1 to the key field and then manually enter the bogus entry (key id = 1 of course).

Now you can (re)start LDAP and see the fruits of your labor with:
ldapsearch -LLL -s sub -b 'ou=contacts,dc=example,dc=com' 'objectclass=*'

If you want to see my notes on exporting an existing Access database for import into mySQL go here and read the section "Set up the Database".

4) Setting up the MUA for the test database

You should now be able to use your email client's ldap interface to query for addresses (at least with the test data), once it is setup. If you have:
access to * by * read
in your slapd.conf file then anonymous and authenticated users can perform searches and read the results. See the openLDAP documentation for more on security.

To setup Outlook Express, go to Tools menu, Accounts, Add -> Directory Service. For the server, enter the IP address or FQDN of your LDAP server. Once it is defined, go into it's Properties, to the Advanced tab and set the search base (o=sql, c=RU for the test database). You should now be able to use this via Addresses, Find People.

To setup Pegasus, open the LDAP client (location varies per version) and click Setup. Add a new directory. For the host name enter the IP address or FQDN of your LDAP server. In search set the base (o=sql, c=RU for the test database). You should now be able to use this directory for LDAP queries.

To setup SquirrelMail, using
perl /path/to/webmail/config/conf.pl
go to option "6. Address Books (LDAP)". To configure use "1. Change Servers":
+
hostname is: localhost (assuming the ldap server is on the same machine)
base is: o=sql, c=RU (for the test database)
port is: 389
name is: (whatever you want to refer to it by)
Everything else is optional. Use "l" to list and "d" to finish. Don't forget to save before exiting. Access to this is through Compose via the Address button.

To setup Horde Imp, I have some information here.

5) LDAP Attributes

These are some of the basics you'll probably need, but not all of them:

Defined in the core.schema:

LDAP Designation Description
cn common name
sn surname
givenName given name (aka first name)
telephoneNumber phone number
facsimileTelephoneNumber fax number
mail email address
street street address
postOfficeBox po box address
postalCode zip code
l locality (aka city) name
st state or province name
c country code
o organization name
ou organizational unit name
title aka job function (ex VP)
businessCategory what the company does

Defined in the cosine schema:

LDAP Designation Description
homePhone home telephone number
mobile mobile (aka cell) telephone number

See also http://ldap.akbkhome.com/objectclass/inetOrgPerson.html

6) SQL to LDAP Misc

A while back I was questioned about this scenerio:
With G1Users in one SQL table and G2Users in another, same database, and the "limitations" of ldap_oc_mappings, how to get this LDAP layout:

+ ou=Groups
 + ou=G1
   + ou=G1Users
      - sn=...
 + ou=G2
   + ou=G2Users
      - sn=...

What we did was make ou=Groups plus ou=G1 and ou=G2 not controlled by a table. To do this we imported them into LDAP, not the SQL backend, via an LDIF file like:

dn: ou=Groups,dc=example,dc=com
ou: Groups
objectclass: organizationalUnit
 
dn: ou=G1,ou=Groups,dc=example,dc=com
ou: G1
objectclass: organizationalUnit
 
dn: ou=G1Users,ou=G1,ou=Groups,dc=example,dc=com
ou: G1Users
objectclass: organizationalUnit
 
dn: ou=G2,ou=Groups,dc=example,dc=com
ou: G2
objectclass: organizationalUnit
 
dn: ou=G2Users,ou=G2,ou=Groups,dc=example,dc=com
ou: G2Users
objectclass: organizationalUnit

With the SQL stuff rem'ed out in slapd.conf for now, the 'ldbm' section correct, and slapd running, the LDIF file was added with:
ldapadd -D "cn=manager,dc=example,dc=com" -H \
ldap://127.0.0.1 -v -W -x -f file_name.ldif

You will be prompted for the password assinged to the 'manager' user (see your slapd.conf file)

Once that is imported, go back to slapd.conf and unrem or add the 'sql' part. For example:

#######################################################################
# sql database definitions
#######################################################################
database	sql
suffix		"ou=G1Users,ou=G1,ou=Groups,dc=example,dc=com"
rootdn		"cn=manager,dc=example,dc=com"
rootpw		secret
dbname		test
dbuser		nobody
dbpasswd  secret
subtree_cond    "ldap_entries.dn LIKE CONCAT('%',?)"
insentry_query  "INSERT INTO ldap_entries (dn,oc_map_id,parent,keval)
VALUES (?,?,?,?)"
 
database	sql
suffix		"ou=G2Users,ou=G2,ou=Groups,dc=example,dc=com"
rootdn		"cn=manager,dc=example,dc=com"
rootpw		secret
dbname		test
dbuser		nobody
dbpasswd  secret
subtree_cond    "ldap_entries.dn LIKE CONCAT('%',?)"
insentry_query  "INSERT INTO ldap_entries (dn,oc_map_id,parent,keval)
VALUES (?,?,?,?)"

For the back-sql specific tables in the database here is the example data:
orgunit table:

id name
1 G1Users
2 G2Users

persons table:
id FirstName LastName
1 Nobody Nowhere

others table:
id FirstName LastName
1 Sombody Here

ldap_oc_mappings:
id name keytbl keycol create_proc delete_proc expect_return
1 organizationalUnit orgunit id     0
2 inetOrgPerson persons id     0

ldap_attr_mappings (check 3's oc_map_id & that it is in a different table):
id oc_map_id name sel_expr from_tbls
1 1 ou name orgunit
2 2 sn LastName persons
3 2 sn LastName others

ldap_entries (note: I use uid which relates back to the primary key of that entry, but you don't have to):
id dn oc_map_id parent keyval
1 ou=G1Users,ou=G1,ou=Groups,dc=example,dc=com 1 0 1
2 uid=1,ou=G1Users,ou=G1,ou=Groups,dc=example,dc=com 2 1 1
10 ou=G2Users,ou=G2,ou=Groups,dc=example,dc=com 1 0 2
11 uid=1,ou=G2Users,ou=G2,ou=Groups,dc=example,dc=com 3 10 1

This may not have been the best way, but it was the easiest considering the tables had to be kept in the same database.