#45 _*_destiny setttings do not work when sql_select_policy

closed
nobody
None
5
2005-01-05
2004-09-06
tengin
No

Currently I have amavis configured to work in conjuction with a
helper milter off of sendmail which allow for virus and spam
checks.  Within the amavis.conf file you can specify what to do
with the mail if it is identified as spam or virus.  What I would like
to do is reject the mail and put in into quarantine.  For some
reason it will not reject mail in conjunction with the mysql policy
configuration.

Whenever I configure amavis.conf to work with a mysql database
to allow for individual policy definitions it does not work in
conjunction with the *_destiny settings.  I would like to D_REJECT
all mail that is considered spam but store it in quarantine as well
as allow for individual policy definitions via mysql. Here is a
snippit of the config:

----------------------amavis.conf--------------------------

#*_destiny config
$final_virus_destiny      = D_REJECT;  # (defaults to D_BOUNCE)
$final_banned_destiny     = D_REJECT;  # (defaults to D_BOUNCE)
$final_spam_destiny       = D_REJECT;  # (defaults to D_REJECT)
$final_bad_header_destiny = D_REJECT;  # (defaults to D_PASS),
D_BOUNCE suggested
#SQL Config
@lookup_sql_dsn =( ['DBI:mysql:database=amavis;
host=localhost', '****', '*****'] );
$sql_select_policy = 'SELECT *,users.id FROM users,policy'.
     ' WHERE (users.policy_id=policy.id) AND (users.email IN
(%k))'.
     ' ORDER BY users.priority DESC';
#$sql_select_policy = undef;
$sql_select_white_black_list = 'SELECT wb FROM wblist'.
  ' WHERE (rid=?) AND (wblist.email IN (%k))'.
  ' ORDER BY wblist.priority DESC'; # undef disables SQL white/
blacklisting

The *_destiny works when I declare $sql_select_policy = undef but
then the black/white list query does not.  The *_destiny settings
also works when I disable lookup_sql_dsn entirely (which b-passes
the mysql database for indiv policies and white/black list
completely).  

When mysql is enabled it seems like all of the queries work as
configured but the *_destiny settings are ignored.  

I installed the following packages as plugins of squirrelmail 1.4.3

amavis-new rpm package - 20030616-9
amavisnewsql-0.7.2-1.4
Here is the mysql schema I used in conjunction with the queires
stated above:

----------------------------
mysql.schemas------------------------------------

CREATE TABLE msg (
  id int(10) unsigned NOT NULL auto_increment,
  stype varchar(8) default 'spam',
  sender varchar(255) default NULL,
  subject varchar(255) default NULL,
  body mediumtext,
  storetime int(11) default NULL,
  score float default NULL,
  UNIQUE KEY id (id)
) TYPE=MyISAM;

#
# Dumping data for table `msg`
#

# --------------------------------------------------------

#
# Table structure for table `msg_id_seq`
#

CREATE TABLE msg_id_seq (
  id bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

#
# Dumping data for table `msg_id_seq`
#

INSERT INTO msg_id_seq (id) VALUES (1);
# --------------------------------------------------------

#
# Table structure for table `msgowner`
#

CREATE TABLE msgowner (
  msgid int(10) unsigned NOT NULL default '0',
  rid int(11) NOT NULL default '0'
) TYPE=MyISAM;

#
# Dumping data for table `msgowner`
#

# --------------------------------------------------------

#
# Table structure for table `policy`
#

CREATE TABLE policy (
  id int(10) unsigned NOT NULL auto_increment,
  policy_name varchar(32) default NULL,
  virus_lover char(1) default 'N',
  spam_lover char(1) default 'N',
  banned_files_lover char(1) default 'N',
  bad_header_lover char(1) default 'N',
  bypass_virus_checks char(1) default 'N',
  bypass_spam_checks char(1) default 'N',
  bypass_banned_checks char(1) default 'N',
  bypass_header_checks char(1) default 'N',
  spam_modifies_subj char(1) default 'Y',
  spam_quarantine_to varchar(64) default 'spam-quarantine',
  spam_tag_level float default '-999',
  spam_tag2_level float default NULL,
  spam_kill_level float default NULL,
  UNIQUE KEY id (id)
) TYPE=MyISAM;

#
# Dumping data for table `policy`
#

INSERT INTO policy (id, policy_name, virus_lover, spam_lover,
banned_files_lover, bad_header_lover, bypass_virus_checks,
bypass_spam_checks, bypass_banned_checks,
bypass_header_checks, spam_modifies_subj, spam_quarantine_to,
spam_tag_level, spam_tag2_level, spam_kill_level) VALUES (7,
'Never Tag and Never Block', 'N', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'Y',
'spam-quarantine', '-999', '999', '999');
INSERT INTO policy (id, policy_name, virus_lover, spam_lover,
banned_files_lover, bad_header_lover, bypass_virus_checks,
bypass_spam_checks, bypass_banned_checks,
bypass_header_checks, spam_modifies_subj, spam_quarantine_to,
spam_tag_level, spam_tag2_level, spam_kill_level) VALUES (3,
'Trigger happy', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'spam-
quarantine', '-999', '5', '5');
INSERT INTO policy (id, policy_name, virus_lover, spam_lover,
banned_files_lover, bad_header_lover, bypass_virus_checks,
bypass_spam_checks, bypass_banned_checks,
bypass_header_checks, spam_modifies_subj, spam_quarantine_to,
spam_tag_level, spam_tag2_level, spam_kill_level) VALUES (1,
'Default_Nonuser', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', NULL, '-999',
'6', '12');
INSERT INTO policy (id, policy_name, virus_lover, spam_lover,
banned_files_lover, bad_header_lover, bypass_virus_checks,
bypass_spam_checks, bypass_banned_checks,
bypass_header_checks, spam_modifies_subj, spam_quarantine_to,
spam_tag_level, spam_tag2_level, spam_kill_level) VALUES (2,
'Default', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'spam-quarantine', '
-999', '6', '12');
INSERT INTO policy (id, policy_name, virus_lover, spam_lover,
banned_files_lover, bad_header_lover, bypass_virus_checks,
bypass_spam_checks, bypass_banned_checks,
bypass_header_checks, spam_modifies_subj, spam_quarantine_to,
spam_tag_level, spam_tag2_level, spam_kill_level) VALUES (6,
'Default Tag Never Block', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'spam-
quarantine', '-999', '6', '999');
INSERT INTO policy (id, policy_name, virus_lover, spam_lover,
banned_files_lover, bad_header_lover, bypass_virus_checks,
bypass_spam_checks, bypass_banned_checks,
bypass_header_checks, spam_modifies_subj, spam_quarantine_to,
spam_tag_level, spam_tag2_level, spam_kill_level) VALUES (5,
'6.5/7.8', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'spam-quarantine', '
-999', '6.5', '7.8');
INSERT INTO policy (id, policy_name, virus_lover, spam_lover,
banned_files_lover, bad_header_lover, bypass_virus_checks,
bypass_spam_checks, bypass_banned_checks,
bypass_header_checks, spam_modifies_subj, spam_quarantine_to,
spam_tag_level, spam_tag2_level, spam_kill_level) VALUES (4,
'Permissive', 'N', 'N', 'N', 'Y', 'N', 'N', 'N', 'N', 'Y', 'spam-quarantine',
'-999', '10', '20');
# --------------------------------------------------------

#
# Table structure for table `policy_id_seq`
#

CREATE TABLE policy_id_seq (
  id int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

#
# Dumping data for table `policy_id_seq`
#

INSERT INTO policy_id_seq (id) VALUES (11);
# --------------------------------------------------------

#
# Table structure for table `users`
#

CREATE TABLE users (
  id int(10) unsigned NOT NULL auto_increment,
  priority smallint(6) NOT NULL default '7',
  policy_id int(11) NOT NULL default '2',
  email varchar(255) NOT NULL default '',
  fullname varchar(255) default NULL,
  digest char(2) default 'WD',
  username varchar(255) default NULL,
  retention smallint(6) default '14',
  UNIQUE KEY id (id)
) TYPE=MyISAM;

#
# Dumping data for table `users`
#

INSERT INTO users (id, priority, policy_id, email, fullname, digest,
username, retention) VALUES (1, 1, 1, '@.', 'Global Match', NULL,
NULL, NULL);
# --------------------------------------------------------

#
# Table structure for table `users_id_seq`
#

CREATE TABLE users_id_seq (
  id int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

#
# Dumping data for table `users_id_seq`
#

INSERT INTO users_id_seq (id) VALUES (2);
# --------------------------------------------------------

#
# Table structure for table `wblist`
#

CREATE TABLE wblist (
  rid int(11) unsigned NOT NULL default '0',
  sid int(11) NOT NULL default '0',
  priority smallint(6) NOT NULL default '7',
  email varchar(255) NOT NULL default '',
  wb char(1) NOT NULL default ''
  ) TYPE=MyISAM;

Any help is appreciated.

Thanks

Discussion

  • Lars Hecking
    Lars Hecking
    2005-01-05

    Logged In: YES
    user_id=28904

    amavisd-new is supported by Mark Martinec.

     
  • Lars Hecking
    Lars Hecking
    2005-01-05

    • status: open --> closed