* on the Mon, Jan 29, 2007 at 01:08:26PM +0200, Michael L Griffin wrote:
>> I'm afraid I'm not going to directly answer either of your queries,
>> but offer an alternative solution that I use, and might help instead.
>> You can still have your static file whitelists, but on top of that
>> you can dynamically generate a whitelist as well. I do this by using
>> an sql database. Every time someone from your office sends an email
>> to someone, add that destination address to a whitelist table in the
>> db, and accept all future emails from that address.
<snip other extraneous details>
> Greetings Mike & List
> Would you be willing to share you config examples and MySQL schema
> to save us (me) from re-inventing the wheel?
Hi Michael. First of all, I reformatted the above because you top posted
in your response. Just thought I'd bring this to your attention in case
you weren't aware. Not having a dig, just wanted to make the email more
readable.
Ok. Here's how I record recipients for outgoing mail in the MySQL
database, and then query that table for whitelisting purposes.
The table definition:
CREATE TABLE `mail_recipient` (
`mail_recipient_id` int(10) unsigned NOT NULL auto_increment,
`email_local_part` varchar(255) NOT NULL,
`email_domain` varchar(255) NOT NULL,
`ctime` datetime NOT NULL,
`atime` datetime NOT NULL,
`hits` int(10) unsigned NOT NULL,
PRIMARY KEY (`mail_recipient_id`),
UNIQUE KEY `email_local_part` (`email_local_part`,`email_domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
The column names are fairly self explanatory IMO but let me know if you
need more info. ctime, atime and hits are redundant for the purpose in
question, but I put them in there for informational purposes. Also,
atime could be used to clean out the table if it grows too large. Also,
hits could be used for example if you only wanted to whitelist addresses
that had been emailed X or more times. I don't personally use it in this
fashion; if you've been emailed, you're whitelisted.
You could probably also get away with setting email_local_part to a 64
character varchar as I think that's the max size allowed by the RFCs.
If you wanted to be anal you'd use a case sensitive datatype for the
email_local_part column, but I doubt you'd see any real life benefit.
The CHARSET being used was the default MySQL applied. If it's not sane,
please tell me and point me in the direction of some docs telling me
why. Also, you could probably use MyISAM rather than InnoDB if you
wanted. I've only used InnoDB here to keep it in line with the rest of
my DB which uses strict foreign key constraints and transactions.
Below is defined a MySQL stored function for handling writing a
recipient to the database. I used a function to keep the complexity
of the exim config to a minimum.
DELIMITER ;;
CREATE FUNCTION exim_record_recipient ( VAR_email TEXT ) RETURNS BOOLEAN
BEGIN
DECLARE _HITS INT;
SET @email_local_part = SUBSTRING_INDEX( VAR_email, '@', 1 );
SET @email_domain = SUBSTRING_INDEX( VAR_email, '@', -1 );
SELECT hits
INTO _HITS
FROM mail_recipient
WHERE email_local_part = @email_local_part
AND email_domain = @email_domain;
IF _HITS > 0 THEN
UPDATE mail_recipient
SET atime = NOW(),
hits = hits+1
WHERE email_local_part = @email_local_part
AND email_domain = @email_domain;
ELSE
INSERT INTO mail_recipient
SET email_local_part = @email_local_part,
email_domain = @email_domain, hits=1,
ctime = NOW(),
atime = NOW();
END IF;
RETURN TRUE;
END;
;;
DELIMITER ;
In my rcpt acl, for authenticated connections I do the following to
record the recipient in the database:
warn authenticated = *
condition = ${lookup mysql{\
SELECT exim_record_recipient('${quote_mysql:$local_part@$domain}')\
}{$value}{false}}
You might want to use a "hosts" section rather than "authenticated = *",
or both depending on how you determine whether or not someone is allowed
to relay.
Now. If you want to check the "whitelist" and accept:
accept condition = ${if >{${lookup mysql{\
SELECT hits \
FROM mail_recipient \
WHERE email_local_part = '${quote_mysql:$sender_address_local_part}' \
AND email_domain = '${quote_mysql:$sender_address_domain}'\
}{$value}{0}}{0}{true}{false}}
That's it.
I also mentioned the other whitelisting configuration I have in place as
well where by each incoming sender gets recorded to the db if it passes
the spam/virus filtering, and they then get whitelisted if they've sent
> 2 emails. This is less of an exact science and is still something I'm
working on so would rather not post anything regarding it right now. How
do you list a sending address? If you use the envelope sender, you have
the problems caused by unique sender addresses caused by some mailing
lists, and by some implementations of SRS. I've tried several things to
get around this: Don't record addresses with +'s or ='s in the sender
address, or sanitise the address by replacing all numbers with *'s.
Neither is perfect. Also, do you link it up with the recipient address
as well? If not, a spammer could email several addresses on your system
using the same sender address and get it whitelisted... I've only played
with this on my own personal email so I don't have that problem here.
I'll post something in future for this once I'm happy with the
methodology used, ironed out the issues and applied it to a more heavily
used mail system.
Hope some of this is of use.
Mike
