Hi people,
I am trying to migrate some users from tpop3d (Maildir mailboxes) to
dovecot. The most important aspect of this migration, and which I
cannot seem to get right, is to first be able to make dovecot to
authenticate against the current DB being used by tpop3d so that
I can use imapsync to transfer the user mails. And I do believe
that is what I am unable to get right!!
So here goes what I have tried so far:
Take a case of one username, eddie, who belongs to the domain,
demo.wananchi.com.
1. This query is what I am using for password_query. I do suspect it is
wrong, but well, this is what it yields from the current DB:
mysql> SELECT CONCAT(domain.path, '/', popbox.mbox_name), popbox.password_hash FROM popbox, domain WHERE popbox.local_part = 'eddie' AND popbox.
domain_name = 'demo.wananchi.com' AND popbox.domain_name = domain.domain_name;
+--------------------------------------------+---------------------------------------+
| CONCAT(domain.path, '/', popbox.mbox_name) | password_hash |
+--------------------------------------------+---------------------------------------+
| /var/spool/virtual/demo.wananchi.com/eddie | {md5}897fad9cbef974ff95254417234ff1df |
+--------------------------------------------+---------------------------------------+
2. This is what I use for user_query and the result:
mysql> SELECT CONCAT(domain.path, '/', popbox.mbox_name) as home, 6 as uid, 6 as gid FROM popbox, domain WHERE popbox.local_part = 'eddie' AND
popbox.domain_name = 'demo.wananchi.com' AND popbox.domain_name = domain.domain_name;
+-------------------------------------------------+-----+-----+
| home | uid | gid |
+-------------------------------------------------+-----+-----+
| /var/spool/virtual/nyerimunicipal.co.ke/eddie | 6 | 6 |
+-------------------------------------------------+-----+-----+
3. Getting a typical user would need this query, for example:
mysql> select CONCAT(local_part,'\@',domain_name) as user from popbox limit 10;
+-------------------------+
| user |
+-------------------------+
| eddie@demo.wananchi.com |
+-------------------------+
4. My tables, and the data they contain:
(a)
mysql> desc popbox;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| domain_name | varchar(255) | | PRI | | |
| local_part | varchar(255) | | PRI | | |
| password_hash | varchar(255) | YES | | NULL | |
| mbox_name | varchar(255) | YES | | NULL | |
| password_hash2 | varchar(255) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
(b)
mysql> select * from popbox limit 1;
+-------------------+------------+---------------------------------------+-----------+----------------+
| domain_name | local_part | password_hash | mbox_name | password_hash2 |
+-------------------+------------+---------------------------------------+-----------+----------------+
| demo.wananchi.com | eddie | {md5}7bb1be8a78498a9de40098a593199819 | eddie | 9IYjWHc4Cs9/w |
+-------------------+------------+---------------------------------------+-----------+----------------+
PS: Not all users have password_hash2, which I think is Unix crypt!!!
(c)
mysql> desc domain;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| domain_name | varchar(255) | | PRI | | |
| unix_user | varchar(255) | YES | | NULL | |
| password_hash | varchar(255) | YES | | NULL | |
| path | varchar(255) | YES | | NULL | |
| max_popbox | int(11) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+
(d)
mysql> select * from domain limit 1;
+--------------+-----------+---------------------------------------+--------------------------------------+-----------------+
| domain_name | unix_user | password_hash | path | max_popbox |
+--------------+-----------+---------------------------------------+--------------------------------------+-----------------+
| dlsnovit.org | exim | {md5}d108219901e577db69c249645ae3a49f | /var/spool/virtual/demo.wananchi.com | 5 |
+--------------+-----------+---------------------------------------+--------------------------------------+-----------------+
[root@ns2]#less /var/log/dovecot.log
dovecot: May 29 17:42:57 Warning: Killed with signal 15
dovecot: May 29 17:42:59 Info: Dovecot v1.0.0 starting up
dovecot: May 29 17:42:59 Info: auth-worker(default): mysql: Connected to localhost (virtualemail)
dovecot: May 29 17:44:02 Info: auth(default): client in: AUTH 1 PLAIN service=POP3 secured lip=62.8.64.4 rip=62.8.64.4 resp=AGVk
ZGllQGRlbW8ud2FuYW5jaGkuY29tAGJvZWluZzg=
dovecot: May 29 17:44:02 Info: auth-worker(default): sql(eddie@demo.wananchi.com,62.8.64.4): query: SELECT CONCAT(domain.path, '/', popbox.mbox_n
ame), popbox.password_hash FROM popbox, domain WHERE popbox.local_part = 'eddie' AND popbox.domain_name = 'demo.wananchi.com' AND popbox.domain_n
ame = domain.domain_name
dovecot: May 29 17:44:02 Error: child 40402 (auth-worker) killed with signal 11
dovecot: May 29 17:44:03 Info: auth(default): client out: FAIL 1 user=eddie@demo.wananchi.com temp
dovecot: May 29 17:44:05 Info: pop3-login: Aborted login: user=