[exim] Split username and domain for authentication

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