Re: [Dovecot] [ #14832]: Capital letters in POP3 logins
support@majordomo.ru wrote:
Can I solve this problem or it is virtually "bug" of dovecot?
No bug, just the "garbage in, garbage out" principle. Mysql's string matches are case-insensisitive by default, so when dovecot asks mysql for the user information of UsEr1, it will get the information of user1 and therefore assumes that UsEr1 is a valid login. It's not dovecot's fault. If you want the match case-insensitive (so UsEr1 will be rejected), add the BINARY attribute to the userid field or change your query to '... WHERE BINARY userid = "%u"' in password_query and user_query.
If you want to allow logins as UsEr1 (and let him access user1's mail), you have to change your dovecot.conf, I think. Use %h instead of %u there, and let user_query return the home directory with something like 'SELECT CONCAT('/var/spool/vmail/',LOWER(userid)) AS home, ...'.
On Wed, 30 Mar 2005 22:27:38 +0200 Jakob Hirsch jh@plonk.de wrote:
If you want to allow logins as UsEr1 (and let him access user1's mail), you have to change your dovecot.conf, I think. Use %h instead of %u there, and let user_query return the home directory with something like 'SELECT CONCAT('/var/spool/vmail/',LOWER(userid)) AS home, ...'.
OK, I see where it SHOULD work as I'd like. But it's not for some reason. When I try to log in via Squirrelmail it pops up this:
ERROR : Connection dropped by imap-server.
Query: CAPABILITY
Here's the appropriate section of dovecot.conf (what it WAS, and what is set to now)
#auth_userdb = static uid=5000 gid=5000 home=/home/vmail/%d/%u
auth_userdb = mysql /etc/dovecot/dovecot-mysql.conf
Here's the appropriate section of dovecot-mysql.conf (this section was previously commented out because it was set to "static" in dovecot.conf)
user_query = SELECT CONCAT('/home/vmail/', maildir) AS home, 5000 AS uid, 5000 AS gid FROM mailbox where username = '%u'
My database contains a "maildir" field that has "domain.tld/user@domain.tld/" in it -- all lower case (converted by postfix.admin when a user is created) so I don't have to worry about the conversion to lower case. I just want "USER", "user", "UsEr", etc to all have the same home set for mailbox access.
When I run this query inside a phpmyadmin query box it works fine:
SQL-query: SELECT CONCAT( '/home/vmail/', maildir ) AS home, 5000 AS uid, 5000 AS gid FROM mailbox WHERE username = 'user@domain.tld' LIMIT 0 , 30
It returns this:
home uid gid /home/vmail/domain.tld/user@domain.tld/ 5000 5000
(yes, I have a record called "user@domain.tld" for testing)
I did a direct copy/paste from the dovecot-mysql.conf file with the only change being that I put the actual username in place of the '%u'.
The direct MySQL query is returning the exact same thing that the old "static" setting in dovecot.conf was setting up.
Ideas??
Thanks,
Gerald
Gerald V. Livingston II wrote:
ERROR : Connection dropped by imap-server.
what's in the logs? Is this with mixed-case usernames or with any username?
#auth_userdb = static uid=5000 gid=5000 home=/home/vmail/%d/%u auth_userdb = mysql /etc/dovecot/dovecot-mysql.conf
Looks ok. What is your default_mail_env? Should be "default_mail_env = maildir:%h" (given that you use maildir and have your folders directly in the home directory).
user_query = SELECT CONCAT('/home/vmail/', maildir) AS home, 5000 AS uid, 5000 AS gid FROM mailbox where username = '%u'
looks also fine.
you could even use SELECT LOWER('/home/vmail/%d/%u/') AS home, ...
And btw, just saw that in 1.0-test you can use use modifiers, e.g. %Lu to have a lowercased userid. May be easier to use this with static...
This is going to wrap badly.
On Thu, 31 Mar 2005 01:28:18 +0200 Jakob Hirsch jh@plonk.de wrote:
Gerald V. Livingston II wrote:
ERROR : Connection dropped by imap-server.
what's in the logs? Is this with mixed-case usernames or with any username?
With any username. The log was the clue. I started 'tail'ing it while attempting a login and saw this:
dovecot-auth: MySQL: Error executing query "SELECT home, uid, gid FROM users WHERE userid = 'user@domain.tld'": Table 'postfix.users' doesn't exist
Uhhh -- that's not what I had defined in my .conf file.
This was in my dovecot-mysql.conf:
# Examples # user_query = SELECT home, uid, gid FROM users WHERE userid = '%n' AND domain = '%d' # user_query = SELECT dir AS home, user AS uid, group AS gid FROM users where userid = '%u' # user_query = SELECT home, 501 AS uid, 501 AS gid FROM users WHERE userid = '%u' # #user_query = SELECT home, uid, gid FROM users WHERE userid = '%u'
user_query = SELECT 5000 AS uid, 5000 AS gid, CONCAT('/home/vmail/', domain, '/', username) AS mail FROM mailbox where username = '%u'
It's a bug in Dovecot -- it seems that it ignores commented out lines (#) after the first call to MySQL. After calling SELECT to get the password (above the section I quoted) it was then grabbing the commented out line that was included in the original dovecot-mysql.conf as an example and trying to use that as the user-info auth mechanism. There was a commented out SELECT line right above the password auth (the one I added to force case matching login) and it ignored that one just fine. I could probably have just moved that auth line up so it was just below the password auth line but instead I created a separate dovecot-mysql-userinfo.conf file with that SELECT line way up in the file and all the old examples at the bottom. I'll clean up all the files to remove all comments after I get it all running.
How does one report a bug like this?
Looks ok. What is your default_mail_env? Should be "default_mail_env = maildir:%h" (given that you use maildir and have your folders directly in the home directory).
default_mail_env = maildir:/home/vmail/%d/%u
There is not really a "home directory". This is all virtual. /etc/passwd only has system daemons, root, and a single maintainer login for me.
I noticed that none of the default examples have a trailing '/' after the username. Seems like they should for Maildir.
user_query = SELECT CONCAT('/home/vmail/', maildir) AS home, 5000 AS uid, 5000 AS gid FROM mailbox where username = '%u'
looks also fine.
Changed it to the following to match the "no trailing slash" I was finding in the default install examples. My 'maildir' field has the trailing slash.
SELECT 5000 AS uid, 5000 AS gid, CONCAT('/home/vmail/', domain, '/', username) AS mail FROM mailbox where username = '%u'
you could even use
SELECT LOWER('/home/vmail/%d/%u/') AS home, ...
I was trying to avoid using case modification since I remembered I have all the required fields -- in lowercase -- in my database
And btw, just saw that in 1.0-test you can use use modifiers, e.g. %Lu to have a lowercased userid. May be easier to use this with static...
I saw that as well -- but I got it fixed now. ;-)
Gerald V. Livingston II wrote:
default_mail_env = maildir:/home/vmail/%d/%u
Well, if you don't use the homedir from your SELECT anywhere, it's not good for anything. Dovecot will go on using the domain and userid as given by the user.
There is not really a "home directory". This is all virtual. /etc/passwd
Does not matter. It's mainly used for being referred with %h.
I noticed that none of the default examples have a trailing '/' after the username. Seems like they should for Maildir.
Does not matter, AFAIK. And works here without.
Changed it to the following to match the "no trailing slash" I was finding in the default install examples. My 'maildir' field has the trailing slash.
SELECT 5000 AS uid, 5000 AS gid, CONCAT('/home/vmail/', domain, '/', username) AS mail FROM mailbox where username = '%u'
ok, it seems that "mail" overrides default_mail_env. But then it should look like "maildir:/some/path", I think (but I'm not really sure about that).
participants (3)
-
Gerald V. Livingston II
-
Jakob Hirsch
-
support＠majordomo.ru