[Date Prev][Date Next] [Chronological] [Thread] [Top]

back-sql on Debian testing



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