0

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;
  • For the purpose of reporting, you probably amended the logs to show "email@mydomain.co.uk" from some original email address. The SQL error suggests there may have been unexpected characters in the "email" part of that address. Can you perhaps confirm what the real part was before the @ sign? – JayMcTee Jan 4 '16 at 18:34
  • I did amend from my real email address, yes. The original part before the @ sign was just 'sam', I just checked for leading blanks / whitespace and it looks fine – scastbur Jan 4 '16 at 20:19
0

Copy and paste the SQL from these logs into your mysql client (CLI). I'm thinking that lone \ is causing issues as the string is read from the config file. Perhaps a concat(home, '\\', maildir) But, shouldn't paths be using a / ?

  • hey @edoceo - yes I thought that strange too :/ I just edited my original question to show the changes I have made to the relevant config files. see /etc/courier/authmysqlrc above – scastbur Jan 4 '16 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.