-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Dallas -- any chance you could put that in a wiki page? It
sounds like a really useful SQL tip.
- --j.
Dallas L. Engelken writes:
> >
> > I was searching bugzilla on SpamAssassin because I'm looking
> > for a per-domain patch for SA 2.63 - so I can set up
> > individual preferences for each virtual domain hosted on my
> > vpopmail system (also using qmail-scanner). I noticed the
> > entry #1079 - and wonder whether anyone has a working setup -
> > and if they could reveal a bit more about the needed SQL
> > entries and SA configuration, and maybe tell me whether the
> > patch attached to #1079 is working with SA 2.63
> >
>
> FYI, I use a sql_custom_query to achieve this now in 3.0. In 2.63, I
> just patched ConfSourceSQL.pm
>
> user_scores_sql_custom_query SELECT preference, value FROM
> sa_rules WHERE username = '$GLOBAL' OR username = CONCAT('%',_DOMAIN_)
> OR username = _USERNAME_ ORDER BY username ASC
>
> The reason I do it this way, as opposed to the standard vpopmail way
> (@GLOBAL), is so the preferences in my user_pref (sa_rules) database
> order correctly...
>
> Here is what the query produces...
>
> mysql> select username,preference,value from sa_rules WHERE
> (username='$GLOBAL' OR username='%nmgi.com' OR
> username='[EMAIL PROTECTED]') ORDER by username ASC;
> +------------------+-------------------------+------------------------+
> | username | preference | value |
> +------------------+-------------------------+------------------------+
> | $GLOBAL | required_hits | 4.00 |
> | $GLOBAL | subject_tag | [SPAM-_HITS_]- |
> | $GLOBAL | score USER_IN_WHITELIST | -10 |
> | $GLOBAL | whitelist_from | [EMAIL PROTECTED] |
> | $GLOBAL | score USER_IN_BLACKLIST | 10 |
> | $GLOBAL | report_safe | 0 |
> | $GLOBAL | use_razor2 | 1 |
> | $GLOBAL | use_pyzor | 1 |
> | $GLOBAL | use_dcc | 1 |
> | $GLOBAL | skip_rbl_checks | 1 |
> | $GLOBAL | use_bayes | 1 |
> | $GLOBAL | ok_locales | en |
> | $GLOBAL | ok_languages | en |
> | $GLOBAL | whitelist_from | [EMAIL PROTECTED]
> |
> | $GLOBAL | use_auto_whitelist | 1 |
> | $GLOBAL | rewrite_header Subject | [SPAM-_HITS_]- |
> | %nmgi.com | use_bayes | 1 |
> | %nmgi.com | whitelist_from | [EMAIL PROTECTED]
> |
> | %nmgi.com | score USER_IN_WHITELIST | -10 |
> | %nmgi.com | score USER_IN_BLACKLIST | 10 |
> | %nmgi.com | ok_locales | en |
> | %nmgi.com | ok_languages | en |
> | %nmgi.com | use_razor2 | 1 |
> | %nmgi.com | use_pyzor | 1 |
> | %nmgi.com | use_dcc | 1 |
> | %nmgi.com | skip_rbl_checks | 1 |
> | %nmgi.com | report_safe | 0 |
> | %nmgi.com | required_hits | 5 |
> | %nmgi.com | quarantine_email | [EMAIL PROTECTED] |
> | [EMAIL PROTECTED] | use_razor2 | 1 |
> | [EMAIL PROTECTED] | use_bayes | 1 |
> | [EMAIL PROTECTED] | use_pyzor | 1 |
> | [EMAIL PROTECTED] | use_dcc | 1 |
> | [EMAIL PROTECTED] | skip_rbl_checks | 0 |
> | [EMAIL PROTECTED] | whitelist_from | [EMAIL PROTECTED] |
> | [EMAIL PROTECTED] | ok_languages | en |
> | [EMAIL PROTECTED] | ok_locales | en |
> | [EMAIL PROTECTED] | score USER_IN_WHITELIST | -10 |
> | [EMAIL PROTECTED] | score USER_IN_BLACKLIST | 10 |
> | [EMAIL PROTECTED] | required_hits | 4.10 |
> | [EMAIL PROTECTED] | report_safe | 0 |
> | [EMAIL PROTECTED] | use_auto_whitelist | 1 |
> | [EMAIL PROTECTED] | rewrite_header Subject | [SPAM-_HITS_]- |
> +------------------+-------------------------+------------------------+
>
> Doing it this way guarantees proper sorting of prefs, so the last
> required_hits found would be that of the user if defined, or that of the
> domain if defined... And if neither exist, it will assume global
> required_hits.
>
> Obviously, you'd need to rewrite a couple lines in your web
> administration package that allows users to modify their SQL prefs...
>
> Dallas
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)
Comment: Exmh CVS
iD8DBQFA4bjgQTcbUG5Y7woRAqLFAJwI/C1caFr55mPZGpt/CMfRx5lrwACZAcHw
kTHEExG3OX1y2TazIYl8C6Y=
=iDzB
-----END PGP SIGNATURE-----
