On May 6, 2010, at 3:20 AM, Ron White wrote:
> I'm looking at the concept of creating a message log database which
> contains one line for each message that my Exim sees.
>
> At this point I'm mostly guessing, but I suspect in the my various ACL
> 'drop' or 'deny' statements I could probably add something like:
>
> set acl_m_nullthing = ${lookup mysql{INSERT INTO
> maillog(recipient,sender,client_ip) VALUES ('${quote_mysql:$local_part}@
> ${quote_mysql:$domain}','foo','bar');}}
>
> But I don't know how I would approach the problem of successful messages
> that are not dropped, ie, have passed through a transport successfully.
> Probably hoping to much, but I'd love to be able to log the message ID
> and, where appropriate, the physical file name and location of the
> delivered message in addition to the ip, to, from, subject.
>
> I can probably hash up some kind of Perl script to trawl the logs at a
> preset interval and populate a database, but it would be much 'nicer' if
> I could do it as the message rolls through Exim.
>
> Anyone know if it's possible to do this? Forgive me if the question is
> stupid - but I'm not the sharpest knife in the drawer ;-)
Our email solution, ECMSquared.com (Exim, Courier, MySQL) stores all configuration information:
sites, domains, users
system settings, greylist exceptions, whitelists, blacklists, DNSbls
spamassassin rules, bayes, auto-whitelist
fetchmail configs, warning messages, multi-langauage texts
in a MySQL database
and our exim config logs all ips with stats: RDNS, helo, proper QUITs, bytes in/out
along with logs for incoming connections, authentications, blocked messages, accepted messages, sent messages, greylists, etc...
Basically, we've broken lookups and logging down with the use of constants/acl named variables like so:
## from datalookups.eximconf
## Lookup sender domain/site to see if it's local
LOOKUP_SENDER_DOMAIN = SELECT domains.domain_id, sites.site_id, \
domains.enabled as dom_enabled, sites.enabled as site_enabled \
FROM domains LEFT JOIN sites USING (site_id) \
WHERE domain = '${quote_mysql:$sender_address_domain}' \
AND domains.enabled = 1 AND (sites.site_id IS NOT NULL)
## we are included a domain enable check here so that a domain can be on a site
## but not enabled in preparation for being enabled.
## This will make email sent with a from address of that domain appear to be not local
## from main.eximconf
## gets called within the MAIL FROM acl
#--------------------------------------------------------------- ACL SENDER CHECK
## Lookup the information on the sender address to see if it's local
#
warn
set acl_m_sender_islocal = false
set acl_c_sender_dbrec = ${lookup mysql{LOOKUP_SENDER_DOMAIN}{$value}{}}
set acl_m_sender_siteid = ${extract{site_id}{$acl_c_sender_dbrec}{$value}{0}}
condition = ${if >{$acl_m_sender_siteid}{0}}
set acl_m_sender_islocal = true
set acl_m_sender_site_enabled = ${extract{site_enabled}{$acl_c_sender_dbrec}{$value}{0}}
set acl_m_sender_domainid = ${extract{domain_id}{$acl_c_sender_dbrec}{$value}{0}}
## from constants.eximconf
SENDER_IP_SQL = INET_ATON('${quote_mysql:$sender_host_address}')
STARTTIME_UTC = STR_TO_DATE('$acl_c_cnxn_starttimeUTC', '%Y%m%d%H%i%s')
## from blocking.eximconf
BLOCK_LOG_DATA_ALLRCPTS = INSERT DELAYED INTO block_log \
(when_blocked,ip,helo,sender,recipients,recip_id,site_id,subject,msgsize,reason,block_desc) \
VALUES (UTC_TIMESTAMP(), SENDER_IP_SQL, \
'${quote_mysql:$sender_helo_name}', '${quote_mysql:${lc:$sender_address}}', '${quote_mysql:$recipients}', \
${if >{$recipients_count}{1}{NULL}{$acl_m_firstrcpt_userid+0}}, $acl_m_rcpt_siteid, \
'${quote_mysql:$h_Subject:}', $message_size, $acl_m_reject_logcode, '${quote_mysql:$acl_m_reject_logtext}')
## from msglogging.eximconf
REJECTMAIL_DEL = DELETE LOW_PRIORITY FROM recent_mail \
WHERE (ip = SENDER_IP_SQL) AND (msgid = '${quote_mysql:$acl_m_msgid_hash}') \
AND (recvd > DATE_ADD(STARTTIME_UTC, INTERVAL -15 MINUTE) ) LIMIT $rcpt_count
## from main.eximconf in the DATA ACL
## if the host is NOT trusted, the message size is checked, and if too large, returns a deny, adds a block log record
## and deletes the recent mail record(s) that were temporarily inserted when the message/recipients
## first started to be processed.
deny
!condition = $acl_c_trusted_host
condition = ${if >{$message_size}{$acl_c_temp_num}}
message = Message size $message_size is larger than limit of $acl_c_temp_num
log_message = Message denied, $message_size is larger than limit of $acl_c_temp_num
set acl_m_reject_logcode = BLOCK_TOOBIG
set acl_m_reject_logtext = $message_size|$acl_c_temp_num
continue = ${lookup mysql{BLOCK_LOG_DATA_ALLRCPTS}}
continue = ${lookup mysql{REJECTMAIL_DEL}}
Also, when a message is blocked from SpamAssassin, that score is stored in the log text so we can display to the user the score that message received.
So, the answer is yes, it can definitely be done.
There are several maintenance processes that we run at different intervals to maintain the tables.
For example, the site admin can choose how many days back in the block/mail logs to keep records for; records older than that are deleted in in the daily maintenance script.
One nice thing about using the SQL db is that when we encounter a misbehaving incoming host, we add them to a blacklist table for a period of time that rejects future connections. This way the system is self-adapting to certain conditions.
And of course, our users/admins can login to the web interface and see/filter all sort of logged information.
We store all date/times in the db using UTC and alter the output based on the users local timezone which we pick up when they login using some javascript.
Brian
