LDAP powered by MySQL on RHEL5

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.

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

  1. Download the source tarball to your machine
  2. Unpack it
    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.

  1. cd into servers/slapd/back-sql/rdbms_depend/mysql from the unpacked openldap source
  2. Feed backsql_create.sql to the ‘mysql’ client
    $ 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

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_idoc_name
1inetOrgPerson
2groupOfUniqueNames

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.

idnamekeytblkeycolcreate_procdelete_procexpect_return
1inetOrgPersonmy_userid0
2groupOfUniqueNamesmy_groupid0

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.

idoc_map_idnamesel_exprsel_expr_ufrom_tblsjoin_whereadd_procdelete_procparam_orderexpect_return
11cnconcat( first_name, ‘ ‘, last_name )my_user30
21givenNamefirst_namemy_user30
31snlast_namemy_user30
41userPasswordpasswordmy_user30
51uidusernamemy_user30
72cnnamemy_group30
82uniqueMemberdnmy_group, my_group_member gu, my_user u, ldap_entriesmy_group.id = gu.my_group_id and gu.my_user_id = u.id and oc_map_id = 1 and keyval = u.id30

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!