Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Ubuntu 8.10)

[VTCop]

I have one problem with this tutorial

Postfix works as MTA, but there is a problem. Its crearte the virtual users folder in wrong place. Then when courier goes to look for email says: chdir failed.

I think the problem is that postfix leave the emails in one place an courier search it in other.

Postfix create this folder structure
/home/vmail/domain1.com/cur
/home/vmail/domain1.com/new
/home/vmail/domain1.com/tmp

/home/vmail/domain2.com/cur
/home/vmail/domain2.com/new
/home/vmail/domain2.com/tmp

But courier search the emails at

/home/vmail/domain1.com/user/
/home/vmail/domain2.com/user/

I think that the problem is in postfix configuration at file /etc/postfix/mysql-virtual_mailboxes.cf
At the tutorial says:

user = mail_admin
password = mail_admin_password
dbname = mail
query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'
hosts = 127.0.0.1

I think than the line query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s' its wrong. Its output some like domain1.com

Courier search the emails with the configuration in /etc/courier/authmysqlrc:

MYSQL_SERVER localhost
MYSQL_USERNAME mail_admin
MYSQL_PASSWORD mail_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

The line CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') output some like domain1.com/user but Postfix configuration output some like domain1.com/

Is this true?
Is this the cause of chdir failed at courier?

I think that the correct line at /etc/postfix/mysql-virtual_mailboxes.cf must be:
query = SELECT CONCAT(SUBSTRING_INDEX(email, '@', -1), '/', SUBSTRING_INDEX(email, '@', 1), '/') FROM users WHERE email='%s'
Am I ok?

Thanks, I have learned a lot with this tutorial

[VTCop]

Ok, I have seen that the tutorial has been corrected

Thanks

[VTCop]

Another thing, how could I check if SpamAssaissin is working correctly? any log or some?

I see at /etc/default/spamsassissn that spamassassin daemon is not enabled. Is it normal?

[wasimriz]

I had checked my cat /etc/postfix/mysql-virtual_mailboxes.cf
below is the setting
user = mail_admin
password = mail_admin_password
dbname = mail
#query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'
# Added by wasim 7 April 09
query = SELECT CONCAT(SUBSTRING_INDEX(email, '@', -1), '/', SUBSTRING_INDEX(email, '@', 1), '/') FROM users WHERE email='%s'
hosts = 127.0.0.1

# cat /etc/courier/authmysqlrc
MYSQL_SERVER localhost
MYSQL_USERNAME mail_admin
MYSQL_PASSWORD mail_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

Please tell me what changes i need to make so that i can able to login from Webmail

[falko]

If you've followed the tutorial to the letter and sent a welcome mail to that account, you should be able to log in without any problems. If not, please check /var/log/mail.log.

[VTCop]

You must be sure that at ALL lines like

MYSQL_PASSWORD mail_admin_password

you put your mysql user password.

There is some lines on some files that you must forgotten, sure.

For example at six Postfix configuration files
/etc/postfix/mysql-virtual_*.cf

or some saslauthd configuration file /etc/pam.d/smtp

auth required pam_mysql.so user=mail_admin passwd=mail_admin_password host=127.0.0.1 db=mail table=users usercolumn=email passwdcolumn=password crypt=1
account sufficient pam_mysql.so user=mail_admin passwd=mail_admin_password host=127.0.0.1 db=mail table=users usercolumn=email passwdcolumn=password crypt=1

This is a common error

[rieschl]

Originally Posted by VTCop

Another thing, how could I check if SpamAssaissin is working correctly? any log or some?

hi

sure! spamassassin has a built-in test-feature: http://spamassassin.apache.org/gtube/
send a mail containing that string to your server and then search for it in the quarantine-folder (/var/lib/amavis/virusmails)

Originally Posted by VTCop

I see at /etc/default/spamsassissn that spamassassin daemon is not enabled. Is it normal?

yes, it's normal, because you don't use spamassassin directly but via amavis.


hth

cheers,
thomas

[falko]

Originally Posted by VTCop

Ok, I have seen that the tutorial has been corrected

Thanks

I didn't change the tutorial, and it was always working for me.

Another thing, how could I check if SpamAssaissin is working correctly? any log or some?

Please check /var/log/mail.log.