Linux: OpenLDAP + MySQL

Raul Unzue Pulido 1 febrero, 2021 Linux Deja un comentario 2,650 Vistas

Linux: OpenLDAP + MySQL

Hoy vamos a explicar una infraestrutura instalada sobre Ubuntu Server. La idea es instalar un servidor OpenLdap como frontend, y que tenga un MySQL en Backend, donde consultará los datos, que serán pre-cargados por un proceso externo.

Lo haremos sobre Ubuntu Server 20.04 LTS, que es la versión estable y de largo soporte que existe actualmente. Así que empezamos. Lo primero que haremos es instalar las dependencias:

sudo apt-get install libssl-dev libdb-dev unixodbc-dev time

1	sudo apt-get install libssl-dev libdb-dev unixodbc-dev time

Instalación OpenLDAP sobre Ubuntu Server 20.04 LTS

Usaremos el paquete slapd:

sudo apt install slapd ldap-utils

1	sudo apt install slapd ldap-utils

Ahora realizamos la configuración:

raulunzue@ubuntu:~$ sudo slapcat
[sudo] password for raulunzue:
dn: dc=nodomain
objectClass: top
objectClass: dcObject
objectClass: organization
o: nodomain
dc: nodomain
structuralObjectClass: organization
entryUUID: 720e8d46-ede2-103a-94f1-af2ed3bf131b
creatorsName: cn=admin,dc=nodomain
createTimestamp: 20210118140855Z
entryCSN: 20210118140855.616576Z#000000#000#000000
modifiersName: cn=admin,dc=nodomain
modifyTimestamp: 20210118140855Z

dn: cn=admin,dc=nodomain
objectClass: simpleSecurityObject
objectClass: organizationalRole
cn: admin
description: LDAP administrator
userPassword:: e1NTSEF9NUlGNFBxYXAwbjFtdUMxMnVUVkpNdWR2c0wrMUVuTTc=
structuralObjectClass: organizationalRole
entryUUID: 720ea9b6-ede2-103a-94f2-af2ed3bf131b
creatorsName: cn=admin,dc=nodomain
createTimestamp: 20210118140855Z
entryCSN: 20210118140855.617341Z#000000#000#000000
modifiersName: cn=admin,dc=nodomain
modifyTimestamp: 20210118140855Z

raulunzue@ubuntu:~$ sudo slapcat

[sudo] password for raulunzue:

dn: dc=nodomain

objectClass: top

objectClass: dcObject

objectClass: organization

o: nodomain

dc: nodomain

structuralObjectClass: organization

entryUUID: 720e8d46-ede2-103a-94f1-af2ed3bf131b

creatorsName: cn=admin,dc=nodomain

createTimestamp: 20210118140855Z

entryCSN: 20210118140855.616576Z#000000#000#000000

modifiersName: cn=admin,dc=nodomain

modifyTimestamp: 20210118140855Z

dn: cn=admin,dc=nodomain

objectClass: simpleSecurityObject

objectClass: organizationalRole

cn: admin

description: LDAP administrator

userPassword:: e1NTSEF9NUlGNFBxYXAwbjFtdUMxMnVUVkpNdWR2c0wrMUVuTTc=

structuralObjectClass: organizationalRole

entryUUID: 720ea9b6-ede2-103a-94f2-af2ed3bf131b

creatorsName: cn=admin,dc=nodomain

createTimestamp: 20210118140855Z

entryCSN: 20210118140855.617341Z#000000#000#000000

modifiersName: cn=admin,dc=nodomain

modifyTimestamp: 20210118140855Z

Instalar MySQL y DRIVER ODBC

Para que OpenLDAP se conecte a la base de datos, necesitaremos un driver que permita la conexión al MySQL.

Lanzamos el instalador del Mysql-server:

raulunzue@ubuntu:~$ sudo apt-get install mysql-server

1	raulunzue@ubuntu:~$ sudo apt-get install mysql-server

Y el driver lo podéis descargar desde el proyecto MySQL:

https://dev.mysql.com/downloads/connector/odbc/

Podéis descargarlos con los siguientes comandos:

wget https://repo.mysql.com/apt/ubuntu/pool/mysql-8.0/m/mysql-community/mysql-community-client-plugins_8.0.23-1ubuntu20.04_amd64.deb

wget https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc_8.0.23-1ubuntu20.04_amd64.deb

