[Dovecot] Master Login using MySQL problem - %{login_domain}

[Jerrale G]

  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 at plf.net*tom at plf.net
>
> Aug 17 10:42:04 auth: Debug: auth(tom at plf.net,127.0.0.1,master): 
> Master user lookup for login: bill at plf.net
> Aug 17 10:42:04 auth: Debug: sql(tom at 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 at plf.net,127.0.0.1): unknown user
> Aug 17 10:42:04 auth: Debug: sql(tom at 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 at plf.net,127.0.0.1): unknown user
> Aug 17 10:42:06 auth: Debug: client out: FAIL   67      user=tom at plf.net
> Aug 17 10:42:06 imap-login: Info: Aborted login (auth failed, 1 
> attempts): user=<tom at 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