LDAP powered by MySQL on RHEL5

February 23, 2009 · by nathan · in Figuring IT Out

Background

We need a quick-and-dirty LDAP server with basically a list of usernames and passwords to point Cisco ACS gear at, in order to provide people with limited-time authentication tokens to a wireless network. I need to populate that with an HL7 interface in Perl and make a web app UI frontend (preferably using symfony). The latter two interfaces are well established around here with MySQL, so I am trying to use OpenLDAP’s SQL backend to make that happen naturally.

Simplifications

Unlike other similar implementations out there, I have smaller goals:

The database will be updated/written by the HL7 interface and web interface. The LDAP tools don’t need to write to it.
All my user data should fit nicely into two tables (users and groups… and another table to relate many-to-many), and two Objectclasses in LDAPland.

Resources

These were unexpectedly hard to Google up, which is partly why I’m documenting them here for Posterity.

Zytrax has an open source book about LDAP entitled LDAP for Rocket Scientists, which has an excellent chapter about LDAP concepts, which is nice since I don’t really understand LDAP like I do the other technologies involved.
Section “10.10.2. back-sql Configuration” of the OpenLDAP manual
The slapd-sql man page is indispensible. See especially the “METAINFORMATION USED” section.
The README that comes with the back-sql samples
The samples themselves (either unpack servers/slapd/back-sql/rdbms_depend/mysql from the source tarball or browse them, but you’ll probably want them unpacked so you can feed the schema to the mysql client. MOST OF WHAT YOU NEED TO LEARN is demonstrated in the samples.
This 4-year-old blog article at/by “Flat Mountain” is sketchy but an improvement over the documents above!
Albert Lash at DocuNext has a 2-year-old post similar to this one with another list of fine references!
This HOWTO for OpenLDAP and Postgresql by Gilles Darold is far superior than the other articles, but needs adaptation of course for MySQL
Likewise, here’s a good article for doing the same thing with Oracle/MSSQL
And here are some extended notes from someone who did it with Oracle, including another explanation of the data mapping part.

I. Install Stuff

I’m using RHEL5 on a virtual machine. Your package system may vary. You may in fact have to compile from source to get SQL support in LDAP.

$ sudo yum install openldap openldap-clients openldap-servers openldap-devel openldap-servers-sql

Update July 2012: On a newer RHEL 5 test machine earlier this year I had to:

sudo yum install openldap openldap-clients openldap-servers openldap-devel openldap-servers-sql
sudo yum install unixODBC unixODBC-libs unixODBC-devel -y
 
# downloaded modern mysql-connector-odbc RPM from MySQL, and installed with 
sudo rpm -ivh install/mysql-connector-odbc-5.1.10-1.rhel5.i386.rpm

II. Get and unpack the source code for the MySQL samples

Download the source tarball to your machine
Unpack it
tar zxf openldap-stable-20080813.tgz
tar zxf openldap-stable-20080813.tgz

III. Create the database and tables

Make sure the mysql server is running of course. We’re assuming it’s on the same host as the LDAP server.

Create the database (my_db_name) and a password-having user (my_mysql_user) with all privileges to that database (I recommend the MySQL Administrator GUI for this, strangely enough since I usually dislike clicking on stuff). You should be able to connect to your database like this now:

$ mysql -umy_mysql_user -pxxxxxxxxxxxxx  my_db_name

Build the tables that configure how OpenLDAP talks to the database.

cd into servers/slapd/back-sql/rdbms_depend/mysql from the unpacked openldap source
Feed backsql_create.sql to the ‘mysql’ client
$ mysql -umy_mysql_user -pxxxxxxxxxxxxx my_db_name < backsql_create.sql
$ mysql -umy_mysql_user -pxxxxxxxxxxxxx my_db_name < backsql_create.sql

IV. Hack the basic config files

a. `slapd.conf`

Basically I’m starting from the slapd.conf in the rdbms_depend/mysql sample (see above), but folding in some RedHatese file locations from the distributed slapd.conf.

I define an adminstrative user with the rootdn and rootpw directives. Use slappasswd to generate the hashed password:

$ slappasswd -h {crypt}

and type in the password. It will spit out the string you need, which is prefixed by the algorithm in curly braces.

# UPDATED July 2012
# 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
 
pidfile         /var/run/openldap/slapd.pid
argsfile        /var/run/openldap/slapd.args
# Timeout in seconds, 0 = never
idletimeout     30
threads         32
# Debuging level, 0 = none
loglevel        32
 
# THIS IS IMPORTANT-- without it you will get "<database> failed init (sql)!"
# Load dynamic backend modules:
modulepath    /usr/lib/openldap
# modules available in openldap-servers-sql RPM package:
moduleload back_sql.la
 
 
#######################################################################
# SQL Database Definitions
#######################################################################
 
database        sql
suffix          "ou=Junk,ou=AK,DC=mycompany,DC=com"
rootdn          "cn=root,ou=Users,ou=Junk,ou=AK,DC=,mycompany,DC=com"
rootpw          {CRYPT}xxxxxxxxxxxx
 
#  The name of the ODBC datasource (from odbc.ini,
#     not necessarily the database instance) to use.
dbname          my_database
dbuser          my_database_user
dbpasswd        xxxxxxxxxxxxxxxx
 
 
# As of April 2012 our database contains baseobjects so we don't need
# BaseObject.
 
# this is important because my simple view for the ldap_entries table
# has no 'organization' object.  See `man slapd-sql`.
# baseObject
 
 
subtree_cond    "ldap_entries.dn LIKE CONCAT('%',?)"
has_ldapinfo_dn_ru      no
 
 
#######################################################################
# Access Control
#    see http://www.openldap.org/doc/admin24/access-control.html
#    and `man slapd.access`
#######################################################################
 
# Here we define who can see which parts of the directory
 
# Hide the password attribute (except for the root user of course) but
#   allow authentication against it
access to attrs=userPassword
        by anonymous auth
        by * none
 
# don't allow anonymous access
# allow authenticated users to see their own entry
access to *
        by self read
        by * none

# UPDATED July 2012 # 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 pidfile /var/run/openldap/slapd.pid argsfile /var/run/openldap/slapd.args # Timeout in seconds, 0 = never idletimeout 30 threads 32 # Debuging level, 0 = none loglevel 32 # THIS IS IMPORTANT-- without it you will get "<database> failed init (sql)!" # Load dynamic backend modules: modulepath /usr/lib/openldap # modules available in openldap-servers-sql RPM package: moduleload back_sql.la ####################################################################### # SQL Database Definitions ####################################################################### database sql suffix "ou=Junk,ou=AK,DC=mycompany,DC=com" rootdn "cn=root,ou=Users,ou=Junk,ou=AK,DC=,mycompany,DC=com" rootpw {CRYPT}xxxxxxxxxxxx # The name of the ODBC datasource (from odbc.ini, # not necessarily the database instance) to use. dbname my_database dbuser my_database_user dbpasswd xxxxxxxxxxxxxxxx # As of April 2012 our database contains baseobjects so we don't need # BaseObject. # this is important because my simple view for the ldap_entries table # has no 'organization' object. See `man slapd-sql`. # baseObject subtree_cond "ldap_entries.dn LIKE CONCAT('%',?)" has_ldapinfo_dn_ru no ####################################################################### # Access Control # see http://www.openldap.org/doc/admin24/access-control.html # and `man slapd.access` ####################################################################### # Here we define who can see which parts of the directory # Hide the password attribute (except for the root user of course) but # allow authentication against it access to attrs=userPassword by anonymous auth by * none # don't allow anonymous access # allow authenticated users to see their own entry access to * by self read by * none

I started without the Access Control section and added it after I had things basically working.

b. ODBC config files

In my case they are in /etc/odbc.ini and /etc/odbcinst.ini already existed and just needed a MySQL section added to odbcinst.ini and a section for this particular database in odbc.ini, but I had to get the paths right for Red Hat. See the MySQL section of step 2 of the samples README document

UPDATE July 2012: You will likely need different versions of these drivers, 64-bit, etc.

/etc/odbcinst.ini:

# Driver from the MyODBC package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc3.so  # note, filename includes a 3, differs from docs
Setup           = /usr/lib/libodbcmyS.so
FileUsage       = 1

/etc/odbc.ini

[my_database]
Description         = my database
Driver              = MySQL
Trace               = No
Database            = my_db_name
Servername          = localhost
UserName            = my_database_user
Password            = xxxxxxxxxxxxxxxxx
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
ConnSettings        =
SOCKET              = /var/lib/mysql/mysql.sock

(OK, here’s where I dinked with the example database and read lots of examples and stuff, but hopefully you can skip that because of this excellent documentation 😉 )

V. Create and populate your user and group tables

These are my basic tables of users and groups:

CREATE TABLE my_user (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(45) NOT NULL,
  password VARCHAR(45) NOT NULL,
  first_name VARCHAR(45) DEFAULT NULL,
  last_name VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY  (id,username)
);
 
CREATE TABLE my_group (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY  (id)
);
 
CREATE TABLE my_group_member (
  -- unique id isn't necessary but easier for symfony
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  my_user_id INT(10) UNSIGNED NOT NULL,
  my_group_id VARCHAR(45) NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY uniquepair (my_user_id,my_group_id)
);
 
-- This part added in 2012
CREATE TABLE org_unit ( id INT PRIMARY KEY NOT NULL, o VARCHAR(32), dn VARCHAR(255) UNIQUE, parent INT);
INSERT INTO org_unit VALUES 
    (1, 'Parent', 'ou=Parent,ou=AK,DC=junk,DC=mycompany,DC=com', 0), 
    (2, 'Users', 'ou=Users,ou=Parent,ou=AK,DC=junk,DC=mycompany,DC=com', 1),
    (3, 'Groups', 'ou=Groups,ou=Parent,ou=AK,DC=junk,DC=mycompany,DC=com', 1)
    ;
INSERT INTO ldap_attr_mappings VALUES(NULL, 3, 'o', 'o', NULL, 'org_unit', NULL, NULL, NULL, 3, 0);
 
INSERT INTO ldap_attr_mappings VALUES('12', '3', 'objectClass',
'oc_name', NULL, 'ldap_entry_objclasses', 'entry_id = 3', NULL, NULL,
'3', '0' );
 
-- make the ldap_entries view fasterer
ALTER TABLE `my_user` ADD INDEX `expiry` (`expires` ASC, `id` ASC, `username`, ASC) ;

You’ll need to populate the tables with a user or two and at least one group, of course.

VI. Insert the special configuration rows

I entered these data sets using the MySQL Administrator GUI. The most helpful explanation was in the “METAINFORMATION USED” section of the slapd-sql man page.

Basically, the ldap_* tables show the SQL backend how to map the database tables onto LDAP objects. This is a nice reference to the common LDAP object schemas. That’s important because you can only map your database fields to fields in the schema for the objectClass you’re using (unless you want to start making your own schema, which is beyond my scope here). In my case I’m using inetOrgPerson, which inherits from organizationalPerson, which inherits from Person, and for groups, groupOfUniqueNames.

The ldap_entry_objclasses table needs one row for each objectClass we’re using:

entry_id	oc_name
1	inetOrgPerson
2	groupOfUniqueNames

The ldap_oc_mappings table also has a row for each objectClass, and it shows the sql backend how to find objects corresponding to those object classes.

id	name	keytbl	keycol	create_proc	delete_proc	expect_return
1	inetOrgPerson	my_user	id			0
2	groupOfUniqueNames	my_group	id			0

The ldap_attr_mappings table is the most complex part. Basically you’re showing the sql backend how to build queries that map the database columns onto LDAP attributes.

id	oc_map_id	name	sel_expr	from_tbls	join_where	param_order
1	1	cn	concat( first_name, ‘ ‘, last_name )	my_user		3
2	1	givenName	first_name	my_user		3
3	1	sn	last_name	my_user		3
4	1	userPassword	password	my_user		3
5	1	uid	username	my_user		3
7	2	cn	name	my_group		3
8	2	uniqueMember	dn	my_group, my_group_member gu, my_user u, ldap_entries	my_group.id = gu.my_group_id and gu.my_user_id = u.id and oc_map_id = 1 and keyval = u.id	3

Create a view for the DNs in ldap_entries

I’m going to use the username field in my DN instead of the common name (CN) in most examples, because it’s more guaranteed to be unique, and it is apparently OK to do so.

 
CREATE VIEW ldap_entries AS 
 
SELECT 
    org_unit.id AS id,
    UCASE(org_unit.dn) AS dn,
    3 AS oc_map_id,
    org_unit.parent AS parent,
    org_unit.id AS keyval
FROM
    org_unit
 
 
UNION 
 
SELECT 
    (1000000+my_user.id) AS id,
    UCASE(CONCAT('uid=',
                    my_user.username,
                    ',ou=Users,ou=Parent,ou=AK,DC=Junk,DC=mycompany,DC=com')) AS dn,
    1 AS oc_map_id,
    0 AS parent,
    my_user.id AS keyval
FROM
    my_user
WHERE
    (ISNULL(my_user.expires) OR (my_user.expires > NOW()))
 
UNION 
 
SELECT 
 
    (9000000+my_group.id) AS id,
    UCASE(CONCAT( 'cn=',
                    my_group.name,
                    ',ou=Groups,ou=Parent,ou=AK,DC=Junk,DC=mycompany,DC=com')) AS dn,
    2 AS oc_map_id,
    3 AS parent,
    my_group.id AS id
FROM
    my_group

VII. Test and stuff

Launch the Slap daemon

It’s nice to launch slapd in debug mode in one terminal. This is pretty verbose and will hopefully tell you if you’ve got something wrong in your mapping tables or anything:

sudo /usr/sbin/slapd -d 5

Test connectivity and data with an LDAP client

Hint: it would help to LEARN HOW TO USE ldapsearch correctly.

Connect anonymously and spit out all the LDAP data (if ACL allows it– by slapd.conf above allows anonymous connections but won’t show any of the data):

ldapsearch -x -s sub -b "ou=Junk,ou=AK,DC=junk,DC=mycompany,DC=com" "(objectClass=*)"

Connect as root (the special account defined in slapd.conf) and show all the data in the directory:

# UPDATED JULY 2012
ldapsearch -x -D uid=root,ou=Users,ou=Parent,ou=AK,DC=junk,DC=mycompany,DC=com -w $LDAP_ROOT_PW -s sub -b "ou=Parent,ou=AK,DC=junk,DC=mycompany,DC=com" "(objectClass=*)"

Here’s an example of what it shows me:

# EXAMPLE UPDATED JULY 2012
 
# extended LDIF
#
# LDAPv3
# base <ou=Parent,ou=AK,DC=junk,DC=mycompany,DC=com> with scope subtree
# filter: (objectClass=*)
# requesting: ALL
#
 
# USER1, USERS, GUESTWIRELESS, AK, JUNK.MYCOMPANY.COM
dn: uid=USER1,ou=USERS,ou=GUESTWIRELESS,ou=AK,dc=JUNK,dc=MYCOMPANY,dc=COM
objectClass: inetOrgPerson
cn: User Test
ou: Test
sn: Test
uid: user1
givenName: User
userPassword:: MjAwMTM0Mzk=
 
# USER2, USERS, GUESTWIRELESS, AK, JUNK.MYCOMPANY.COM
dn: uid=USER2,ou=USERS,ou=GUESTWIRELESS,ou=AK,dc=JUNK,dc=MYCOMPANY,dc=COM
objectClass: inetOrgPerson
cn: FOO BAR
ou: Test
sn: BAR
uid: USER2
givenName: FOO
userPassword:: MjAwMTM0Mzk=
 
 
# ...
 
 
# VALIDUSERS, GROUPS, GUESTWIRELESS, AK, JUNK.MYCOMPANY.COM
dn: cn=VALIDUSERS,ou=GROUPS,ou=GUESTWIRELESS,ou=AK,dc=JUNK,dc=MYCOMPANY,dc=C
 OM
objectClass: groupOfUniqueNames
cn: validUsers
uniqueMember: uid=USER1,ou=USERS,ou=GUESTWIRELESS,ou=AK,dc=JUNK,dc=MYCOMPANY,dc=COM
uniqueMember: uid=USER2,ou=USERS,ou=GUESTWIRELESS,ou=AK,dc=JUNK,dc=MYCOMPANY,dc=COM
uniqueMember: uid=USER3,ou=USERS,ou=GUESTWIRELESS,ou=AK,dc=JUNK,dc=MYCOMPANY,dc=COM
uniqueMember: uid=USER4,ou=USERS,ou=GUESTWIRELESS,ou=AK,dc=JUNK,dc=MYCOMPANY,dc=COM
# ...
 
 
# PARENT, AK, JUNK.MYCOMPANY.COM
dn: ou=PARENT,ou=AK,dc=JUNK,dc=MYCOMPANY,dc=COM
objectClass: organizationalUnit
o: Parent
 
# USERS, PARENT, AK, JUNK.MYCOMPANY.COM
dn: ou=USERS,ou=PARENT,ou=AK,dc=JUNK,dc=MYCOMPANY,dc=COM
objectClass: organizationalUnit
o: Users
 
# GROUPS, PARENT, AK, JUNK.MYCOMPANY.COM
dn: ou=GROUPS,ou=PARENT,ou=AK,dc=JUNK,dc=MYCOMPANY,dc=COM
objectClass: organizationalUnit
o: Groups
 
 
# search result
search: 2
result: 0 Success
 
# numResponses: 8
# numEntries: 7

Connect as a valid user and ask for everything, but according to my ACLs the user can only see itself:

ldapsearch -x -D uid=TESTUSER,ou=USERS,OU=JUNK,OU=AK,DC=MYCOMPANY,DC=COM -w testpass -s sub -b "ou=Junk,ou=AK,DC=mycompany,DC=com" "(objectClass=*)"

Additional Considerations

chkconfig is the Red Hat tool for managing init scripts– here’s how to make sure the slapd starts at boot time.

$ sudo chkconfig --add ldap
$ sudo chkconfig ldap on

Ah, Mr. Flat Mountain reminds,

9) Ports for the Firewall LDAP runs on port 389/tcp by default and LDAP over SSL is 636/tcp.

