1

Topic: Adding Users with create_mail_user_MySQL.sh trouble...??????????

Hi I need help on geting the script to work . when I run it it says:

Usage: sh create_mail_user_MySQL.sh userlist.txt

What do I have to do to make this work?? I would Appreciate the help thank you

----

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

2

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

write :  console#sh  create_mail_user_MySQL.sh

or write

console#./create_mail_user_MySQL.sh

first edit the .sh file

3

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

I improved tools/create_mail_user_MySQL.sh in revision 983, it's easier now:

# sh create_mail_user_MySQL.sh domain.ltd newuser [newuser2 newuser3 ...]

Download: http://iredmail.googlecode.com/svn/trun … r_MySQL.sh

4

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

I downloaded file create_mail_user_MySQL.sh. I modify like

# ---- Maildir settings ----
# Maildir style: hashed, normal.
# Hashed maildir style, so that there won't be many large directories
# in your mail storage file system. Better performance in large scale
# deployment.
# Format: e.g. username@domain.td
#   hashed  -> domain.ltd/u/us/use/username/
#   normal  -> domain.ltd/username/
# Default hash level is 3.
MAILDIR_STYLE='normal'      # hashed, normal.

file output.sql

INSERT INTO mailbox (username, password, name, maildir, quota, domain, active)
    VALUES ('user01@example.org', '$1$2XPy.uYI$fasR6Pw/cuB9dlBkmzgqs1', 'user01', 'example.org/user01/', '100', 'example.org', '1');

but user01 not login.

Thank for reading.

5

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

Could you please post related IMAP/POP3 login auth log in /var/log/dovecot.log?

6

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

file /var/log/dovecot.log

dovecot: May 07 18:02:16 Info: imap-login: Disconnected (auth failed, 1 attempts): user=<user01@example.org>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured
dovecot: May 07 18:05:23 Info: imap-login: Disconnected (auth failed, 1 attempts): user=<user01@example.org>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured
dovecot: May 07 18:08:14 Info: imap-login: Disconnected (auth failed, 1 attempts): user=<user01@example.org>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured
dovecot: May 07 18:08:19 Info: imap-login: Disconnected (auth failed, 1 attempts): user=<user01@example.org>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured

I checked in postfixadmin no user01 exist.
Thank for your help.

7

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

nothingelse wrote:

f
dovecot: May 07 18:02:16 Info: imap-login: Disconnected (auth failed, 1 attempts)

Enable debug options in /etc/dovecot.conf:

mail_debug = yes
auth_debug = yes
auth_debug_passwords = yes

Restart dovecot service, try again. smile

8

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

dovecot: May 07 19:04:10 Info: dovecot v1.1.11 starting up (core dumps disabled)
dovecot: May 07 19:04:10 Info: auth-worker(default): mysql: Connected to localhost (vmail)
dovecot: May 07 19:04:11 Info: auth(default): new auth connection: pid=12251
dovecot: May 07 19:04:11 Info: auth(default): new auth connection: pid=12253
dovecot: May 07 19:04:11 Info: auth(default): new auth connection: pid=12252
dovecot: May 07 19:04:11 Info: auth(default): new auth connection: pid=12248
dovecot: May 07 19:04:11 Info: auth(default): new auth connection: pid=12249
dovecot: May 07 19:04:11 Info: auth(default): new auth connection: pid=12250
dovecot: May 07 19:04:50 Info: auth(default): client in: AUTH    1    PLAIN    service=imap    secured    lip=127.0.0.1    rip=127.0.0.1    lport=143    rport=46435    resp=AHVzZXIwMUBleGFtcGxlLm9yZwB1c2VyMDE=
dovecot: May 07 19:04:50 Info: auth-worker(default): sql(user01@example.org,127.0.0.1): query: SELECT password FROM mailbox WHERE username='user01@example.org' AND active='1' AND expired >= NOW()
dovecot: May 07 19:04:50 Info: auth-worker(default): sql(user01@example.org,127.0.0.1): unknown user
dovecot: May 07 19:04:51 Info: auth(default): new auth connection: pid=12259
dovecot: May 07 19:04:52 Info: auth(default): client out: FAIL    1    user=user01@example.org
dovecot: May 07 19:04:52 Info: imap-login: Disconnected (auth failed, 1 attempts): user=<user01@example.org>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured
dovecot: May 07 19:04:57 Info: auth(default): client in: AUTH    1    PLAIN    service=imap    secured    lip=127.0.0.1    rip=127.0.0.1    lport=143    rport=46436    resp=AHVzZXIwMUBleGFtcGxlLm9yZwA4ODg4ODg4OA==
dovecot: May 07 19:04:57 Info: auth-worker(default): sql(user01@example.org,127.0.0.1): query: SELECT password FROM mailbox WHERE username='user01@example.org' AND active='1' AND expired >= NOW()
dovecot: May 07 19:04:57 Info: auth-worker(default): sql(user01@example.org,127.0.0.1): unknown user
dovecot: May 07 19:04:58 Info: auth(default): new auth connection: pid=12260
dovecot: May 07 19:04:59 Info: auth(default): client out: FAIL    1    user=user01@example.org
dovecot: May 07 19:04:59 Info: imap-login: Disconnected (auth failed, 1 attempts): user=<user01@example.org>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured

I think sh create_mail_user_MySQL.sh example.org user01. It not create record user01 in vmail.mailbox. I checked database vmail.mailbox. I see user01 not exist.
Thank for your help.

9

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

