[Dovecot] Master Login using MySQL problem - %{login_domain}
Hi Timo,
Been trying to track this problem down further. The problem seems to be related to verifying the master user failing.
My passdb looks like this:
passdb { driver = sql args = /etc/dovecot/dovecot-domain-owner-sql.conf master = yes pass = yes }
dovecot-domain-owner-sql.conf contains:
user_query = SELECT user_name, domain_name FROM users WHERE user_name = '%n' AND domain_name = '%d' AND owns_domain='1'
password_query = SELECT user_name, domain_name, password FROM users WHERE user_name = '%n' AND domain_name = '%d' AND owns_domain='1' AND '%d'='%{login_domain}'
In this example - bill@plf.net is an ordinary user. tom@plf.net is a domain owner. Tom wants to read bills email.
The login is: bill@plf.net*tom@plf.net
This results in tom@plf.net being classified as an unknown user.
Aug 17 08:38:30 auth: Debug: sql(tom@plf.net,127.0.0.1): query: SELECT user_name, domain_name, password FROM users WHERE user_name = 'tom' AND domain_name = 'plf.net' AND owns_domain='1' AND 'plf.net'='' Aug 17 08:38:30 auth: Info: sql(tom@plf.net,127.0.0.1): unknown user Aug 17 08:38:32 auth: Debug: client out: FAIL 15 user=tom@plf.net Aug 17 08:38:32 imap-login: Info: Aborted login (auth failed, 1 attempts): user=<tom@plf.net>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=0, secured
Note that the query used to determine that tom@plf.net is valid is the password_query and not the user_query resulting in unknown user. I think that's where the problem is.
On Tue, 2010-08-17 at 08:50 -0700, Marc Perkel wrote:
Been trying to track this problem down further. The problem seems to be related to verifying the master user failing.
Show the whole dovecot -n output and the whole logs when master user is logging in? You've cut away some stuff I'd like to see.
On 8/17/2010 9:45 AM, Timo Sirainen wrote:
On Tue, 2010-08-17 at 08:50 -0700, Marc Perkel wrote:
Been trying to track this problem down further. The problem seems to be related to verifying the master user failing. Show the whole dovecot -n output and the whole logs when master user is logging in? You've cut away some stuff I'd like to see.
dovecot -n
# 2.0.0: /etc/dovecot/dovecot.conf # OS: Linux 2.6.32-budarin.1 x86_64 Fedora release 12 (Constantine) simfs auth_debug = yes auth_master_user_separator = * disable_plaintext_auth = no dotlock_use_excl = yes first_valid_uid = 12 info_log_path = /var/log/dovecot.log log_path = /var/log/dovecot.log login_greeting = Computer Tyme Dovecot ready. mail_fsync = never mail_gid = mail mail_location = maildir:/vhome/%d/home/%n:INDEX=/imap-cache/%d-%n mail_uid = mail mmap_disable = yes passdb { args = /etc/dovecot/sql.conf driver = sql } passdb { args = /etc/dovecot/domain-owner-sql.conf driver = sql master = yes pass = yes } passdb { args = /etc/dovecot/masteradmin-sql.conf driver = sql master = yes pass = yes } plugin { xexec = blacklist:/usr/local/dovecot/blacklist.sh %u xexec2 = whitelist:/usr/local/dovecot/whitelist.sh %u xexec3 = average:/usr/local/dovecot/average xexec4 = smtp:/usr/local/dovecot/smtp } protocols = imap pop3 service auth { unix_listener auth-client { mode = 0666 } unix_listener auth-master { mode = 0666 } } service imap-login { process_limit = 800 process_min_avail = 100 service_count = 0 vsz_limit = 64 } service imap { process_limit = 400 } service pop3-login { process_limit = 800 process_min_avail = 40 service_count = 0 vsz_limit = 64 } service pop3 { process_limit = 400 } ssl_cert = </usr/share/ssl/certs/imapd.pem ssl_key = </usr/share/ssl/certs/imapd.pem verbose_proctitle = yes protocol pop3 { pop3_uidl_format = %v.%u }
domain-owner-sql.conf
user_query = SELECT user_name, domain_name FROM users WHERE user_name = '%n' AND domain_name = '%d' AND owns_domain='1'
password_query = SELECT user_name, domain_name, password FROM users
WHERE user_name = '%n' AND domain_name = '%d' AND owns_domain='1'
AND '%d'='%{login_domain}'
Result:
Aug 17 09:59:17 auth: Debug: sql(tom@plf.net,127.0.0.1): query: SELECT user_name, domain_name, password FROM users WHERE user_name = 'tom' AND domain_name = 'plf.net' AND owns_domain='1' AND 'plf.net'='' Aug 17 09:59:17 auth: Info: sql(tom@plf.net,127.0.0.1): unknown user Aug 17 09:59:17 auth: Debug: sql(tom@plf.net,127.0.0.1): query: SELECT user_name, domain_name, password FROM users WHERE user_name = 'tom' AND domain_name = 'plf.net' AND masteradmin='1' Aug 17 09:59:17 auth: Info: sql(tom@plf.net,127.0.0.1): unknown user Aug 17 09:59:19 auth: Debug: client out: FAIL 10 user=tom@plf.net Aug 17 09:59:19 imap-login: Info: Aborted login (auth failed, 1 attempts): user=<tom@plf.net>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=0, secured
Note - I have two different kinds of masters here. I have a masteradmin who can read anyone's email and I have domain owners who can read anyone's email in the same domain. At least that is what I'm hoping to do.
Does it log "Master user lookup for login" if you patch Dovecot with http://hg.dovecot.org/dovecot-2.0/raw-rev/7428338c8df2 ?
On 8/17/2010 10:11 AM, Timo Sirainen wrote:
Does it log "Master user lookup for login" if you patch Dovecot with http://hg.dovecot.org/dovecot-2.0/raw-rev/7428338c8df2 ?
Ran the patch - here's the results for bill@plf.net*tom@plf.net
Aug 17 10:42:04 auth: Debug: auth(tom@plf.net,127.0.0.1,master): Master user lookup for login: bill@plf.net Aug 17 10:42:04 auth: Debug: sql(tom@plf.net,127.0.0.1): query: SELECT user_name, domain_name, password FROM users WHERE user_name = 'tom' AND domain_name = 'plf.net' AND owns_domain='1' AND 'plf.net'='' Aug 17 10:42:04 auth: Info: sql(tom@plf.net,127.0.0.1): unknown user Aug 17 10:42:04 auth: Debug: sql(tom@plf.net,127.0.0.1): query: SELECT user_name, domain_name, password FROM users WHERE user_name = 'tom' AND domain_name = 'plf.net' AND masteradmin='1' Aug 17 10:42:04 auth: Info: sql(tom@plf.net,127.0.0.1): unknown user Aug 17 10:42:06 auth: Debug: client out: FAIL 67 user=tom@plf.net Aug 17 10:42:06 imap-login: Info: Aborted login (auth failed, 1 attempts): user=<tom@plf.net>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=0, secured
Again my query is:
user_query = SELECT user_name, domain_name FROM users WHERE user_name = '%n' AND domain_name = '%d' AND owns_domain='1'
password_query = SELECT user_name, domain_name, password FROM users WHERE user_name = '%n' AND domain_name = '%d' AND owns_domain='1' AND '%d'='%{login_domain}'
On Tue, 2010-08-17 at 10:45 -0700, Marc Perkel wrote:
Aug 17 10:42:04 auth: Debug: auth(tom@plf.net,127.0.0.1,master): Master user lookup for login: bill@plf.net Aug 17 10:42:04 auth: Debug: sql(tom@plf.net,127.0.0.1): query: SELECT user_name, domain_name, password FROM users WHERE user_name = 'tom' AND domain_name = 'plf.net' AND owns_domain='1' AND 'plf.net'=''
Oh, right, MySQL. I was only testing it with PostgreSQL. This'll fix it: http://hg.dovecot.org/dovecot-2.0/rev/3ba227176cde
On 8/17/2010 11:16 AM, Timo Sirainen wrote:
On Tue, 2010-08-17 at 10:45 -0700, Marc Perkel wrote:
Aug 17 10:42:04 auth: Debug: auth(tom@plf.net,127.0.0.1,master): Master user lookup for login: bill@plf.net Aug 17 10:42:04 auth: Debug: sql(tom@plf.net,127.0.0.1): query: SELECT user_name, domain_name, password FROM users WHERE user_name = 'tom' AND domain_name = 'plf.net' AND owns_domain='1' AND 'plf.net'='' Oh, right, MySQL. I was only testing it with PostgreSQL. This'll fix it: http://hg.dovecot.org/dovecot-2.0/rev/3ba227176cde
WORKS!!!
Aug 17 11:29:37 auth: Debug: auth(tom@plf.net,127.0.0.1,master): Master
user lookup for login: bill@plf.net
Aug 17 11:29:37 auth: Debug: sql(tom@plf.net,127.0.0.1,master): query:
SELECT user_name, domain_name, password FROM users WHERE user_name =
'tom' AND domain_name = 'plf.net' AND owns_domain='1' AND
'plf.net'='plf.net'
Aug 17 11:29:37 auth: Info: passdb(tom@plf.net,127.0.0.1,master): Master
user logging in as bill@plf.net
Aug 17 11:29:37 auth: Debug: sql(bill@plf.net,127.0.0.1): query: SELECT
user_name, domain_name, password FROM users WHERE user_name = 'bill' AND
domain_name = 'plf.net'
Aug 17 11:29:37 auth: Debug: client out: OK 25
user=bill@plf.net user_name=tom domain_name=plf.net
user_name=bill domain_name=plf.net
Aug 17 11:29:37 auth: Debug: master out: USER 1 bill@plf.net
master_user=tom@plf.net
Aug 17 11:29:37 imap-login: Info: Login: user=<bill@plf.net>,
method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=32204, secured
Aug 17 11:29:37 imap(bill@plf.net): Info: Disconnected: Logged out
bytes=28/635
Aug 17 11:29:38 auth: Debug: auth(tom@plf.net,127.0.0.1,master): Master
user lookup for login: bill@plf.net
Aug 17 11:29:38 auth: Debug: sql(tom@plf.net,127.0.0.1,master): query:
SELECT user_name, domain_name, password FROM users WHERE user_name =
'tom' AND domain_name = 'plf.net' AND owns_domain='1' AND
'plf.net'='plf.net'
Aug 17 11:29:38 auth: Info: passdb(tom@plf.net,127.0.0.1,master): Master
user logging in as bill@plf.net
Aug 17 11:29:38 auth: Debug: sql(bill@plf.net,127.0.0.1): query: SELECT
user_name, domain_name, password FROM users WHERE user_name = 'bill' AND
domain_name = 'plf.net'
Aug 17 11:29:38 auth: Debug: client out: OK 27
user=bill@plf.net user_name=tom domain_name=plf.net
user_name=bill domain_name=plf.net
Aug 17 11:29:38 auth: Debug: master out: USER 1 bill@plf.net
master_user=tom@plf.net
Aug 17 11:29:38 imap-login: Info: Login: user=<bill@plf.net>,
method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=32216, secured
Aug 17 11:29:38 imap(bill@plf.net): Info: Disconnected: Logged out
bytes=97/497
Now - on to the really tricky stuff.
On 8/17/2010 1:45 PM, Marc Perkel wrote:
On 8/17/2010 10:11 AM, Timo Sirainen wrote:
Does it log "Master user lookup for login" if you patch Dovecot with http://hg.dovecot.org/dovecot-2.0/raw-rev/7428338c8df2 ?
Ran the patch - here's the results for bill@plf.net*tom@plf.net
Aug 17 10:42:04 auth: Debug: auth(tom@plf.net,127.0.0.1,master): Master user lookup for login: bill@plf.net Aug 17 10:42:04 auth: Debug: sql(tom@plf.net,127.0.0.1): query: SELECT user_name, domain_name, password FROM users WHERE user_name = 'tom' AND domain_name = 'plf.net' AND owns_domain='1' AND 'plf.net'='' Aug 17 10:42:04 auth: Info: sql(tom@plf.net,127.0.0.1): unknown user Aug 17 10:42:04 auth: Debug: sql(tom@plf.net,127.0.0.1): query: SELECT user_name, domain_name, password FROM users WHERE user_name = 'tom' AND domain_name = 'plf.net' AND masteradmin='1' Aug 17 10:42:04 auth: Info: sql(tom@plf.net,127.0.0.1): unknown user Aug 17 10:42:06 auth: Debug: client out: FAIL 67 user=tom@plf.net Aug 17 10:42:06 imap-login: Info: Aborted login (auth failed, 1 attempts): user=<tom@plf.net>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=0, secured
Again my query is:
user_query = SELECT user_name, domain_name FROM users WHERE user_name = '%n' AND domain_name = '%d' AND owns_domain='1'
password_query = SELECT user_name, domain_name, password FROM users WHERE user_name = '%n' AND domain_name = '%d' AND owns_domain='1' AND '%d'='%{login_domain}'
you're not selecting the username LOL. The user query, related to how you are storing your mail and where you are storing it, should be:
SELECT CONCAT('/path/to/mail/root/', mailboxes.domain, '/', mailboxes.username, '/' ), AS home, CONCAT('/path/to/mail/root/', mailboxes.domain, '/', mailboxes.username, '/mail' ) AS maildir, CONCAT('/path/to/mail/root/', mailboxes.domain, '/', mailboxes.username, '/sieve' ) AS sieve_dir, CONCAT('*:storage=', mailboxes.quota, 'M') AS quota_rule WHERE user_name=%n AND domain_name=%d AND owns_domain='1'
/path/to/mail/root/example.com/johndoe is johndoe's home directory /path/to/mail/root/example.com/johndoe/mail is johndoe's maildirectory, where the mail directories, for imap, are stored and the inbox is stored (unless you defined inbox in dovecot.conf through a namespace or something) /path/to/mail/root/example.com/johndoe/sieve is where the sieve scripts are stored (if you use pigeonhole)
*Your mysql userquery should ALWAYS define, at least, your home directory and the mail directory, for dovecot.* If you use mbox or dbox, omit the maildir part but you MUST use the home!
The password query should select the password, not the username and domain; correct your thinking of mysql queries! If you have your passwords stored in md5, default_pass_scheme=md5 should be in your mysql config file.
Jerrale G. SC Senior Admin
participants (3)
-
Jerrale G
-
Marc Perkel
-
Timo Sirainen