Lo everyone,
I just felt that there's not allot of detailed examples of working exim
implementations with mysql support. Especially in regards to delivering
mail to mailboxes that does not necessarily have to have accounts on the
systems (/etc/passwd, etc).
This also COMPLETELY eliminates the need to maintain any sort of text file
on the mail server itself. It is completely a setup and forget about it
type of solution, where everything is managed via DBs.
Database redundancy can be provided in various ways. One that I am
currently utilising, is where I specify multiple mysql_servers in exim, and
then I use real-time MySQL replication between the databases. For a POP3
solution, I am also successfully using tpop3d with this configuration. The
queries may seem rather complex to the faint hearted, but rest assured, it
can be simplified quite allot. The system is based on a virtual domain
setup, currently serving in the region of 1500 virtual domains with a
average of 2500 mailboxes per domain... (3.75 million accounts)...
Something else to mention I think, is that tpop3d also has support for the
"pop before SMTP" type of relaying, however I feel that this is unnecessary,
seeing that I have implemented SMTP Authentication, and it authenticates
using the same username / password pair as the POP3 account uses.
Currently, this is using username@domain for the login, with a mysql
encrypted password using the password() function.
The configuration is also not *completely* done yet. I still have to verify
a few stuff, and also implement some generic stuff that is not necessarily
going to affect mail sending / delivery, but rather some generic stuff
relating to the SMTP server itself, such as relay_domains, backup smtp
servers, etc...
Well, here it is then... Use it, abuse it, the normal disclaimer stands,
meaning I provide this as is, and take no claim or responsibility for any
rm -rf's which you may accidentally do while implementing my configs :P
-- exim.conf --
############ IMPORTANT ########## IMPORTANT ########### IMPORTANT
############
#
#
# Whenever you change Exim's configuration file, you *must* remember to HUP
#
# the Exim daemon, because it will not pick up the new configuration until
#
# until you do this. It is usually a good idea to test a new configuration
#
# for syntactic correctness (e.g. using "exim -C /config/file -bV") first.
#
#
#
############ IMPORTANT ########## IMPORTANT ########### IMPORTANT
############
######################################################################
# MAIN CONFIGURATION SETTINGS #
######################################################################
accept_8bitmime
admin_groups = wheel
always_bcc
auth_always_advertise
#auth_hosts = msql;SELECT HOSTS THAT *MUST* AUTHENTICATE
#auth_over_tls_hosts = mysql;SELECT HSOTS THAT *MUST* AUTHENTICATE WITH SSL
ENCRYPTION
auto_thaw = 6h
check_log_inodes = 100
check_log_space = 10M
check_spool_inodes = 100
check_spool_space = 10M
delay_warning = 6h:12h:24h
deliver_load_max = 3
errors_address = postmaster@???
errors_reply_to = support@???
forbid_domain_literals
gecos_name = $1
gecos_pattern = ([^,]*)
headers_check_syntax
headers_sender_verify
#helo_accept_junk_hosts = MYIPLIST
helo_strict_syntax
helo_verify = !192.168.1.0/24:*
#host_accept_relay = MYIPLIST
host_auth_accept_relay = *
host_lookup = *
#host_reject = HOSTS THAT WE HAVE BANNED
#host_reject_recipients = HOSTS THAT WE HAVE BANNED
ignore_errmsg_errors_after = 7d
local_domains = mysql;SELECT ZoneName FROM CompanyDNSZones WHERE
CompanyDNSZones.ZoneName='$key' AND ( \
CompanyDNSZones.ZoneServices LIKE '%HasMail%' OR
CompanyDNSZones.ZoneServices LIKE '%HasMailingList%' );
local_from_check
local_interfaces = 192.168.1.1
localhost_number = 254
locally_caseless
log_all_parents
log_arguments
log_ip_options
log_received_recipients
log_received_sender
log_refused_recipients
log_rewrites
log_sender_on_delivery
log_smtp_confirmation
log_smtp_connections
log_smtp_syntax_errors
log_subject
#message_filter = GLOBAL SYSTEM FILTER
message_size_limit = 10M
message_size_limit_count_recipients
hide mysql_servers = mysqldb01.megalan.co.za/meg001/meg001/l3tm31n
never_users = root:toor:daemon:operator:bin:tty:kmem:games:news:man:named: \
uucp:xten:pop:squid:www:wwwclients:iplog:ftp:mysql:nagios:nobody
primary_hostname = netsonic.megalan.co.za
prod_requires_admin
prohibition_message = ${lookup{$prohibition_reason} \
lsearch{/usr/local/etc/exim/prohibition.messages}{${expand:$value}}}
qualify_domain = megalan.co.za
queue_list_requires_admin
queue_only_load = 3
queue_run_in_order
queue_run_max = 10
rbl_domains = bl.spamcop.net/reject
#rbl_hosts = !MYIPLIST:*
rbl_hosts = *
receiver_try_verify
receiver_verify_hosts = *
receiver_verify_senders = *
relay_domains = mysql;SELECT ZoneName FROM CompanyDNSZones LEFT JOIN
CompanyDetails ON \
CompanyDetails.CompanyID=CompanyDNSZones.CompanyID
WHERE CompanyDetails.CompanySuspended='0' AND \
CompanyDNSZones.ZoneName='$key' AND
CompanyDNSZones.ZoneServices LIKE '%HasBackupMail%';
#relay_match_host_or_sender
remote_max_parallel = 5
remote_sort = *.megalan.co.za:*.za:*.za.*:*.com
rfc1413_hosts = !MYIPLIST:*
#security =
#sender_reject = partial-dbm;/usr/local/etc/exim/senders.reject.dbm
#sender_reject_recipients =
partial-dbm;/usr/local/etc/exim/senders.reject.dbm
sender_try_verify
#sender_verify_callback_domains = *
#sender_verify_callback_timeout = 30s
sender_verify_fixup
sender_verify_hosts = *
#sender_verify_hosts_callback = *
smtp_accept_max = 250
smtp_accept_max_per_host = 10
smtp_accept_queue = 50
smtp_accept_queue_per_connection = 20
smtp_accept_reserve = 50
smtp_banner = "Welcome! This system does not accept Unsolicited \
Commercial Email and will\nblacklist offenders through RBL and our \
internal list. Have a nice day!\n\n${primary_hostname} ESMTP Exim \
${version_number} ${tod_full}"
smtp_load_reserve = 2.5
smtp_reserve_hosts = 192.168.1.0/24
smtp_verify
strip_excess_angle_brackets
strip_trailing_dot
timeout_frozen_after = 7d
tls_advertise_hosts = *
#tls_certificate =
tls_host_accept_relay = *
#tls_hosts = SYSTEMS THAT REQUIRE SSL
tls_log_cipher
tls_log_peerdn
#tls_privatekey =
#tls_verify_certificates =
tls_verify_hosts = *
trusted_groups = exim:mailman:vscan
trusted_users = exim:mailman:vscan
######################################################################
# TRANSPORTS CONFIGURATION #
######################################################################
# ORDER DOES NOT MATTER #
# Only one appropriate transport is called for each delivery. #
######################################################################
remote_smtp:
driver = smtp
local_delivery:
driver = appendfile
no_from_hack
prefix = ""
suffix = ""
maildir_format
create_directory
directory = "/var/spool/mail/${lc:$domain}/${lc:$local_part}/"
user = exim
group = exim
mode = 0660
# TODO:
# Add Quota Checks via DB to limit mailbox sizes?
address_pipe:
driver = pipe
return_output
address_file:
driver = appendfile
delivery_date_add
envelope_to_add
return_path_add
address_reply:
driver = autoreply
mailman_list:
driver = pipe
command = /usr/local/mailman/mail/wrapper post ${lc:$local_part}
current_directory = /usr/local/mailman
home_directory = /usr/local/mailman
user = mailman
group = exim
mailman_request:
driver = pipe
command = /usr/local/mailman/mail/wrapper mailcmd ${lc:$local_part}
current_directory = /usr/local/mailman
home_directory = /usr/local/mailman
user = mailman
group = exim
mailman_admin:
driver = pipe
command = /usr/local/mailman/mail/wrapper mailowner ${lc:$local_part}
current_directory = /usr/local/mailman
home_directory = /usr/local/mailman
user = mailman
group = exim
amavis:
driver = pipe
command = "/usr/local/sbin/amavis <${sender_address}> ${pipe_addresses}"
prefix =
suffix =
check_string =
escape_string =
return_output = false
return_path_add = false
user = vscan
group = exim
path = "/bin:/sbin:/usr/bin:/usr/sbin"
current_directory = "/var/spool/amavis"
######################################################################
# DIRECTORS CONFIGURATION #
# Specifies how local addresses are handled #
######################################################################
# ORDER DOES MATTER #
# A local address is passed to each in turn until it is accepted. #
######################################################################
mailman_owner:
driver = smartuser
domains = mysql;SELECT ZoneName FROM CompanyDNSZones LEFT JOIN
CompanyDetails ON \
CompanyDetails.CompanyID=CompanyDNSZones.CompanyID WHERE
CompanyDetails.CompanySuspended='0' AND \
CompanyDNSZones.ZoneName='$domain' AND
CompanyDNSZones.ZoneServices LIKE '%HasMailingList%';
require_files = /var/spool/mailman/lists/${lc:$local_part}/config.db
suffix = "-owner"
new_address = "${lc:$local_part}-admin@${domain}"
mailman_admin:
driver = smartuser
domains = mysql;SELECT ZoneName FROM CompanyDNSZones LEFT JOIN
CompanyDetails ON \
CompanyDetails.CompanyID=CompanyDNSZones.CompanyID WHERE
CompanyDetails.CompanySuspended='0' AND \
CompanyDNSZones.ZoneName='$domain' AND
CompanyDNSZones.ZoneServices LIKE '%HasMailingList%';
suffix = -admin
require_files = /var/spool/mailman/lists/${lc:$local_part}/config.db
transport = mailman_admin
mailman_request:
driver = smartuser
domains = mysql;SELECT ZoneName FROM CompanyDNSZones LEFT JOIN
CompanyDetails ON \
CompanyDetails.CompanyID=CompanyDNSZones.CompanyID WHERE
CompanyDetails.CompanySuspended='0' AND \
CompanyDNSZones.ZoneName='$domain' AND
CompanyDNSZones.ZoneServices LIKE '%HasMailingList%';
suffix = -request
require_files = /var/spool/mailman/lists/${lc:$local_part}/config.db
transport = mailman_request
mailman_list:
driver = smartuser
domains = mysql;SELECT ZoneName FROM CompanyDNSZones LEFT JOIN
CompanyDetails ON \
CompanyDetails.CompanyID=CompanyDNSZones.CompanyID WHERE
CompanyDetails.CompanySuspended='0' AND \
CompanyDNSZones.ZoneName='$domain' AND
CompanyDNSZones.ZoneServices LIKE '%HasMailingList%';
require_files = /var/spool/mailman/lists/${lc:$local_part}/config.db
transport = mailman_list
system_aliases:
driver = aliasfile
domains = megalan.co.za
file = /usr/local/etc/exim/aliases
search_type = lsearch
user = exim
file_transport = address_file
pipe_transport = address_pipe
user_aliases:
driver = aliasfile
check_ancestor
forbid_file
forbid_include
forbid_pipe
one_time
search_type = mysql
query = SELECT CONCAT(CompanyMailUsers.Username, '@',
CompanyDNSZones.ZoneName) AS NewAddress FROM CompanyMailAliases \
LEFT JOIN CompanyMailUsers ON
CompanyMailAliases.UserID=CompanyMailUsers.UserID RIGHT JOIN CompanyDNSZones
ON \
CompanyMailAliases.DomainID=CompanyDNSZones.ZoneID \WHERE
CompanyMailAliases.Alias='${local_part}@${domain}' \
LIMIT 1;
user = exim
user_forwards:
driver = aliasfile
check_ancestor
forbid_file
forbid_include
forbid_pipe
one_time
search_type = mysql
query = SELECT ForwardTo FROM CompanyMailForward LEFT JOIN
CompanyMailUsers ON \
CompanyMailForward.UserID=CompanyMailUsers.UserID RIGHT JOIN
CompanyDNSZones ON \
CompanyMailForward.DomainID=CompanyDNSZones.ZoneID WHERE
CompanyMailUsers.Username='${local_part}' AND \
CompanyDNSZones.ZoneName='${domain}' LIMIT 1;
user = exim
#amavis_director:
# condition = "${if eq {$received_protocol}{scanned-ok} {0}{1}}"
# driver = smartuser
# transport = amavis
localuser:
driver = aliasfile
one_time
forbid_file
forbid_include
forbid_pipe
search_type = mysql
query = SELECT Username FROM CompanyMailUsers LEFT JOIN CompanyDetails ON
\
CompanyMailUsers.CompanyID=CompanyDetails.CompanyID RIGHT JOIN
CompanyDNSZones ON \
CompanyMailUsers.DomainID=CompanyDNSZones.ZoneID WHERE
CompanyDNSZones.ZoneName='${quote:$domain}' AND \
CompanyMailUsers.Username='${quote:$local_part}' LIMIT 1;
transport = local_delivery
######################################################################
# ROUTERS CONFIGURATION #
# Specifies how remote addresses are handled #
######################################################################
# ORDER DOES MATTER #
# A remote address is passed to each in turn until it is accepted. #
######################################################################
#amavis_router:
# condition = "${if eq {$received_protocol}{scanned-ok} {0}{1}}"
# driver = domainlist
# route_list = "*"
# transport = amavis
lookuphost:
driver = lookuphost
transport = remote_smtp
ignore_target_hosts = 127.0.0.0/8
# domain_literal:
# driver = ipliteral
# transport = remote_smtp
######################################################################
# RETRY CONFIGURATION #
######################################################################
# Domain Error Retries
# ------ ----- -------
* * F,2h,15m; G,16h,1h,1.5; F,4d,8h
######################################################################
# REWRITE CONFIGURATION #
######################################################################
# There are no rewriting specifications in this default configuration file.
######################################################################
# AUTHENTICATION CONFIGURATION #
######################################################################
plain:
driver = plaintext
public_name = PLAIN
server_condition = ${lookup mysql{ SELECT COUNT(Username) FROM
CompanyMailUsers LEFT JOIN CompanyDetails ON \
CompanyMailUsers.CompanyID=CompanyDetails.CompanyID RIGHT JOIN
CompanyDNSZones \
ON
CompanyMailUsers.DomainID=CompanyDNSZones.ZoneID WHERE \
CompanyDetails.CompanySuspended='0' AND
CompanyMailUsers.Username = \
LEFT('${quote_mysql:$1}', (LOCATE('@',
'${quote_mysql:$1}') -1)) AND \
CompanyMailUsers.Password =
PASSWORD('${quote_mysql:$2}') AND \
CompanyDNSZones.ZoneName =
SUBSTRING('${quote_mysql:$1}', (LOCATE('@', \
'${quote_mysql:$1}') +1)) }}
server_set_id = $1
login:
driver = plaintext
public_name = LOGIN
server_prompts = "Username:: : Password::"
server_condition = ${lookup mysql{ SELECT COUNT(Username) FROM
CompanyMailUsers LEFT JOIN CompanyDetails ON \
CompanyMailUsers.CompanyID=CompanyDetails.CompanyID RIGHT JOIN
CompanyDNSZones \
ON
CompanyMailUsers.DomainID=CompanyDNSZones.ZoneID WHERE \
CompanyDetails.CompanySuspended='0' AND
CompanyMailUsers.Username = \
LEFT('${quote_mysql:$1}', (LOCATE('@',
'${quote_mysql:$1}') -1)) AND \
CompanyMailUsers.Password =
PASSWORD('${quote_mysql:$2}') AND \
CompanyDNSZones.ZoneName =
SUBSTRING('${quote_mysql:$1}', (LOCATE('@', \
'${quote_mysql:$1}') +1)) }}
server_set_id = $1
# End of Exim configuration file