Setup d’un Annuaire LDAP s’appuyant sur le backend SQL de postfixadmin

L’idée est d’avoir une base source unique pour ses utilisateurs.
Postfixadmin s’appuye sur une db sql et ne permet pas l’usage d’un annuaire LDAP en backend. Pourtant beaucoup d’applications s’appuyent sur du LDAP, c’est donc handicapant de n’avoir qu’une base sql.
Alors voici un moyen d’avoir également un annuaire LDAP automatiquement mappé sur la base sql utilisateurs de postfixadmin.

Ce setup s’appuye sur des HASH SHA512 pour le stockage des passwords.

* /usr/local/www/postfixadmin/config.inc.php

Par default, postfixadmin créé des hash MD5 pour stocker les password, c’est pas très rassurant, alors mettons nous plutot sur du SHA512, réputé plus solide.

/ Encrypt
// In what way do you want the passwords to be crypted?
// md5crypt = internal postfix admin md5
// md5 = md5 sum of the password
// system = whatever you have set as your PHP system default
// cleartext = clear text passwords (ouch!)
// mysql_encrypt = useful for PAM integration
// authlib = support for courier-authlib style passwords
// dovecot:CRYPT-METHOD = use dovecotpw -s 'CRYPT-METHOD'. Example: dovecot:CRAM-MD5
$CONF['encrypt'] = 'dovecot:SHA512-CRYPT';

* Cette étape n’a d’importance uniquement si vous souhaitez faire du PAM LDAP sur vos OS.

– Ajouter une colonne id sur le schéma existant de postfixadmin de la table mailbox:
Et Pas d’inquiétude; ca ne casse rien au bon fonctionnement de postfixadmin

ALTER TABLE mailbox ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE KEY;

– Alimenter cette colonne avec des id sur les entrées existantes; à partir de 1100 pour ne pas empiéter sur des services utilisant les ranges inférieures.

– Installer l’annuaire Openldap

# cd /usr/ports/net/openldap24-server
# make config

Cocher ODBC
Cocher SHA2

# make install clean

– Editer /usr/local/etc/openldap/slapd.conf


# See slapd.conf(5) for details on configuration options.
# This file should NOT be world readable.
#
include         /usr/local/etc/openldap/schema/core.schema
include         /usr/local/etc/openldap/schema/cosine.schema
include         /usr/local/etc/openldap/schema/inetorgperson.schema
include         /usr/local/etc/openldap/schema/nis.schema


TLSCipherSuite HIGH:MEDIUM:+SSLv3
TLSCertificateFile /usr/local/etc/openldap/cert.crt
TLSCertificateKeyFile /usr/local/etc/openldap/cert.key


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

# Load dynamic backend modules:
modulepath      /usr/local/libexec/openldap
moduleload      back_sql

# Module for supporting SHA512
moduleload      pw-sha2

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

database        sql
suffix          "dc=infranix,dc=eu"
# You only need these if normal ldap backends are defined and hold the "root"
rootdn          "cn=admin,dc=infranix,dc=eu"

# Postfixadmin Password format (SHA512) using libcrypt)
password-hash {CRYPT}
password-crypt-salt-format "$6$%.16s"

dbhost          192.168.X.Y
dbname          ldap
dbuser          ldap_user
dbpasswd        xxxxxxxxxx

lastmod off

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

* Créer une db ldap

create database ldap;

La suite du schéma de la base va avoir beaucoup de vues afin d’avoir une automatisation extrême.

* Pour privilégier un domain le schéma aura 2 vues users quasi-identiques, l’une incluant le domaine privilégié et l’autre l’excluant.

* Vue users_publics

drop view users_public;

create view users_public AS

select 
  id as id, 
  local_part as username ,
 password,
  name,
  name as surname,
  username as email,
  domain as domain
from postfix.mailbox where mailbox.active =1 and mailbox.username  NOT LIKE '%@infranix.eu';

– Vue users_people

drop view users_people;

create view users_people AS

select 
  id as id, 
  local_part as username ,
 password,
  name,
  name as surname,
  username as email,
  domain as domain
from postfix.mailbox where mailbox.active =1 and mailbox.username  LIKE '%@infranix.eu';

– users

drop view users

create view users AS

select * from users_people
UNION
select * from users_public

– org_unit_original

c’est à la base ce schéma qui est fourni par default.

