[Mimedefang] MySQL support for users, domains in MIMEDefang-Spamass. A basic example.

[Kai Ung]

I have been looking for DB support for MIMEDefang - Spamass. config and
did not find any so I have hacked out this starting point if someone
else is looking to.
I am NOT an experienced perl programmer so don't knock me down to hard
:)
No fancy web or GUI interfaces...sorry
Only tested with MySQL but should work on most DBIs.

It enables very BASIC user based preferences for MIMEDefang and
Spamassassin via MySQL (or any DBI)

Create MySQL DB and table:

CREATE database spam;
USE spam;
CREATE TABLE `prefs` (
  `who` varchar(50) NOT NULL default '',
  `alias` varchar(50) default NULL,
  `taglevel` int(11) default NULL,
  `droplevel` int(11) default NULL,
  `tagsubject` tinyint(4) default NULL,
  `subjecttag` varchar(100) default NULL,
  `checkspam` tinyint(4) default NULL,
  UNIQUE KEY `prefs_who` (`who`)
);

GRANT ALL PRIVILEGES ON spam.* TO 'spam'@'localhost' IDENTIFIED BY
'spam' WITH GRANT OPTION;

INSERT INTO prefs
(who,alias,taglevel,droplevel,tagsubject,subjecttag,checkspam)
VALUES("@GLOBAL","@GLOBAL",10,100,0,"***SPAM***",1);

#The DB is now setup for defaults. Add users and domains as you need
them :)

#DB fields and there meaning.
#Who =	mailaddress where these settings are applied. No wildcars. (ex:
firstname.lastname at domain.com)
#Who =	or mail domain where all recipients will receive settings (ex:
domain.com )
#Alias = not used yet. List support.
#taglevel = When to tag the mail. With headerinfo and if tagsubject the
subject with subjecttag
#droplevel = For droping mail. Dont deliver to user.
#tagsubject = If msg should be tagged if taglevel is reached.
#subjecttad = String til tag the Subject line with
#checkspam = Enable og disable spamchecking.


#Put this next section in your mimedefang-filter file. Somewhere before
the filter_ i think.

######################### BEGIN

#Set up some globals so we dont have to do expencive connects/prepare
for each mail
use vars qw($DBH $dbsth);

#Get user preferences from database. Call before spamchecking
#Use usual defaults (sa-mimefang.cf) if anything fails
#Kai Ung - NET Works AS - Use at your own risk.
sub get_recipient_pref () {
	my ($reqdb,$drop_hitsdb,$tagsubjectdb,$subjecttagdb,$checkspam);
	#We only check for first recipient. You could use resend to make
this work with multiple.
	my $recipient = $Recipients[0];
	#Strip <>
	$recipient =~ s/[<>]//g;
	my $dom = $recipient;
	#Get recipients domain for domain wide preferences in database
	$dom =~ s/^.*\@//g;
	unless ($DBH) {
		#We dont have a connection to DB so make it or return if
something fails.
		$DBH =
DBI->connect("DBI:mysql:database=spam;host=localhost","spam", "spam") or
return (undef);
		md_log('Connecting to spam DB');
		}
	unless ($dbsth) {
		#We have not got at prepared SQL statment. Do it.
		$dbsth = $DBH->prepare('select
taglevel,droplevel,tagsubject,subjecttag,checkspam from prefs where who
= ?') or return (undef);
		md_log('Preparing statment to get userprefs from spam
DB');
		}
	#Check if recipient has preferences. This has precedence over
domain and global
	$dbsth->execute($recipient) or return (undef);
	
$dbsth->bind_columns(\$reqdb,\$drop_hitsdb,\$tagsubjectdb,\$subjecttagdb
,\$checkspam) or return (undef);
	return
($reqdb,$drop_hitsdb,$tagsubjectdb,$subjecttagdb,$checkspam) if
($dbsth->fetch());
	#Check for domain wide preferences.
	$dbsth->execute($dom) or return (undef);
	return
($reqdb,$drop_hitsdb,$tagsubjectdb,$subjecttagdb,$checkspam) if
($dbsth->fetch());
	#If not user og domain wide then get the global preferences.
	$dbsth->execute("\@GLOBAL") or return (undef);
	return
($reqdb,$drop_hitsdb,$tagsubjectdb,$subjecttagdb,$checkspam) if
($dbsth->fetch());
	#We should never come her but just in case
	return (undef,undef,undef,undef,undef);
}

########################### END


####This is an EXAMPLE filter_end() that uses the DB prefrences.

sub filter_end ($) {
	my($entity) = @_;

	# No sense doing any extra work

	return if message_rejected();


    # Spam checks if SpamAssassin is installed
    if ($Features{"SpamAssassin"}) {
	if (-s "./INPUTMSG" < 100*1024) {

	#Get the user,domain og global settings
	(my $reqdb,my $drop_hits,my $tagsubject,my $subjecttag,my
$checkspam) = get_recipient_pref();

	#If spamchecking is active for user,domain og global the doit.
	if ($checkspam !=0) {

	my($hits, $req, $names, $report) = spam_assassin_check();

	#Check that we got something and what we got from the DB query
	if ($reqdb) {
		#Only set $req if DB query returned a value
		$req = $reqdb;
		#If the the user,domain og global wants subjecttagging.
		$subject_rewrite = $subjecttag if ($tagsubject);
		#!! CAUTION If the user,domain og global drop_hits is
less than 8 set to 8 !!! CAUTION !! if not set then 100
		$drop_hits = ($drop_hits ? $drop_hits < 8 ? 8 :
$drop_hits : 100);

		if ($hits >= $drop_hits) {
		#Bounce mail if the settings said so.
		md_log('spam-bounce', $hits, $RelayAddr);
		return action_bounce("Sorry but this looks like SPAM.
This is an auto SPAM checker.\n Send a new and simpler mail to the same
person if this is an error !.");
		}
	}
	#Alway add this. To check the spam mails that get past.
	action_add_header("X-Spam-Score", "$hits $names");

            if ($hits >= $req) {
                my $score = '*' x ($hits > 50 ? 50 : $hits);
		action_add_header("X-Spam-Level","$score");
		action_change_header("Subject", $subject_rewrite . " " .
$Subject ) if ($subject_rewrite);
		#Attache the full report.
		action_add_part($entity, "text/plain",
"-suggest","$report\n","SPAM-Report.txt", "inline");
	    }
	}
	}
	}
}

# DO NOT delete the next line, or Perl will complain.
1;



Thats if folks :)

Kai