After you execute create_mail_user_MySQL.sh, it will print some message on the terminal:

SQL template file was generated successfully, Please import it
*MANUALLY* after verify the records:

    - output.sql

Steps looks like below:

    # mysql -uroot -p
    mysql> USE vmail;
    mysql> SOURCE output.sql;

Did you see that?

10

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

I see lines.

[root@mx ~]# sh create_mail_user_MySQL.sh example.org user01

SQL template file was generated successfully, Please import it
*MANUALLY* after verify the records:

    - output.sql

Steps looks like below:

    # mysql -uroot -p
    mysql> USE vmail;
    mysql> SOURCE output.sql;

Please help for me.

11

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

I've got it. I forgot use command.

# mysql -uroot -p
    mysql> USE vmail;
    mysql> SOURCE output.sql;

Thank for your help.

12

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

Hi, All!

There is script that can add user with not-predifined password

#!/bin/bash


# Usage:
#   program_name mysql_root_passwd mail_user_name mail_user_passwd
if [ $# -lt 4 ]; then
    echo "Usage: $0 mysql_root_passwd mail_user_name mail_user_passwd mysql_user_domain"
exit;
fi

DEFAULT_PASSWD=$3
username=$2
DOMAIN=$4

# Default mail quota.
DEFAULT_QUOTA='100'   # 100 -> 100M

# Cyrpt the password.
    export CRYPT_PASSWD="$(openssl passwd -1 ${DEFAULT_PASSWD})"

    maildir="${DOMAIN}/${username}"

#sqltext="use vmail;INSERT INTO mailbox (username, password, name, maildir, quota, domain, active) VALUES (\'${username}@${DOMAIN}\', \'${CRYPT_PASSWD}\', \'${username}\', \'${maildir}\', \'${DEFAULT_QUOTA}\', \'${DOMAIN}\', \'1\');"
sqltext="use vmail;INSERT INTO mailbox (username, password, name, maildir, quota, domain, active) VALUES ('${username}@${DOMAIN}', '${CRYPT_PASSWD}', '${username}', '${maildir}', '${DEFAULT_QUOTA}', '${DOMAIN}', '1');"

#sqltext="use vmail;SELECT * FROM `mailbox`;"
echo $sqltext;

/usr/bin/mysql -uroot -p$1 -h localhost -e "$sqltext"

13

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

Would using phpmyadmin to load the output.sql file be any different than using the command line MySQL?

14

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

Here is my log from dovecot


dovecot: Jul 22 09:51:07 Info: auth(default): client in: AUTH   1       PLAIN  service=imap     secured lip=127.0.0.1   rip=127.0.0.1   lport=143       rport=48937     resp=AGphc29uLmRAb2Zpbm5pZ2Fucy5jb20Ad3d3NGlyaXNo
dovecot: Jul 22 09:51:07 Info: auth-worker(default): sql(jason.d@ofinnigans.com,127.0.0.1): query: SELECT password FROM mailbox WHERE username='jason.d@ofinnigans.com' AND active='1' AND expired >= NOW()
dovecot: Jul 22 09:51:07 Info: auth-worker(default): sql(jason.d@ofinnigans.com,127.0.0.1): Password mismatch
dovecot: Jul 22 09:51:07 Info: auth-worker(default): sql(jason.d@ofinnigans.com,127.0.0.1): CRYPT(www4irish) != '$1$ANa.jBCB$20zD0I2MGWi38CYIJgiMN1'
dovecot: Jul 22 09:51:07 Info: auth(default): new auth connection: pid=2982
dovecot: Jul 22 09:51:09 Info: auth(default): client out: FAIL  1       user=jason.d@ofinnigans.com
dovecot: Jul 22 09:51:09 Info: imap-login: Disconnected (auth failed, 1 attempts): user=<jason.d@ofinnigans.com>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured


Here is my DB entry into mailbox

SQL result

Host: localhost
Database: vmail
Generation Time: Jul 22, 2009 at 09:55 AM
Generated by: phpMyAdmin 2.11.9.4 / MySQL 5.0.45
SQL query: SELECT * FROM `mailbox` where username like "jason.d%" LIMIT 0, 30 ;
Rows: 1
username     password     name     maildir     quota     domain     department     rank     enablesmtp     enablepop3     enableimap     enabledeliver     enablesieve     created     modified     expired     active
jason.d@ofinnigans.com     $1$ANa.jBCB$20zD0I2MGWi38CYIJgiMN1     jason.d     ofinnigans.com/jason.d/     100     ofinnigans.com           normal     1     1     1     1     1     0000-00-00 00:00:00     0000-00-00 00:00:00     9999-12-31 00:00:00     1

Also DB entry from alias

SQL result

Host: localhost
Database: vmail
Generation Time: Jul 22, 2009 at 09:56 AM
Generated by: phpMyAdmin 2.11.9.4 / MySQL 5.0.45
SQL query: SELECT * FROM `alias` where address like 'jason.d%' LIMIT 0, 30 ;
Rows: 1
address     goto     domain     created     modified     expired     active
jason.d@ofinnigans.com     jason.d@ofinnigans.com     ofinnigans.com     0000-00-00 00:00:00     0000-00-00 00:00:00     9999-12-31 00:00:00     1

15

Re: Adding Users with create_mail_user_MySQL.sh trouble...??????????

dovecot: Jul 22 09:51:07 Info: auth-worker(default): sql(jason.d@ofinnigans.com,127.0.0.1): Password mismatch

Did you see this log entry?