You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spamassassin.apache.org by mm...@apache.org on 2008/04/23 14:53:19 UTC
svn commit: r650852 - in /spamassassin/trunk: lib/Mail/SpamAssassin/
lib/Mail/SpamAssassin/Plugin/ sql/
Author: mmartinec
Date: Wed Apr 23 05:53:18 2008
New Revision: 650852
URL: http://svn.apache.org/viewvc?rev=650852&view=rev
Log:
PR-5891: let AWL keep separate records for DKIM-signed and unsigned mail
Modified:
spamassassin/trunk/lib/Mail/SpamAssassin/AutoWhitelist.pm
spamassassin/trunk/lib/Mail/SpamAssassin/DBBasedAddrList.pm
spamassassin/trunk/lib/Mail/SpamAssassin/PersistentAddrList.pm
spamassassin/trunk/lib/Mail/SpamAssassin/Plugin/AWL.pm
spamassassin/trunk/lib/Mail/SpamAssassin/SQLBasedAddrList.pm
spamassassin/trunk/sql/README.awl
spamassassin/trunk/sql/awl_mysql.sql
spamassassin/trunk/sql/awl_pg.sql
Modified: spamassassin/trunk/lib/Mail/SpamAssassin/AutoWhitelist.pm
URL: http://svn.apache.org/viewvc/spamassassin/trunk/lib/Mail/SpamAssassin/AutoWhitelist.pm?rev=650852&r1=650851&r2=650852&view=diff
==============================================================================
--- spamassassin/trunk/lib/Mail/SpamAssassin/AutoWhitelist.pm (original)
+++ spamassassin/trunk/lib/Mail/SpamAssassin/AutoWhitelist.pm Wed Apr 23 05:53:18 2008
@@ -106,7 +106,7 @@
###########################################################################
-=item $meanscore = awl->check_address($addr, $originating_ip);
+=item $meanscore = awl->check_address($addr, $originating_ip, $signedby);
This method will return the mean score of all messages associated with the
given address, or undef if the address hasn't been seen before.
@@ -116,7 +116,7 @@
=cut
sub check_address {
- my ($self, $addr, $origip) = @_;
+ my ($self, $addr, $origip, $signedby) = @_;
if (!defined $self->{checker}) {
return undef; # no factory defined; we can't check
@@ -125,14 +125,14 @@
$self->{entry} = undef;
my $fulladdr = $self->pack_addr ($addr, $origip);
- $self->{entry} = $self->{checker}->get_addr_entry ($fulladdr);
+ $self->{entry} = $self->{checker}->get_addr_entry ($fulladdr, $signedby);
if (!defined $self->{entry}->{count} || $self->{entry}->{count} == 0) {
# no entry found
if (defined $origip) {
# try upgrading a default entry (probably from "add-addr-to-foo")
my $noipaddr = $self->pack_addr ($addr, undef);
- my $noipent = $self->{checker}->get_addr_entry ($noipaddr);
+ my $noipent = $self->{checker}->get_addr_entry ($noipaddr, $signedby);
if (defined $noipent->{count} && $noipent->{count} > 0) {
dbg("auto-whitelist: found entry w/o IP address for $addr: replacing with $origip");
@@ -153,6 +153,23 @@
###########################################################################
+=item $meanscore = awl->check_signer_reputation($addr, $signedby);
+
+This method will return a mean score of all messages signed by the
+given signing identity, or undef if no such entries exist.
+
+=cut
+
+sub check_signer_reputation {
+ my ($self, $addr, $signedby) = @_;
+
+ return undef if !defined $self->{checker};
+ return undef if !$self->{checker}->UNIVERSAL::can("get_signer_reputation");
+ return $self->{checker}->get_signer_reputation($addr, $signedby);
+}
+
+###########################################################################
+
=item awl->count();
This method will return the count of messages used in determining the
@@ -197,9 +214,9 @@
=cut
sub add_known_good_address {
- my ($self, $addr) = @_;
+ my ($self, $addr, $signedby) = @_;
- return $self->modify_address($addr, -100);
+ return $self->modify_address($addr, -100, $signedby);
}
@@ -213,30 +230,30 @@
=cut
sub add_known_bad_address {
- my ($self, $addr) = @_;
+ my ($self, $addr, $signedby) = @_;
- return $self->modify_address($addr, 100);
+ return $self->modify_address($addr, 100, $signedby);
}
###########################################################################
sub remove_address {
- my ($self, $addr) = @_;
+ my ($self, $addr, $signedby) = @_;
- return $self->modify_address($addr, undef);
+ return $self->modify_address($addr, undef, $signedby);
}
###########################################################################
sub modify_address {
- my ($self, $addr, $score) = @_;
+ my ($self, $addr, $score, $signedby) = @_;
if (!defined $self->{checker}) {
return undef; # no factory defined; we can't check
}
my $fulladdr = $self->pack_addr ($addr, undef);
- my $entry = $self->{checker}->get_addr_entry ($fulladdr);
+ my $entry = $self->{checker}->get_addr_entry ($fulladdr, $signedby);
# remove any old entries (will remove per-ip entries as well)
# always call this regardless, as the current entry may have 0
@@ -247,7 +264,7 @@
if (!defined($score)) { return 1; }
# else add score. get a new entry first
- $entry = $self->{checker}->get_addr_entry ($fulladdr);
+ $entry = $self->{checker}->get_addr_entry ($fulladdr, $signedby);
$self->{checker}->add_score($entry, $score);
return 1;
Modified: spamassassin/trunk/lib/Mail/SpamAssassin/DBBasedAddrList.pm
URL: http://svn.apache.org/viewvc/spamassassin/trunk/lib/Mail/SpamAssassin/DBBasedAddrList.pm?rev=650852&r1=650851&r2=650852&view=diff
==============================================================================
--- spamassassin/trunk/lib/Mail/SpamAssassin/DBBasedAddrList.pm (original)
+++ spamassassin/trunk/lib/Mail/SpamAssassin/DBBasedAddrList.pm Wed Apr 23 05:53:18 2008
@@ -115,7 +115,7 @@
###########################################################################
sub get_addr_entry {
- my ($self, $addr) = @_;
+ my ($self, $addr, $signedby) = @_;
my $entry = {
addr => $addr,
@@ -126,6 +126,13 @@
dbg("auto-whitelist: db-based $addr scores ".$entry->{count}.'/'.$entry->{totscore});
return $entry;
+}
+
+###########################################################################
+
+sub get_signer_reputation {
+ my ($self, $addr, $signedby) = @_;
+ return undef;
}
###########################################################################
Modified: spamassassin/trunk/lib/Mail/SpamAssassin/PersistentAddrList.pm
URL: http://svn.apache.org/viewvc/spamassassin/trunk/lib/Mail/SpamAssassin/PersistentAddrList.pm?rev=650852&r1=650851&r2=650852&view=diff
==============================================================================
--- spamassassin/trunk/lib/Mail/SpamAssassin/PersistentAddrList.pm (original)
+++ spamassassin/trunk/lib/Mail/SpamAssassin/PersistentAddrList.pm Wed Apr 23 05:53:18 2008
@@ -111,7 +111,7 @@
=cut
sub get_addr_entry {
- my ($self, $addr) = @_;
+ my ($self, $addr, $signedby) = @_;
my $entry = { };
die "auto-whitelist: unimplemented base method"; # override this
return $entry;
Modified: spamassassin/trunk/lib/Mail/SpamAssassin/Plugin/AWL.pm
URL: http://svn.apache.org/viewvc/spamassassin/trunk/lib/Mail/SpamAssassin/Plugin/AWL.pm?rev=650852&r1=650851&r2=650852&view=diff
==============================================================================
--- spamassassin/trunk/lib/Mail/SpamAssassin/Plugin/AWL.pm (original)
+++ spamassassin/trunk/lib/Mail/SpamAssassin/Plugin/AWL.pm Wed Apr 23 05:53:18 2008
@@ -164,6 +164,22 @@
type => $Mail::SpamAssassin::Conf::CONF_TYPE_STRING
});
+=item auto_whitelist_distinguish_signed
+
+If this option is set the SQLBasedAddrList module will keep separate
+database entries for DKIM-validated e-mail addresses and for non-validated
+ones. A pre-requisite when setting this option is that a field awl.signedby
+exists in a SQL table, otherwise SQL operations will fail. A plugin DKIM
+should also be enabled, as otherwise turning on this option makes no sense.
+
+=cut
+
+ push (@cmds, {
+ setting => 'auto_whitelist_distinguish_signed',
+ default => 0,
+ type => $Mail::SpamAssassin::Conf::CONF_TYPE_BOOL
+ });
+
=back
=head1 ADMINISTRATOR SETTINGS
@@ -320,10 +336,10 @@
return 0 unless ($pms->{conf}->{use_auto_whitelist});
- my $timer = $self->{main}->time_method("check_awl");
+ my $timer = $self->{main}->time_method("total_awl");
- local $_ = lc $pms->get('From:addr');
- return 0 unless /\S/;
+ my $from = lc $pms->get('From:addr');
+ return 0 unless $from =~ /\S/;
# find the earliest usable "originating IP". ignore private nets
my $origip;
@@ -338,6 +354,8 @@
my $scores = $pms->{conf}->{scores};
my $tflags = $pms->{conf}->{tflags};
my $points = 0;
+ my $signedby = $pms->get_tag('DKIMIDENTITY');
+ $signedby = undef if defined $signedby && $signedby eq '';
foreach my $test (@{$pms->{test_names_hit}}) {
# ignore tests with 0 score in this scoreset,
@@ -355,18 +373,48 @@
eval {
$whitelist = Mail::SpamAssassin::AutoWhitelist->new($pms->{main});
- # check
- my $meanscore = $whitelist->check_address($_, $origip);
+ my $meanscore;
+ { # check
+ my $timer = $self->{main}->time_method("check_awl");
+ $meanscore = $whitelist->check_address($from, $origip, $signedby);
+ }
my $delta = 0;
+ my $signeravg;
+
+ ### commented out to avoid additional load on a SQL server for the time being;
+ ### the average score (reputation) is still there and is accessible offline
+ #
+ # if (defined $signedby) {
+ # my $timer = $self->{main}->time_method("check_awl_reput");
+ # $signeravg = $whitelist->check_signer_reputation($from, $signedby);
+ # }
- dbg("auto-whitelist: AWL active, pre-score: $pms->{score}, autolearn score: $awlpoints, ".
- "mean: ". ($meanscore || 'undef') .", IP: ". ($origip || 'undef'));
+ dbg("auto-whitelist: AWL active, pre-score: %s, autolearn score: %s, ".
+ "mean: %s%s, IP: %s, address: %s %s",
+ $pms->{score}, $awlpoints, $meanscore || 'undef',
+ !defined $signeravg ? '' : sprintf(", signer_avg: %.2f",$signeravg),
+ $origip || 'undef',
+ $from, $signedby ? "SIGNED by $signedby" : '(not signed)');
- if (defined ($meanscore)) {
- $delta = ($meanscore - $awlpoints) * $pms->{main}->{conf}->{auto_whitelist_factor};
+ if (defined $signeravg) {
+ $pms->set_tag('AWLSIGNERMEAN', sprintf("%2.1f", $signeravg));
+ }
+ if (defined $meanscore || defined $signeravg) {
+ my $past_avg;
+ if (defined $meanscore && defined $signeravg) {
+ $past_avg = ($meanscore + $signeravg) / 2;
+ } elsif (defined $meanscore) {
+ $past_avg = $meanscore;
+ } else {
+ $past_avg = $signeravg;
+ }
+ $delta = $past_avg - $awlpoints;
+ $delta *= $pms->{main}->{conf}->{auto_whitelist_factor};
$pms->set_tag('AWL', sprintf("%2.1f",$delta));
- $pms->set_tag('AWLMEAN', sprintf("%2.1f", $meanscore));
+ if (defined $meanscore) {
+ $pms->set_tag('AWLMEAN', sprintf("%2.1f", $meanscore));
+ }
$pms->set_tag('AWLCOUNT', sprintf("%2.1f", $whitelist->count()));
$pms->set_tag('AWLPRESCORE', sprintf("%2.1f", $pms->{score}));
}
@@ -375,6 +423,7 @@
# early high-scoring messages are reinforced compared to
# later ones. http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=159704
if (!$pms->{disable_auto_learning}) {
+ my $timer = $self->{main}->time_method("update_awl");
$whitelist->add_score($awlpoints);
}
@@ -397,7 +446,7 @@
return 0;
};
- dbg("auto-whitelist: post auto-whitelist score: ".$pms->{score});
+ dbg("auto-whitelist: post auto-whitelist score: %.3f", $pms->{score});
# test hit is above
return 0;
@@ -419,7 +468,7 @@
eval {
$whitelist = Mail::SpamAssassin::AutoWhitelist->new($self->{main});
- if ($whitelist->add_known_bad_address($args->{address})) {
+ if ($whitelist->add_known_bad_address($args->{address}, $args->{signedby})) {
print "SpamAssassin auto-whitelist: adding address to blacklist: " . $args->{address} . "\n";
$status = 0;
}
@@ -455,7 +504,7 @@
eval {
$whitelist = Mail::SpamAssassin::AutoWhitelist->new($self->{main});
- if ($whitelist->add_known_good_address($args->{address})) {
+ if ($whitelist->add_known_good_address($args->{address}, $args->{signedby})) {
print "SpamAssassin auto-whitelist: adding address to whitelist: " . $args->{address} . "\n";
$status = 1;
}
@@ -492,7 +541,7 @@
eval {
$whitelist = Mail::SpamAssassin::AutoWhitelist->new($self->{main});
- if ($whitelist->remove_address($args->{address})) {
+ if ($whitelist->remove_address($args->{address}, $args->{signedby})) {
print "SpamAssassin auto-whitelist: removing address: " . $args->{address} . "\n";
$status = 1;
}
Modified: spamassassin/trunk/lib/Mail/SpamAssassin/SQLBasedAddrList.pm
URL: http://svn.apache.org/viewvc/spamassassin/trunk/lib/Mail/SpamAssassin/SQLBasedAddrList.pm?rev=650852&r1=650851&r2=650852&view=diff
==============================================================================
--- spamassassin/trunk/lib/Mail/SpamAssassin/SQLBasedAddrList.pm (original)
+++ spamassassin/trunk/lib/Mail/SpamAssassin/SQLBasedAddrList.pm Wed Apr 23 05:53:18 2008
@@ -47,12 +47,26 @@
ip VARCHAR NOT NULL,
count INT NOT NULL,
totscore FLOAT NOT NULL,
- PRIMARY KEY (username, email, ip)
+ signedby VARCHAR(255) NOT NULL DEFAULT '',
+ PRIMARY KEY (username, email, signedby, ip)
)
Your table definition may change depending on which database driver
you choose. There is a config option to override the table name.
+To add a field 'signedby' into an existing table under MySQL
+and to modify a primary key, use:
+ ALTER TABLE awl
+ DROP PRIMARY KEY,
+ ADD signedby varchar(255) NOT NULL DEFAULT '',
+ ADD PRIMARY KEY (username,email,signedby,ip);
+
+To add a field 'signedby' into an existing table under PostgreSQL, use:
+ DROP INDEX awl_pkey;
+ ALTER TABLE awl
+ ADD signedby varchar(255) NOT NULL DEFAULT '',
+ ADD PRIMARY KEY (username,email,signedby,ip);
+
This module introduces several new config variables:
user_awl_dsn
@@ -159,6 +173,9 @@
$self->{_username} = "GLOBAL";
}
}
+ $self->{_with_awl_signer} =
+ $main->{conf}->{auto_whitelist_distinguish_signed};
+
dbg("auto-whitelist: sql-based using username: ".$self->{_username});
return bless ($self, $class);
@@ -166,13 +183,16 @@
=head2 get_addr_entry
-public instance (\%) get_addr_entry (String $addr)
+public instance (\%) get_addr_entry (String $addr, String $signedby)
Description:
This method takes a given C<$addr> and splits it between the email address
component and the ip component and performs a lookup in the database. If
nothing is found in the database then a blank entry hash is created and
returned, otherwise an entry containing the found information is returned.
+If a parameter $signedby is defined, only addresses signed by this signing
+identity are taken into account, if $signedby is undefined only addresses
+from unsigned mail are considered.
A key, C<exists_p>, is set to 1 if an entry already exists in the database,
otherwise it is set to 0.
@@ -180,22 +200,36 @@
=cut
sub get_addr_entry {
- my ($self, $addr) = @_;
+ my ($self, $addr, $signedby) = @_;
my $entry = { addr => $addr,
exists_p => 0,
count => 0,
totscore => 0,
};
+ $entry->{signedby} = $signedby if defined $signedby;
my ($email, $ip) = $self->_unpack_addr($addr);
return $entry unless ($email && $ip);
- my $sql = "SELECT count, totscore FROM $self->{tablename}
- WHERE username = ? AND email = ? AND ip = ?";
+ my $sql = "SELECT count, totscore FROM $self->{tablename} " .
+ "WHERE username = ? AND email = ?";
+ my @args = ($email);
+ if ($self->{_with_awl_signer} && defined $signedby && $signedby ne '') {
+ my @signedby = split(' ', lc $signedby);
+ if (@signedby == 1) {
+ $sql .= " AND signedby = ?";
+ } elsif (@signedby > 1) {
+ $sql .= " AND signedby IN (" . join(',', ('?') x @signedby) . ")";
+ }
+ push(@args, @signedby);
+ } elsif (defined $ip && $ip ne '') {
+ $sql .= " AND ip = ?";
+ push(@args, $ip);
+ }
my $sth = $self->{dbh}->prepare($sql);
- my $rc = $sth->execute($self->{_username}, $email, $ip);
+ my $rc = $sth->execute($self->{_username}, @args);
if (!$rc) { # there was an error, but try to go on
my $err = $self->{dbh}->errstr;
@@ -210,19 +244,58 @@
$entry->{count} = $aryref->[0] || 0;
$entry->{totscore} = $aryref->[1] || 0;
$entry->{exists_p} = 1;
- dbg("auto-whitelist: sql-based get_addr_entry: found existing entry for $addr");
+ dbg("auto-whitelist: sql-based get_addr_entry: found entry for %s",
+ join('|',@args));
}
else {
- dbg("auto-whitelist: sql-based get_addr_entry: no entry found for $addr");
+ dbg("auto-whitelist: sql-based get_addr_entry: no entry found for %s",
+ join('|',@args));
}
}
$sth->finish();
- dbg("auto-whitelist: sql-based $addr scores ".$entry->{count}.'/'.$entry->{totscore});
+ dbg("auto-whitelist: sql-based %s scores %s, count %s",
+ join('|',@args), $entry->{totscore}, $entry->{count});
return $entry;
}
+=head2 get_signer_reputation
+
+=cut
+
+sub get_signer_reputation {
+ my ($self, $addr, $signedby) = @_;
+
+ my $signer_avg_score;
+ if ($self->{_with_awl_signer} && defined $signedby && $signedby ne '') {
+ my $sql = "SELECT sum(totscore), sum(count) FROM awl";
+ my @signedby = split(' ', lc $signedby);
+ if (@signedby == 1) {
+ $sql .= " WHERE signedby = ?";
+ } elsif (@signedby > 1) {
+ $sql .= " WHERE signedby IN (" . join(',', ('?') x @signedby) . ")";
+ }
+ my $sth = $self->{dbh}->prepare($sql);
+ my $rc = $sth->execute(@signedby);
+ if (!$rc) { # there was an error, but try to go on
+ my $err = $self->{dbh}->errstr;
+ dbg("auto-whitelist: sql-based get_signer_reputation: SQL error: $err");
+ }
+ else {
+ my $aryref = $sth->fetchrow_arrayref();
+ my($totscore,$totcount) = !defined($aryref) ? (0,0) : @$aryref;
+ if (defined $totcount && $totcount > 0) {
+ $signer_avg_score = $totscore/$totcount;
+ dbg("auto-whitelist: sql-based signer avg score %.3f",
+ $signer_avg_score);
+ }
+ $sth->finish();
+ }
+ }
+ return $signer_avg_score;
+}
+
=head2 add_score
public instance (\%) add_score (\% $entry, Integer $score)
@@ -250,17 +323,30 @@
return $entry unless ($email && $ip);
+ my $signedby = $entry->{signedby};
if ($entry->{exists_p}) { # entry already exists, so just update
- my $sql = "UPDATE $self->{tablename} SET count = count + 1,
- totscore = totscore + ?
- WHERE username = ? AND email = ? AND ip = ?";
-
+ my(@args) = ($score, $self->{_username}, $email);
+ my $sql = "UPDATE $self->{tablename} ".
+ "SET count = count + 1, totscore = totscore + ? ".
+ "WHERE username = ? AND email = ?";
+ if ($self->{_with_awl_signer} && defined $signedby && $signedby ne '') {
+ my @signedby = split(' ', lc $signedby);
+ if (@signedby == 1) {
+ $sql .= " AND signedby = ?";
+ } elsif (@signedby > 1) {
+ $sql .= " AND signedby IN (" . join(',', ('?') x @signedby) . ")";
+ }
+ push(@args, @signedby);
+ } elsif (defined $ip && $ip ne '') {
+ $sql .= " AND ip = ?";
+ push(@args, $ip);
+ }
my $sth = $self->{dbh}->prepare($sql);
- my $rc = $sth->execute($score, $self->{_username}, $email, $ip);
+ my $rc = $sth->execute(@args);
if (!$rc) {
my $err = $self->{dbh}->errstr;
- dbg("auto-whitelist: sql-based add_score: SQL error: $err");
+ dbg("auto-whitelist: sql-based add_score/update: SQL error: $err");
}
else {
dbg("auto-whitelist: sql-based add_score: new count: ". $entry->{count} .", new totscore: ".$entry->{totscore}." for ".$entry->{addr});
@@ -268,12 +354,23 @@
$sth->finish();
}
else { # no entry yet, so insert a new entry
- my $sql = "INSERT INTO $self->{tablename} (username,email,ip,count,totscore) VALUES (?,?,?,?,?)";
+ my @fields = qw(username email ip count totscore);
+ my @signedby;
+ if ($self->{_with_awl_signer}) {
+ push(@fields, 'signedby');
+ @signedby = split(' ', lc $signedby) if defined $signedby;
+ }
+ @signedby = ( '' ) if !@signedby; # empty string indicates unsigned
+ my $sql = sprintf("INSERT INTO %s (%s) VALUES (%s)", $self->{tablename},
+ join(',', @fields), join(',', ('?') x @fields));
my $sth = $self->{dbh}->prepare($sql);
- my $rc = $sth->execute($self->{_username},$email,$ip,1,$score);
- if (!$rc) {
- my $err = $self->{dbh}->errstr;
- dbg("auto-whitelist: sql-based add_score: SQL error: $err");
+ my @args = ($self->{_username},$email,$ip,1,$score);
+ for my $s (@signedby) { # contains at least one element, possibly ''
+ my $rc = $sth->execute(@args, (@fields > @args ? $s : ()) );
+ if (!$rc) {
+ my $err = $self->{dbh}->errstr;
+ dbg("auto-whitelist: sql-based add_score/insert: SQL error: $err");
+ }
}
$entry->{exists_p} = 1;
dbg("auto-whitelist: sql-based add_score: created new entry for ".$entry->{addr}." with totscore: $score");
@@ -313,6 +410,17 @@
$sql .= " AND ip = ?";
push(@args, $ip);
dbg("auto-whitelist: sql-based remove_entry: removing single entry matching ".$entry->{addr});
+ }
+ # if a key 'signedby' exists in the $entry, be selective on its value too
+ my $signedby = $entry->{signedby};
+ if ($self->{_with_awl_signer} && defined $signedby && $signedby ne '') {
+ my @signedby = split(' ', lc $signedby);
+ if (@signedby == 1) {
+ $sql .= " AND signedby = ?";
+ } elsif (@signedby > 1) {
+ $sql .= " AND signedby IN (" . join(',', ('?') x @signedby) . ")";
+ }
+ push(@args, @signedby);
}
my $sth = $self->{dbh}->prepare($sql);
Modified: spamassassin/trunk/sql/README.awl
URL: http://svn.apache.org/viewvc/spamassassin/trunk/sql/README.awl?rev=650852&r1=650851&r2=650852&view=diff
==============================================================================
--- spamassassin/trunk/sql/README.awl (original)
+++ spamassassin/trunk/sql/README.awl Wed Apr 23 05:53:18 2008
@@ -77,9 +77,21 @@
ip varchar(10) # this is the ip key
count int(11) # this is the message counter
totscore float # this is the total calculated score
+ signedby varchar(255) # a DKIM or DomainKeys signing id
-You can add as many other fields you wish as long as the above fields
-are contained in the table.
+You can add as many other fields you wish as long as the above fields are
+contained in the table.
+
+The 'signedby' field was introduced in version 3.3 and is only needed
+if auto_whitelist_distinguish_signed is true, e.g. (in local.cf):
+ auto_whitelist_distinguish_signed 1
+and is only useful if a plugin DKIM is enabled. If the setting is off
+the field is not used, but it does no harm to have it in a table.
+The new field makes AWL keep separate records for author addresses with
+valid DKIM or DomainKeys signatures, and separate records for unsigned mail,
+which does a good job for popular domains such as gmail.com and yahoo.com
+where most of the spam claiming to be from such domain does not come from
+a freemail provider and therefore can not carry a valid signature.
Included is a default table that can be safely used in your own setup. To use
the default table, you must first create a database, and a username/password
@@ -95,16 +107,34 @@
CREATE TABLE awl (
username varchar(100) NOT NULL default '',
- email varchar(200) NOT NULL default '',
+ email varchar(255) NOT NULL default '',
ip varchar(10) NOT NULL default '',
- count int(11) default '0',
- totscore float default '0',
- PRIMARY KEY (username,email,ip)
+ count int(11) NOT NULL default '0',
+ totscore float NOT NULL default '0',
+ signedby varchar(255) NOT NULL default '',
+ PRIMARY KEY (username,email,signedby,ip)
) TYPE=MyISAM;
For PostgreSQL, use the following:
psql -U <username> -f awl_pg.sql <databasename>
+
+
+To add a field 'signedby' to an existing table and to modify a primary key:
+under MySQL:
+ ALTER TABLE awl
+ DROP PRIMARY KEY,
+ ADD signedby varchar(255) NOT NULL DEFAULT '',
+ ADD PRIMARY KEY (username,email,signedby,ip);
+under PostgreSQL:
+ DROP INDEX awl_pkey;
+ ALTER TABLE awl
+ ADD signedby varchar(255) NOT NULL DEFAULT '',
+ ADD PRIMARY KEY (username,email,signedby,ip);
+then add the following to local.cf to let SpamAssassin start using the
+newly added field 'signedby' :
+ auto_whitelist_distinguish_signed 1
+
Once you have created the database and added the table, just add the required
lines to your global configuration file (local.cf). Note that you
Modified: spamassassin/trunk/sql/awl_mysql.sql
URL: http://svn.apache.org/viewvc/spamassassin/trunk/sql/awl_mysql.sql?rev=650852&r1=650851&r2=650852&view=diff
==============================================================================
--- spamassassin/trunk/sql/awl_mysql.sql (original)
+++ spamassassin/trunk/sql/awl_mysql.sql Wed Apr 23 05:53:18 2008
@@ -1,8 +1,9 @@
CREATE TABLE awl (
username varchar(100) NOT NULL default '',
- email varchar(200) NOT NULL default '',
+ email varchar(255) NOT NULL default '',
ip varchar(10) NOT NULL default '',
- count int(11) default '0',
- totscore float default '0',
- PRIMARY KEY (username,email,ip)
+ count int(11) NOT NULL default '0',
+ totscore float NOT NULL default '0',
+ signedby varchar(255) NOT NULL default '',
+ PRIMARY KEY (username,email,signedby,ip)
) TYPE=MyISAM;
Modified: spamassassin/trunk/sql/awl_pg.sql
URL: http://svn.apache.org/viewvc/spamassassin/trunk/sql/awl_pg.sql?rev=650852&r1=650851&r2=650852&view=diff
==============================================================================
--- spamassassin/trunk/sql/awl_pg.sql (original)
+++ spamassassin/trunk/sql/awl_pg.sql Wed Apr 23 05:53:18 2008
@@ -2,7 +2,8 @@
username varchar(100) NOT NULL default '',
email varchar(200) NOT NULL default '',
ip varchar(10) NOT NULL default '',
- count bigint default '0',
- totscore float default '0'
+ count bigint NOT NULL default '0',
+ totscore float NOT NULL default '0',
+ signedby varchar(255) NOT NULL default '',
+ PRIMARY KEY (username,email,signedby,ip)
);
-CREATE UNIQUE INDEX awl_pkey ON awl (username,email,ip);