ldap server with mysql backend

README.md

I wanted to build an LDAP server that queries a MySQL server to fetch users and check their passwords. It is mainly used for old software that does not work with custom OAuth2 providers. Redmine is an example of this.

• ldapjs: library to start-off LDAP servers from scratch
• node.js driver for mysql: to connect to a mysql server

Instructions:

1. Create the database and table with insert.sql

2.  npm install ldapjs mysql
    node ldap-mysql.js
   

3. Test the server:

    ldapsearch -H ldap://localhost:1389 -x -D cn=root,o=example -w secret -b "o=example" objectclass=*
   

Redmine LDAP settings (optional):

Host: server
Port: 1389
LDAPS: false
Account: cn=root,o=example
Password: secret
Base DN: o=example
On-the-fly user creation: true
Login attribute: cn
Firstname attribute: fn
Lastname attribute: sn
Email attribute: mail

insert.sql

CREATE TABLE IF NOT EXISTS `users2` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `name` varchar(50) NOT NULL,
  `surname` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
INSERT INTO `users2` (`username`, `password`, `name`, `surname`, `email`) VALUES
('jane', 'demo', 'Jane', 'Doe', 'jane@example.com' ),
('john', 'demo', 'John', 'Doe', 'john@example.com' ),
('eren', 'demo', 'Eren', 'Bekce', 'eren@example.com' );

ldap-mysql.js

// ldap server with mysql backend
// author: selim eren bekçe

var ldap = require('ldapjs'),
    mysql = require("mysql"),
    server = ldap.createServer(),
    root_user = "root",
    root_pass = "secret",
    ldap_port = 1389,
    basedn = "o=example",
    db = mysql.createConnection({
      user: "mysqluser",
      password: "mysqlpass",
      database: "ldap"
    }),
    db_name = "users2",
    rev_map = { // reverse field mapping, used for building sql query
      "mail":"email",
      "cn":"username",
      "fn":"name",
      "sn":"surname"
    };

server.bind(basedn, function (req, res, next) {
  var username = req.dn.toString(),
      password = req.credentials;
  username = username.substring(3, username.indexOf(", "+basedn));
  console.log("bind|" + username + "|" + password);
  // if user is root, just check its password right here
  if(root_user == username && root_pass == password){
    req.is_root = 1;
    console.log("root");
    res.end();
    return next();
  } else {
    // query the mysql database and validate the user
    db.query("select c.* from "+db_name+" c where c.username='"+username+"' and c.password='"+password+"'", function(err, users) {
      if (err) {
        console.log("Error fetching users", err);
        return next(new ldap.LDAPError());
      }
      if(users.length <= 0) {
        console.log("invalid credentials");
        return next(new ldap.InvalidCredentialsError());
      } else {
        res.end();
        return next();
      }
    });
  }
});

// recursively prepares the sql query.
// it can handle 'and', 'or', 'substring' and 'equal' type filters.
// see http://ldapjs.org/filters.html to implement all.
function prepareQuery(filter){
  var query = '';
  if(filter.type == 'and' || filter.type == 'or'){
    query += ' ( ';
    for(let i=0; i<filter.filters.length; i++){
      if(query.length>3) query += filter.type;
      query += prepareQuery(filter.filters[i]);
    }
    query += ' ) ';
  }
  else if(filter.type == 'substring'){
    query += " c."+rev_map[filter.attribute]+" LIKE '"+filter.initial+"%' ";
  }
  else if(filter.type == 'equal'){
    query += " c."+rev_map[filter.attribute]+" = '"+filter.value+"' ";
  }
  return query;
}

server.search(basedn, function(req, res, next) {
  var binddn = req.connection.ldap.bindDN.toString();
  var username = binddn.substring(3, binddn.indexOf(", "+basedn));
  console.log("search() username="+username);
  var query = prepareQuery(req.filter).trim();
  if(query!=''){
    query = " where " + query;
  }

  //console.log(req.filter);
  console.log(`query: ${query}`);
  if(username == root_user){
    db.query("select c.* from "+db_name+" c"+query, function(err, users) {
      if (err) {
        console.log("Error fetching users", err);
        return next(new ldap.LDAPError());
      }
      for (var i=0; i<users.length; i++){
        var user = {
          dn: "cn="+users[i].username+", "+basedn,
          attributes: {
            objectclass: [ "top" ],
            cn: users[i].username,
            mail: users[i].email,
            fn: users[i].name,
            sn: users[i].surname
          }
        };
        res.send(user);
      }
      res.end();
    });
  } else {
    res.end();
  }
});

server.listen(ldap_port, function() {
  console.log("LDAP server started at %s", server.url);
});