From: dwt (admin at d-w-t dot com)
Date: Thu Mar 11 2004 - 15:30:36 EST
Andreas,
Not exactly. Our table is closer to this:
| Account ID | Username | Password | Email Address |
|^^^^^^^^^^^^^|^^^^^^^^^^^^|^^^^^^^^^^^^|^^^^^^^^^^^^^^^^^^^^^|
| 1001 | janedoe | pickles | janedoe at domain1 dot com |
| 1002 | janedoe | plums | janedoe at domain2 dot com |
--------------------------------------------------------------
This is done this way because everybody is currently setup
(thousands of users) to authenticate just using their username, not their
email address. Perhaps I misunderstood the second comment, but nothing needs
to be inserted into the database.
When an authentication attempt comes in for janedoe on the second
domain, the only thing the server sees is janedoe and plums. Using the SASL
auxprop configuration, it looks for the username janedoe - finds the first
one and compares the password. The first janedoe entry has a password or
pickles so the attempt gets rejected. This could be solved if we could tell
auxprop to look for a password/username combination in the database, like
making %p = whatever the user has configured as the password for his email
client. So when Janedoe #2 tries to send mail she has %u = janedoe and %p =
plums in Outlook Express. Out statement would say:
Select password from users where username='%u' and password='%p';
I know this isn't %p, but maybe we can make it that...? Far fetched,
maybe, but necessary.
- Demian
-----Original Message-----
From: owner-cyrus-sasl at lists dot andrew dot cmu dot edu
[mailto:owner-cyrus-sasl at lists dot andrew dot cmu dot edu] On Behalf Of Andreas
Winkelmann
Sent: Thursday, March 11, 2004 2:56 PM
To: cyrus-sasl at lists dot andrew dot cmu dot edu
Subject: Re: SASL 2.1.17 with auxprop to Myqsl
Am Donnerstag, 11. März 2004 20:09 schrieb dwt:
> I spent quite a bit of time digging through the archives.. 3 hours
> to be exact. I saw a couple similar requests, yet didn't see one of them
> get answered. I'm hoping this one has better luck.
>
> Smtpd.conf:
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> pwcheck_method: auxprop
> auxprop_plugin: sql
> sql_engine: mysql
> mech_list: plain login
>
> sql_user: removed
> sql_passwd: removed
> sql_hostnames: localhost
> sql_database: postfix
> sql_statement: select Password from users where username='%u';
> sql_verbose: true
>
> ---------------------------------
>
> This setup works fine with one domain. Not a single glitch ever.
> Regardless of how frustrating SASL may be to configure and compile, I have
> to give it a gold star on reliability. But now I've run into a problem.
I'm
> building a massive server to support multiple domains: Courier IMAP,
> Postfix, and Mysql as an authentication module on all ends with Cyrus SASL
> for the outbound authentication. All users for all domains are stored in
> our one database table, "users".
> So in this instance we have janedoe at domain1 dot com with password
> pickles and janedoe at domain2 dot com with password plums. The problem we've
> found is, when SASL hits up the database to authenticate the user, it
finds
> the first entry and then stops. So when janedoe at domain2 dot com tries to send
> mail, with her password plums, SASL looks through the database for janedoe
> and sees the password as pickles and thus rejects the authentication
I think i don't understand you. You have a table:
user | password
-------------------------------------------------
janedoe at domain1 dot com | pickels
janedoe at domain2 dot com | plums
And you query/select the table for "janedoe at domain2 dot com", please tell me why
the rdbms returns the value "pickels"?
> attempt. The solution is to make a statement that looks for the password
> and the username and then compares the two with the authentication attempt
> such as:
>
> Select username from users where password='%p' and username='%u';
>
> I know %p doesn't give me what I want... but I was hoping there was
> some way I could make it what I want since according to some documentation
> I read, %p can "technically be anything".
How should this work? The sense of the query is to recieve the password from
the table, how should this be inserted in the query? And why?
--
|
|
|