[tpop3d-discuss] vmail-sql README for Exim 4.x

ODHIAMBO Washington wash at wananchi.com
Thu, 8 May 2003 17:36:50 +0300


--oLBj+sq0vYjzfsbl
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline


Hello Chris,

Attached is a patch against the README file that is distributed with
vmail-sql-0.7 that is adapted to Exim 4.x.
So maybe you can have README-exim-3.x and README-exim-4.x

I have tested the setup extensively, except for domain_alias which I
do not use in my environment. This is what I run on production.
I believe the SQL syntax in the routers can still be rewritten in a
better way though;)


Thanks for all the work you do. I use tpop3d here (1.4.2) handling
800 virtual domains (using vmail-sql) and I must say I am very happy
with tpop3d.


-Wash

-- 
Odhiambo Washington   <wash@wananchi.com>  "The box said 'Requires
Wananchi Online Ltd.  www.wananchi.com      Windows 95, NT, or better,'
Tel: +254 2 313985-9  +254 2 313922         so I installed FreeBSD."   
GSM: +254 72 743223   +254 733 744121       This sig is McQ!  :-)


"All flesh is grass"
		-- Isiah
Smoke a friend today.

--oLBj+sq0vYjzfsbl
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="README.patch"

--- README.orig	Thu May  8 16:57:36 2003
+++ README	Thu May  8 17:18:43 2003
@@ -119,23 +119,20 @@
 the database are `local'. You probably have other local domains which you list
 explicitly, so you should do something like
 
-    local_domains = <#(your existing domains separated by #s)#\
-                     mysql;select domain_name from domain \
-                            where domain_name = '${quote_mysql:$key}'#\
-                     mysql;select alias from domain_alias where alias = '${quote_mysql:$key}'
+domainlist sql_virtual_domains = mysql;select 1 from domain where domain_name='${quote_mysql:$domain}' : \
+                                 mysql;select 1 from domain_alias where alias='${quote_mysql:$domain}'
+
+
+domainlist local_domains = (your existing domains) : +sql_virtual_domains
 
-(Note use of the <# syntax to change the separator of the domain list. This is
-described in section 7.11 of spec.txt, and is required because otherwise the
-: in the quote_mysql operator causes the list to be broken half-way through
-the SQL query. Cf.
-  http://www.exim.org/pipermail/exim-users/Week-of-Mon-20000515/018154.html
-which is incorrect.)
 
 Next, you need to organise things so that mail to virtual-domain users actually
 gets delivered. This is done using three directors, which figure out where
 virtual addresses go:
 
---- from exim.conf, DIRECTORS section
+
+--- from exim.conf, ROUTERS section (Exim 4.x)
+
 
 # The virtualemail database allows local parts to correspond to remote
 # addresses (forwarding), to be aliases for local maildrops, or to be local
@@ -145,29 +142,36 @@
 # Handle forwarders and aliases (same database table)
 
 virtual_forward:
-  driver = aliasfile
-  search_type = mysql
-  query = "select remote_name from forwarder left join domain_alias on domain_alias.domain_name = forwarder.domain_name where local_part = '${quote_mysql:$local_part}' and (forwarder.domain_name = '${quote_mysql:$domain}' or alias = '${quote_mysql:$domain}')"
-  forbid_file = true
-  forbid_pipe = true
+  driver        = redirect
+  data          =  ${lookup mysql{select remote_name from forwarder left join domain_alias on domain_alias.domain_name = forwarder.domain_name where local_part = '${quote_mysql:$local_part}' and (forwarder.domain_name = '${quote_mysql:$domain}' or alias = '${quote_mysql:$domain}')}}
+  forbid_file
+  forbid_pipe
+  allow_defer
+  allow_fail
+  retry_use_local_part
+  
 
 # Handle delivery; see sample config C009 for what this is based on; it only
 # handles things for the virtual domains, which are listed in the database.
 
 virtual_localuser:
-  driver = aliasfile
-  search_type = mysql
-  query = "select mbox_name from popbox left join domain_alias on domain_alias.domain_name = popbox.domain_name where local_part = '${quote_mysql:$local_part}' and (popbox.domain_name = '${quote_mysql:$domain}' or alias = '${quote_mysql:$domain}')"
-  transport = virtual_localdelivery
+   driver       = accept 
+   domains      = mysql;select 1 from domain where domain_name='${quote_mysql:$domain}'
+   local_parts  = mysql;select 1 from popbox where local_part='${quote_mysql:$local_part}' and domain_name ='$domain'
+   transport    = domainplus_delivery
+
 
 # deliver any undelivered virtual email according to the _default_ forwarder
 
 virtual_defaultuser:
-  driver = aliasfile
-  search_type = mysql
-  query = "select remote_name from forwarder left join domain_alias on domain_alias.domain_name = forwarder.domain_name where local_part = '_default_' and (forwarder.domain_name = '${quote_mysql:$domain}' or domain_alias.alias = '${quote_mysql:$domain}')"
-  forbid_file = true
-  forbid_pipe = true
+  driver        = redirect
+  data          = ${lookup mysql{select remote_name from forwarder left join domain_alias on domain_alias.domain_name = forwarder.domain_name where local_part = '_default_' and (forwarder.domain_name = '${quote_mysql:$domain}' or domain_alias.alias = '${quote_mysql:$domain}')}}
+  forbid_file   = true
+  forbid_pipe   = true
+  allow_defer
+  allow_fail
+  retry_use_local_part
+
 
 The virtual POP boxes are handled by a transport as follows:
 
@@ -178,21 +182,34 @@
 # triggered from a director below.
 
 virtual_localdelivery:
-  driver = appendfile
-  file = ${lookup mysql{select path from domain left join domain_alias on domain_alias.domain_name = domain.domain_name where domain.domain_name = '${quote_mysql:$domain}' or domain_alias.alias = '${quote_mysql:$domain}'}{$value}fail}/${lookup mysql{select mbox_name from popbox left join domain_alias on popbox.domain_name = domain_alias.domain_name where (popbox.domain_name = '${quote_mysql:$domain}' or domain_alias.alias = '${quote_mysql:$domain}') and local_part = '${quote_mysql:$local_part}'}{$value}fail}
+  driver        = appendfile 
+  file          = ${lookup mysql{select path from domain left join domain_alias on domain_alias.domain_name = domain.domain_name where domain.domain_name = '${quote_mysql:$domain}' or domain_alias.alias = '${quote_mysql:$domain}'}{$value}fail}/${lookup mysql{select mbox_name from popbox left join domain_alias on popbox.domain_name = domain_alias.domain_name where (popbox.domain_name = '${quote_mysql:$domain}' or domain_alias.alias = '${quote_mysql:$domain}') and local_part = '${quote_mysql:$local_part}'}{$value}fail}
+  user          = ${lookup mysql{select unix_user from domain left join domain_alias on domain_alias.domain_name = domain.domain_name where domain.domain_name = '${quote_mysql:$domain}' or domain_alias.alias = '${quote_mysql:$domain}'}{$value}fail}
+  group         = mail
+  mode          = 0660
   delivery_date_add
   envelope_to_add
   return_path_add
-  user = ${lookup mysql{select unix_user from domain left join domain_alias on domain_alias.domain_name = domain.domain_name where domain.domain_name = '${quote_mysql:$domain}' or domain_alias.alias = '${quote_mysql:$domain}'}{$value}fail}
-  mode = 0660
+
+
 
 (The comment about directories is true in the setup in which we use this,
 but it's not a requirement. You can put the virtual mailspools anywhere.)
 
 In order to have Exim talk to the database at all, you will need a
-configuration line of the form
+configuration line of the form of
+
+hide mysql_servers = localhost/DBNAME/DBUSER/DBPASS
+
+[This option is used if mysql is running on a remote host]
+
+OR
+
+hide mysql_servers = localhost::(/tmp/mysql.sock)/DBNAME/DBUSER/DBPASS
+
+[This option is used when mysql is running on same box as Exim. There
+is an advantage to socket connection, than using TCP connection]
 
-    hide mysql_servers = "host/name/user/pass"
 
 where host is the host running the MySQL server, name is the database name,
 user is the username under which to access the database, and pass is that

--oLBj+sq0vYjzfsbl--