You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@spamassassin.apache.org by Erik Wasser <er...@iquer.net> on 2004/02/24 21:49:23 UTC
Interest in a patch for Mail/SpamAssassin/ConfSourceSQL.pm for enabling a per-user config with mysql?
Hi SA friends,
today I turned on the "MySQL-connection" of SA so I can save the config
in a mysql database described at
"http://www.spamassassin.org/full/2.6x/dist/sql/README". If was
satisfied with the results, but one little thing scratched my back. B-)
There a global and a per user config like this example:
+--------+-------------+-----------------+-------+
| prefid | username | preference | value |
+--------+-------------+-----------------+-------+
| 1 | GLOBAL | rewrite_subject | 0 |
| 2 | foo@bar.com | rewrite_subject | 1 |
+--------+-------------+-----------------+-------+
The subjects will be *not* rewritten except for the user foo@bar.com. So
far so good: but I want a per site config (one setting for a whole
domain) too, so I extended the table a little bit:
+--------+-------------+-----------------+-------+ The 'prefid'
| prefid | username | preference | value | is not
+--------+-------------+-----------------+-------+ important
| 1 | GLOBAL | rewrite_subject | 0 | for the
| 2 | foo@bar.com | rewrite_subject | 0 | ordering!
| 3 | bar.com | rewrite_subject | 1 |
+--------+-------------+-----------------+-------+
Mails to "foo@bar.com" are not(!) marked.
Mails to other members of bar.com are marked.
All other's are not marked. Got it?
1) global (least important)
2) per site
3) per user (most important)
Advantages:
- backward compabtible to the "@GLOBAL" and "GLOBAL" settings.
- only changes made in "Mail/SpamAssassin/ConfSourceSQL.pm"
- no changes in calling the method 'load_with_dbi'
- the modified SQL query is fully ANSI compatible (I hope so. B-)
No weird MySQL stuff in here.
Disadvantages:
- The sorting of the parameter of the query is now done by perl
My Questions:
- Is anybody interessested in the patch?
- Is there any chance that this patch goes in the offical release? I
would like it!
Result:
- Please drop a mail if you are interessested I don't wan't to polute
the ML more than it's needed.
Feedback is welcome.
BTW: Thanks to the SA maintainers for the great work. You guys saved my
inbox. B-)
P.S: I used SA 2.60 for the work but there is no change in the file
"Mail/SpamAssassin/ConfSourceSQL.pm" between 2.60 and 2.63.
--
So long... Fuzz
Re: trouble getting mysql prefs to work
Posted by Josh Endries <je...@pragmeta.com>.
On Tuesday 24 February 2004 16:48, Josh Endries wrote:
> I'm trying to get SA to mingle with MySQL for user preferences and it
> isn't working at all. I followed the directions at
Ha, score one for Captain Obvious. I forgot to use -qx when starting spamd.
It's working now. Sorry!
Josh
trouble getting mysql prefs to work
Posted by Josh Endries <je...@pragmeta.com>.
Hello list,
I'm trying to get SA to mingle with MySQL for user preferences and it isn't
working at all. I followed the directions at
<http://www.spamassassin.org/full/2.6x/dist/sql/README>, have DBI 1.37
installed, Msql-Mysql-modules-1.2219, and SA 2.63, on FreeBSD 4.9. I've
tried ports and normal versions of SA, as well as DBI/Mysql-modules.
My /usr/local/etc/mail/spamassassin/local.cf is simply:
required_hits 10.0
user_scores_dsn DBI:mysql:mail:192.168.0.17:3306
user_scores_sql_username mail
user_scores_sql_password mail
user_scores_sql_table spamassassin_prefs
allow_user_rules 1
I've tried a bunch of different options in there. The required_hits gets
through so this file is being read, but my MySQL tables aren't. They're on
another host in a table:
mysql> describe spamassassin_prefs;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| username | varchar(100) | | MUL | | |
| preference | varchar(30) | | | | |
| value | varchar(100) | | | | |
| prefid | int(11) | | PRI | NULL | auto_increment |
+------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from spamassassin_prefs;
+-------------------+---------------+-------+--------+
| username | preference | value | prefid |
+-------------------+---------------+-------+--------+
| jendries | required_hits | 15.0 | 1 |
+-------------------+---------------+-------+--------+
1 row in set (0.01 sec)
Now when I start up spamd -D, it doesn't say anything about SQL
preferences. Okay, no problem since it isn't scanning anything, but when I
run spamc, still nothing happens:
logmsg: connection from localhost [127.0.0.1] at port 2645
logmsg: info: setuid to jendries succeeded
debug: read_scoreonly_config: cannot open
"/home/jendries/.spamassassin/user_prefs": No such file or directory
debug: user has changed
debug: bayes: 96083 untie-ing
debug: bayes: no dbs present, cannot
scan: /home/jendries/.spamassassin/bayes_toks
debug: Score set 1 chosen.
logmsg: processing message <GT...@example.net> for jendries:1001.
debug: bayes: no dbs present, cannot
scan: /home/jendries/.spamassassin/bayes_toks
debug: is Net::DNS::Resolver available? yes
debug: all '*From' addrs: sender@example.net
debug: running header regexp tests; score so far=0
debug: running body-text per-line regexp tests; score so far=0
debug: Razor2 is available
debug: entering helper-app run mode
Razor-Log: Computed razorhome from env: /root/.razor
Razor-Log: Found razorhome: /root/.razor, however, can't write to it.
Razor-Log: No /root/.razor/razor-agent.conf found, skipping.
Razor-Log: No razor-agent.conf found, using defaults.
Feb 24 16:41:09.885297 check[96083]: [ 1] [bootup] Logging initiated
LogDebugLevel=9 to stdout
Feb 24 16:41:09.887277 check[96083]: [ 5] computed razorhome=/root/.razor,
conf=, ident=/root/.razor/identity
Feb 24 16:41:09.888648 check[96083]: [ 8] Client supported_engines: 1 2 3 4
Feb 24 16:41:09.891527 check[96083]: [ 8] prep_mail done: mail 1
headers=293, mime0=616
Feb 24 16:41:09.893318 check[96083]: [ 5] read_file: 1 items read
from /root/.razor/servers.discovery.lst
Feb 24 16:41:09.894582 check[96083]: [ 5] read_file: 2 items read
from /root/.razor/servers.nomination.lst
Feb 24 16:41:09.895634 check[96083]: [ 5] read_file: 1 items read
from /root/.razor/servers.catalogue.lst
Feb 24 16:41:09.897250 check[96083]: [ 9] Assigning defaults to
joy.cloudmark.com
Feb 24 16:41:09.898287 check[96083]: [ 9] Assigning defaults to
folly.cloudmark.com
Feb 24 16:41:09.899305 check[96083]: [ 9] Assigning defaults to
pride.cloudmark.com
Feb 24 16:41:09.901646 check[96083]: [ 5] read_file: 13 items read
from /root/.razor/server.pride.cloudmark.com.conf
Feb 24 16:41:09.903623 check[96083]: [ 5] read_file: 13 items read
from /root/.razor/server.pride.cloudmark.com.conf
Feb 24 16:41:09.904832 check[96083]: [ 5] 147047 seconds before closest
server discovery
Feb 24 16:41:09.905814 check[96083]: [ 6] pride.cloudmark.com is a
Catalogue Server srl 248; computed min_cf=6, Server se: D8
Feb 24 16:41:09.906890 check[96083]: [ 8] Computed supported_engines: 4
Feb 24 16:41:09.907767 check[96083]: [ 8] Using next closest server
pride.cloudmark.com:2703, cached info srl 248
Feb 24 16:41:09.908522 check[96083]: [ 8] mail 1 has no subject
Feb 24 16:41:09.912809 check[96083]: [ 6] preproc: mail 1.0 went from 616
bytes to 503
Feb 24 16:41:09.913632 check[96083]: [ 6] computing sigs for mail 1.0, len
503
Feb 24 16:41:09.918775 check[96083]: [ 6] skipping whitelist file
(empty?): /root/.razor/razor-whitelist
Feb 24 16:41:09.919797 check[96083]: [ 5] Connecting to
pride.cloudmark.com ...
Feb 24 16:41:13.174985 check[96083]: [ 8] Connection established
Feb 24 16:41:13.175720 check[96083]: [ 4] pride.cloudmark.com >> 35 server
greeting: sn=C&srl=248&a=l&a=cg&ep4=7542-10
Feb 24 16:41:13.177428 check[96083]: [ 4] pride.cloudmark.com << 25
Feb 24 16:41:13.178023 check[96083]: [ 6] cn=razor-agents&cv=2.36
Feb 24 16:41:13.179028 check[96083]: [ 6] pride.cloudmark.com is a
Catalogue Server srl 248; computed min_cf=6, Server se: D8
Feb 24 16:41:13.179964 check[96083]: [ 8] Computed supported_engines: 4
Feb 24 16:41:13.180935 check[96083]: [ 8] mail 1.0 e4 sig:
maD-J2LHO2hbHzZrbOXcsZ6ndsoA
Feb 24 16:41:13.182070 check[96083]: [ 8] preparing 1 queries
Feb 24 16:41:13.183213 check[96083]: [ 8] sending 1 batches
Feb 24 16:41:13.184078 check[96083]: [ 4] pride.cloudmark.com << 52
Feb 24 16:41:13.184619 check[96083]: [ 6]
a=c&e=4&ep4=7542-10&s=maD-J2LHO2hbHzZrbOXcsZ6ndsoA
Feb 24 16:41:13.476148 check[96083]: [ 4] pride.cloudmark.com >> 12
Feb 24 16:41:13.476737 check[96083]: [ 6] response to sent.2
p=1&cf=100
Feb 24 16:41:13.478764 check[96083]: [ 6] mail 1.0 e=4
sig=maD-J2LHO2hbHzZrbOXcsZ6ndsoA: Is spam: cf 100 >= min_cf 6
Feb 24 16:41:13.479548 check[96083]: [ 7] method 4: mail 1.0: no-contention
part, spam=1
Feb 24 16:41:13.480115 check[96083]: [ 7] method 4: mail 1: a
non-contention part was spam, mail spam
Feb 24 16:41:13.480661 check[96083]: [ 3] mail 1 is known spam.
Feb 24 16:41:13.481248 check[96083]: [ 5] disconnecting from server
pride.cloudmark.com
Feb 24 16:41:13.482107 check[96083]: [ 4] pride.cloudmark.com << 5
Feb 24 16:41:13.482656 check[96083]: [ 6] a=q
debug: Using results from Razor v2.36
debug: Found Razor2 part: part=0 engine=4 ct=0 cf=100
debug: leaving helper-app run mode
debug: Razor2 results: spam? 1 highest cf score: 100
debug: running raw-body-text per-line regexp tests; score so far=1.552
debug: running uri tests; score so far=1.552
debug: uri tests: Done uriRE
debug: running full-text regexp tests; score so far=1.552
debug: Pyzor is not available: pyzor not found
debug: Razor2 is available
debug: DCCifd is not available: no r/w dccifd socket found.
debug: DCC is not available: no executable dccproc found.
debug: all '*To' addrs: recipient@example.net
debug: DNS MX records found: 0
debug: DNS A records found: 1
debug: RBL: success for 1 of 1 queries
debug: running meta tests; score so far=3.84
debug: auto-learn? ham=0.1, spam=12, body-hits=3.84, head-hits=2.288
debug: auto-learn: currently using scoreset 1. no need to recompute.
debug: auto-learn? no: inside auto-learn thresholds
debug: is spam? score=1003.84 required=10
tests=DNS_FROM_RFCI_DSN,GTUBE,RAZOR2_CF_RANGE_51_100,RAZOR2_CHECK
logmsg: identified spam (1003.8/10.0) for jendries:1001 in 4.0 seconds, 799
bytes.
Do I have to do something special to get MySQL stuff into SA? I just ran
perl Makefile.PL;make;make install to install SA and didn't see
anything...and the Mysql/DBI stuff seemed to install fine. This will host
vhosted stuff so I can't/won't depend on home directories and
whatnot...hence my trying to get this set up. Roar!
Thanks,
Josh
Re: Interest in a patch for Mail/SpamAssassin/ConfSourceSQL.pm for enabling a per-user config with mysql?
Posted by Michael Parker <pa...@pobox.com>.
On Tue, Feb 24, 2004 at 02:31:08PM -0800, Justin Mason wrote:
>
> The problems we've had with ConfSourceSQL is that it's effectively
> unmaintained, and the dev team didn't have enough SQL experience
> to know what's likely to work portably etc. However now that
> we have Michael Parker on board he might be able to review these ;)
> Michael?
>
I'm planning to pick this up and work on it. The original itch behind
SQL based bayes and awl was me moving to ConfSourceSQL. Now that I've
finished that part, I can move on :)
Michael
Re: Interest in a patch for Mail/SpamAssassin/ConfSourceSQL.pm for enabling a per-user config with mysql?
Posted by Michael Parker <pa...@pobox.com>.
On Tue, Feb 24, 2004 at 02:31:08PM -0800, Justin Mason wrote:
>
> The problems we've had with ConfSourceSQL is that it's effectively
> unmaintained, and the dev team didn't have enough SQL experience
> to know what's likely to work portably etc. However now that
> we have Michael Parker on board he might be able to review these ;)
> Michael?
>
I'm planning to pick this up and work on it. The original itch behind
SQL based bayes and awl was me moving to ConfSourceSQL. Now that I've
finished that part, I can move on :)
Michael
Re: Interest in a patch for Mail/SpamAssassin/ConfSourceSQL.pm for enabling a per-user config with mysql?
Posted by Erik Wasser <er...@iquer.net>.
On Tuesday 24 February 2004 23:11, Adam Denenberg wrote:
> my $sql = "select $f_preference, $f_value from $f_table where ".
> " $f_username = 'GLOBAL' union ".
> " select $f_preference, $f_value from $f_table where ".
> " $f_username = '\@GLOBAL' union ".
> " select $f_preference, $f_value from $f_table where ".
> " $f_username = ".$dbh->quote($domain). " union " .
> " select $f_preference, $f_value from $f_table where ".
> " $f_username = ".$dbh->quote($username);
I'm not so familiar with SQL so I choose to do the sorting in perl. B-)
--
So long... Fuzz
Re: Interest in a patch for Mail/SpamAssassin/ConfSourceSQL.pm for
enabling a per-user config with mysql?
Posted by Adam Denenberg <ad...@sa.dberg.org>.
Erik,
Inside ConfSourceSql.pm just do 2 things
1) pull out the domain like
my ($local, $domain) = split(/\@/, $username);
2) then re-order the select query .. i used unions, there may be a
better way
my $sql = "select $f_preference, $f_value from $f_table where ".
" $f_username = 'GLOBAL' union ".
" select $f_preference, $f_value from $f_table where ".
" $f_username = '\@GLOBAL' union ".
" select $f_preference, $f_value from $f_table where ".
" $f_username = ".$dbh->quote($domain). " union " .
" select $f_preference, $f_value from $f_table where ".
" $f_username = ".$dbh->quote($username);
this will order the preferences, with the more specific ones overriding
the previous ones. If no domain or user pref is found, the gloabal will
be used.. and so on.
SA will use the last one found so this will probably do what you
want.. it seems to work for us.
:adam
On Tue, 2004-02-24 at 15:49, Erik Wasser wrote:
> Hi SA friends,
>
> today I turned on the "MySQL-connection" of SA so I can save the config
> in a mysql database described at
> "http://www.spamassassin.org/full/2.6x/dist/sql/README". If was
> satisfied with the results, but one little thing scratched my back. B-)
>
> There a global and a per user config like this example:
>
> +--------+-------------+-----------------+-------+
> | prefid | username | preference | value |
> +--------+-------------+-----------------+-------+
> | 1 | GLOBAL | rewrite_subject | 0 |
> | 2 | foo@bar.com | rewrite_subject | 1 |
> +--------+-------------+-----------------+-------+
>
> The subjects will be *not* rewritten except for the user foo@bar.com. So
> far so good: but I want a per site config (one setting for a whole
> domain) too, so I extended the table a little bit:
>
> +--------+-------------+-----------------+-------+ The 'prefid'
> | prefid | username | preference | value | is not
> +--------+-------------+-----------------+-------+ important
> | 1 | GLOBAL | rewrite_subject | 0 | for the
> | 2 | foo@bar.com | rewrite_subject | 0 | ordering!
> | 3 | bar.com | rewrite_subject | 1 |
> +--------+-------------+-----------------+-------+
>
> Mails to "foo@bar.com" are not(!) marked.
> Mails to other members of bar.com are marked.
> All other's are not marked. Got it?
>
> 1) global (least important)
> 2) per site
> 3) per user (most important)
>
> Advantages:
> - backward compabtible to the "@GLOBAL" and "GLOBAL" settings.
> - only changes made in "Mail/SpamAssassin/ConfSourceSQL.pm"
> - no changes in calling the method 'load_with_dbi'
> - the modified SQL query is fully ANSI compatible (I hope so. B-)
> No weird MySQL stuff in here.
>
> Disadvantages:
> - The sorting of the parameter of the query is now done by perl
>
> My Questions:
>
> - Is anybody interessested in the patch?
> - Is there any chance that this patch goes in the offical release? I
> would like it!
>
> Result:
>
> - Please drop a mail if you are interessested I don't wan't to polute
> the ML more than it's needed.
>
> Feedback is welcome.
>
> BTW: Thanks to the SA maintainers for the great work. You guys saved my
> inbox. B-)
>
> P.S: I used SA 2.60 for the work but there is no change in the file
> "Mail/SpamAssassin/ConfSourceSQL.pm" between 2.60 and 2.63.