This problem is driving me insane and I'm now stumped. Need some help please ;)
I'm setting up a mail server on an Ubuntu 14 AWS EC2 instance using mysql backend, postfix MTA and roundcube webmail client. Everything is working up to the point of logging in via roundcube. ie, I can send and receive mail from the shell when i telnet into localhost on port 25 and mail boxes are created in /var/mail/virtual/. All looks good.
When I try to login with a user that exists in the "users" table the roundcube client returns the following error:
Connection to storage server failed.
So. Here is the /var/log/roundcube/error message:
[04-Jan-2016 17:43:42 +0000]: IMAP Error: Login failed for email@mydomain.co.uk from [IPADDR] LOGIN: Temporary problem, please try again later in /usr/share/roundcube/program/lib/Roundcube/rcube_imap.php on line 184 (POST /roundcube/?_task=login&_action=login)
and the error logged in /var/log/mail :
Jan 4 17:49:56 ip-172-31-46-150 imapd: Connection, ip [::ffff:127.0.0.1]
Jan 4 17:49:56 ip-172-31-46-150 authdaemond: received auth request, service=imap, authtype=login
Jan 4 17:49:56 ip-172-31-46-150 authdaemond: authmysql: trying this module
Jan 4 17:49:56 ip-172-31-46-150 authdaemond: authmysqllib: connected. Versions: header 50534, client 50546, server 50546
Jan 4 17:49:56 ip-172-31-46-150 authdaemond: SQL query: SELECT id, crypt, "", uid, gid, home, concat(home,'\',maildir), "", name, "" FROM users WHERE id = 'email@mydomain.co.uk' AND (enabled=1)
Jan 4 17:49:56 ip-172-31-46-150 authdaemond: mysql_query failed, reconnecting: 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 '@mydomain.co.uk' AND (enabled=1)' at line 1
Jan 4 17:49:56 ip-172-31-46-150 authdaemond: authmysqllib: connected. Versions: header 50534, client 50546, server 50546
Jan 4 17:49:56 ip-172-31-46-150 authdaemond: mysql_query failed second time, giving up: 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 '@mydomain.co.uk' AND (enabled=1)' at line 1
Jan 4 17:49:56 ip-172-31-46-150 authdaemond: authmysql: TEMPFAIL - no more modules will be tried
Jan 4 17:49:56 ip-172-31-46-150 imapd: LOGIN FAILED, user=email@mydomain.co.uk, ip=[::ffff:127.0.0.1]
Jan 4 17:49:56 ip-172-31-46-150 imapd: authentication error: Input/output error
I can obviously post up configuration files upon request but I didn't want to clog up the original question too much.
Any help much appreciated! Thanks
relevant modified config files below:
I have made the following changes to /etc/courier/authdaemonrc
authmodulelist="authmysql"
DEBUG_LOGIN=2
/etc/courier/authmysqlrc
MYSQL_USERNAME mail
MYSQL_PASSWORD mypassword
MYSQL_DATABASE maildb
MYSQL_USER_TABLE users
MYSQL_CRYPT_PWFIELD crypt
MYSQL_MAILDIR_FIELD concat(home,'/',maildir)
MYSQL_WHERE_CLAUSE enabled=1
/etc/roundcube/main.inc.php
$rcmail_config['default_host'] = 'localhost';
$rcmail_config['default_port'] = '143';
$rcmail_config['imap_force_ns'] = true;
$rcmail_config['smtp_server'] = 'localhost';
$rcmail_config['smtp_port'] = 25;
$rcmail_config['smtp_helo_host'] = 'localhost';
$rcmail_config['create_default_folders'] = TRUE;
