dovecot - Sep 2008 - usernames with/without domain - my solution

My existing pop3/imap environment is courier imap + vpopmail + qmail with MySQL
backend. I'm looking at migrating this to Dovecot.

Currently I have multiple instances of Courier bound to different loopback IPs
(the IPs correspond with the virtual IPs on a server load balancer). When a user
authenticates they don't have to specify a domain - if they don't,
Courier will use a 'default' domain (defined in the Courier config for
that instance).

Moving to Dovecot I want to avoid having to running an instance of Dovecot
listening on each loopback IP, but still handle usernames that may not have the
domain component.

I wasn't able to find any examples showing how to achieve this exactly, so
came up with the following solution:

1. create a new 2 column SQL table: 'domain', 'IP' - this allows
me to map between the local Dovecot IP and the 'default' domain
2. create a (rather ugly looking) sql query that combines data from both the
vpopmail table and the new table

The SQL query is as follows:


SELECT 
  IF('%d' = '',dovecot.domain,'%d') AS authdomain,
  CONCAT(pw_name, '@', pw_domain) AS user, 
  pw_passwd AS password, pw_dir AS userdb_home, 89 AS userdb_uid, 89 AS
userdb_gid
FROM vpopmail, dovecot_authmap AS dovecot 
WHERE  pw_name = '%n' 
  AND  dovecot.local_ip = SUBSTRING_INDEX('%l', ':', -1)  
GROUP BY pw_domain HAVING pw_domain = authdomain


NOTE: I'm prefetching userdb info

This works, however I'd like to know what others think of this approach, and
whether there is a better way to achieve what I require.

Thanks,
Ian.


      Win a MacBook Air or iPod touch with Yahoo!7.
http://au.docs.yahoo.com/homepageset

We do it the same and it works fine, however we do it by inbound port
(because we're behind some loadbalancers that can do port translation.
We create a port -> domain mapping table and then you can just do the
join with "where port = '%a'" (dovecot 1.1 only). Seems a
little neater
than the per-ip solution if you're in a loadbalanced environment. I
don't see why you need the group by or having statements (assuming you
have normalized tables).

Mark

--
Mark Zealey -- Shared Hosting Team Leader
Product Development * Webfusion
123-reg.co.uk, webfusion.co.uk, donhost.co.uk, supanames.co.uk

This mail is subject to http://www.gxn.net/disclaimer 
> -----Original Message-----
> From: dovecot-bounces+mark.zealey=webfusion.com at dovecot.org 
> [mailto:dovecot-bounces+mark.zealey=webfusion.com at dovecot.org]
>  On Behalf Of Ian B
> Sent: 01 September 2008 09:29
> To: dovecot at dovecot.org
> Subject: [Dovecot] usernames with/without domain - my solution
> 
> My existing pop3/imap environment is courier imap + vpopmail 
> + qmail with MySQL backend. I'm looking at migrating this to Dovecot.
> 
> Currently I have multiple instances of Courier bound to 
> different loopback IPs (the IPs correspond with the virtual 
> IPs on a server load balancer). When a user authenticates 
> they don't have to specify a domain - if they don't, Courier 
> will use a 'default' domain (defined in the Courier config 
> for that instance).
> 
> Moving to Dovecot I want to avoid having to running an 
> instance of Dovecot listening on each loopback IP, but still 
> handle usernames that may not have the domain component.
> 
> I wasn't able to find any examples showing how to achieve 
> this exactly, so came up with the following solution:
> 
> 1. create a new 2 column SQL table: 'domain', 'IP' - this 
> allows me to map between the local Dovecot IP and the 'default'
domain
> 2. create a (rather ugly looking) sql query that combines 
> data from both the vpopmail table and the new table
> 
> The SQL query is as follows:
> 
> 
> SELECT 
>   IF('%d' = '',dovecot.domain,'%d') AS authdomain,
>   CONCAT(pw_name, '@', pw_domain) AS user, 
>   pw_passwd AS password, pw_dir AS userdb_home, 89 AS 
> userdb_uid, 89 AS userdb_gid 
> FROM vpopmail, dovecot_authmap AS dovecot 
> WHERE  pw_name = '%n' 
>   AND  dovecot.local_ip = SUBSTRING_INDEX('%l', ':', -1)  
> GROUP BY pw_domain HAVING pw_domain = authdomain
> 
> 
> NOTE: I'm prefetching userdb info
> 
> This works, however I'd like to know what others think of 
> this approach, and whether there is a better way to achieve 
> what I require.
> 
> Thanks,
> Ian.
> 
> 
>       Win a MacBook Air or iPod touch with Yahoo!7. 
> http://au.docs.yahoo.com/homepageset
>