Hello,
I posted this on the users list and got no responses, so here goes. I've
patched SQL.pm to generate more verbose output
when debug is on in an attempt to isolate the problem. Using the patched
SQL.pm, it has read the preferences and values
from the db, but doesn't use them. I believe I have started spamd with the
proper options, but please let me know if
I've missed something.
Thanks in advance.
OS: RHES 3.0
SA 3.1.0
MySQL 4.1.12
spamd start (for testing): spamd -q -D -x -p 6666
in another ssh session: echo -e "From: user\nTo:user\nSubject: Test\n\n" |
spamc -u 'dale' -p 6666
This way I can test sql userprefs without affecting the running spamd listening
on 783.
Here is the output from the spamc side:
=====
From: user
To:user
Subject: Test
=====
Note that there is no indication of any scanning taking place.
Here is the output from the spamd side:
=====
[29347] info: spamd: connection from frodo [127.0.0.1] at port 53771
[29347] dbg: config: Conf::SQL: database connection established
[29347] dbg: config: Conf::SQL: using deliverd query
[29347] dbg: config: Conf::SQL: preparing SQL: select preference, value from
userpref where username = 'dale' or
username = '$GLOBAL' order by username asc
[29347] dbg: config: Conf::SQL: executing SQL: select preference, value from
userpref where username = 'dale' or
username = '$GLOBAL' order by username asc
[29347] dbg: config: Conf::SQL: retrieving prefs for dale from SQL server
[29347] dbg: config: Conf::SQL: retrieved 12 rows from SQL server
[29347] dbg: config: Conf::SQL: fetched row: score SPF_FAIL 5
[29347] dbg: config: Conf::SQL: fetched row: score UNPARSEABLE_RELAY 3
[29347] dbg: config: Conf::SQL: fetched row: score RCVD_ILLEGAL_IP 4
[29347] dbg: config: Conf::SQL: fetched row: score SPF_HELO_FAIL 5
[29347] dbg: config: Conf::SQL: fetched row: required_score 5
[29347] dbg: config: Conf::SQL: fetched row: rewrite_header Subject ***
Potential SPAM (_SCORE_) ***
[29347] dbg: config: Conf::SQL: fetched row: whitelist_from [EMAIL PROTECTED]
[29347] dbg: config: Conf::SQL: fetched row: whitelist_from [EMAIL PROTECTED]
[29347] dbg: config: Conf::SQL: fetched row: whitelist_from [EMAIL PROTECTED]
[29347] dbg: config: Conf::SQL: fetched row: required_score 6
[29347] dbg: config: Conf::SQL: fetched row: whitelist_from [EMAIL PROTECTED]
[29347] dbg: config: Conf::SQL: fetched row: report_safe 1
[29335] dbg: prefork: new lowest idle kid: 29478
[29335] info: spamd: handled cleanup of child pid 29347 due to SIGCHLD
[29335] dbg: prefork: child closed connection
[29335] info: prefork: child states: I
[29616] dbg: prefork: sysread(8) not ready, wait max 300 secs
[29335] info: spamd: server successfully spawned child process, pid 29616
[29335] dbg: prefork: child 29616: entering state 0
[29335] dbg: prefork: new lowest idle kid: 29478
[29335] dbg: prefork: child 29616: entering state 1
[29335] dbg: prefork: new lowest idle kid: 29478
[29335] dbg: prefork: child reports idle
[29335] info: prefork: child states: II
=====
To get this debugging output, I patched SQL.pm as follows:
--- SQL.pm.org 2005-11-20 12:39:22.000000000 -0600
+++ SQL.pm 2005-11-20 12:45:06.000000000 -0600
@@ -125,8 +125,10 @@
my $dbh = DBI->connect($dsn, $dbuser, $dbpass, {'PrintError' => 0});
if ($dbh) {
+ dbg("config: Conf::SQL: database connection established");
my $sql;
if (defined($custom_query)) {
+ dbg("config: Conf::SQL: custom query defined");
$sql = $custom_query;
my $quoted_username = $dbh->quote($username);
my ($mailbox, $domain) = split('@', $username);
@@ -139,19 +141,24 @@
$sql =~ s/_DOMAIN_/$quoted_domain/g;
}
else {
+ dbg("config: Conf::SQL: using deliverd query");
$sql = "select $f_preference, $f_value from $f_table where ".
"$f_username = ".$dbh->quote($username).
- " or $f_username = '[EMAIL PROTECTED]' order by $f_username asc";
+ " or $f_username = '\$GLOBAL' order by $f_username asc";
}
- dbg("config: Conf::SQL: executing SQL: $sql");
+ dbg("config: Conf::SQL: preparing SQL: $sql");
my $sth = $dbh->prepare($sql);
if ($sth) {
+ dbg("config: Conf::SQL: executing SQL: $sql");
my $rv = $sth->execute();
if ($rv) {
- dbg("config: retrieving prefs for $username from SQL server");
+ dbg("config: Conf::SQL: retrieving prefs for $username from SQL
server");
+ my $numrows = $sth->rows;
+ dbg("config: Conf::SQL: retrieved $numrows rows from SQL server");
my @row;
my $text = '';
while (@row = $sth->fetchrow_array()) {
+ dbg("config: Conf::SQL: fetched row: $row[0] $row[1]");
$text .= (defined($row[0]) ? $row[0] : '') . "\t" .
(defined($row[1]) ? $row[1] : '') . "\n";
}
Thanks in advance for any suggestions.
--
Dale Morin, Mustang Internet Services, Inc.
"Support Without Compromise"
main office: 847.541.2811
direct line: 815.496.9853
email: [EMAIL PROTECTED]
