Not authenticating - Virtual users and domains with postfix, courier, and mysql

[browngb]

I've followed the instructions from the "Virtual Users and Domains with Postfix, Courier, and Mysql" and am having an issue with logging in. The setup receives emails fine, and apparently sends them fine too, because it will forward to an external email if asked too. The trouble is that I can not log in to the account. Log in attempts return "Login Failed". The log at /var/log/mail.log shows

Jan 25 15:07:55 web1 courierpop3login: Connection, ip=[::ffff:12.21.108.19]
Jan 25 15:08:18 web1 authdaemond.mysql: failed to connect to mysql server (server=localhost, userid=mail_admin.)
Jan 25 15:08:23 web1 courierpop3login: LOGIN FAILED, ip=[::ffff:12.21.108.19]


I see 250-STARTTTLS and 250-AUTH in ehlo. I've checked all the config files to make sure the username and password are correct for mysql connections, and it seems they are.

I am pretty sure that I'm not using the wrong email address and password to cause the login failure, because the log says it can't connect to the DB. I would appreciate it if someone could tell me where to start looking for a configuration problem that would cause it to not connect to the DB. Thanks in advance for your help.

[jacob]

I ran into this same thing. I reset the password for the mail_admin user in phpMyadmin for both the localhost and localhost.localdomain and this corrected the error.

[jacob]

You can also test the user/pass by login in to MySql from the command line.
Try
mysql -u mail_admin -p

If that works then see if you have permissions to access the tables
use mail;
select * from users;

[browngb]

I can log into phpmyadmin and mysql fine with the username and password. It even lets me query for the users. I couldn't reset the password in phpmyadmin because it doesn't give me the option.

It receives and forwards emails just fine, so I know something is working right. It has to do db lookups to perform those actions, right? My gut feeling is that something in the authentication portion isn't set correctly, but I can't find it.

[falko]

Does the MySQL password contain special characters? That might be the reason.

Also make sure that the format of /etc/courier/authmysqlrc is correct (whitespace, etc.). Courier is very finicky about this.

[browngb]

My mysql password is alphanumeric. Below is my /etc/courier/authmysqlrc:

MYSQL_SERVER localhost
MYSQL_USERNAME mail_admin
MYSQL_PASSWORD (removed to protect the guilty)
MYSQL_PORT 0
MYSQL_DATABASE mail
MYSQL_USER_TABLE users
MYSQL_CRYPT_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_QUOTA_FIELD quota


Does it look right?

[martinfst]

I'm not familiar with this tutorial, but this MYSQL_PORT 0does not look oke. The default port is 3306 for MySQL. Could that be (part of) your problem?

[browngb]

I tried changing the port to 3306 and then restarting

/etc/init.d/courier-authdaemon restart
/etc/init.d/courier-imap restart
/etc/init.d/courier-imap-ssl restart
/etc/init.d/courier-pop restart
/etc/init.d/courier-pop-ssl restart

but it still doesn't work. Is there something else I'd need to restart for it to change the settings, or is the port not the problem?

[martinfst]

Sorry, should have been more complete. To see if MySQL is listening, do netstat -tapand check if mysql is running and you can see on which port it's listening. If unsure, post the output of the above command here.

[browngb]

