[tpop3d-discuss] MySQL problems

[Chris Elsworth]

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 
<address@hidden>'
listeners_post_select: client [6]195.11.8.226/shagged.org: connected
connection_parsecommand: client [6]195.11.8.226/shagged.org: received `user 
address@hidden'
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 address@hidden 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 address@hidden 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 
address@hidden'
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 address@hidden' 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 address@hidden(195.11.8.226): sent `+OK Welcome 
aboard! You have no messages at all.'
fork_child: address@hidden(195.11.8.226): successfully authenticated with mysql
connection_parsecommand: client address@hidden(195.11.8.226): received `STAT'
connection_sendresponse: client address@hidden(195.11.8.226): sent `+OK 0 0'
connection_parsecommand: client address@hidden(195.11.8.226): received `QUIT'
connection_sendresponse: client address@hidden(195.11.8.226): sent `+OK Done'
connections_post_select: client address@hidden(195.11.8.226): disconnected
authcontext_delete: finished session for address@hidden' 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 
<address@hidden>'
listeners_post_select: client [6]195.149.46.61/shagged.org: connected
connection_parsecommand: client [6]195.149.46.61/shagged.org: received `USER 
address@hidden'
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 address@hidden 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