Securing communication between the LDAP client and OpenLDAP server with SSL/TLS is beyond my scope here, but it seems to be much better documented.

Good luck!

Tags: LDAP, Linux, MySQL, Perl, symfony, sysadmin

11 Responses

nathan March 30, 2009 at 3:22 pm ·
Logging for successes and failures:

I only care about success and failure, not exhaustive debugging in the real log.
1. add this line to /etc/syslog.conf, so that stuff will actually go to a log, /var/log/slapd.log:
  local4.* /var/log/slapd.log
  local4.* /var/log/slapd.log
2. Turn the log level to 256 in /etc/openldap/slapd.conf
  loglevel 256
  loglevel 256
3. Restart slapd and syslogd:
  sudo /sbin/service syslog restart sudo /sbin/service ldap restart
  sudo /sbin/service syslog restart sudo /sbin/service ldap restart
4. Now you can grep and tail that file and stuff. This line indicates an authentication success:
```
Mar 30 16:15:49 fai0v1338 slapd[29584]: conn=0 op=0 RESULT tag=97 err=0 text=
```
  This line indicates an authentication failure:
```
Mar 30 16:16:01 fai0v1338 slapd[29584]: conn=1 op=0 RESULT tag=97 err=49 text=
```
nathan March 29, 2010 at 5:55 pm ·
Oh, you should probably also edit the chkconfig levels in the header to /etc/init.d/ldap so that slapd starts after mysql, and stops before it.