web1:/home/admin# netstat -tap
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 *:imaps *:* LISTEN 1245/couriertcpd
tcp 0 0 *:pop3s *:* LISTEN 1268/couriertcpd
tcp 0 0 localhost.localdo:10024 *:* LISTEN 474/amavisd (master
tcp 0 0 localhost.localdo:10025 *:* LISTEN 1478/master
tcp 0 0 localhost.localdo:mysql *:* LISTEN 1335/mysqld
tcp 0 0 *:843 *:* LISTEN 1509/rpc.statd
tcp 0 0 *:pop3 *:* LISTEN 1927/couriertcpd
tcp 0 0 *:imap2 *:* LISTEN 1911/couriertcpd
tcp 0 0 *:sunrpc *:* LISTEN 996/portmap
tcp 0 0 *:www *:* LISTEN 1778/apache
tcp 0 0 web1.brojoh.com:www 195-64-132-255.sof:1020 SYN_RECV -
tcp 0 0 *:auth *:* LISTEN 1384/inetd
tcp 0 0 *:ftp *:* LISTEN 1516/proftpd: (acce
tcp 0 0 web1.brojoh.com:domain *:* LISTEN 1503/mydns
tcp 0 0 localhost.locald:domain *:* LISTEN 1503/mydns
tcp 0 0 *:ssh *:* LISTEN 1498/sshd
tcp 0 0 *:smtp *:* LISTEN 1478/master
tcp 0 0 web1.brojoh.com:42869 208.67.80.27:www CLOSE_WAIT 1214/freshclam
tcp 0 0 web1.brojoh.com:42868 208.67.80.27:www CLOSE_WAIT 1214/freshclam
tcp 0 0 web1.brojoh.com:39419 mail2.unet.brandeis:www CLOSE_WAIT 1214/freshclam
tcp 0 0 web1.brojoh.com:39418 mail2.unet.brandeis:www CLOSE_WAIT 1214/freshclam
tcp 0 0 web1.brojoh.com:39422 mail2.unet.brandeis:www CLOSE_WAIT 1214/freshclam
tcp 0 0 web1.brojoh.com:38010 clamav-005.mirrors.:www CLOSE_WAIT 1214/freshclam
tcp 0 0 web1.brojoh.com:40938 soyuz.df.lth.se:www CLOSE_WAIT 1214/freshclam
tcp 0 0 web1.brojoh.com:40939 soyuz.df.lth.se:www CLOSE_WAIT 1214/freshclam
tcp 0 0 web1.brojoh.com:52475 182.63.21.72.revers:www CLOSE_WAIT 1214/freshclam
tcp 0 0 web1.brojoh.com:52476 182.63.21.72.revers:www CLOSE_WAIT 1214/freshclam
tcp 0 0 web1.brojoh.com:52459 182.63.21.72.revers:www CLOSE_WAIT 1214/freshclam
tcp 0 0 web1.brojoh.com:54145 edebris.com:www CLOSE_WAIT 1214/freshclam


There's the netstat -tap output. There's probably another thousand lines of that freshclam. Is that normal? I know the database is working for everything but logins. Is there something in there that tells you if it is or isn't listening for the login script?

[falko]

Can you try MYSQL_SERVER localhost.localdomain
instead of
MYSQL_SERVER localhost
in /etc/courier/authmysqlrc?

[browngb]

Ok, added .localdomain and it didn't work. Log output:

Jan 28 13:27:14 web1 courierpop3login: Connection, ip=[::ffff:71.127.83.18]
Jan 28 13:27:25 web1 authdaemond.mysql: failed to connect to mysql server (server=localhost, userid=mail_admin.)
Jan 28 13:27:30 web1 courierpop3login: LOGIN FAILED, ip=[::ffff:71.127.83.18]
Jan 28 19:36:53 web1 postfix/smtpd[2614]: connect from 125-225-1-151.dynamic.hinet.net[125.225.1.151]
Jan 28 19:36:53 web1 postfix/smtpd[2614]: lost connection after CONNECT from 125-225-1-151.dynamic.hinet.net[125.225.1.151]
Jan 28 19:36:53 web1 postfix/smtpd[2614]: disconnect from 125-225-1-151.dynamic.hinet.net[125.225.1.151]
Jan 28 19:53:30 web1 authdaemond.mysql: restarting authdaemond children
Jan 28 19:53:30 web1 authdaemond.mysql: modules="authmysql", daemons=5
Jan 28 19:53:30 web1 authdaemond.mysql: modules="authmysql", daemons=5
Jan 28 19:55:42 web1 courierpop3login: Connection, ip=[::ffff:71.127.83.18]
Jan 28 19:56:04 web1 authdaemond.mysql: failed to connect to mysql server (server=localhost.localdomain, userid=mail_admin.)
Jan 28 19:56:09 web1 courierpop3login: LOGIN FAILED, ip=[::ffff:71.127.83.18]

Is there a way I can debug the authdeamond.mysql to see if it's giving some other info as to why it can't connect?

[falko]

Please try
MYSQL_SERVER 127.0.0.1
then.
Also, what's in /etc/hosts?

Please check the record(s) for mail_admin in the mysql.user table. Which host name is listed there?

[browngb]

When you said check the white space, you weren't kidding. I did check it, but only in front. Turns out there was an extra space behind the username that once I got rid of it, it now connects.

There is still a problem though. While it can now connect to the DB, it's still refusing logins. Here's the log:

Feb 7 15:06:52 web1 courierpop3login: Connection, ip=[::ffff:12.21.108.19]
Feb 7 15:07:08 web1 courierpop3login: LOGIN FAILED, ip=[::ffff:12.21.108.19]


To test the connection, I've been trying to telnet into it (telnet brojoh.com pop3) and using the username scheme of user@domain.com and the password. Is there a problem with special characters in the password? Will telnet not authenticate? Is there a log that would tell me the reason why it refused the login? Thanks for your help.

[falko]

Please don't use special characters in your password and try again.

[browngb]

Well then. It does accept special characters, but it will now authenticate via telnet. The important thing is that I can log in and receive emails now. There is an issue with sending emails, but I'll start a new thread for it. Thanks for all your help.