[Dovecot] Re: MySQL passdb, auth_verbose, and documentation

[Felix Schwarz]

Hi Tom,

I can't answer all your questions but maybe some of them:

Tom wrote:
> I encountered more serious problems when I tried migrating from real
> user accounts to virtual accounts via MySQL. Has anyone written a 
> howto on setting up Dovecot with MySQL? (Just pointing to 
> dovecot-mysql.conf leaves out a lot.)

Yes, I did setup a new server with dovecot today. Here is my
dovecot-mysql.conf:
db_host = localhost
db_port = 3306
db = xams
db_user = dovecot
db_passwd = hallo
db_client_flags = 0
default_pass_scheme = PLAIN-MD5
#password_query = SELECT password FROM users WHERE username = '%u'
password_query = SELECT u.password FROM pm_sites s INNER JOIN pm_domains d ON s.id = d.siteid INNER JOIN pm_users u ON s.id = u.siteid WHERE s.sitestate = 'default' AND d.name = '%d' AND u.name = '%n' AND u.accountstate = 'default'
#user_query = SELECT home, uid, gid FROM users WHERE username = '%u'
db_unix_socket = 

Password query is a bit more complex as I am using XAMS
(www.xams.org). As default_pass_scheme says the password in the
database is md5 encrypted.

> I set up dovecot-mysql.conf as I thought it should be, created a
> database, added a record with a digest-md5 password, and changed some 
> dovecot.conf directives as follows:

> default_mail_env = maildir:/var/mail/%d/%n/
> auth_mechanisms = digest-md5
> auth_default_realm = example.com
> auth_userdb = static uid=5000 gid=5000 home=/var/mail/%d/%n/
> auth_passdb = mysql /etc/dovecot-mysql.conf

That is very similar to my setup except that I'm using
     auth_mechanisms = plain
because Mozilla doesn't support other mechanisms.

> I tried putting the password into the database unencrypted, but that
> didn't work (probably because of my default_pass_scheme setting?). 

Yes, I think so.

> This leads to some questions: auth_mechanisms doesn't seem to be
> describing the way in which the password is stored, so what does it 
> describe?

I think it descrips the format in which the password is delivered by
the mail client.

> Aside from the insufficient detail in the log,

I want to second that one. More and detailled error messages can
definitely help. To me it would have been very helpful if there were
a debugging log mode where all communication with the client and the
database is shown in the logs (including passwords etc!).

HTH

-- 
Felix