1 Topic by kiwistag

  • kiwistag
  • Member
  • Offline
  • Registered: 2015-10-30
  • Posts: 15

Topic: New user database integration query

==== Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.3
- Linux/BSD distribution name and version: CentOS 7
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro?
- Related log if you're reporting an issue:
====

We are migrating between an existing webhost and iRedMail, due to the complexities involved and the fact we want to start new users using the new server I am looking at a portal for admin to create the new account which also updates a script to query the webhost server via imapsync.

So basically the portal will:
Create a new user in iRedMail
Query for existing credentials of the user on the existing webhost.
Add the credentials to the imapsync script and pull the data into the new iRedMail account (on schedule).

I understand that it's possible to update the database to add the new user but is there any other steps involved like to create the physical mailshare folder, etc?

----

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
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,172

Re: New user database integration query

*) We don't have such tool yet. but it should be easy for you to write one if you're good at shell scripting.
*) What do you mean "physical mailshare folder"? Does this match your need?
http://www.iredmail.org/docs/public.folder.html

By the way, we offer paid remote support for mail server migration:
http://www.iredmail.org/support.html

3 Reply by kiwistag

  • kiwistag
  • Member
  • Offline
  • Registered: 2015-10-30
  • Posts: 15

Re: New user database integration query

ZhangHuangbin wrote:

*) We don't have such tool yet. but it should be easy for you to write one if you're good at shell scripting.
*) What do you mean "physical mailshare folder"? Does this match your need?
http://www.iredmail.org/docs/public.folder.html

By the way, we offer paid remote support for mail server migration:
http://www.iredmail.org/support.html

Hi Zhang. Part of the issue with migration from our webhost provider is that we would have to en-masse reset our clients passwords, the current hosted system has no ways to pull data out of it's environment (we've asked and the securities to do so are not allowed to us).

The physical mail folder I mean is typical to /var/vmail/vmail1/*domainname*/a/a/a/aaanexample-2015.12.12.12.00.00

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,172

Re: New user database integration query

kiwistag wrote:

but is there any other steps involved like to create the physical mailshare folder, etc?

If you create user by inserting SQL records directly, you're free to change the maildir path. If you create with iRedAdmin, you can find 3 options to control the maildir path in libs/default_settings.py (if you want to change settings in this file, please just copy the settings to "settings.py", do not touch "libs/default_settings.py"):

# It's RECOMMEND for better performance. Samples:
#   - hashed: domain.ltd/u/s/e/username-2009.09.04.12.05.33/
#   - non-hashed: domain.ltd/username-2009.09.04.12.05.33/
MAILDIR_HASHED = True

# Prepend domain name in path. Samples:
#   - with domain name: domain.ltd/username/
#   - without: username/
MAILDIR_PREPEND_DOMAIN = True

# Append timestamp in path. Samples:
#   - with timestamp: domain.ltd/username-2010.12.20.13.13.33/
#   - without timestamp: domain.ltd/username/
MAILDIR_APPEND_TIMESTAMP = True

5 Reply by kiwistag

  • kiwistag
  • Member
  • Offline
  • Registered: 2015-10-30
  • Posts: 15

Re: New user database integration query

So, would I create the new user simply in the vmail database under the mailbox table (SQL install) with the appropriate flags/settings and a generated SSHA512 passkey?

6 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,172

Re: New user database integration query

Yes you can do it this way.

7 Reply by kiwistag (edited by kiwistag 2016-03-09 07:27:44)

  • kiwistag
  • Member
  • Offline
  • Registered: 2015-10-30
  • Posts: 15

Re: New user database integration query

For some reason existing records in vmail/mailbox have null values such as transport and department. However in the table setup they do not allow null values (allow null=no), is there anything I should put in there that won't affect the records? If so what?
I found this when creating the SQL query to add a new mailbox.

8 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,172

Re: New user database integration query

*) if "mailbox.transport" is empty, Postfix will use per-domain transport in "domain.transport" instead. so it's ok.
*) "mailbox.department" is optional, so it's ok if it's empty.

9 Reply by kiwistag

  • kiwistag
  • Member
  • Offline
  • Registered: 2015-10-30
  • Posts: 15

Re: New user database integration query

So I can change those fields to allow null in the database table? At present they won't allow null values.

10 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,172

Re: New user database integration query

Why are you insisting in using NULL value? Their values are just empty string, not null.

11 Reply by kiwistag

  • kiwistag
  • Member
  • Offline
  • Registered: 2015-10-30
  • Posts: 15

Re: New user database integration query

It seems that with using my script, if a user e-mail address has a decimal point in it, the mailbox is not created, BUT is shown in iRedAdmin as being all normal as well as in the mailbox database from what I can see...

