1 Topic by dynaguy

  • dynaguy
  • Member
  • Offline
  • Registered: 2013-07-17
  • Posts: 4

Topic: Transfer user account (MySQL)

==== Required information ====
- iRedMail version:
- Store mail accounts in which backend (LDAP/MySQL/PGSQL):
- Linux/BSD distribution name and version:
- Related log if you're reporting an issue:
======== Required information ====
- iRedMail version: 0.8.5
- Store mail accounts in which backend : MySQL
- Linux/BSD distribution name and version: CentOS 6.4 64bit
- Related log if you're reporting an issue:
====

Hello,

I have a question about user account migration.

I have a 5-years old mail server (postfix+courier) using MySQL to store virtual user account. When I create a new account on the server I need do a SQL INSERT like this:
INSERT INTO  `mail`.(`email` ,`name` ,`password` ,`quota` ,`time_stamp`)
VALUES ('zzz@zzz.com',  'zzz', ENCRYPT(  '12345' ) ,  '524288000', CURRENT_TIMESTAMP)

Now I want to use iRedMail to build a new server. I installed iRedMail 0.8.5 and it works fine. Now I need to test moving all the user accounts from the old server to new iRedMail server.

Here is my question: The password field on my old server is using MySQL's ENCRYPT function to encrypt the password. So how can I transfer the user account safely to the iRedMail? I am not a programmer, but when I tried look at the code I saw that it seems iRedMail encrypt the password through python script and then save the password as plain text into the table vamil.mailbox. 

Many thanks in advance.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Online
  • Registered: 2009-05-06
  • Posts: 31,091

Re: Transfer user account (MySQL)

dynaguy wrote:

Here is my question: The password field on my old server is using MySQL's ENCRYPT function to encrypt the password. So how can I transfer the user account safely to the iRedMail?

Please try to prepend '{CRYPT}' in your password. For example, old password is 'xxxxx' (encrypted with ENCRYPT function), the new one in iRedMail should be '{CRYPT}xxxxx'.

This password doesn't work with iRedAdmin (our web admin panel), so you can migrate with '{CRYPT}' for all users first, then reset the admin account to MD5 password.

NOTE: it's recommended to migrate one or just few accounts for testing purpose first, if it works, migrate all if you want.

dynaguy wrote:

I am not a programmer, but when I tried look at the code I saw that it seems iRedMail encrypt the password through python script and then save the password as plain text into the table vamil.mailbox. 

Not exactly.
iRedMail stores MD5 hash by default, not plain text. Plain text is optional and NOT RECOMMENDED.

3 Reply by dynaguy

  • dynaguy
  • Member
  • Offline
  • Registered: 2013-07-17
  • Posts: 4

Re: Transfer user account (MySQL)

Thank you Zhang for the help!

I tested and did not make it work. Here is what I did:
1. I pick a test account, reset the password on the old email server to "12345":
phpMyAdmin output this command:

UPDATE  `mail`.`users` SET  `password` = ENCRYPT(  '12345' ) WHERE CONVERT(  `users`.`email` USING utf8 ) =  'g@veecall.com' LIMIT 1 ;

Now the encrypted string of password "12345" is "2I6JOeg.JukJ."

2. On the iRedMail server, I created the same test email account in the iRedAdmin (web panel), but with a different/random  password "abcde". I looked at phpmyadmin and I see it encrypted to string "$1$by5v9hmD$Q1yNwGab4v6vRlnXZzIEh1". I also make sure this account works by successfully login in RoundCube.

3. Still in phpMaAdmin in the iRedMail, I updated the password like this:

UPDATE  `vmail`.`mailbox` SET  `password` =  '{CRYPT}2I6JOeg.JukJ.' WHERE  `mailbox`.`username` =  'g@veecall.com';

Now I see the password in mySQL changed to string "{CRYPT}2I6JOeg.JukJ."
Then go to RoundCube, try login with password "12345", failed!

==========================
I notice that all the password in iRedMail database start with "$1$", which indicated it is a MD5 crypt string. So I think if I can somehow to transfer MySQL ENCRYPT password string(2I6JOeg.JukJ.) to MD5 string($1$..........), then my problem solved. But I don't know how to do it.

Or maybe I didn't your instruction well. Please take a look of the steps I did above, and let me know if I did it wrong.

4 Reply by dynaguy

  • dynaguy
  • Member
  • Offline
  • Registered: 2013-07-17
  • Posts: 4

Re: Transfer user account (MySQL)

I am trying to understand how the iRedMail put everything together to authenticate user. I see in /etc/dovecot/dovecot-mysql.conf:

password_query = SELECT password FROM mailbox WHERE username='%u' AND active='1'

So the dovecot get the MD5 hash string from mysql.

Here is my old server using Courier-authlib to authenticated user against MySQL. I have this configuration file:

# cat /etc/authlib/authmysqlrc
MYSQL_SERVER localhost
MYSQL_USERNAME mysql_administrator
MYSQL_PASSWORD admin_password
MYSQL_PORT 0
MYSQL_DATABASE mail
MYSQL_USER_TABLE users
MYSQL_CRYPT_PWFIELD password
#MYSQL_CLEAR_PWFIELD password
MYSQL_UID_FIELD 5000
MYSQL_GID_FIELD 5000
MYSQL_LOGIN_FIELD email
MYSQL_HOME_FIELD "/home/vmail"
MYSQL_MAILDIR_FIELD CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/')
#MYSQL_NAME_FIELD
MYSQL_QUOTA_FIELD quota

5 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Online
  • Registered: 2009-05-06
  • Posts: 31,091

Re: Transfer user account (MySQL)

*) Does password without '{CRYPT}' prefix work for you?
*) Here's Dovecot wiki page about password schemes: http://wiki2.dovecot.org/Authentication/PasswordSchemes

6 Reply by dynaguy

  • dynaguy
  • Member
  • Offline
  • Registered: 2013-07-17
  • Posts: 4

Re: Transfer user account (MySQL)

ZhangHuangbin wrote:

*) Does password without '{CRYPT}' prefix work for you?

No, it doesn't work.

I turned on MySQL query log and I see this when I change the user's password from the iRedAdmin web panel:

UPDATE mailbox SET password = '$1$XH98MD4L$nkOzngU9XuMJLt3wP/iJT.', modified = '2013-07-18 19:49:29' WHERE username='g@veecall.com' AND domain='veecall.com'

So this confirmed that the password encryption is done before saved in MySQL, possibly by iRedAdmin code.

7 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Online
  • Registered: 2009-05-06
  • Posts: 31,091

Re: Transfer user account (MySQL)

dynaguy wrote:

So this confirmed that the password encryption is done before saved in MySQL, possibly by iRedAdmin code.

Yes.
If it doesn't work with/without '{CRYPT}' prefix, i have no idea about how to migrate these passwords. Sorry.