1

Topic: Convert accounts from existing qmail-ldap into SQL backend?

==== 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.4
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): PGSQL
- Linux/BSD distribution name and version: debian 7
- Related log if you're reporting an issue:
====

Hi,

I'm migrating my server from qmail-ldap to iredmail and looking for a way how to load accounts (few hunderds of domains with few thousands of accounts) from my old LDAP server.

As I want to use PgSQL backend, the task is not so trivial and any existing tool I can start with is welcome.

Also there seems to be potential incompatibility in password format - in my LDAP I have mostly MD5 hashed passwords, but there are several old crypt passwords - is there a way how to deal with them? (eg. convert them after successful login?). From what I found on this forum, iRedMail supports only MD5 and SHA256?

Any hints?

2

Re: Convert accounts from existing qmail-ldap into SQL backend?

camel1cz wrote:

As I want to use PgSQL backend, the task is not so trivial and any existing tool I can start with is welcome.

Check scripts used to create mail accounts shipped in iRedMail: iRedMail-0.8.4/tools/create_mail_user_*.

camel1cz wrote:

Also there seems to be potential incompatibility in password format - in my LDAP I have mostly MD5 hashed passwords, but there are several old crypt passwords - is there a way how to deal with them? (eg. convert them after successful login?). From what I found on this forum, iRedMail supports only MD5 and SHA256?

*) For MD5 hashed passwords, you can copy it directly. For example, "$1$WjyyDmty$u1C6fhMKFF0m9ncoNqafj1", store it in column `mailbox.password`.

*) For crypt passwords, if it already has prefix '{CRYPT}' (without quotes), then copy it directly (same as above one). If it doesn't, prepend '{CRYPT}' (without quotes) then store it.

This should work. Let me know whether it works for you or not. if it doesn't work for you, please paste one or two sample passwords here, so that we can help troubleshoot. (Of course you MUST mess up the original password before pasting here, for example, replace several characters.)

3

Re: Convert accounts from existing qmail-ldap into SQL backend?

Thank you for your reply! The info about crypt support is really great - I'm already coding a conversion script and will let you know...

4 (edited by camel1cz 2013-04-21 02:04:45)

Re: Convert accounts from existing qmail-ldap into SQL backend?

ZhangHuangbin, can you please point me in right direction converting following data?

dn: cn=something,ou=example.com,dc=my,dc=dcroot
sn: Lastname
cn: Firstname
objectClass: top
objectClass: person
objectClass: inetOrgPerson
objectClass: qmailUser
mail: something@example.com
mailAlternateAddress: someting2@example.com
mailAlternateAddress: someting3@example.com
mailForwardingAddress: something2@example.net
mailForwardingAddress: something3@example.net
mailMessageStore: example.com/something/
mailQuotaSize: 500000000
uid: something@example.com
userPassword:: e01ENX0yVDhySXFLNkhsNFJwaFZxelBzblJRPT0=

The meaning is as follows:
- the user should be able to log in using uid and userPassword (I have found this to be equivalent of mailbox table),
- the MTA should accept all emails in mail AND mailAlternateAddress
- and forward the to ALL emails in mailForwardingAddress

I understand the mailbox and alias meaning in iRedMail as follows:

EDIT (version 2):

-- create account, user is able to login but unable to receive email as it doesn't have alias entries yet.
INSERT INTO mailbox(username, password, name, storagebasedirectory, storagenode, maildir, quota, domain, active, local_part, created)
VALUES('something@example.com', 'e01ENX0yVDhySXFLNkhsNFJwaFZxelBzblJRPT0=', 'Firstname Lastname', '/var/vmail', 'vmail1', 'example.com/something/', '500', 'example.com', '1', 'something', NOW());

-- point email address to the account (now emails go to the account available via eg IMAP)
INSERT INTO alias (address, goto, domain, created, active)
VALUES ('something@example.com', 'something@example.com,something2@example.net,something3@example.net','example.com', NOW(), 1);

-- now all mailAlternateAddresses needs to be inserted as alias entries each in it's own row
INSERT INTO alias (address, goto, domain, created, active)
VALUES ('something@example.com', 'something@example.com,something2@example.net,something3@example.net','example.com', NOW(), 1);
INSERT INTO alias (address, goto, domain, created, active)
VALUES ('something2@example.com', 'something@example.com,something2@example.net,something3@example.net','example.com', NOW(), 1);
INSERT INTO alias (address, goto, domain, created, active)
VALUES ('something3@example.com', 'something@example.com,something2@example.net,something3@example.net','example.com', NOW(), 1);

And in case I don't want keep copy of emails on server, I should left out the something@example.com in the goto?

5

Re: Convert accounts from existing qmail-ldap into SQL backend?

camel1cz wrote:

And in case I don't want keep copy of emails on server, I should left out the something@example.com in the goto?

You should forward it to other address, don't use empty value in `alias.goto`.

6

Re: Convert accounts from existing qmail-ldap into SQL backend?

Great - I have the script (PHP) ready... I can confirm, the passwords have the same format as in LDAP (tested crypt, md5, sha and shaa). They are not base64 encoded.

One small issue left - do you happen to know what's the equivalent of catchall in LDAP? Have tested *, catchall and catch-all@example.com with no success...

7

Re: Convert accounts from existing qmail-ldap into SQL backend?

OK, found it by myself - the address attribute should be the domain name (w/o the @)

I'm attaching the code in PHP - basically you need only to edit the IN LDIF and/or OUT SQL filenames...

Don't forget to check manually if the script is doing what you want!

Use at your own risk!

Post's attachments

convert-0.1.1.php.gz 2.36 kb, 5 downloads since 2013-04-22 

You don't have the permssions to download the attachments of this post.