May 16 22:08:02 mail postfix/smtpd[6843]: NOQUEUE: reject: RCPT from mail-yw0-f174.google.com[209.85.161.174]: 550 5.1.1 <test.user2@isp.domain>: Recipient address rejected: User unknown in local recipient table; from=<sender@anotherdomain.com> to=<test.user2@isp.domain> proto=ESMTP helo=<mail-yw0-f174.google.com>

It only happens with ones with say the decimal point in it.

12 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,172

Re: New user database integration query

Dear kiwistag,

Seems the sql records you generated have improper values.

*) Did you compare the SQL commands used to generate user account with the ones in iRedMail-0.9.5-1/tools/create_mail_user_SQL.sh?

*) Try commands below to figure it out:

cd /etc/postfix/mysql/
postmap -v -q "test.user2@your_domain.com" mysql:./sender_login_maps.cf
postmap -v -q "test.user2@your_domain.com" mysql:./virtual_mailbox_maps.cf

13 Reply by kiwistag

  • kiwistag
  • Member
  • Offline
  • Registered: 2015-10-30
  • Posts: 15

Re: New user database integration query

Results:

[root@mail mysql]# postmap -v -q "test.account@domain.com" mysql:./sender_login_maps.cf
postmap: name_mask: ipv4
postmap: inet_addr_local: configured 3 IPv4 addresses
postmap: cfg_get_str: ./sender_login_maps.cf: user = vmail
postmap: cfg_get_str: ./sender_login_maps.cf: password = *****notimportant*****
postmap: cfg_get_str: ./sender_login_maps.cf: dbname = vmail
postmap: cfg_get_str: ./sender_login_maps.cf: result_format = %s
postmap: cfg_get_int: ./sender_login_maps.cf: expansion_limit = 0
postmap: cfg_get_str: ./sender_login_maps.cf: query = SELECT mailbox.username FROM mailbox,domain WHERE mailbox.username='%s' AND mailbox.domain='%d' AND mailbox.domain=domain.domain AND mailbox.enablesmtp=1 AND mailbox.active=1 AND domain.backupmx=0 AND domain.active=1
postmap: cfg_get_str: ./sender_login_maps.cf: domain =
postmap: cfg_get_str: ./sender_login_maps.cf: hosts = 127.0.0.1
postmap: dict_open: mysql:./sender_login_maps.cf
postmap: dict_mysql_get_active: attempting to connect to host 127.0.0.1
postmap: dict_mysql: successful connection to host 127.0.0.1
postmap: dict_mysql: successful query from host 127.0.0.1
postmap: dict_mysql_lookup: retrieved 1 rows
test.account@domain.com
[root@mail mysql]# postmap -v -q "test.account@domain.com" mysql:./virtual_mailbox_maps.cf
postmap: name_mask: ipv4
postmap: inet_addr_local: configured 3 IPv4 addresses
postmap: cfg_get_str: ./virtual_mailbox_maps.cf: user = vmail
postmap: cfg_get_str: ./virtual_mailbox_maps.cf: password = *****notimportant*****
postmap: cfg_get_str: ./virtual_mailbox_maps.cf: dbname = vmail
postmap: cfg_get_str: ./virtual_mailbox_maps.cf: result_format = %s
postmap: cfg_get_int: ./virtual_mailbox_maps.cf: expansion_limit = 0
postmap: cfg_get_str: ./virtual_mailbox_maps.cf: query = SELECT CONCAT(mailbox.storagenode, '/', mailbox.maildir, '/Maildir/') FROM mailbox,domain WHERE mailbox.username='%s' AND mailbox.active=1 AND mailbox.enabledeliver=1 AND domain.domain = mailbox.domain AND domain.active=1
postmap: cfg_get_str: ./virtual_mailbox_maps.cf: domain =
postmap: cfg_get_str: ./virtual_mailbox_maps.cf: hosts = 127.0.0.1
postmap: dict_open: mysql:./virtual_mailbox_maps.cf
postmap: dict_mysql_get_active: attempting to connect to host 127.0.0.1
postmap: dict_mysql: successful connection to host 127.0.0.1
postmap: dict_mysql: successful query from host 127.0.0.1
postmap: dict_mysql_lookup: retrieved 1 rows
vmail1/yrless.nz/w/a/l/test.account-2016.5.4.8.38.5//Maildir/

14 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,172

Re: New user database integration query

The postmap commands look just fine, no error/warning.

You replied in 2 weeks, i'm not sure whether we're still talking about the same issue. So to make it clear: what's the issue now?

15 Reply by kiwistag

  • kiwistag
  • Member
  • Offline
  • Registered: 2015-10-30
  • Posts: 15

Re: New user database integration query

Same issue unfortunately.
For one user they have the same error as mentioned prior even though all shows fine from the postmap queries.

16 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,172

Re: New user database integration query

I'm afraid that you may need to re-create the mail user account, then use the same maildir path to read old mailbox.