Andrew Hearn
2006-Jul-11 11:53 UTC
[Dovecot] Dovecot and MySQL auth - initial setup issues
Hello,
I'm just setting up a new server and wanting to use MySQL for user
authentication.
Here is what I have:
Fedora Core 5: 2.6.17-1.2145_FC5
Dovecot v1.0.rc2 (RPM install from atrpms.net)
MySQL v5.0.22
SquirrelMail v 1.4.6-7.fc5
...Running on a Dual XEON machine
I've added the following to dovecot.conf:
auth default {
passdb sql {
args = /etc/dovecot-mysql.conf
}
userdb sql {
args = /etc/dovecot-mysql.conf
}
The rest of dovecot.conf is standard.
/etc/dovecot-mysql.conf has:
driver=mysql
connect = host=127.0.0.1 port=3306 dbname=maildb user=root client_flags=0
password_query = SELECT password FROM users WHERE id = '%u'
user_query = SELECT maildir FROM users WHERE id = '%u'
(I've run the SQL queries within mysql and they give the right results)
When I start Dovecot the log shows:
dovecot: Jul 11 12:47:25 Info: Dovecot v1.0.rc2 starting up
dovecot: Jul 11 12:47:27 Info: auth-worker(default): mysql: Connected to
127.0.0.1 (maildb)
When I try to log in using SquirrelMail, it takes about 3 seconds, then
I get
dovecot: Jul 11 12:48:00 Info: imap-login: Aborted login:
user=<andrew at mail-test.aaisp.net.uk>, method=PLAIN,
rip=::ffff:127.0.0.1, lip=::ffff:127.0.0.1, secured
...So, no log of it trying MySQL...
And SquirrelMail then says:
Unknown user or password incorrect
Any ideas of what I'm missing?
Thanks!
Andrew.
Andrew Hearn
2006-Jul-11 11:57 UTC
[Dovecot] Dovecot and MySQL auth - initial setup issues
Hello,
I'm just setting up a new server and wanting to use MySQL for user
authentication.
Here is what I have:
Fedora Core 5: 2.6.17-1.2145_FC5
Dovecot v1.0.rc2 (RPM install from atrpms.net)
MySQL v5.0.22
SquirrelMail v 1.4.6-7.fc5
...Running on a Dual XEON machine
I've added the following to dovecot.conf:
auth default {
passdb sql {
args = /etc/dovecot-mysql.conf
}
userdb sql {
args = /etc/dovecot-mysql.conf
}
The rest of dovecot.conf is standard.
/etc/dovecot-mysql.conf has:
driver=mysql
connect = host=127.0.0.1 port=3306 dbname=maildb user=root client_flags=0
password_query = SELECT password FROM users WHERE id = '%u'
user_query = SELECT maildir FROM users WHERE id = '%u'
(I've run the SQL queries within mysql and they give the right results)
When I start Dovecot the log shows:
dovecot: Jul 11 12:47:25 Info: Dovecot v1.0.rc2 starting up
dovecot: Jul 11 12:47:27 Info: auth-worker(default): mysql: Connected to
127.0.0.1 (maildb)
When I try to log in using SquirrelMail, it takes about 3 seconds, then
I get
dovecot: Jul 11 12:48:00 Info: imap-login: Aborted login:
user=<andrew at mail-test.aaisp.net.uk>, method=PLAIN,
rip=::ffff:127.0.0.1, lip=::ffff:127.0.0.1, secured
...So, no log of it trying MySQL...
And SquirrelMail then says:
Unknown user or password incorrect
Any ideas of what I'm missing?
Thanks!
Andrew.
--
Andrew Hearn, Senior Support Engineer
Andrews & Arnold Ltd 01344 400999
For efficient support, please email:
support at aaisp.net.uk
Andrew Hearn
2006-Jul-11 16:01 UTC
[Dovecot] Dovecot and MySQL auth - initial setup issues
James Bowling wrote:> Are you using a password encryption scheme for your user passwords in > the mysql db? You might want to enable the following to help debug the > issue more in your dovecot.conf: > > auth_verbose = yes > auth_debug = yes > auth_debug_passwords = yes > > Restart Dovecot and then try logging in. This will spit out alot more > information about the process and might give us a better look into your > issue. >Thanks James, I'm using plain at the moment whilst testing and getting this working, the database has a plain password, and under auth default { I've now set: mechanisms = plain The log I now get is: dovecot: Jul 11 17:01:21 Info: auth(default): client in: AUTH 1 PLAIN service=IMAP lip=::ffff:81.187.11.54 rip=::ffff:81.187.11.8 resp=AGFuZHJld0BtYWlsLXRlc3QuYWFpc3AubmV0LnVrADIzNzQyNg=dovecot: Jul 11 17:01:21 Info: auth-worker(default): sql(andrew at mail-test.aaisp.net.uk,::ffff:81.187.11.8): query: SELECT password FROM users WHERE id = 'andrew at mail-test.aaisp.net.uk' dovecot: Jul 11 17:01:21 Info: auth(default): client out: OK 1 user=andrew at mail-test.aaisp.net.uk dovecot: Jul 11 17:01:21 Info: auth(default): master in: REQUEST 4 17643 1 dovecot: Jul 11 17:01:21 Info: auth-worker(default): sql(andrew at mail-test.aaisp.net.uk,::ffff:81.187.11.8): SELECT maildir FROM users WHERE id = 'andrew at mail-test.aaisp.net.uk' dovecot: Jul 11 17:01:21 Error: auth-worker(default): sql(andrew at mail-test.aaisp.net.uk,::ffff:81.187.11.8): Password query didn't return uid, or it was NULL dovecot: Jul 11 17:01:21 Info: auth(default): passwd(andrew at mail-test.aaisp.net.uk,::ffff:81.187.11.8): unknown user dovecot: Jul 11 17:01:21 Error: auth(default): userdb(andrew at mail-test.aaisp.net.uk,::ffff:81.187.11.8): user not found from userdb dovecot: Jul 11 17:01:21 Info: auth(default): master out: NOTFOUND 4 dovecot: Jul 11 17:01:21 Info: imap-login: Internal login failure: user=<andrew at mail-test.aaisp.net.uk>, method=PLAIN, rip=::ffff:81.187.11.8, lip=::ffff:81.187.11.54 I'm not sure why it's saying "Password query didn't return uid", that that seems the issue. I'll carry on playing... Andrew
James Bowling
2006-Jul-11 20:48 UTC
[Dovecot] Dovecot and MySQL auth - initial setup issues
If you setup is in a virtual mailbox setup then your query should look something like this: user_query = SELECT maildir, 500 AS uid, 500 AS gid FROM users WHERE id = '%u' Try that out, replacing the 500's with the appropriate uid and gid and let me know what you see then. Regards, James Bowling On Tue, 11 Jul 2006 17:01:41 +0100, Andrew Hearn <andrew at aa.nu> wrote:> James Bowling wrote: >> Are you using a password encryption scheme for your user passwords in >> the mysql db? You might want to enable the following to help debug the >> issue more in your dovecot.conf: >> >> auth_verbose = yes >> auth_debug = yes >> auth_debug_passwords = yes >> >> Restart Dovecot and then try logging in. This will spit out alot more >> information about the process and might give us a better look into your >> issue. >> > > > Thanks James, > > I'm using plain at the moment whilst testing and getting this working, > the database has a plain password, and > under auth default { I've now set: > mechanisms = plain > > > The log I now get is: > > dovecot: Jul 11 17:01:21 Info: auth(default): client in: AUTH 1 > PLAIN service=IMAP lip=::ffff:81.187.11.54 rip=::ffff:81.187.11.8 > resp=AGFuZHJld0BtYWlsLXRlc3QuYWFpc3AubmV0LnVrADIzNzQyNg=> dovecot: Jul 11 17:01:21 Info: auth-worker(default): > sql(andrew at mail-test.aaisp.net.uk,::ffff:81.187.11.8): query: SELECT > password FROM users WHERE id = 'andrew at mail-test.aaisp.net.uk' > dovecot: Jul 11 17:01:21 Info: auth(default): client out: OK 1 > user=andrew at mail-test.aaisp.net.uk > dovecot: Jul 11 17:01:21 Info: auth(default): master in: REQUEST > 4 17643 1 > dovecot: Jul 11 17:01:21 Info: auth-worker(default): > sql(andrew at mail-test.aaisp.net.uk,::ffff:81.187.11.8): SELECT maildir > FROM users WHERE id = 'andrew at mail-test.aaisp.net.uk' > dovecot: Jul 11 17:01:21 Error: auth-worker(default): > sql(andrew at mail-test.aaisp.net.uk,::ffff:81.187.11.8): Password query > didn't return uid, or it was NULL > dovecot: Jul 11 17:01:21 Info: auth(default): > passwd(andrew at mail-test.aaisp.net.uk,::ffff:81.187.11.8): unknown user > dovecot: Jul 11 17:01:21 Error: auth(default): > userdb(andrew at mail-test.aaisp.net.uk,::ffff:81.187.11.8): user not found > from userdb > dovecot: Jul 11 17:01:21 Info: auth(default): master out: NOTFOUND 4 > dovecot: Jul 11 17:01:21 Info: imap-login: Internal login failure: > user=<andrew at mail-test.aaisp.net.uk>, method=PLAIN, > rip=::ffff:81.187.11.8, lip=::ffff:81.187.11.54 > > > > I'm not sure why it's saying "Password query didn't return uid", that > that seems the issue. > > > I'll carry on playing... Andrew