[tpop3d-discuss] MySQL Authentication

[Chris Lightfoot]

On Sat, Jun 23, 2001 at 06:55:48PM +0100, Chris Elsworth wrote:
> Hi. Me again. :)
> 
> tpop3d has now compiled fine, and everything appears to work except MySQL 
> authentication. Maildirs, mbox, and pam auth, are all fine.
> 
> I'm not sure I 100% understand the data I have to return from MySQL to 
> make it work, so first of all, could anyone here confirm that this is the 
> right info:
> 
> mysql> SELECT 
> pop3_mbox.MailPath,pop3_mbox.UserName,pop3_mbox.Password,domain.UserID 
> FROM pop3_mbox,domain WHERE pop3_mbox.UserName='mats' AND 
> domain.DomainName='zakalwe.com' AND pop3_mbox.DomainID=domain.pop3_pwID;
> 
> this returns one match:
> 
> | MailPath                   | UserName | Password | UserID |
> | /var/mail/zakalwe.com/mats | mats     | snip     |   1025 |
> 
> [where snip is an MD5 hash of a password, like $1$lJ$EN6ExaPgfp1d.T9w59/S4/ ]
> 
> That's what I've put into auth_mysql.c :
> 
> char user_pass_query_template[] =
> "SELECT pop3_mbox.UserName,pop3_mbox.Password,pop3_mbox.MailPath,domain.UserID "
>   "FROM pop3_mbox,domain "
>  "WHERE pop3_mbox.UserName = '%s' "
>    "AND domain.DomainName = '%s' "
>    "AND pop3_mbox.DomainID = domain.pop3_pwID";
> 
> (same for APOP, even though I'm not currently using it)

Errm. Have you altered the code which gets those values
out of the row returned by MySQL? If not, they will be
coming out in the wrong order. auth_mysql.c gets them in
the order:

    path prefix for mailbox
    mailbox file name
    password hash
    unix user

whereas you are, I think,  retrieving

    username
    password hash
    path prefix
    unix user

-- if you reorder things to look like

SELECT pop3_mbox.MailPath, pop3_mbox.UserName,
       pop3_mbox.Password, domain.UserID
    [...]

it should work (assuming that fred@example.com's mailspool
is in MailPath/fred).

> But alas, I get:
> 
> auth_mysql_new_user_pass: mats@zakalwe.com failed login with wrong passwordauth_
> 
> Am I doing the right thing, returning the right information?

Not quite, unless you've modified the authentication stuff
separately. To use crypt_md5 as you appear to be doing,
you need to insert {crypt_md5} before the password, like

    '{crypt_md5}$1$lJ$EN6ExaPgfp1d.T9w59/S4/'

in the appropriate field. If this is already what you're
doing, then something is wrong, but I'd need more
information to diagnose it....

If you don't want to put {crypt_md5} in, you can make that
password format the default, by altering lines 483--508 of
auth_mysql.c

-- 
Chris Lightfoot -- www.ex-parrot.com/~chris/
 ``There's so much comedy on television.
   Does that cause comedy in the streets?'' (Dick Cavett)