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@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@EXAMPLE.COM,DC=EXAMPLE,DC=COM> => ldap_bv2dn(UID=ADWORDS@EXAMPLE.COM,DC=EXAMPLE,DC=COM,0) <= ldap_bv2dn(UID=ADWORDS@EXAMPLE.COM,DC=EXAMPLE,DC=COM)=0 => ldap_dn2bv(272) <= ldap_dn2bv(uid=ADWORDS@EXAMPLE.COM,dc=EXAMPLE,dc=COM)=0 => ldap_dn2bv(272) <= ldap_dn2bv(uid=adwords@example.com,dc=example,dc=com)=0 53839731 <<< dnPrettyNormal: <uid=ADWORDS@EXAMPLE.COM,dc=EXAMPLE,dc=COM>, <uid=adwords@example.com,dc=example,dc=com> 53839731 SRCH "UID=ADWORDS@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@example.com,dc=example,dc=com" 53839731 ==>backsql_search(): base="uid=adwords@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@example.com,dc=example,dc=com") matched expected 53839731 backsql_dn2id("uid=adwords@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@example.com,dc=example,dc=com"): id=adwords@example.com keyval=adwords@example.com oc_id=1 dn=UID=ADWORDS@EXAMPLE.COM,DC=EXAMPLE,DC=COM 53839731 <==backsql_dn2id("uid=adwords@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