CREATE TABLE `org_unit_original` (
  `id` int(11) NOT NULL,
  `o` varchar(32) DEFAULT NULL,
  `dn` varchar(255) DEFAULT NULL,
  `parent` int(11) DEFAULT NULL,
  `oc_map_id` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dn` (`dn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `org_unit_original` VALUES (1,'Infranix','dc=infranix,dc=eu',0,3),(2,'People','ou=People,dc=infranix,dc=eu',1,2),(3,'Groups','ou=Groups,dc=infranix,dc=eu',1,2),(4,'Public','ou=Public,dc=infranix,dc,eu',1,2),(6,'Devnix','dc=devnix.fr,dc=infranix,dc=eu',1,3),(7,'People','ou=People,dc=devnix.fr,dc=infranix,dc=eu',6,2);


mysql> select id,o,dn,parent,oc_map_id from org_unit_original;
+—-+———-+——————————————+——–+———–+
| id | o | dn | parent | oc_map_id |
+—-+———-+——————————————+——–+———–+
| 1 | Infranix | dc=infranix,dc=eu | 0 | 3 |
| 2 | People | ou=People,dc=infranix,dc=eu | 1 | 2 |
| 3 | Groups | ou=Groups,dc=infranix,dc=eu | 1 | 2 |
| 4 | Public | ou=Public,dc=infranix,dc,eu | 1 | 2 |
| 6 | Devnix | dc=devnix.fr,dc=infranix,dc=eu | 1 | 3 |
| 7 | People | ou=People,dc=devnix.fr,dc=infranix,dc=eu | 6 | 2 |
+—-+———-+——————————————+——–+———–+

– org_unit_distributed

On va créé une orgUnit pour chaque domaine via une vue s’appuyant sur la vue users_distributed, elle même une vue générée par la table mailbox de postfixadmin.

drop view org_unit_distributed;
create view org_unit_distributed AS


   select 
    (domain) as o,
   MIN((id)+50000) as id,
   CONCAT('dc=',domain,',dc=infranix,dc=eu') AS dn, 
        3 as oc_map_id,
        1 as parent
   from users_distributed group by domain
 
UNION 

select * from org_unit_original;

* org_unit

Cette vue définit l’arbre du LDAP avec la relation entre niveau (parent).

drop view org_unit;
create view org_unit AS

select id,o,dn,parent,oc_map_id from org_unit_original

UNION

   select 
   
   MIN((id)+50000) as id,
    (domain) as o,
   CONCAT('dc=',domain,',dc=infranix,dc=eu') AS dn, 

        1 as parent,
                3 as oc_map_id
   from users_distributed group by domain
 
UNION 


    SELECT
     (users_distributed.id)+90000 as id,
        'People' ,
        CONCAT('ou=People,dc=',domain,',dc=infranix,dc=eu') AS dn, 
  
        org_unit_distributed.id as parent,
                2 as oc_map_id
        from users_distributed, org_unit_distributed
        where org_unit_distributed.o = domain
        group by domain
mysql> select * from org_unit limit 10;
+--------+-------------------+------------------------------------------+--------+-----------+
| id     | o                 | dn                                       | parent | oc_map_id |
+--------+-------------------+------------------------------------------+--------+-----------+
|     1  | Infranix          | dc=infranix,dc=eu                        |      0 |         3 |
|     2  | People            | ou=People,dc=infranix,dc=eu              |      1 |         2 |
|     3  | Groups            | ou=Groups,dc=infranix,dc=eu              |      1 |         2 |
|     4  | Public            | ou=Public,dc=infranix,dc,eu              |      1 |         2 |
|     6  | Devnix            | dc=devnix.fr,dc=infranix,dc=eu           |      1 |         3 |
|     7  | People            | ou=People,dc=devnix.fr,dc=infranix,dc=eu |      6 |         2 |
| 60047  | toto.net          | dc=toto.net,dc=infranix,dc=eu            |      1 |         3 |
| 60045  | titi.com          | dc=titi.com,dc=infranix,dc=eu            |      1 |         3 |
| 60009  | tutu.biz          | dc=tutu.biz,dc=infranix,dc=eu            |      1 |         3 |
| 60021  | aaaa.com          | dc=aaaa.com,dc=infranix,dc=eu            |      1 |         3 |
| 100044 | People            | ou=People,dc=toto.net,dc=infranix,dc=eu  |  60044 |         2 |
| 100000 | People            | ou=People,dc=titi.com,dc=infranix,dc=eu  |  60000 |         2 |
| 100007 | People            | ou=People,dc=tutu.biz,dc=infranix,dc=eu  |  60007 |         2 |
| 100014 | People            | ou=People,dc=aaaaa.com,dc=infranix,dc=eu |  60014 |         2 |
+--------+-------------------+------------------------------------------+--------+-----------+
10 rows in set (0.01 sec)

* ldap_entries

drop view ldap_entries;


CREATE VIEW ldap_entries AS 
 
SELECT 
    org_unit.id AS id,
    org_unit.dn AS dn,
    org_unit.oc_map_id AS oc_map_id,
    org_unit.parent AS parent,
    org_unit.id AS keyval
FROM
    org_unit
 

UNION 

 
SELECT 
    (users_people.id) AS id,
    CONCAT('uid=',
                    users_people.username,
                    ',ou=People,dc=infranix,dc=eu') AS dn,
    1 AS oc_map_id,
    2 AS parent,
    (users_people.id) AS keyval
FROM
    users_people



 
UNION 
 
SELECT 
 
    (groups.id) AS id,
    CONCAT( 'cn=',
                    groups.name,
                    ',ou=Groups,dc=infranix,dc=eu') AS dn,
    5 AS oc_map_id,
    3 AS parent,
    groups.id AS id
FROM
    groups


UNION

SELECT 
    (users_distributed.id) AS id,
    CONCAT('uid=',
            users_distributed.username,
            ',ou=People,dc=',
            users_distributed.domain,
            ',dc=infranix,dc=eu') AS dn,
    1 AS oc_map_id,
    org_unit.id AS parent,
    (users_distributed.id) AS keyval
FROM
    users_distributed,org_unit
WHERE
    org_unit.dn = CONCAT('ou=People,dc=',users_distributed.domain,',dc=infranix,dc=eu');

* ldap_entry_objclasses

Pour l’usage de pam, il fallait ajouter de nouveaux objectClass à inetOrgPerson, notamment posixAccount et shadowAccount.
L’astuce est de créer une vue pour construire la table optionnelle ldap_entry_objclasses en l’autogénérant via la table users.

drop view ldap_entry_objclasses;

CREATE VIEW ldap_entry_objclasses AS 
 
SELECT 
    (users.id) AS entry_id,
    'posixAccount' AS oc_name
FROM
    users
 
UNION 

SELECT 
    (users.id) AS entry_id,
    'shadowAccount' AS oc_name
FROM
    users

* ldap_oc_mappings

Cette table permet d’identifier la table de toutes les objectCLass

CREATE TABLE `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=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `ldap_oc_mappings` VALUES (3,'organization','org_unit','id',NULL,NULL,0),(2,'organizationalUnit','org_unit','id',NULL,NULL,0),(1,'inetOrgPerson','users','id',NULL,NULL,0),(5,'posixGroup','groups','id',NULL,NULL,0),(6,'posixAccount','users','id',NULL,NULL,0);

son contenu:

mysql> select * from ldap_oc_mappings;
+----+--------------------+----------+--------+-------------+-------------+---------------+
| id | name               | keytbl   | keycol | create_proc | delete_proc | expect_return |
+----+--------------------+----------+--------+-------------+-------------+---------------+
|  3 | organization       | org_unit | id     | NULL        | NULL        |             0 |
|  2 | organizationalUnit | org_unit | id     | NULL        | NULL        |             0 |
|  1 | inetOrgPerson      | users    | id     | NULL        | NULL        |             0 |
|  5 | posixGroup         | groups   | id     | NULL        | NULL        |             0 |
|  6 | posixAccount       | users    | id     | NULL        | NULL        |             0 |
+----+--------------------+----------+--------+-------------+-------------+---------------+

* ldap_attr_mappings

Cette table est cruciale; c’est elle qui définit les attributs de chaque objectClass.
Dans mon usage pour du pam_ldap, il fallait donc bien définir homeDirectory, userPassword, uidMember, gidNumber, loginShell:

CREATE TABLE `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=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

INSERT INTO `ldap_attr_mappings` VALUES (1,1,'cn','users.username','','users',NULL,NULL,NULL,3,0),(2,1,'telephoneNumber','users.phone','','users','NULL',NULL,NULL,3,0),(3,1,'sn','users.name','','users',NULL,NULL,NULL,3,0),(7,1,'homeDirectory','concat(\'/home/\',users.domain, \'/\',users.username)',NULL,'users',NULL,NULL,NULL,3,0),(5,1,'mail','users.email',NULL,'users',NULL,NULL,NULL,3,0),(6,2,'ou','Groups',NULL,'groups',NULL,NULL,NULL,3,0),(13,1,'userPassword','replace(users.password, \'SHA512-CRYPT\', \'CRYPT\')',NULL,'users','users.password IS NOT NULL',NULL,NULL,3,0),(8,3,'o','org_unit.o',NULL,'org_unit',NULL,NULL,NULL,3,0),(14,1,'displayName','users.name',NULL,'users',NULL,NULL,NULL,3,0),(9,1,'loginShell','concat(\'/bin/bash\')',NULL,'users',NULL,NULL,NULL,3,0),(4,1,'uidNumber','users.id',NULL,'users',NULL,NULL,NULL,3,0),(21,5,'cn','groups.name',NULL,'groups',NULL,NULL,NULL,3,0),(15,5,'gidNumber','groups.id',NULL,'groups',NULL,NULL,NULL,3,0),(16,5,'memberUid','users.username',NULL,'groups,group_member,users','group_member.users_id=users.id and group_member.groups_id=groups.id',NULL,NULL,3,0),(11,1,'gidNumber','100',NULL,'users',NULL,NULL,NULL,3,0),(17,3,'objectClass','concat(\'top\')',NULL,'org_unit',NULL,NULL,NULL,3,0),(18,3,'objectClass','concat(\'dcObject\')',NULL,'org_unit',NULL,NULL,NULL,3,0),(19,1,'uid','users.username',NULL,'users',NULL,NULL,NULL,3,0),(20,3,'dc','org_unit.o',NULL,'org_unit',NULL,NULL,NULL,3,0);

Le contenu des attributs de l’objectClass inetOrgPerson (id àa 1 dans la table ldap_oc_mappings ci-dessus) via un SELECT :


mysql> mysql> select * from ldap_attr_mappings where oc_map_id=1;
+----+-----------+-----------------+---------------------------------------------------+------------+-----------+----------------------------+----------+-------------+-------------+---------------+
| id | oc_map_id | name            | sel_expr                                          | sel_expr_u | from_tbls | join_where                 | add_proc | delete_proc | param_order | expect_return |
+----+-----------+-----------------+---------------------------------------------------+------------+-----------+----------------------------+----------+-------------+-------------+---------------+
|  1 |         1 | cn              | users.username                                    |            | users     | NULL                       | NULL     | NULL        |           3 |             0 |
|  2 |         1 | telephoneNumber | users.phone                                       |            | users     | NULL                       | NULL     | NULL        |           3 |             0 |
|  3 |         1 | sn              | users.name                                        |            | users     | NULL                       | NULL     | NULL        |           3 |             0 |
|  7 |         1 | homeDirectory   | concat('/home/',users.domain, '/',users.username) | NULL       | users     | NULL                       | NULL     | NULL        |           3 |             0 |
|  5 |         1 | mail            | users.email                                       | NULL       | users     | NULL                       | NULL     | NULL        |           3 |             0 |
| 13 |         1 | userPassword    | replace(users.password, 'SHA512-CRYPT', 'CRYPT')  | NULL       | users     | users.password IS NOT NULL | NULL     | NULL        |           3 |             0 |
| 14 |         1 | displayName     | users.name                                        | NULL       | users     | NULL                       | NULL     | NULL        |           3 |             0 |
|  9 |         1 | loginShell      | concat('/bin/bash')                               | NULL       | users     | NULL                       | NULL     | NULL        |           3 |             0 |
|  4 |         1 | uidNumber       | users.id                                          | NULL       | users     | NULL                       | NULL     | NULL        |           3 |             0 |
| 11 |         1 | gidNumber       | 100                                               | NULL       | users     | NULL                       | NULL     | NULL        |           3 |             0 |
| 19 |         1 | uid             | users.username                                    | NULL       | users     | NULL                       | NULL     | NULL        |           3 |             0 |
+----+-----------+-----------------+---------------------------------------------------+------------+-----------+----------------------------+----------+-------------+-------------+---------------+

A suivre, je vais alimenter au fur et à mesure.

One thought on “Setup d’un Annuaire LDAP s’appuyant sur le backend SQL de postfixadmin”

  1. C’est une approche intéressante et astucieuse en solution de dépannage mais il serait quand même bien plus intéressant que postfixadmin supporte enfin nativement LDAP non ?

Leave a Reply

Your email address will not be published. Required fields are marked *


three + 9 =