Virtual Users And Domains With Postfix, Courier And MySQL

[JhonF]

Hi, I followed the tutorial but I still have a problem:

Apparently the courier and postfix can accede without problems to the mysql DB (the telnet 25 work fine), but when I want to access pop3 the mail.err says:

Apr 19 11:25:13 localhost courierpop3login: LOGIN FAILED, ip=[::ffff:192.168.0.2 31]

And the mysql.log:

SELECT email, "", password, 5000, 5000, /home/vmail, CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',-1),'/'), "", "", "" FROM users where email = "user@virtual.test"


I believe that the error is in this SQL sentence, but how I make to modify it?
Thanks.

[till]

If you execute this query in e.g. phpmyadmin, which result do you get?

[arajasi]

Hi everyone,

I have the same problem with courier.
If I execute the query, mysql returns this error message:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '/home/vmail, CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING

Any suggestions?

Thanks.

[JhonF]

phpMyAdmin says:

SQL query:
SELECT email, "",
PASSWORD , 5000, 5000, / home / vmail, CONCAT( SUBSTRING_INDEX( email, '@' , -1 ) , '/', SUBSTRING_INDEX( email, '@' , -1 ) , '/' ) , "", "", ""
FROM users
WHERE email = "user@virtual.test"
LIMIT 0 , 30

MySQL said:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '/home/vmail, CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING.

I believe that Courier is constructing a bad SQL sentence, but I dont know why.


This is my /etc/courier/authmysqlrc

MYSQL_SERVER 127.0.0.1
MYSQL_USERNAME provider_admin
MYSQL_PASSWORD *****
MYSQL_PORT 0
MYSQL_OPT 0
MYSQL_DATABASE provider
MYSQL_USER_TABLE users
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),'/')

thanks

[falko]

What's in /etc/pam.d/smtp?
I see that your /etc/courier/authmysqlrc is not the same than in the tutorial. Please copy the one from the tutorial (only change the MySQL user, password and database).

[JhonF]

It is working!!

I decided to begin the tutorial from the beginning, and now it works well. I was due to have forgotten some the steps.

Falko, Thanks for this tutorial. I have test many of which there are in the web, but this one was the unique one that worked.

A few (add) questions:
- What about Auto-Answers ? (for example, when the user is in vacations)
How it would be possible to be implemented with the structure that we have?

- What about alias ? Not forwardings, alias.

Thanks!

[falko]

- What about Auto-Answers ? (for example, when the user is in vacations)
How it would be possible to be implemented with the structure that we have?
http://www.howtoforge.com/forums/showthread.php?t=2622
Have a look here: http://www.howtoforge.com/forums/showthread.php?t=2622