This works in my case.
```
# chkconfig: - 97 33
```
Gaurav Talwar July 22, 2010 at 4:24 pm ·

Haven’t done this yet.. But excellent documentation anyways!
Tárikly Távora April 6, 2011 at 8:33 am ·

Nice article, hope more people find it!

Regards,

Btw, this link is broken: http://www.docunext.com/blog/2006/10/31/openldap-mysql-documentation/
nathan April 6, 2011 at 9:07 am ·

Thanks, I’ve found and fixed that link.
Brian December 4, 2011 at 4:11 pm ·

Good work. Thanks alot.
aabed July 18, 2012 at 4:45 pm ·
each time i run the last sql for creating the view i get this error

RROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘2 AS oc_map_id,
```
0 AS parent,
my_group.id AS id 
```
FROM my’ at line 38 any help please
nathan July 18, 2012 at 9:27 pm ·

aabed, I’m missing a comma before the 2 (after AS dn), which is bad syntax!

A couple of months ago I had to revisit this. Tomorrow I will update this post with more recently revised code.
Richard E. July 12, 2013 at 5:30 am ·
Hi,

I am following your instructions and I get a error at this line: ALTER TABLE my_user ADD INDEX expiry (expires ASC, id ASC, username, ASC) ;

The problem seems to be that the freshly created table “my_user” does not contain any column “expires’ that can be indexed.

