Hello!
Setup again to authenticate my users with MySQL but can't login.
Sep 13 11:55:49 auth: Info: passwd-file(a.meyer@nimmini.de,95.88.63.177,<R7Qe9J0fTgBfWD+x>): unknown user Sep 13 11:55:49 auth: Info: passwd-file(a.meyer@nimmini.de,95.88.63.177,<R7Qe9J0fTgBfWD+x>): unknown user Sep 13 11:55:49 imap-login: Info: Login: user=<a.meyer@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@nimmini.de): Error: user a.meyer@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@nimmini.de): Error: Invalid user settings. Refer to server log for more information.
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@nimmini.de,95.88.63.177,<R7Qe9J0fTgBfWD+x>): lookup: user=a.meyer@nimmini.de file=/etc/dovecot/passwd Sep 13 11:55:49 auth-worker(30822): Debug: sql(a.meyer@nimmini.de,95.88.63.177): query: SELECT username AS username, password FROM mailbox WHERE username = 'a.meyer@nimmini.de' AND active = 1 Sep 13 11:55:49 auth: Debug: client passdb out: OK 1 user=a.meyer@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@nimmini.de,95.88.63.177,<R7Qe9J0fTgBfWD+x>): lookup: user=a.meyer@nimmini.de file=/etc/dovecot/passwd Sep 13 11:55:49 auth-worker(30822): Debug: passwd-file(a.meyer@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@nimmini.de' Sep 13 11:55:49 auth: Debug: master userdb out: USER 997457921 a.meyer@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@nimmini.de): Debug: Effective uid=5000, gid=5000, home= Sep 13 11:55:49 imap(a.meyer@nimmini.de): Debug: Quota root: name=User quota backend=maildir args= Sep 13 11:55:49 imap(a.meyer@nimmini.de): Debug: Quota rule: root=User quota mailbox=* bytes=5368709120 messages=0 Sep 13 11:55:49 imap(a.meyer@nimmini.de): Debug: Quota rule: root=User quota mailbox=Trash bytes=+102400 messages=0 Sep 13 11:55:49 imap(a.meyer@nimmini.de): Debug: Quota grace: root=User quota bytes=53687091 (1%) Sep 13 11:55:49 imap(a.meyer@nimmini.de): Debug: Namespace inbox: type=private, prefix=, sep=/, inbox=yes, hidden=no, list=yes, subscriptions=yes location=maildir:~/
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@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@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@nimmini.de' AND active = 1
-> ;
+--------------------+----------+
| username | password |
+--------------------+----------+
| a.meyer@nimmini.de | hidden |
+--------------------+----------+
1 row in set (0,00 sec)
mysql> quit
What's wrong? Any help appreciated!
Regards
Andreas
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@nimmini.de,95.88.63.177,<R7Qe9J0fTgBfWD+x>): unknown user Sep 13 11:55:49 auth: Info: passwd-file(a.meyer@nimmini.de,95.88.63.177,<R7Qe9J0fTgBfWD+x>): unknown user Sep 13 11:55:49 imap-login: Info: Login: user=<a.meyer@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@nimmini.de): Error: user a.meyer@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@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@nimmini.de,95.88.63.177,<R7Qe9J0fTgBfWD+x>): lookup: user=a.meyer@nimmini.de file=/etc/dovecot/passwd Sep 13 11:55:49 auth-worker(30822): Debug: sql(a.meyer@nimmini.de,95.88.63.177): query: SELECT username AS username, password FROM mailbox WHERE username = 'a.meyer@nimmini.de' AND active = 1 Sep 13 11:55:49 auth: Debug: client passdb out: OK 1 user=a.meyer@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@nimmini.de,95.88.63.177,<R7Qe9J0fTgBfWD+x>): lookup: user=a.meyer@nimmini.de file=/etc/dovecot/passwd Sep 13 11:55:49 auth-worker(30822): Debug: passwd-file(a.meyer@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@nimmini.de' Sep 13 11:55:49 auth: Debug: master userdb out: USER 997457921 a.meyer@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@nimmini.de): Debug: Effective uid=5000, gid=5000, home= Sep 13 11:55:49 imap(a.meyer@nimmini.de): Debug: Quota root: name=User quota backend=maildir args= Sep 13 11:55:49 imap(a.meyer@nimmini.de): Debug: Quota rule: root=User quota mailbox=* bytes=5368709120 messages=0 Sep 13 11:55:49 imap(a.meyer@nimmini.de): Debug: Quota rule: root=User quota mailbox=Trash bytes=+102400 messages=0 Sep 13 11:55:49 imap(a.meyer@nimmini.de): Debug: Quota grace: root=User quota bytes=53687091 (1%)
Sep 13 11:55:49 imap(a.meyer@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@kivalo.at): Debug: Namespace inbox: type=private, prefix=, sep=/, inbox=yes, hidden=no, list=yes, subscriptions=yes location=maildir:/srv/mail/aaa@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@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@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@nimmini.de' AND active = 1 -> ; +--------------------+----------+ | username | password | +--------------------+----------+ | a.meyer@nimmini.de | hidden | +--------------------+----------+ 1 row in set (0,00 sec)
mysql> quit
What's wrong? Any help appreciated!
Regards
Andreas
Regards
- Christian
Hello!
Christian Kivalo <ml+dovecot@valo.at> schrieb am 13.09.15 um 19:51:38 Uhr:
Sep 13 11:55:49 imap(a.meyer@nimmini.de): Error: user a.meyer@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@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?
In dovecot.conf mail_location = maildir:~/ is set. This works with the system-users defined in the passwd file.
I think the query-string für MySQL would overwrite this mail_location, right? But how do I define it in the query?
With my setup "mail" and "home" is returned from the userdb query from mysql
home: /srv/mail/%u mail: /srv/mail/%u/Maildir
The query-strings for virtual users look like this:
password_query = SELECT username AS username, password FROM mailbox WHERE username = '%u' AND active = 1 user_query = SELECT maildir, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u'
There is no mail_loation defined. I thought if maildir is queried, the %u would expand to the mail_location of the user. Where the users only with the local part of the address exist in the filesystem.
/var/spool/vhosts/nimmini.de/a.meyer
whereas /var/spool/vhosts is a symlink to /home/vhosts.
This setup worked fine sometime ago and I wanted to reactivate MySQL for dovecot. Now the query doesn't work anymore, the location seems to be invalid.
location=maildir:~/ instead of /var/spool/vhosts/nimmini.de/%u.
Sep 13 11:55:49 imap(a.meyer@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@kivalo.at): Debug: Namespace inbox: type=private, prefix=, sep=/, inbox=yes, hidden=no, list=yes, subscriptions=yes location=maildir:/srv/mail/aaa@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.
I cannot set the absolute path in mail_location, because I have systemusers and virtual users.
Don't know how to set mail_location in the query-string for MySQL, if the expandation of %u is not enough. %u would expand to the full address a.meyer@nimmini.de whereas the location looks like /var/spool/vhosts/nimmini.de/a.meyer
Don't know what to do.
Regards
Andreas
On 2015-09-13 21:05, Andreas Meyer wrote:
Hello!
Christian Kivalo <ml+dovecot@valo.at> schrieb am 13.09.15 um 19:51:38 Uhr:
Sep 13 11:55:49 imap(a.meyer@nimmini.de): Error: user a.meyer@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@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?
In dovecot.conf mail_location = maildir:~/ is set. This works with the system-users defined in the passwd file.
I think the query-string für MySQL would overwrite this mail_location, right? But how do I define it in the query?
yes thats how it should work but your query (looking at the query result from your first email) overwrites the mail_location with "/var/spool/vhosts/nimmini.de/" without a username.
With my setup "mail" and "home" is returned from the userdb query from mysql
home: /srv/mail/%u mail: /srv/mail/%u/Maildir
The query-strings for virtual users look like this:
password_query = SELECT username AS username, password FROM mailbox WHERE username = '%u' AND active = 1 user_query = SELECT maildir, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u'
what about user_query = SELECT CONCAT(maildir, SUBSTRING_INDEX(username,'@',+1)) as mail, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u'
The SUBSTRING_INDEX returns everything left of the '@' from field username, that should give you something like
+--------------------------------------+------+------+-------------------+--------------------+
| mail | uid | gid | quota_rule
| quota_rule2 |
+--------------------------------------+------+------+-------------------+--------------------+
| /var/spool/vhosts/nimmini.de/a.meyer | 5000 | 5000 | *:storage=5242880
| Trash:storage=+100 |
+--------------------------------------+------+------+-------------------+--------------------+
which hopefully is the path to your maildir.
There is no mail_loation defined. I thought if maildir is queried, the %u would expand to the mail_location of the user. Where the users only with the local part of the address exist in the filesystem.
I don't know a setting called "maildir" dovecot expects a field "mail" to be returned by your sql query or uses the default one (set in 10-mail.conf).
maybe even this works, but it too will only return the path without the username user_query = SELECT maildir AS mail, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u'
%u is the user@domain username %n is the username without the @domain part
/var/spool/vhosts/nimmini.de/a.meyer
whereas /var/spool/vhosts is a symlink to /home/vhosts.
This setup worked fine sometime ago and I wanted to reactivate MySQL for dovecot. Now the query doesn't work anymore, the location seems to be invalid.
location=maildir:~/ instead of /var/spool/vhosts/nimmini.de/%u.
Sep 13 11:55:49 imap(a.meyer@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@kivalo.at): Debug: Namespace inbox: type=private, prefix=, sep=/, inbox=yes, hidden=no, list=yes, subscriptions=yes location=maildir:/srv/mail/aaa@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.
I cannot set the absolute path in mail_location, because I have systemusers and virtual users.
Don't know how to set mail_location in the query-string for MySQL, if the expandation of %u is not enough. %u would expand to the full address a.meyer@nimmini.de whereas the location looks like /var/spool/vhosts/nimmini.de/a.meyer
Don't know what to do.
Regards
Andreas
- christian
Christian Kivalo <ml+dovecot@valo.at> schrieb am 13.09.15 um 22:08:04 Uhr:
I think the query-string für MySQL would overwrite this mail_location, right? But how do I define it in the query?
yes thats how it should work but your query (looking at the query result from your first email) overwrites the mail_location with "/var/spool/vhosts/nimmini.de/" without a username.
With my setup "mail" and "home" is returned from the userdb query from mysql
home: /srv/mail/%u mail: /srv/mail/%u/Maildir
The query-strings for virtual users look like this:
password_query = SELECT username AS username, password FROM mailbox WHERE username = '%u' AND active = 1 user_query = SELECT maildir, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u'
what about user_query = SELECT CONCAT(maildir, SUBSTRING_INDEX(username,'@',+1)) as mail, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u'
With this query above I can login again with Roundcube and claws-mail but with the mysql-client I get:
mysql> SELECT CONCAT(maildir, SUBSTRING_INDEX(username,'@',+1)) as mail, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u'; Empty set (0,00 sec)
This is my old query with wich I couldn't log in. mysql> SELECT maildir, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u'; Empty set (0,00 sec)
The SUBSTRING_INDEX returns everything left of the '@' from field username, that should give you something like
+--------------------------------------+------+------+-------------------+--------------------+ | mail | uid | gid | quota_rule
| quota_rule2 | +--------------------------------------+------+------+-------------------+--------------------+ | /var/spool/vhosts/nimmini.de/a.meyer | 5000 | 5000 | *:storage=5242880 | Trash:storage=+100 | +--------------------------------------+------+------+-------------------+--------------------+which hopefully is the path to your maildir.
I get am Emty set
Don't understand it.
Andreas
On 2015-09-13 22:52, Andreas Meyer wrote:
Christian Kivalo <ml+dovecot@valo.at> schrieb am 13.09.15 um 22:08:04 Uhr:
I think the query-string für MySQL would overwrite this mail_location, right? But how do I define it in the query? yes thats how it should work but your query (looking at the query result from your first email) overwrites the mail_location with "/var/spool/vhosts/nimmini.de/" without a username.
With my setup "mail" and "home" is returned from the userdb query from mysql
home: /srv/mail/%u mail: /srv/mail/%u/Maildir The query-strings for virtual users look like this:
password_query = SELECT username AS username, password FROM mailbox WHERE username = '%u' AND active = 1 user_query = SELECT maildir, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u' what about user_query = SELECT CONCAT(maildir, SUBSTRING_INDEX(username,'@',+1)) as mail, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u' With this query above I can login again with Roundcube and claws-mail but with the mysql-client I get:
mysql> SELECT CONCAT(maildir, SUBSTRING_INDEX(username,'@',+1)) as mail, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u'; Empty set (0,00 sec)
This is my old query with wich I couldn't log in. mysql> SELECT maildir, uid, gid, concat('*:storage=', quota) AS quota_rule, concat('Trash:storage=+', quota_trash) AS quota_rule2 FROM mailbox WHERE username = '%u'; Empty set (0,00 sec)
The SUBSTRING_INDEX returns everything left of the '@' from field username, that should give you something like
+--------------------------------------+------+------+-------------------+--------------------+ | mail | uid | gid | quota_rule | quota_rule2 | +--------------------------------------+------+------+-------------------+--------------------+ | /var/spool/vhosts/nimmini.de/a.meyer | 5000 | 5000 | *:storage=5242880 | Trash:storage=+100 | +--------------------------------------+------+------+-------------------+--------------------+
which hopefully is the path to your maildir. I get am Emty set
Don't understand it.
Andreas
Hi Andreas,
Make sure to return a "home" attribute, and only optionally a "mail" attribute, from your SQL user query.
Your "home" attribute for user "a.meyer@nimmini.de" should return "/var/spool/vhosts/nimmini.de/a.meyer", and only if you want to override the global mail_location specify a "mail" attribute to return something like "maildir:/var/spool/vhosts/nimmini.de/a.meyer/Maildir". There is no "maildir" attribute used from the SQL query, to my knowledge.
If you want to have the maildir stored directly in the user's home folder, e.g. "/var/spool/vhosts/nimmini.de/a.meyer", and your global mail_location already is "maildir:~/", then you only need to return a proper "home" attribute from SQL.
BR Tobias
Benny Pedersen <me@junc.eu> schrieb am 14.09.15 um 01:17:17 Uhr:
Andreas Meyer skrev den 2015-09-13 22:52:
Don't understand it.
in mysql shell you self need to expand %u
since there is possible no user email that is %u
Yes, of course. Too much stress today.
But I solved my problems with mysql, auth, dovecot_lda and postfix deferred mail by just setting mail_home = /var/spool/vhosts/%d/%n in dovecot.conf again.
And the old queries work again. Now is bedtime. Thank you everybody!
Andreas
participants (4)
-
Andreas Meyer
-
Benny Pedersen
-
Christian Kivalo
-
Tobias Franzén