I am trying to get OpenLDAP 2.4.39 working with MySQL using back-sql to
query the database for a specific application. I have spent weeks on this
and I feel like I am very close. The database is for vpopmail, I realize
that there is an ldap auth module for vpopmail, however it is not very well
supported so I hesitate to jump to that in a production system. I have it
90% working I would say, just missing a few minor things I believe. I was
able to get the OpenLDAP example includes in the docs working so I know it's
something with my configuration specifically that is not correct. I would
greatly appreciate if anyone could see something wrong with my setup. I
have included all relevant configuration files as well as the MySQL vpopmail
database information.
This is the slapd.conf file:
include /etc/ldap/schema/core.schema
include /etc/ldap/schema/cosine.schema
include /etc/ldap/schema/inetorgperson.schema
pidfile /var/run/slapd/slapd.pid
argsfile /var/run/slapd/slapd.args
idletimeout 30
threads 32
loglevel 0xFFFF
modulepath /usr/lib/ldap
moduleload back_sql.la
database sql
suffix "dc=example,dc=com"
rootdn "cn=root,dc=example,dc=com"
rootpw {CRYPT}rootpassword
dbname vpopmail
dbuser vpopmail
dbpasswd somepassword
subtree_cond "ldap_entries.dn LIKE CONCAT('%',?)"
has_ldapinfo_dn_ru no
There is an /etc/odbc.ini file and /etc/odbcinst.ini file, but the issue is
not the connection, so that should be irrelevant.
When running in debug mode here is the relevant part of the console log when
executing:
ldapsearch -x -s sub -b "UID=ADWORDS(a)EXAMPLE.COM,DC=EXAMPLE,DC=COM"
"(objectClass=*)"
5383972d slapd startup: initiated.
5383972d backend_startup_one: starting "cn=config"
5383972d config_back_db_open
5383972d config_build_entry: "cn=config"
5383972d config_build_entry: "cn=module{0}"
5383972d config_build_entry: "cn=schema"
5383972d >>> dnNormalize: <cn={0}core>
5383972d <<< dnNormalize: <cn={0}core>
5383972d config_build_entry: "cn={0}core"
5383972d >>> dnNormalize: <cn={1}cosine>
5383972d <<< dnNormalize: <cn={1}cosine>
5383972d config_build_entry: "cn={1}cosine"
5383972d >>> dnNormalize: <cn={2}inetorgperson>
5383972d <<< dnNormalize: <cn={2}inetorgperson>
5383972d config_build_entry: "cn={2}inetorgperson"
5383972d config_build_entry: "olcDatabase={-1}frontend"
5383972d config_build_entry: "olcDatabase={0}config"
5383972d config_build_entry: "olcDatabase={1}sql"
5383972d backend_startup_one: starting "dc=example,dc=com"
5383972d ==>backsql_db_open(): testing RDBMS connection
5383972d backsql_db_open(): concat func not specified (use "concat_pattern"
directive in slapd.conf)
5383972d backsql_db_open(): children search SQL condition not specified (use
"children_cond" directive in slapd.conf); preparing default
5383972d backsql_db_open(): setting "ldap_entries.dn LIKE CONCAT('%,',?)" as
default "children_cond"
5383972d backsql_db_open(): DN match search SQL condition not specified (use
"dn_match_cond" directive in slapd.conf); preparing default
5383972d backsql_db_open(): setting "ldap_entries.dn=?" as default
"dn_match_cond"
5383972d backsql_db_open(): objectclass mapping SQL statement not specified
(use "oc_query" directive in slapd.conf)
5383972d backsql_db_open(): setting "SELECT
id,name,keytbl,keycol,create_proc,delete_proc,expect_return FROM
ldap_oc_mappings" by default
5383972d backsql_db_open(): attribute mapping SQL statement not specified
(use "at_query" directive in slapd.conf)
5383972d backsql_db_open(): setting "SELECT
name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_r
eturn,sel_expr_u FROM ldap_attr_mappings WHERE oc_map_id=?" by default
5383972d backsql_db_open(): entry insertion SQL statement not specified (use
"insentry_stmt" directive in slapd.conf)
5383972d backsql_db_open(): setting "INSERT INTO ldap_entries
(dn,oc_map_id,parent,keyval) VALUES (?,?,?,?)" by default
5383972d backsql_db_open(): entry deletion SQL statement not specified (use
"delentry_stmt" directive in slapd.conf)
5383972d backsql_db_open(): setting "DELETE FROM ldap_entries WHERE id=?" by
default
5383972d backsql_db_open(): entry deletion SQL statement not specified (use
"renentry_stmt" directive in slapd.conf)
5383972d backsql_db_open(): setting "UPDATE ldap_entries SET
dn=?,parent=?,keyval=? WHERE id=?" by default
5383972d backsql_db_open(): objclasses deletion SQL statement not specified
(use "delobjclasses_stmt" directive in slapd.conf)
5383972d backsql_db_open(): setting "DELETE FROM ldap_entry_objclasses WHERE
entry_id=?" by default
5383972d ==>backsql_get_db_conn()
5383972d ==>backsql_open_db_handle()
5383972d <==backsql_open_db_handle()
5383972d <==backsql_get_db_conn()
5383972d ==>backsql_load_schema_map()
5383972d backsql_load_schema_map(): oc_query "SELECT
id,name,keytbl,keycol,create_proc,delete_proc,expect_return FROM
ldap_oc_mappings"
5383972d objectClass: id="1" name="inetOrgPerson" keytbl="vpopmail"
keycol="id" create_proc="" create_keyval="" delete_proc=""
expect_return="0"create_hint=""
5383972d backsql_load_schema_map(): objectClass "inetOrgPerson":
keytbl="vpopmail" keycol="id"
5383972d expect_return: add=0, del=0; attributes:
5383972d objectClass: id="2" name="groupOfUniqueNames" keytbl="vpopmail"
keycol="id" create_proc="" create_keyval="" delete_proc=""
expect_return="0"create_hint=""
5383972d backsql_load_schema_map(): objectClass "groupOfUniqueNames":
keytbl="vpopmail" keycol="id"
5383972d expect_return: add=0, del=0; attributes:
5383972d backsql_load_schema_map(): at_query "SELECT
name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_r
eturn,sel_expr_u FROM ldap_attr_mappings WHERE oc_map_id=?"
5383972d backsql_oc_get_attr_mapping(): executing at_query
"SELECT
name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_r
eturn,sel_expr_u FROM ldap_attr_mappings WHERE oc_map_id=?"
for objectClass "inetOrgPerson"
with param oc_id=1
5383972d attributeType: name="cn" sel_expr="pw_gecos" from="vpopmail"
join_where="" add_proc="" delete_proc="" sel_expr_u=""
5383972d backsql_oc_get_attr_mapping(): preconstructed query "SELECT
pw_gecos AS cn FROM vpopmail WHERE vpopmail.id=? ORDER BY cn"
5383972d attributeType: name="givenName"
sel_expr="SUBSTRING_INDEX(`pw_gecos`, ' ', 1)" from="vpopmail" join_where=""
add_proc="" delete_proc="" sel_expr_u=""
5383972d backsql_oc_get_attr_mapping(): preconstructed query "SELECT
SUBSTRING_INDEX(`pw_gecos`, ' ', 1) AS givenName FROM vpopmail WHERE
vpopmail.id=? ORDER BY givenName"
5383972d attributeType: name="sn" sel_expr="SUBSTRING_INDEX(`pw_gecos`, ' ',
-1)" from="vpopmail" join_where="" add_proc="" delete_proc="" sel_expr_u=""
5383972d backsql_oc_get_attr_mapping(): preconstructed query "SELECT
SUBSTRING_INDEX(`pw_gecos`, ' ', -1) AS sn FROM vpopmail WHERE vpopmail.id=?
ORDER BY sn"
5383972d attributeType: name="userPassword" sel_expr="pw_passwd"
from="vpopmail" join_where="" add_proc="" delete_proc="" sel_expr_u=""
5383972d backsql_oc_get_attr_mapping(): preconstructed query "SELECT
pw_passwd AS userPassword FROM vpopmail WHERE vpopmail.id=? ORDER BY
userPassword"
5383972d attributeType: name="uid" sel_expr="CONCAT(pw_name,'@',pw_domain)"
from="vpopmail" join_where="" add_proc="" delete_proc="" sel_expr_u=""
5383972d backsql_oc_get_attr_mapping(): preconstructed query "SELECT
CONCAT(pw_name,'@',pw_domain) AS uid FROM vpopmail WHERE vpopmail.id=? ORDER
BY uid"
5383972d backsql_load_schema_map("inetOrgPerson"): autoadding 'objectClass'
and 'ref' mappings
5383972d backsql_oc_get_attr_mapping(): executing at_query
"SELECT
name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_r
eturn,sel_expr_u FROM ldap_attr_mappings WHERE oc_map_id=?"
for objectClass "groupOfUniqueNames"
with param oc_id=2
5383972d attributeType: name="cn" sel_expr="UNIQUE(pw_domain)"
from="vpopmail" join_where="" add_proc="" delete_proc="" sel_expr_u=""
5383972d backsql_oc_get_attr_mapping(): preconstructed query "SELECT
UNIQUE(pw_domain) AS cn FROM vpopmail WHERE vpopmail.id=? ORDER BY cn"
5383972d backsql_load_schema_map("groupOfUniqueNames"): autoadding
'objectClass' and 'ref' mappings
5383972d <==backsql_load_schema_map()
5383972d ==>backsql_free_db_conn()
5383972d ==>backsql_close_db_handle(0x7f9f7427da70)
5383972d <==backsql_close_db_handle(0x7f9f7427da70)
5383972d <==backsql_free_db_conn()
5383972d <==backsql_db_open(): test succeeded, schema map loaded
5383972d slapd starting
53839731 slap_listener_activate(8):
53839731 >>> slap_listener(ldap:///)
53839731 connection_get(10)
53839731 connection_get(10): got connid=1000
53839731 connection_read(10): checking for input on id=1000
ber_get_next
ber_get_next: tag 0x30 len 12 contents:
53839731 op tag 0x60, time 1401132849
ber_get_next
53839731 conn=1000 op=0 do_bind
ber_scanf fmt ({imt) ber:
ber_scanf fmt (m}) ber:
53839731 >>> dnPrettyNormal: <>
53839731 <<< dnPrettyNormal: <>, <>
53839731 do_bind: version=3 dn="" method=128
53839731 send_ldap_result: conn=1000 op=0 p=3
53839731 send_ldap_result: err=0 matched="" text=""
53839731 send_ldap_response: msgid=1 tag=97 err=0
ber_flush2: 14 bytes to sd 10
53839731 do_bind: v3 anonymous bind
53839731 connection_get(10)
53839731 connection_get(10): got connid=1000
53839731 connection_read(10): checking for input on id=1000
ber_get_next
ber_get_next: tag 0x30 len 76 contents:
53839731 op tag 0x63, time 1401132849
ber_get_next
53839731 conn=1000 op=1 do_search
ber_scanf fmt ({miiiib) ber:
53839731 >>> dnPrettyNormal: <UID=ADWORDS(a)EXAMPLE.COM,DC=EXAMPLE,DC=COM>
=> ldap_bv2dn(UID=ADWORDS(a)EXAMPLE.COM,DC=EXAMPLE,DC=COM,0)
<= ldap_bv2dn(UID=ADWORDS(a)EXAMPLE.COM,DC=EXAMPLE,DC=COM)=0
=> ldap_dn2bv(272)
<= ldap_dn2bv(uid=ADWORDS(a)EXAMPLE.COM,dc=EXAMPLE,dc=COM)=0
=> ldap_dn2bv(272)
<= ldap_dn2bv(uid=adwords(a)example.com,dc=example,dc=com)=0
53839731 <<< dnPrettyNormal: <uid=ADWORDS(a)EXAMPLE.COM,dc=EXAMPLE,dc=COM>,
<uid=adwords(a)example.com,dc=example,dc=com>
53839731 SRCH "UID=ADWORDS(a)EXAMPLE.COM,DC=EXAMPLE,DC=COM" 2 053839731 0
0 0
ber_scanf fmt (m) ber:
53839731 filter: (objectClass=*)
ber_scanf fmt ({M}}) ber:
53839731 attrs:53839731
53839731 ==> limits_get: conn=1000 op=1 self="[anonymous]"
this="uid=adwords(a)example.com,dc=example,dc=com"
53839731 ==>backsql_search():
base="uid=adwords(a)example.com,dc=example,dc=com", filter="(objectClass=*)",
scope=2,53839731 deref=0, attrsonly=0, attributes to load: all
53839731 ==>backsql_get_db_conn()
53839731 ==>backsql_open_db_handle()
53839731 <==backsql_open_db_handle()
53839731 <==backsql_get_db_conn()
53839731 ==>backsql_dn2id("uid=adwords(a)example.com,dc=example,dc=com")
matched expected
53839731 backsql_dn2id("uid=adwords(a)example.com,dc=example,dc=com"):
id_query "SELECT id,keyval,oc_map_id,dn FROM ldap_entries WHERE dn=?"
53839731 backsql_dn2id("uid=adwords(a)example.com,dc=example,dc=com"):
id=adwords(a)example.com keyval=adwords(a)example.com oc_id=1
dn=UID=ADWORDS(a)EXAMPLE.COM,DC=EXAMPLE,DC=COM
53839731 <==backsql_dn2id("uid=adwords(a)example.com,dc=example,dc=com"):
err=80
53839731 send_ldap_result: conn=1000 op=1 p=3
53839731 send_ldap_result: err=80 matched="" text=""
53839731 send_ldap_response: msgid=2 tag=101 err=80
ber_flush2: 14 bytes to sd 10
53839731 <==backsql_search()
53839731 connection_get(10)
53839731 connection_get(10): got connid=1000
53839731 connection_read(10): checking for input on id=1000
ber_get_next
ber_get_next: tag 0x30 len 5 contents:
53839731 op tag 0x42, time 1401132849
ber_get_next
53839731 ber_get_next on fd 10 failed errno=0 (Success)
53839731 conn=1000 op=2 do_unbind
53839731 connection_close: conn=1000 sd=10\
Here is the vpopmail database structure without any LDAP tables added:
--
-- Table structure for table `dir_control`
--
CREATE TABLE IF NOT EXISTS `dir_control` (
`domain` char(64) NOT NULL DEFAULT '',
`cur_users` int(11) DEFAULT NULL,
`level_cur` int(11) DEFAULT NULL,
`level_max` int(11) DEFAULT NULL,
`level_start0` int(11) DEFAULT NULL,
`level_start1` int(11) DEFAULT NULL,
`level_start2` int(11) DEFAULT NULL,
`level_end0` int(11) DEFAULT NULL,
`level_end1` int(11) DEFAULT NULL,
`level_end2` int(11) DEFAULT NULL,
`level_mod0` int(11) DEFAULT NULL,
`level_mod1` int(11) DEFAULT NULL,
`level_mod2` int(11) DEFAULT NULL,
`level_index0` int(11) DEFAULT NULL,
`level_index1` int(11) DEFAULT NULL,
`level_index2` int(11) DEFAULT NULL,
`the_dir` char(160) DEFAULT NULL,
PRIMARY KEY (`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `lastauth`
--
CREATE TABLE IF NOT EXISTS `lastauth` (
`user` char(32) NOT NULL DEFAULT '',
`domain` char(64) NOT NULL DEFAULT '',
`remote_ip` char(18) NOT NULL DEFAULT '',
`timestamp` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`user`,`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `relay`
--
CREATE TABLE IF NOT EXISTS `relay` (
`ip_addr` char(18) NOT NULL DEFAULT '',
`timestamp` char(12) DEFAULT NULL,
PRIMARY KEY (`ip_addr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `valias`
--
CREATE TABLE IF NOT EXISTS `valias` (
`alias` varchar(32) NOT NULL DEFAULT '',
`domain` varchar(64) NOT NULL DEFAULT '',
`valias_line` text NOT NULL,
KEY `alias` (`alias`,`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `vlog`
--
CREATE TABLE IF NOT EXISTS `vlog` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user` varchar(32) DEFAULT NULL,
`passwd` varchar(32) DEFAULT NULL,
`domain` varchar(64) DEFAULT NULL,
`logon` varchar(200) DEFAULT NULL,
`remoteip` varchar(18) DEFAULT NULL,
`message` varchar(255) DEFAULT NULL,
`timestamp` bigint(20) NOT NULL DEFAULT '0',
`error` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_idx` (`user`),
KEY `domain_idx` (`domain`),
KEY `remoteip_idx` (`remoteip`),
KEY `error_idx` (`error`),
KEY `message_idx` (`message`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21403 ;
-- --------------------------------------------------------
--
-- Table structure for table `vpopmail`
--
CREATE TABLE IF NOT EXISTS `vpopmail` (
`pw_name` char(32) NOT NULL,
`pw_domain` char(96) NOT NULL,
`pw_passwd` char(40) DEFAULT NULL,
`pw_uid` int(11) DEFAULT NULL,
`pw_gid` int(11) DEFAULT NULL,
`pw_gecos` char(48) DEFAULT NULL,
`pw_dir` char(160) DEFAULT NULL,
`pw_shell` char(20) DEFAULT NULL,
`pw_clear_passwd` char(16) DEFAULT NULL,
PRIMARY KEY (`pw_name`,`pw_domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Here are the tables added to the vpopmail database with their info:
--
-- Table structure for table `ldap_attr_mappings`
--
CREATE TABLE IF NOT EXISTS `ldap_attr_mappings` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`oc_map_id` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`sel_expr` varchar(255) NOT NULL,
`sel_expr_u` varchar(255) DEFAULT NULL,
`from_tbls` varchar(255) NOT NULL,
`join_where` varchar(255) DEFAULT NULL,
`add_proc` varchar(255) DEFAULT NULL,
`delete_proc` varchar(255) DEFAULT NULL,
`param_order` tinyint(4) NOT NULL,
`expect_return` tinyint(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
-- --------------------------------------------------------
--
-- Table structure for table `ldap_attr_mappings`
--
CREATE TABLE IF NOT EXISTS `ldap_attr_mappings` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`oc_map_id` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`sel_expr` varchar(255) NOT NULL,
`sel_expr_u` varchar(255) DEFAULT NULL,
`from_tbls` varchar(255) NOT NULL,
`join_where` varchar(255) DEFAULT NULL,
`add_proc` varchar(255) DEFAULT NULL,
`delete_proc` varchar(255) DEFAULT NULL,
`param_order` tinyint(4) NOT NULL,
`expect_return` tinyint(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
-- Dumping data for table `ldap_attr_mappings`
--
INSERT INTO `ldap_attr_mappings` (`id`, `oc_map_id`, `name`, `sel_expr`,
`sel_expr_u`, `from_tbls`, `join_where`, `add_proc`, `delete_proc`,
`param_order`, `expect_return`) VALUES
(1, 1, 'cn', 'pw_gecos', NULL, 'vpopmail', NULL, NULL, NULL, 3, 0),
(2, 1, 'givenName', 'SUBSTRING_INDEX(`pw_gecos`, '' '', 1)', NULL,
'vpopmail', NULL, NULL, NULL, 3, 0),
(3, 1, 'sn', 'SUBSTRING_INDEX(`pw_gecos`, '' '', -1)', NULL, 'vpopmail',
NULL, NULL, NULL, 3, 0),
(4, 1, 'userPassword', 'pw_passwd', NULL, 'vpopmail', NULL, NULL, NULL, 3,
0),
(5, 1, 'uid', 'CONCAT(pw_name,''@'',pw_domain)', NULL, 'vpopmail', NULL,
NULL, NULL, 3, 0),
(7, 2, 'cn', 'UNIQUE(pw_domain)', NULL, 'vpopmail', NULL, NULL, NULL, 3, 0);
-- --------------------------------------------------------
--
-- Table structure for table `ldap_entry_objclasses`
--
CREATE TABLE IF NOT EXISTS `ldap_entry_objclasses` (
`entry_id` int(11) NOT NULL,
`oc_name` varchar(64) DEFAULT NULL,
PRIMARY KEY (`entry_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `ldap_entry_objclasses`
--
INSERT INTO `ldap_entry_objclasses` (`entry_id`, `oc_name`) VALUES
(1, 'inetOrgPerson'),
(2, 'groupOfUniqueNames');
-- --------------------------------------------------------
--
-- Table structure for table `ldap_oc_mappings`
--
CREATE TABLE IF NOT EXISTS `ldap_oc_mappings` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`keytbl` varchar(64) NOT NULL,
`keycol` varchar(64) NOT NULL,
`create_proc` varchar(255) DEFAULT NULL,
`delete_proc` varchar(255) DEFAULT NULL,
`expect_return` tinyint(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `ldap_oc_mappings`
--
INSERT INTO `ldap_oc_mappings` (`id`, `name`, `keytbl`, `keycol`,
`create_proc`, `delete_proc`, `expect_return`) VALUES
(1, 'inetOrgPerson', 'vpopmail', 'id', NULL, NULL, 0),
(2, 'groupOfUniqueNames', 'vpopmail', 'id', NULL, NULL, 0);
This is the code used to create the ldap_entries view:
CREATE VIEW ldap_entries AS
SELECT
CONCAT(vpopmail.pw_name,'@',vpopmail.pw_domain) AS id,
UCASE(CONCAT('uid=',
vpopmail.pw_name,'@',vpopmail.pw_domain,
',DC=example,DC=com')) AS dn,
1 AS oc_map_id,
0 AS parent,
CONCAT(vpopmail.pw_name,'@',vpopmail.pw_domain) AS keyval
FROM
vpopmail
UNION
SELECT
vpopmail.pw_domain AS id,
UCASE(CONCAT( 'cn=',
vpopmail.pw_domain,
',DC=example,DC=com')) AS dn,
2 AS oc_map_id,
3 AS parent,
vpopmail.pw_domain AS id
FROM
vpopmail
