For the ML archives, I just wanted to document what I had to do to get
authentication working where the username and domain are stored in two
seperate tables linked by foreign keys. On my servers, I have an
email table and a domain table linked with a foreign key, with data as
below. Additionally, to login to our system, there is a user table
which holds an md5 crypt hashed password that is linked with a foreign
key. On a normal account, the email username 'info' with domain
'example.org' has a real user account named 'info@???'. My
original query expected that. However in the special case shown
below, the user account is named something totally different,
"bigtreehouse". That causes a problem because the user account name
doesn't match the full email address, so PLAIN ($auth2) and LOGIN
($auth1) email addresses don't equal what's in the database. So I
modified the query to split the username and domain parts of the email
address and query directly on that.
Here is some sample data to visualize this:
domain table: id, name
values: 13091, example.org
email table: id, name, domain_id
values: 45508, info, 13091
user table: id, name, password, email_id
values: 83001, bigtreehouse, $1$l51C7d7h$AjwikCnRyHYqq1umMWgXZ0, 45508
(md5 crypted hash of "password")
I apologize in advance for how badly the following lines are going to
be wrapped. Pay attention to the long query line that will probably
get wrapped 3 or 4 times.
If the password is stored in the user table (my configuration), the
auth configurations are:
plain:
driver = plaintext
public_name = PLAIN
server_prompts = :
server_condition = ${if and { \
{!eq{$auth2}{}} \
{!eq{$auth3}{}} \
{ crypteq{$auth3}{\{crypt\}${lookup mysql{SELECT u.password
FROM user u JOIN email e ON u.email_id=e.id JOIN domain d ON
e.domain_id=d.id WHERE
e.name=substring_index('${quote_mysql:$auth2}','@',1) AND
d.name=substring_index('${quote_mysql:$auth2}','@',-1) AND e.active=1
and d.active=1;}{$value}fail}} } \
} {1}{0}}
server_set_id = $auth2
login:
driver = plaintext
public_name = LOGIN
server_prompts = Username:: : Password::
server_condition = ${if and { \
{!eq{$auth1}{}} \
{!eq{$auth2}{}} \
{ crypteq{$auth2}{\{crypt\}${lookup mysql{SELECT u.password
FROM user u JOIN email e ON u.email_id=e.id JOIN domain d ON
e.domain_id=d.id WHERE
e.name=substring_index('${quote_mysql:$auth1}','@',1) AND
d.name=substring_index('${quote_mysql:$auth1}','@',-1) AND e.active=1
and d.active=1;}{$value}fail}} } \
} {1}{0}}
server_set_id = $auth1
If the password was only stored in the email table (i.e. user table
doesn't exist is isn't related to the mailboxes) the auth
configurations are:
plain:
driver = plaintext
public_name = PLAIN
server_prompts = :
server_condition = ${if and { \
{!eq{$auth2}{}} \
{!eq{$auth3}{}} \
{ crypteq{$auth3}{\{crypt\}${lookup mysql{SELECT e.password
FROM email e JOIN domain d ON e.domain_id=d.id WHERE
e.name=substring_index('${quote_mysql:$auth2}','@',1) AND
d.name=substring_index('${quote_mysql:$auth2}','@',-1) AND e.active=1
and d.active=1;}{$value}fail}} } \
} {1}{0}}
server_set_id = $auth2
login:
driver = plaintext
public_name = LOGIN
server_prompts = Username:: : Password::
server_condition = ${if and { \
{!eq{$auth1}{}} \
{!eq{$auth2}{}} \
{ crypteq{$auth2}{\{crypt\}${lookup mysql{SELECT e.password
FROM email e JOIN domain d ON e.domain_id=d.id WHERE
e.name=substring_index('${quote_mysql:$auth1}','@',1) AND
d.name=substring_index('${quote_mysql:$auth1}','@',-1) AND e.active=1
and d.active=1;}{$value}fail}} } \
} {1}{0}}
server_set_id = $auth1
Hopefully this will help someone down the road who has a similar issue.
--
Regards... Todd
I seek the truth...it is only persistence in self-delusion and
ignorance that does harm. -- Marcus Aurealius