Or am I misunderstanding something?

Thanks,
- Richard E.
Joseph Carroll August 28, 2013 at 10:29 am ·

I am new to OpenLDAP and I have found your resources here very valuable, thank you for your documentation and the external resources. So far I have been able to figure out most of what is going on, however, I am having some difficulties with the ldap_entries and org_unit tables.

Would you be able to share your ldap_entries table for this example? I’m not really following the distinguished name (DN) and how that relates back to the org_unit table.

Thanks,

JD
nathan August 28, 2013 at 9:54 pm ·

Joseph, ldap_entries is a view, not a table. See the CREATE VIEW ldap_entries section above for the definition. Basically, it builds a virtual view from the other tables with a unique hierarchical DN for each user and group. This is what translates from “relationalese” of the MySQL world to the “heirarchicalese” of the LDAP world.

Once you create the view, you can query it from MySQL like SELECT * FROM ldap_entries to get a feel for how it works. I am on vacation right now but will try to post some example data when I return to work next Tuesday.

Comments are closed.

LDAP powered by MySQL on RHEL5

Background

Simplifications

Resources

I. Install Stuff

II. Get and unpack the source code for the MySQL samples

III. Create the database and tables

IV. Hack the basic config files

a. `slapd.conf`

b. ODBC config files

V. Create and populate your user and group tables

VI. Insert the special configuration rows

Create a view for the DNs in ldap_entries

VII. Test and stuff

Launch the Slap daemon

Test connectivity and data with an LDAP client

Additional Considerations

11 Responses

Can you help me too?

Search

I’ve written about

Recently del.icio.us

Spam Blocked

Meta

LDAP powered by MySQL on RHEL5

Background

Simplifications

Resources

I. Install Stuff

II. Get and unpack the source code for the MySQL samples

III. Create the database and tables

IV. Hack the basic config files

a. slapd.conf

b. ODBC config files

V. Create and populate your user and group tables

VI. Insert the special configuration rows

Create a view for the DNs in ldap_entries

VII. Test and stuff

Launch the Slap daemon

Test connectivity and data with an LDAP client

Additional Considerations

11 Responses

Can you help me too?

Search

I’ve written about

Recently del.icio.us

Spam Blocked

Meta

a. `slapd.conf`