[tpop3d-discuss] MySQL problems
Chris Elsworth
chris at shagged.org
Sun, 25 Nov 2001 15:39:36 +0000
Hiya,
Just tinkering with 1.4.1pre1, and I'm experiencing strange
intermittent MySQL failures.
One user can authenticate, but most others can't.
Here's a failed authentication (make your terminal wide) :)
connection_sendresponse: client [6]195.11.8.226/shagged.org: sent `+OK <5ce7c6479c371fdb5cdbd8ae8f0ea709@shagged.org>'
listeners_post_select: client [6]195.11.8.226/shagged.org: connected
connection_parsecommand: client [6]195.11.8.226/shagged.org: received `user test@dmeon.net'
connection_sendresponse: client [6]195.11.8.226/shagged.org: sent `+OK Tell me your password.'
connection_parsecommand: client [6]195.11.8.226/shagged.org: received `pass [...]'
auth_mysql_new_user_pass: SQL query: SELECT CONCAT(pop3_mbox.MailPath, '/', pop3_mbox.UserName), pop3_mbox.Password, pop3_mbox.UserName, 'bsd' FROM pop3_mbox,domain WHERE pop3_mbox.UserName = 'test' AND domain.DomainName = 'dmeon.net' AND pop3_mbox.DomainID = domain.DomainID
auth_mysql_new_user_pass: unix user `bsd' for test@dmeon.net does not make sense
connection_sendresponse: client [6]195.11.8.226/shagged.org: sent `-ERR Authentication failed.'
connection_do: client `[6]195.11.8.226/shagged.org': 1 authentication failures
connection_parsecommand: client [6]195.11.8.226/shagged.org: received `quit'
connection_sendresponse: client [6]195.11.8.226/shagged.org: sent `+OK Done.'
connections_post_select: client [6]195.11.8.226/shagged.org: disconnected
Note:
auth_mysql_new_user_pass: unix user `bsd' for test@dmeon.net does not make sense
This would lead me to believe that my MySQL query is wrong, but if thats
the case, why does it then work for this user:
connection_parsecommand: client [6]195.11.8.226/shagged.org: received `USER chris@black-sun.co.uk'
connection_sendresponse: client [6]195.11.8.226/shagged.org: sent `+OK Tell me your password.'
connection_parsecommand: client [6]195.11.8.226/shagged.org: received `PASS [...]'
auth_mysql_new_user_pass: SQL query: SELECT CONCAT(pop3_mbox.MailPath, '/', pop3_mbox.UserName), pop3_mbox.Password, pop3_mbox.UserName, 'bsd' FROM pop3_mbox,domain WHERE pop3_mbox.UserName = 'chris' AND domain.DomainName = 'black-sun.co.uk' AND pop3_mbox.DomainID = domain.DomainID
authcontext_new_user_pass: began session for `chris@black-sun.co.uk' with mysql; uid 1001, gid 6
fork_child: new child is PID 42059
mailspool_new_from_file: indexed mailspool /var/mail/black-sun.co.uk/chris (0 bytes) in 0.000s
connection_sendresponse: client [6]chris@black-sun.co.uk(195.11.8.226): sent `+OK Welcome aboard! You have no messages at all.'
fork_child: [6]chris@black-sun.co.uk(195.11.8.226): successfully authenticated with mysql
connection_parsecommand: client [6]chris@black-sun.co.uk(195.11.8.226): received `STAT'
connection_sendresponse: client [6]chris@black-sun.co.uk(195.11.8.226): sent `+OK 0 0'
connection_parsecommand: client [6]chris@black-sun.co.uk(195.11.8.226): received `QUIT'
connection_sendresponse: client [6]chris@black-sun.co.uk(195.11.8.226): sent `+OK Done'
connections_post_select: client [6]chris@black-sun.co.uk(195.11.8.226): disconnected
authcontext_delete: finished session for `chris@black-sun.co.uk' with mysql
Same query, same database, same permissions on all the files its using.
To enforce the point further, here's another user from the same domain
as the one above, black-sun.co.uk, failing to auth:
connection_sendresponse: client [6]195.149.46.61/shagged.org: sent `+OK <3d348fdeada808876317506ae9edff72@shagged.org>'
listeners_post_select: client [6]195.149.46.61/shagged.org: connected
connection_parsecommand: client [6]195.149.46.61/shagged.org: received `USER alex@black-sun.co.uk'
connection_sendresponse: client [6]195.149.46.61/shagged.org: sent `+OK Tell me your password.'
connection_parsecommand: client [6]195.149.46.61/shagged.org: received `PASS [...]'
auth_mysql_new_user_pass: SQL query: SELECT CONCAT(pop3_mbox.MailPath, '/', pop3_mbox.UserName), pop3_mbox.Password, pop3_mbox.UserName, 'bsd' FROM pop3_mbox,domain WHERE pop3_mbox.UserName = 'alex' AND domain.DomainName = 'black-sun.co.uk' AND pop3_mbox.DomainID = domain.DomainID
auth_mysql_new_user_pass: unix user `bsd' for alex@black-sun.co.uk does not make sense
connection_sendresponse: client [6]195.149.46.61/shagged.org: sent `-ERR Authentication failed.'
connection_do: client `[6]195.149.46.61/shagged.org': 1 authentication failures
connection_parsecommand: client [6]195.149.46.61/shagged.org: received `QUIT'
connection_sendresponse: client [6]195.149.46.61/shagged.org: sent `+OK Done.'
connections_post_select: client [6]195.149.46.61/shagged.org: disconnected
The query, though you can probably work it out from the above:
auth-mysql-pass-query: SELECT CONCAT(pop3_mbox.MailPath, '/', pop3_mbox.UserName), pop3_mbox.Password, pop3_mbox.UserName, 'bsd' FROM pop3_mbox,domain WHERE pop3_mbox.UserName = '$(local_part)' AND domain.DomainName = '$(domain)' AND pop3_mbox.DomainID = domain.DomainID
Which I understand should be right, looking at the source:
/* Default query templates. The returned fields are:
* [0] location of mailbox
* [1] password hash
* [2] unix user
* [3] mailbox type
*/
Relevant contents of spooldir:
drwxrwx--- 2 mail mail 512 Nov 25 15:32 .
-rw-rw---- 1 mail mail 0 Nov 25 13:05 alex
-rw-rw---- 1 mail mail 0 Nov 25 13:11 chris
Anyone got a clue cos I'm all out :)
Cheers,
--
Chris Elsworth - Software & Systems Developer / Systems Administrator