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.