wget https://repo.mysql.com/apt/ubuntu/pool/mysql-8.0/m/mysql-community/mysql-community-client-plugins_8.0.23-1ubuntu20.04_amd64.deb

wget https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc_8.0.23-1ubuntu20.04_amd64.deb

Comprobamos que el servicio de mysql server está arrancado:

Configuración MySQL

Lanzamos la configuración del servidor MySQL Server:

raulunzue@ubuntu:~$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) :

 ... skipping.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) :

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) :

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

 ... skipping.
All done!

raulunzue@ubuntu:~$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords

and improve security. It checks the strength of password

and allows the users to set only those passwords which are

secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y

There are three levels of password validation policy:

LOW Length >= 8

MEDIUM Length >= 8, numeric, mixed case, and special characters

STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 100

Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y

By default, a MySQL installation has an anonymous user,

allowing anyone to log into MySQL without having to have

a user account created for them. This is intended only for

testing, and to make the installation go a bit smoother.

You should remove them before moving into a production

environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) :

... skipping.

Normally, root should only be allowed to connect from

'localhost'. This ensures that someone cannot guess at

the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) :

... skipping.

By default, MySQL comes with a database named 'test' that

anyone can access. This is also intended only for testing,

and should be removed before moving into a production

environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) :

... skipping.

Reloading the privilege tables will ensure that all changes

made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

... skipping.

All done!

Ahora comprobamos que podemos acceder:

mysql -u root -p
show databases;
exit;

mysql -u root -p

show databases;

exit;

Instalación PhpMyAdmin

Según para quién sea el servidor, siempre es bueno colocar una herramienta como PhpMyAdmin, que les permita una gestión gráfica. Lo haremos sobre el servidor MySQL:

apt-get install phpmyadmin
-- Si nos da el error "mysqli_real_connect(): (HY000/1698): Access denied for user 'root'@'localhost'" debemos crear un superusuario nuevo para no usar root --
mysql -u root -p
SELECT user,plugin,host FROM mysql.user WHERE user = 'root';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'enter_password_here'; -- Esto soluciona el error de login desde la web
FLUSH PRIVILEGES;
Exit

apt-get install phpmyadmin

-- Si nos da el error "mysqli_real_connect(): (HY000/1698): Access denied for user 'root'@'localhost'" debemos crear un superusuario nuevo para no usar root --

mysql -u root -p

SELECT user,plugin,host FROM mysql.user WHERE user = 'root';

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'enter_password_here'; -- Esto soluciona el error de login desde la web

FLUSH PRIVILEGES;

Exit

La url de acceso sería http://IP-MYSQL/phpmyadmin

Configuración OPENLDAP

Lo primero que haremos es generar una cuenta en la base de datos:

mysql -u root -p
CREATE USER 'openldap'@'%' IDENTIFIED BY 'elblogdenegu';
CREATE DATABASE IF NOT EXISTS openldap;
GRANT ALL PRIVILEGES ON openldap.* TO 'openldap'@'%';
FLUSH PRIVILEGES;
exit

mysql -u root -p

CREATE USER 'openldap'@'%' IDENTIFIED BY 'elblogdenegu';

CREATE DATABASE IF NOT EXISTS openldap;

GRANT ALL PRIVILEGES ON openldap.* TO 'openldap'@'%';

FLUSH PRIVILEGES;

exit

Ahora deberemos ir configurando los diferentes ficheros de configuración, os dejo todas las configuraciones:

nano /etc/odbc.ini
        [openldap]
        Description         = Example for OpenLDAP's back-sql
        Driver              = MySQL
        Trace               = No
        Database            = openldap
        Servername          = localhost
        UserName            = openldap
        Password            = elblogdenegu
        ReadOnly            = No
        RowVersioning       = No
        ShowSystemTables    = No
        ShowOidColumn       = No
        FakeOidIndex        = No
        ConnSettings        =
        #SOCKET              = /run/mysqld/mysqld.sock
        Server              = localhost
        Port                = 3306

	
nano /etc/odbcinst.ini
	[MySQL]
	Description=ODBC for MySQL
	Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
	FileUsage=1

