We use Ldap for handling our users and I have spent time setting up Openldap and tryng to configur it.
But now i have given up my ldap skills and setup my openldap to use a sql backend and then i config my user with SQL that i like more.
I have also build i Dockerfile for docker that you can use.
So what you need is one sql databserver to hold that database, One odbc connection from the ldap server to that sql server. And then to configure Opendal to use that odbc to get users.
yum install mariadb-server -y
Load that database with this data
mysql < init-sql.sql
init-sql.sql
create database ldap;
CREATE USER 'ldap'@'%' IDENTIFIED BY 'ldap';
GRANT ALL PRIVILEGES ON ldap.* TO 'ldap'@'%';
CREATE USER 'ldap'@'localhost' IDENTIFIED BY 'ldap';
GRANT ALL PRIVILEGES ON ldap.* TO 'ldap'@'localhost';
use ldap;
drop table if exists ldap_oc_mappings;
create table ldap_oc_mappings
(
id integer unsigned not null primary key auto_increment,
name varchar(64) not null,
keytbl varchar(64) not null,
keycol varchar(64) not null,
create_proc varchar(255),
delete_proc varchar(255),
expect_return tinyint not null
);
drop table if exists ldap_attr_mappings;
create table ldap_attr_mappings
(
id integer unsigned not null primary key auto_increment,
oc_map_id integer unsigned not null references ldap_oc_mappings(id),
name varchar(255) not null,
sel_expr varchar(255) not null,
sel_expr_u varchar(255),
from_tbls varchar(255) not null,
join_where varchar(255),
add_proc varchar(255),
delete_proc varchar(255),
param_order tinyint not null,
expect_return tinyint not null
);
drop table if exists ldap_entries;
create table ldap_entries
(
id integer unsigned not null primary key auto_increment,
dn varchar(255) not null,
oc_map_id integer unsigned not null references ldap_oc_mappings(id),
parent int NOT NULL ,
keyval int NOT NULL
);
alter table ldap_entries add
constraint unq1_ldap_entries unique
(
oc_map_id,
keyval
);
alter table ldap_entries add
constraint unq2_ldap_entries unique
(
dn
);
drop table if exists ldap_entry_objclasses;
create table ldap_entry_objclasses
(
entry_id integer not null references ldap_entries(id),
oc_name varchar(64)
);
drop table if exists persons;
CREATE TABLE persons (
id int NOT NULL,
name varchar(255) NOT NULL,
surname varchar(255) NOT NULL,
password varchar(64)
);
drop table if exists institutes;
CREATE TABLE institutes (
id int NOT NULL,
name varchar(255)
);
drop table if exists documents;
CREATE TABLE documents (
id int NOT NULL,
title varchar(255) NOT NULL,
abstract varchar(255)
);
drop table if exists authors_docs;
CREATE TABLE authors_docs (
pers_id int NOT NULL,
doc_id int NOT NULL
);
drop table if exists phones;
CREATE TABLE phones (
id int NOT NULL ,
phone varchar(255) NOT NULL ,
pers_id int NOT NULL
);
drop table if exists certs;
CREATE TABLE certs (
id int NOT NULL ,
cert LONGBLOB NOT NULL,
pers_id int NOT NULL
);
drop table if exists referrals;
CREATE TABLE referrals (
id int NOT NULL,
name varchar(255) NOT NULL,
url varchar(255) NOT NULL
);
insert into institutes (id,name) values (1,'life');
insert into persons (id,name,surname,password) values (1,'Mitya','Kovalev','mit');
insert into persons (id,name,surname) values (2,'Torvlobnor','Puzdoy');
insert into persons (id,name,surname) values (3,'Akakiy','Zinberstein');
insert into phones (id,phone,pers_id) values (1,'332-2334',1);
insert into phones (id,phone,pers_id) values (2,'222-3234',1);
insert into phones (id,phone,pers_id) values (3,'545-4563',2);
insert into documents (id,abstract,title) values (1,'abstract1','book1');
insert into documents (id,abstract,title) values (2,'abstract2','book2');
insert into authors_docs (pers_id,doc_id) values (1,1);
insert into authors_docs (pers_id,doc_id) values (1,2);
insert into authors_docs (pers_id,doc_id) values (2,1);
insert into referrals (id,name,url) values (1,'Referral','ldap://localhost:9012/');
insert into certs (id,cert,pers_id) values (1,UNHEX('3082036b308202d4a003020102020102300d06092a864886f70d01010405003077310b3009060355040613025553311330110603550408130a43616c69666f726e6961311f301d060355040a13164f70656e4c444150204578616d706c652c204c74642e311330110603550403130a4578616d706c65204341311d301b06092a864886f70d010901160e6361406578616d706c652e636f6d301e170d3033313031373136333331395a170d3034313031363136333331395a307e310b3009060355040613025553311330110603550408130a43616c69666f726e6961311f301d060355040a13164f70656e4c444150204578616d706c652c204c74642e311830160603550403130f557273756c612048616d7073746572311f301d06092a864886f70d01090116107568616d406578616d706c652e636f6d30819f300d06092a864886f70d010101050003818d0030818902818100eec60a7910b57d2e687158ca55eea738d36f10413dfecf31435e1aeeb9713b8e2da7dd2dde6bc6cec03b4987eaa7b037b9eb50e11c71e58088cc282883122cd8329c6f24f6045e6be9d21b9190c8292998267a5f7905292de936262747ab4b76a88a63872c41629a69d32e894d44c896a8d06fab0a1bc7de343c6c1458478f290203010001a381ff3081fc30090603551d1304023000302c06096086480186f842010d041f161d4f70656e53534c2047656e657261746564204365727469666963617465301d0603551d0e04160414a323de136c19ae0c479450e882dfb10ad147f45e3081a10603551d2304819930819680144b6f211a3624d290f943b053472d7de1c0e69823a17ba4793077310b3009060355040613025553311330110603550408130a43616c69666f726e6961311f301d060355040a13164f70656e4c444150204578616d706c652c204c74642e311330110603550403130a4578616d706c65204341311d301b06092a864886f70d010901160e6361406578616d706c652e636f6d820100300d06092a864886f70d010104050003818100881470045bdce95660d6e6af59e6a844aec4b9f5eaea88d4eb7a5a47080afa64750f81a3e47d00fd39c69a17a1c66d29d36f06edc537107f8c592239c2d4da55fb3f1d488e7b2387ad2a551cbd1ceb070ae9e020a9467275cb28798abb4cbfff98ddb3f1e7689b067072392511bb08125b5bec2bc207b7b6b275c47248f29acd'),3);
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
values (1,'inetOrgPerson','persons','id',NULL,NULL,0);
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
values (2,'document','documents','id',NULL,NULL,0);
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
values (3,'organization','institutes','id',NULL,NULL,0);
insert into ldap_oc_mappings (id,name,keytbl,keycol,create_proc,delete_proc,expect_return)
values (4,'referral','referrals','id',NULL,NULL,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (2,1,'telephoneNumber','phones.phone','persons,phones',
'phones.pers_id=persons.id',NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (3,1,'givenName','persons.name','persons',NULL,NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (4,1,'sn','persons.surname','persons',NULL,NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (5,1,'userPassword','persons.password','persons','persons.password IS NOT NULL',NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (6,1,'seeAlso','seeAlso.dn','ldap_entries AS seeAlso,documents,authors_docs,persons',
'seeAlso.keyval=documents.id AND seeAlso.oc_map_id=2 AND authors_docs.doc_id=documents.id AND authors_docs.pers_id=persons.id',
NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (7,2,'description','documents.abstract','documents',NULL,NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (8,2,'documentTitle','documents.title','documents',NULL,NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (9,2,'documentAuthor','documentAuthor.dn','ldap_entries AS documentAuthor,documents,authors_docs,persons',
'documentAuthor.keyval=persons.id AND documentAuthor.oc_map_id=1 AND authors_docs.doc_id=documents.id AND authors_docs.pers_id=persons.id',
NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (10,2,'documentIdentifier','concat(''document '',documents.id)','documents',NULL,NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (11,3,'o','institutes.name','institutes',NULL,NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (12,3,'dc','lower(institutes.name)','institutes,ldap_entries AS dcObject,ldap_entry_objclasses as auxObjectClass',
'institutes.id=dcObject.keyval AND dcObject.oc_map_id=3 AND dcObject.id=auxObjectClass.entry_id AND auxObjectClass.oc_name=''dcObject''',
NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (13,4,'ou','referrals.name','referrals',NULL,NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (14,4,'ref','referrals.url','referrals',NULL,NULL,NULL,3,0);
insert into ldap_attr_mappings (id,oc_map_id,name,sel_expr,from_tbls,join_where,add_proc,delete_proc,param_order,expect_return)
values (15,1,'userCertificate','certs.cert','persons,certs',
'certs.pers_id=persons.id',NULL,NULL,3,0);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval)
values (1,'dc=life,dc=com',3,0,1);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval)
values (2,'cn=Mitya Kovalev,dc=life,dc=com',1,1,1);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval)
values (3,'cn=Torvlobnor Puzdoy,dc=life,dc=com',1,1,2);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval)
values (4,'cn=Akakiy Zinberstein,dc=life,dc=com',1,1,3);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval)
values (5,'documentTitle=book1,dc=life,dc=com',2,1,1);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval)
values (6,'documentTitle=book2,dc=life,dc=com',2,1,2);
insert into ldap_entries (id,dn,oc_map_id,parent,keyval)
values (7,'ou=Referral,dc=life,dc=com',4,1,1);
-- objectClass mapping: entries that have multiple objectClass instances are listed here with the objectClass name (view them as auxiliary objectClass)
-- entry_id the "ldap_entries.id" of the entry this objectClass value must be added
-- oc_name the name of the objectClass; it MUST match the name of an objectClass that is loaded in slapd's schema
insert into ldap_entry_objclasses (entry_id,oc_name)
values (1,'dcObject');
insert into ldap_entry_objclasses (entry_id,oc_name)
values (4,'pkiUser');
insert into ldap_entry_objclasses (entry_id,oc_name)
values (7,'extensibleObject');
And now we have a working database lets connect it with odbc
yum install unixODBC mysql-connector-odbc
Create the file
vi /etc/odbc.ini
And add the follow
[ldap] Description = LdapToMysql Driver = MySQL Database = ldap Server = 127.0.0.1 User = ldap Password = ldap Port = 3306
Test soo that it work
isql -v ldap SQL> show databases;
And you show now see you ldap database in there. And now we are eady to setup openldap to use our sql database.
yum install wget make gcc mysql-devel unixODBC-devel groff -y cd /opt wget ftp://mirror.switch.ch/mirror/OpenLDAP/openldap-release/openldap-2.4.43.tgz tar zxvf openldap-*.tgz rm -rf openldap-*.tgz mv openldap-* openldap cd /opt/openldap ./configure --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var --mandir=/usr/share/man --infodir=/usr/share/info --enable-sql --disable-bdb --disable-ndb --disable-hdb make depend make make install
Create the file /etc/openldap/slapd.conf and add the following to it
You can create you own password with slappasswd and copy it in here
# $OpenLDAP: pkg/ldap/servers/slapd/slapd.conf,v 1.23.2.8 2003/05/24 23:19:14 ku
#
# 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/slapd.pid
argsfile /var/run/slapd.args
#######################################################################
# sql database definitions
#######################################################################
database sql
suffix "dc=life,dc=com"
# Only need if not using the ldbm/bdb stuff below
rootdn "cn=manager,dc=life,dc=com"
rootpw {SSHA}bv2pkHnDTvS6cOCK/jbhsnX5UEgdrd7F
dbname ldap
dbuser ldap
dbpasswd ldap
has_ldapinfo_dn_ru no
subtree_cond "ldap_entries.dn LIKE CONCAT('%',?)"
And now we can start it with
/opt/openldap/servers/slapd/slapd -d 5 -h 'ldap:/// ldapi:///' -f /etc/openldap/slapd.conf &
And you can get you uses from it by running
ldapsearch -x -b "dc=life,dc=com"
DockerFile
I have also made a Dockerfile for this that you can use
from fareoffice/base MAINTAINER Mattias Hemmingsson RUN yum install wget make gcc mysql-devel unixODBC-devel groff -y WORKDIR /opt/ #Setup openldap RUN wget ftp://mirror.switch.ch/mirror/OpenLDAP/openldap-release/openldap-2.4.43.tgz RUN tar zxvf openldap-*.tgz RUN rm -rf openldap-*.tgz RUN mv openldap-* openldap WORKDIR /opt/openldap RUN ./configure --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var --mandir=/usr/share/man --infodir=/usr/share/info --enable-sql --disable-bdb --disable-ndb --disable-hdb RUN make depend RUN make RUN make install ADD config/slapd.conf /etc/openldap/slapd.conf #Setting up mysql connection RUN yum install mysql-connector-odbc -y RUN odbcinst -j ADD config/odbc.ini /etc/odbc.ini #Setting up local mysql ADD config/init-sql.sql /opt/install/ RUN yum install mariadb-server -y RUN mysql_install_db RUN chown mysql:mysql -R /var/lib/mysql/ RUN mysqld_safe & sleep 3 && mysql < /opt/install/init-sql.sql CMD mysqld_safe & sleep 3 && /opt/openldap/servers/slapd/slapd -d 5 -h 'ldap:/// ldapi:///' -f /etc/openldap/slapd.conf &