no login with MySQL

Sun Sep 13 17:51:38 UTC 2015

On 2015-09-13 13:58, Andreas Meyer wrote:
> Hello!
> 
> Setup again to authenticate my users with MySQL but can't login.
> 
> Sep 13 11:55:49 auth: Info:
> passwd-file(a.meyer at nimmini.de,95.88.63.177,<R7Qe9J0fTgBfWD+x>):
> unknown user
> Sep 13 11:55:49 auth: Info:
> passwd-file(a.meyer at nimmini.de,95.88.63.177,<R7Qe9J0fTgBfWD+x>):
> unknown user
> Sep 13 11:55:49 imap-login: Info: Login: user=<a.meyer at nimmini.de>,
> method=CRAM-MD5, rip=95.88.63.177, lip=46.38.231.143, mpid=30826, TLS,
> TLSv1 with cipher ECDHE-RSA-AES256-SHA (256/256 bits)
> Sep 13 11:55:49 imap(a.meyer at nimmini.de): Error: user
> a.meyer at nimmini.de: Initialization failed: Namespace '': Home
> directory not set for user. Can't expand ~/ for mail root dir in: ~/
> Sep 13 11:55:49 imap(a.meyer at nimmini.de): Error: Invalid user
> settings. Refer to server log for more information.

i see an error here "Error: Invalid user settings" relating to the users 
mail_location direcory.

Is "mail_location" (and not so important "mail_home") defined?

With my setup "mail" and "home" is returned from the userdb query from 
mysql

home: /srv/mail/%u
mail: /srv/mail/%u/Maildir

see http://wiki2.dovecot.org/VirtualUsers/Home

> Sep 13 11:55:49 auth: Debug: auth client connected (pid=30825)
> Sep 13 11:55:49 auth: Debug: client in: AUTH    1       CRAM-MD5
>  service=imap    secured session=R7Qe9J0fTgBfWD+x
> lip=46.38.231.143       rip=95.88.63.177        lport=143 rport=45646
> Sep 13 11:55:49 auth: Debug: client passdb out: CONT    1
> PDkzMDcwNTI5Mzk5NTk1NTYuMTQ0MjEzODE0OUBiaXRtYWNoaW5lMT4=
> Sep 13 11:55:49 auth: Debug: client in: CONT<hidden>
> Sep 13 11:55:49 auth: Debug:
> passwd-file(a.meyer at nimmini.de,95.88.63.177,<R7Qe9J0fTgBfWD+x>):
> lookup: user=a.meyer at nimmini.de file=/etc/dovecot/passwd
> Sep 13 11:55:49 auth-worker(30822): Debug:
> sql(a.meyer at nimmini.de,95.88.63.177): query: SELECT username AS
> username, password FROM mailbox WHERE username = 'a.meyer at nimmini.de'
> AND active = 1
> Sep 13 11:55:49 auth: Debug: client passdb out: OK      1
> user=a.meyer at nimmini.de
> Sep 13 11:55:49 auth: Debug: master in: REQUEST 997457921       30821
>  1       dc2456b4ee1453e5458806ab92da9ee7        session_pid=30826
>   request_auth_token
> Sep 13 11:55:49 auth: Debug:
> passwd-file(a.meyer at nimmini.de,95.88.63.177,<R7Qe9J0fTgBfWD+x>):
> lookup: user=a.meyer at nimmini.de file=/etc/dovecot/passwd
> Sep 13 11:55:49 auth-worker(30822): Debug:
> passwd-file(a.meyer at nimmini.de,95.88.63.177): SELECT maildir, uid,
> gid, concat('*:storage=', quota) AS quota_rule,
> concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox
> WHERE username = 'a.meyer at nimmini.de'
> Sep 13 11:55:49 auth: Debug: master userdb out: USER    997457921
>  a.meyer at nimmini.de      maildir=/var/spool/vhosts/nimmini.de/
> uid=5000        gid=5000        quota_rule=*:storage=5242880
> quota_rule2=Trash:storage=+100
> auth_token=2a4c8e78ff8fcf3f9599eecbefb6a5605f22abe5
> Sep 13 11:55:49 imap: Debug: Loading modules from directory:
> /usr/lib64/dovecot/modules
> Sep 13 11:55:49 imap: Debug: Module loaded:
> /usr/lib64/dovecot/modules/lib01_acl_plugin.so
> Sep 13 11:55:49 imap: Debug: Module loaded:
> /usr/lib64/dovecot/modules/lib02_imap_acl_plugin.so
> Sep 13 11:55:49 imap: Debug: Module loaded:
> /usr/lib64/dovecot/modules/lib10_quota_plugin.so
> Sep 13 11:55:49 imap: Debug: Module loaded:
> /usr/lib64/dovecot/modules/lib11_imap_quota_plugin.so
> Sep 13 11:55:49 imap: Debug: Added userdb setting:
> plugin/maildir=/var/spool/vhosts/nimmini.de/
> Sep 13 11:55:49 imap: Debug: Added userdb setting:
> plugin/quota_rule=*:storage=5242880
> Sep 13 11:55:49 imap: Debug: Added userdb setting:
> plugin/quota_rule2=Trash:storage=+100
> Sep 13 11:55:49 imap(a.meyer at nimmini.de): Debug: Effective uid=5000,
> gid=5000, home=
> Sep 13 11:55:49 imap(a.meyer at nimmini.de): Debug: Quota root: name=User
> quota backend=maildir args=
> Sep 13 11:55:49 imap(a.meyer at nimmini.de): Debug: Quota rule: root=User
> quota mailbox=* bytes=5368709120 messages=0
> Sep 13 11:55:49 imap(a.meyer at nimmini.de): Debug: Quota rule: root=User
> quota mailbox=Trash bytes=+102400 messages=0
> Sep 13 11:55:49 imap(a.meyer at nimmini.de): Debug: Quota grace:
> root=User quota bytes=53687091 (1%)