nano /etc/ldap/slapd.conf
	# $OpenLDAP$
	#
	# See slapd.conf(5) for details on configuration options.
	# This file should NOT be world readable.
	#
	include         /etc/ldap/schema/core.schema
	include         /etc/ldap/schema/cosine.schema
	include         /etc/ldap/schema/inetorgperson.schema

	# Define global ACLs to disable default read access.

	# Do not enable referrals until AFTER you have a working directory
	# service AND an understanding of referrals.
	#referral       ldap://root.openldap.org

	pidfile         /run/slapd/slapd.pid
	argsfile        /run/slapd/slapd.args

	modulepath      /usr/lib/ldap
	moduleload      back_sql.la

	#######################################################################
	# sql database definitions
	#######################################################################

	database        sql
	suffix          "dc=example,dc=com"
	rootdn          "cn=root,dc=example,dc=com"
	rootpw          elblogdenegu
	dbname          openldap
	dbuser          openldap
	dbpasswd        elblogdenegu
	subtree_cond    "ldap_entries.dn LIKE CONCAT('%',?)"
	insentry_stmt   "INSERT INTO ldap_entries (dn,oc_map_id,parent,keyval) VALUES (?,?,?,?)"
	has_ldapinfo_dn_ru      no

nano /etc/apparmor.d/usr.sbin.slapd
	/etc/odbc.ini r,
	/etc/odbcinst.ini r,
	/run/mysqld/mysqld.sock w,

nano /etc/odbc.ini

[openldap]

Description = Example for OpenLDAP's back-sql

Driver = MySQL

Trace = No

Database = openldap

Servername = localhost

UserName = openldap

Password = elblogdenegu

ReadOnly = No

RowVersioning = No

ShowSystemTables = No

ShowOidColumn = No

FakeOidIndex = No

ConnSettings =

#SOCKET = /run/mysqld/mysqld.sock

Server = localhost

Port = 3306

nano /etc/odbcinst.ini

[MySQL]

Description=ODBC for MySQL

Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so

FileUsage=1

nano /etc/ldap/slapd.conf

# $OpenLDAP$

# See slapd.conf(5) for details on configuration options.

# This file should NOT be world readable.

include /etc/ldap/schema/core.schema

include /etc/ldap/schema/cosine.schema

include /etc/ldap/schema/inetorgperson.schema

# Define global ACLs to disable default read access.

# Do not enable referrals until AFTER you have a working directory

# service AND an understanding of referrals.

#referral ldap://root.openldap.org

pidfile /run/slapd/slapd.pid

argsfile /run/slapd/slapd.args

modulepath /usr/lib/ldap

moduleload back_sql.la

#######################################################################

# sql database definitions

#######################################################################

database sql

suffix "dc=example,dc=com"

rootdn "cn=root,dc=example,dc=com"

rootpw elblogdenegu

dbname openldap

dbuser openldap

dbpasswd elblogdenegu

subtree_cond "ldap_entries.dn LIKE CONCAT('%',?)"

insentry_stmt "INSERT INTO ldap_entries (dn,oc_map_id,parent,keyval) VALUES (?,?,?,?)"

has_ldapinfo_dn_ru no

nano /etc/apparmor.d/usr.sbin.slapd

/etc/odbc.ini r,

/etc/odbcinst.ini r,

/run/mysqld/mysqld.sock w,

Una vez configurados, recargamos el servicio:

systemctl reload apparmor.service

1	systemctl reload apparmor.service

Instalación PHPLDAPADMIN

Como hemos hecho con phpmyadmin, existe también la posibilidad de instalar un gestor gráfico para openldap. Lo haremos con PHPLDAPADMIN. La instalación se realiza sobre el Openldap:

sudo apt install phpldapadmin

1	sudo apt install phpldapadmin

A nivel de configuración:

nano /etc/phpldapadmin/config.php

$config->custom->apariencia['hide_template_warning'] = false; cambiar por:
$config->custom->apariencia['hide_template_warning'] = true;

$servers->setValue('server','base',array('dc=example,dc=com')); cambiar por:
$servers->setValue('server','base',array());

$servers->setValue('login','anon_bind',true); cambiar por:
$servers->setValue('login','anon_bind',false);

nano /etc/phpldapadmin/config.php

$config->custom->apariencia['hide_template_warning'] = false; cambiar por:

$config->custom->apariencia['hide_template_warning'] = true;

$servers->setValue('server','base',array('dc=example,dc=com')); cambiar por:

$servers->setValue('server','base',array());

$servers->setValue('login','anon_bind',true); cambiar por:

$servers->setValue('login','anon_bind',false);

La url de acceso es http://IP-OPENLDAP:139/phpldapadmin

Precarga de datos en MYSQL para OPENLDAP

El punto clave de la instalación, para que la interconexión entre Frontend (Openldap) y Backend (Mysql) funcione, es la precarga de tablas de metadatos en el mysql.

Esto se hace con los ficheros del proyecto:

https://github.com/openldap/openldap/tree/master/servers/slapd/back-sql/rdbms_depend/mysql

En concreto se realizaría en este orden:

backsql_create.sql

wget https://github.com/openldap/openldap/blob/master/servers/slapd/back-sql/rdbms_depend/mysql/backsql_create.sql

1

wget https://github.com/openldap/openldap/blob/master/servers/slapd/back-sql/rdbms_depend/mysql/backsql_create.sql
testdb_create.sql

https://github.com/openldap/openldap/blob/master/servers/slapd/back-sql/rdbms_depend/mysql/testdb_create.sql

1

https://github.com/openldap/openldap/blob/master/servers/slapd/back-sql/rdbms_depend/mysql/testdb_create.sql
Testdb_data.sql

https://github.com/openldap/openldap/blob/master/servers/slapd/back-sql/rdbms_depend/mysql/testdb_data.sql

1

https://github.com/openldap/openldap/blob/master/servers/slapd/back-sql/rdbms_depend/mysql/testdb_data.sql
testdb_metadata.sql

https://github.com/openldap/openldap/blob/master/servers/slapd/back-sql/rdbms_depend/mysql/testdb_metadata.sql

1

https://github.com/openldap/openldap/blob/master/servers/slapd/back-sql/rdbms_depend/mysql/testdb_metadata.sql

Luego la instalación de los ficheros sería:

root@ubuntu:~# mysql -u openldap -p openldap &lt; backsql_create.sql
Enter password:

1 2	root@ubuntu:~# mysql -u openldap -p openldap < backsql_create.sql Enter password:

Y asi por cada fichero

root@ubuntu:~# mysql -u openldap -p openldap &lt; testdb_create.sql
Enter password:
root@ubuntu:~# mysql -u openldap -p openldap &lt; testdb_data.sql
Enter password:
root@ubuntu:~# mysql -u openldap -p openldap &lt; testdb_metadata.sql
Enter password:

root@ubuntu:~# mysql -u openldap -p openldap < testdb_create.sql

Enter password:

root@ubuntu:~# mysql -u openldap -p openldap < testdb_data.sql

Enter password:

root@ubuntu:~# mysql -u openldap -p openldap < testdb_metadata.sql

Enter password:

Comprobaciones Openldap

Os dejo unos comprobaciones para verificar:

root@ubuntu:~# netstat -putan | grep 389
tcp        0      0 0.0.0.0:389             0.0.0.0:*               LISTEN      889/slapd
tcp6       0      0 :::389                  :::*                    LISTEN      889/slapd


root@ubuntu:~# slapcat
dn: dc=nodomain
objectClass: top
objectClass: dcObject
objectClass: organization
o: nodomain
dc: nodomain
structuralObjectClass: organization
entryUUID: 720e8d46-ede2-103a-94f1-af2ed3bf131b
creatorsName: cn=admin,dc=nodomain
createTimestamp: 20210118140855Z
entryCSN: 20210118140855.616576Z#000000#000#000000
modifiersName: cn=admin,dc=nodomain
modifyTimestamp: 20210118140855Z

dn: cn=admin,dc=nodomain
objectClass: simpleSecurityObject
objectClass: organizationalRole
cn: admin
description: LDAP administrator
userPassword:: e1NTSEF9NUlGNFBxYXAwbjFtdUMxMnVUVkpNdWR2c0wrMUVuTTc=
structuralObjectClass: organizationalRole
entryUUID: 720ea9b6-ede2-103a-94f2-af2ed3bf131b
creatorsName: cn=admin,dc=nodomain
createTimestamp: 20210118140855Z
entryCSN: 20210118140855.617341Z#000000#000#000000
modifiersName: cn=admin,dc=nodomain
modifyTimestamp: 20210118140855Z


root@ubuntu:~# ldapsearch -x -b dc=example,dc=com
# extended LDIF
#
# LDAPv3
# base <dc=example,dc=com> with scope subtree
# filter: (objectclass=*)
# requesting: ALL
#

# search result
search: 2
result: 32 No such object

# numResponses: 1



ldapsearch -LLL -h localhost -p 389 -x -D "cn=root,dc=example,dc=com" -w elblogdenegu -b "cn=openldap,dc=example,dc=com"