> Sep 13 11:55:49 imap(a.meyer at nimmini.de): Debug: Namespace inbox:
> type=private, prefix=, sep=/, inbox=yes, hidden=no, list=yes,
> subscriptions=yes location=maildir:~/

on one of my accounts this same line looks like this:
imap(aaa at kivalo.at): Debug: Namespace inbox: type=private, prefix=, 
sep=/, inbox=yes, hidden=no, list=yes, subscriptions=yes 
location=maildir:/srv/mail/aaa at kivalo.at/Maildir:LAYOUT=fs

where location is the "mail_location" setting from 10-mail.conf and is 
overridden by the userdb from sql for every user.

it's probably enough to set the option mail_location in 10-mail.conf to 
an absolute path.

> This is what claws-mail says:
> 
> [12:22:48] IMAP4< * OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR
> LOGIN-REFERRALS ID ENABLE IDLE STARTTLS AUTH=PLAIN AUTH=CRAM-MD5]
> Dovecot ready.
> [12:22:48] IMAP4> 1 STARTTLS
> [12:22:48] IMAP4< 1 OK Begin TLS negotiation now.
> * IMAP connection is un-authenticated
> [12:22:48] IMAP4> 2 CAPABILITY
> [12:22:48] IMAP4< * CAPABILITY IMAP4rev1 LITERAL+ SASL-IR
> LOGIN-REFERRALS ID ENABLE IDLE AUTH=PLAIN AUTH=CRAM-MD5
> [12:22:48] IMAP4< 2 OK Pre-login capabilities listed, post-login
> capabilities have more.
> [12:22:48] IMAP4> Logging a.meyer at nimmini.de to mail.nimmini.de using 
> CRAM-MD5
> ** IMAP Fehler auf mail.nimmini.de: parse error (sehr wahrscheinlich
> ein nicht RFC-konformer Server)
> ** IMAP4-Verbindung unterbrochen
> [12:22:48] IMAP4< Error logging in to mail.nimmini.de
> 
> When I set auth_type PLAIN in Roundcube, login fails too.
> 
> The SELECTS in the database are ok:
> 
> mysql> SELECT maildir, uid, gid, concat('*:storage=', quota) AS
> quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM
> mailbox WHERE username = 'a.meyer at nimmini.de';
> +-------------------------------+------+------+-------------------+--------------------+
> | maildir                       | uid  | gid  | quota_rule        |
> quota_rule2        |
> +-------------------------------+------+------+-------------------+--------------------+
> | /var/spool/vhosts/nimmini.de/ | 5000 | 5000 | *:storage=5242880 |
> Trash:storage=+100 |
> +-------------------------------+------+------+-------------------+--------------------+
> 1 row in set (0,00 sec)
> 
> mysql> SELECT username AS username, password FROM mailbox WHERE
> username = 'a.meyer at nimmini.de' AND active = 1
>     -> ;
> +--------------------+----------+
> | username           | password |
> +--------------------+----------+
> | a.meyer at nimmini.de | hidden   |
> +--------------------+----------+
> 1 row in set (0,00 sec)
> 
> mysql> quit
> 
> What's wrong? Any help appreciated!
> 
> Regards
> 
>   Andreas

Regards
- Christian