Search:  
Gentoo Wiki

Complete_Virtual_Mail_Server/Quarantine_and_Spam_Management


Please format this article according to the guidelines and Wikification suggestions, then remove this notice {{Wikify}} from the article


Complete Virtual Mail Server


Getting Started


Basic Mail Setup


Enhanced Mail Services


Anti-Spam Configuration


Anti-Virus Configuration


Log Analyzer


Wrapping it Up

edit

Quarantine and Spam Management

This section has turned out to be much tougher than originally anticipated. Amavisd is a great start but was lacking a web-based interface. When I started searching for a package to cover this off, I found a real mixed bag of solutions with no clear winner. I have actually installed a number of packages only to find they were lacking some necessary component or didn’t quite do what I was looking for.

I was not able to get the functionality I wanted out of a single package. Either they were missing functionality, wouldn’t run across multiple servers, required a bunch of custom scripts, etc. etc. etc. I eventually decided the right thing to do was to install the base database schema supported by amavisd and have quarantined messages stored in there for now. From there, you can either write a script, or use a package such as MailZu (www.mailzu.net) to provide an interface. Unfortunately this solution is not nicely tied into the other components (as say a per-user mail folder or integrated with Squirrelmail), but better than a kick in the teeth.

I know that many of you will say there are ready made plugins for squirrelmail that will do the job. Well yes and no. There are some ready made plugins, but they either require different database schemas, their own special scripts to move things into the database and/or will not operate across multiple servers without the need to install a mess of additional support, which I am not prepared to do.


Creating the Database

Quarantined messages and all of our amavisd/spamassassin settings are going to be stored in our database. We will start by creating the database and database user we will need.


Code: Creating the Database
 
# createdb -E SQL_ASCII -U postgres amavis
CREATE DATABASE

# createuser -U postgres -P
Enter name of user to add:  amavis
Enter password for new user: $password
Enter it again: $password
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
  

Don’t forget that before the amavis user is going to be able to connect to your new database, you will need to give the amavis user the appropriate rights. As we did way back in this document for the Postgres and Postfix users, I used Webmin to setup the Postgres access using the “Allowed hosts” feature.

The reason for SQL_ASCII-encoding of the database is to allow spamassassin to store mails with strange mail encodings too. We can't use UNICODE, because we have no way to specify a client-encoding in the spamassassin configs. SQL_ASCII means to ignore conversions and store everything as-is.
(This is not a great solution, but the only one i could find, that seems to work. Pay attention to UNICODE-conversion-errors your logs if you're not using this setting! I got bad bayes learning for some months before i could solve this! And I'm personally still not convinced that postgres spits out the data in the same format as spamassassin expects it... [Could someone who knows SA better than me please verify this.] -- 212.100.47.100 05:09, 4 October 2006 (UTC) )

The tables for our database are gong to be created from a number of sources. We will step through this one plugin at a time, ensuring that they are tested before moving on.

Create the Tables

We will use the amavisd schema taken from the README.sql.txt document found at http://www.ijs.si/software/amavisd/ README.sql.txt. Have a read through the document before you carry on (along with some of the other readme’s here) as there is a lot of good information that will increase your understanding of amavisd.

To keep things easy for the reader, I have included the entire schema here, adjusted for Postrgres with the necessary Grant statements for the amavis user we created above. You should be able to copy the text below into a file and use it to create the required tables.

{pderbyshire - 7-nov-2006: note db schema has changed since versions 2.4.1 and above so best copy the SQL from http://www.ijs.si/software/amavisd/README.sql.txt making appropriate changes for PostgreSQL. Don't forget to include the GRANTs (SELECT, INSERT, UPDATE, DELETE) on all the amavis tables and indexes.}

...{sbavalis - 9-nov-2007: I don't know if this is another change or not but there is a readme at http://ijs.si/software/amavisd for generic sql, postgres, and mysql.}...

{Edit by michrech@yahoo.com -- the bad_header_lover field should be larger than 1 character to be able to hold a username as the sample data suggests. 1 character just isn't enough as I discovered while I tried to enter some data into the table. If this was meant to be a "y" or "n" like the bypass_* fields, then "Test User" in the "Test Data" box in the 'policy' table should be replaced with "y" or "n".}

{This was my problem for a few hours, i couldn't store corectly incomming spam in database. Solution from amavis readme: Upgrading from pre 2.4.0 amavisd-new SQL schema to the 2.4.0 schema requires adding column 'quar_loc' to table msgs, and creating FOREIGN KEY constraint to facilitate deletion of expired records. The following clauses should be executed for upgrading pre-2.4.0 amavisd-new SQL schema to the 2.4.0 schema}

 ALTER TABLE msgs ADD quar_loc varchar(255) DEFAULT ' ';

{Edit by kardasa@kardasa.pl for me above wasn't enough as I'm using amavisd-new-2.5.2 according to documentation [1] you have to change data type of mail_text field

 ALTER TABLE quarantine ALTER mail_text TYPE bytea
   USING decode(replace(mail_text,'\\','\\\\'),'escape');

This will also require to change an option in MailZu config.php file.

{Edit by ryan@ryanholt.net I needed to add $sql_allow_8bit_address = 1; to my config; i was getting errors with about the bytea variable.

Sugestion: /var/amavis/amavis.log - is yours friend.

{Edit by Alphacube 2008-05-08 for MySql users see http://www.ijs.si/software/amavisd/README.sql-mysql.txt }


Code: Database Tables
 
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,  -- unique id, not necessarily auto-assigned
  priority   integer      NOT NULL DEFAULT '7',  -- sort field, 0 is low prior.
  policy_id  integer      NOT NULL DEFAULT '1',  -- JOINs with policy.id
  email      varchar(255) NOT NULL,
  fullname   varchar(255) DEFAULT NULL,    -- not used by amavisd-new
  local      char(1)      -- Y/N  (optional field, see note further down)
);
CREATE UNIQUE INDEX users_idx_email ON users (email);
GRANT SELECT, INSERT, UPDATE, DELETE on users, users_id_seq to amavis;

-- any e-mail address (non- rfc2822-quoted), external or local,
-- used as senders in wblist
CREATE TABLE mailaddr (
  id         SERIAL PRIMARY KEY,
  priority   integer      NOT NULL DEFAULT '7',  -- 0 is low priority
  email      varchar(255) NOT NULL
);
CREATE UNIQUE INDEX mailaddr_idx_email ON mailaddr (email);
GRANT SELECT, INSERT, UPDATE, DELETE on mailaddr, mailaddr_id_seq to amavis;

-- per-recipient whitelist and/or blacklist,
-- puts sender and recipient in relation wb  (white or blacklisted sender)
CREATE TABLE wblist (
  rid        integer      NOT NULL,  -- recipient: users.id
  sid        integer      NOT NULL,  -- sender: mailaddr.id
  wb         varchar(10)  NOT NULL,  -- W or Y / B or N / space=neutral / score
  PRIMARY KEY (rid,sid)
);
GRANT SELECT, INSERT, UPDATE, DELETE on wblist to amavis;

CREATE TABLE policy (
  id         SERIAL PRIMARY KEY,    -- this is the _only_ required field
  policy_name      varchar(32),     -- not used by amavisd-new

  virus_lover          char(1) default NULL,     -- Y/N
  spam_lover           char(1) default NULL,     -- Y/N
  banned_files_lover   char(1) default NULL,     -- Y/N
  bad_header_lover     char(1) default NULL,     -- Y/N

  bypass_virus_checks  char(1) default NULL,     -- Y/N
  bypass_spam_checks   char(1) default NULL,     -- Y/N
  bypass_banned_checks char(1) default NULL,     -- Y/N
  bypass_header_checks char(1) default NULL,     -- Y/N

  spam_modifies_subj   char(1) default NULL,     -- Y/N

  virus_quarantine_to      varchar(64) default NULL,
  spam_quarantine_to       varchar(64) default NULL,
  banned_quarantine_to     varchar(64) default NULL,
  bad_header_quarantine_to varchar(64) default NULL,

  spam_tag_level  float default NULL,  -- higher score inserts spam info headers
  spam_tag2_level float default NULL,  -- inserts 'declared spam' header fields
  spam_kill_level float default NULL,  -- higher score activates evasive actions, e.g.
                                       -- reject/drop, quarantine, ...
                                     -- (subject to final_spam_destiny setting)
  spam_dsn_cutoff_level float default NULL,

  addr_extension_virus      varchar(64) default NULL,
  addr_extension_spam       varchar(64) default NULL,
  addr_extension_banned     varchar(64) default NULL,
  addr_extension_bad_header varchar(64) default NULL,

  warnvirusrecip      char(1)     default NULL, -- Y/N
  warnbannedrecip     char(1)     default NULL, -- Y/N
  warnbadhrecip       char(1)     default NULL, -- Y/N
  newvirus_admin      varchar(64) default NULL,
  virus_admin         varchar(64) default NULL,
  banned_admin        varchar(64) default NULL,
  bad_header_admin    varchar(64) default NULL,
  spam_admin          varchar(64) default NULL,
  spam_subject_tag    varchar(64) default NULL,
  spam_subject_tag2   varchar(64) default NULL,
  message_size_limit  integer     default NULL, -- max size in bytes, 0 disable
  banned_rulenames    varchar(64) default NULL  -- comma-separated list of ...
        -- names mapped through %banned_rules to actual banned_filename tables
);
GRANT SELECT, INSERT, UPDATE, DELETE on policy, policy_id_seq to amavis;


-- R/W part of the dataset (optional)
--   May reside in the same or in a separate database as lookups database;
--   requires support for transactions; specified in @storage_sql_dsn
--
--  Please create additional indexes on keys when needed, or drop suggested
--  ones as appropriate to optimize queries needed by a management application.
--  See your database documentation for further optimization hints.


-- provide unique id for each e-mail address, avoids storing copies
CREATE TABLE maddr (
  id         SERIAL PRIMARY KEY,
  email      varchar(255) NOT NULL,    -- full mail address
  domain     varchar(255) NOT NULL     -- only domain part of the email address
                                       -- with subdomain fields in reverse
);
CREATE UNIQUE INDEX maddr_idx_email     ON maddr (email);
CREATE        INDEX maddr_idx_domain    ON maddr (domain);
GRANT SELECT, INSERT, UPDATE, DELETE on maddr, maddr_id_seq to amavis;


-- information pertaining to each processed message as a whole;
-- NOTE: records with NULL msgs.content should be ignored by utilities,
--   as such records correspond to messages just being processes, or were lost
CREATE TABLE msgs (
  mail_id    varchar(12)   NOT NULL,    -- long-term unique mail id
  secret_id  varchar(12)   DEFAULT '',  -- authorizes release of mail_id
  am_id      varchar(20)   NOT NULL,    -- id used in the log
  time_num   integer       NOT NULL, -- rx_time: second since Unix epoch
  time_iso   char(16)      NOT NULL,    -- rx_time: ISO8601 UTC ascii time
  sid        integer       NOT NULL, -- sender: maddr.id
  policy     varchar(255)  DEFAULT '',  -- policy bank path (like macro %p)
  client_addr varchar(255) DEFAULT '',  -- SMTP client IP address (IPv4 or v6)
  size       integer       NOT NULL, -- message size in bytes
  content    char(1),                   -- content type: V/B/S/H/O/C, is NULL
                                        -- ...on partially processed mail
  quar_type  char(1),                   -- quarantined as: ' '/F/Z/B/Q/M
                                        --  none/file/zipfile/bsmtp/sql/mailbox
  dsn_sent   char(1),                   -- was DSN sent? Y/N/q (q=quenched)
  spam_level float,                     -- base message spam level (no boosts)
  message_id varchar(255)  DEFAULT '',  -- mail Message-ID header field
  from_addr  varchar(255)  DEFAULT '',  -- mail From header field,    UTF8
  subject    varchar(255)  DEFAULT '',  -- mail Subject header field, UTF8
  host       varchar(255)  NOT NULL,    -- hostname where amavisd is running
  PRIMARY KEY (mail_id)
);
CREATE INDEX msgs_idx_sid ON msgs (sid);
GRANT SELECT, INSERT, UPDATE, DELETE on msgs to amavis;


-- per-recipient information related to each processed message;
-- NOTE: records in msgrcpt without corresponding msgs.mail_id record are
--  orphaned and should be ignored and eventually deleted by external utilities
CREATE TABLE msgrcpt (
  mail_id    varchar(12)   NOT NULL,     -- (must allow duplicates)
  rid        integer       NOT NULL,  -- recipient: maddr.id (dupl. allowed)
  ds         char(1)       NOT NULL,     -- delivery status: P/R/B/D/T
                                         -- pass/reject/bounce/discard/tempfail
  rs         char(1)       NOT NULL,     -- release status: initialized to ' '
  bl         char(1)       DEFAULT ' ',  -- sender blacklisted by this recip
  wl         char(1)       DEFAULT ' ',  -- sender whitelisted by this recip
  bspam_level float,                     -- spam level + per-recip boost
  smtp_resp  varchar(255)  DEFAULT ''
);
CREATE INDEX msgrcpt_idx_mail_id ON msgrcpt (mail_id);
CREATE INDEX msgrcpt_idx_rid     ON msgrcpt (rid);
GRANT SELECT, INSERT, UPDATE, DELETE on msgrcpt to amavis;


-- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'
-- NOTE: records in quarantine without corresponding msgs.mail_id record are
--  orphaned and should be ignored and eventually deleted by external utilities
CREATE TABLE quarantine (
  mail_id    varchar(12)   NOT NULL,    -- long-term unique mail id
  chunk_ind  integer       NOT NULL,-- chunk number, starting with 1
  mail_text  text          NOT NULL,    -- store mail as chunks up to 16 kB
  PRIMARY KEY (mail_id,chunk_ind)
);
GRANT SELECT, INSERT, UPDATE, DELETE on quarantine to amavis;


-- field msgrcpt.rs is primarily intended for use by quarantine management
-- software; the value initially assigned by amavisd is a space;
-- a short _preliminary_ list of possible values:
--   'V' => viewed (marked as read)
--   'R' => released (delivered) to this recipient
--   'p' => pending (a status given to messages when the admin received the
--                   request but not yet released; targeted to banned parts)
--   'D' => marked for deletion; a cleanup script may delete it
  

Database Connections

Here we give amavisd the information it needs to connect with our database for reading per-user settings. Back to the mail sever to adjust the settings in amavisd.conf for its lookups.


Code: /etc/amavisd.conf
 
# nano /etc/amavisd.conf

@lookup_sql_dsn = ( ['DBI:Pg:dbname=amavis;host=dbServerhostname;port=5432', 'dbuser', 'dbpass']);

@storage_sql_dsn = @lookup_sql_dsn;  # none, same, or separate database.
  


While debug testing amavis i discovered that unless a username and password is provided in the sql dsn string pg login was denied. Fixed the example to represent functional values - ali3nx 27.11.06


Code: /etc/amavisd.conf @lookup_sql_dsn notes
 

amavis[11790]: (11790-01) sql begin, nontransaction
amavis[11790]: (11790-01) Connecting to SQL database server
amavis[11790]: (11790-01) connect_to_sql: trying 'DBI:Pg:dbname=amavis;host=127.0.0.1;port=5432'
amavis[11790]: (11790-01) (!) connect_to_sql: unable to connect to DSN 'DBI:Pg:dbname=amavis;host=127.0.0.1;port=5432': fe_sendauth: no password supplied\n
amavis[11790]: (11790-01) (!!) TROUBLE in process_request: connect_to_sql: unable to connect to any dataset at (eval 52) line 196, <GEN5> line 5.
amavis[11790]: (11790-01) (!) Requesting process rundown after fatal error
amavis[11790]: (11790-01) Amavis::In::SMTP DESTROY called, sock=Net::Server::Proto::TCP=GLOB(0x40a1990), normal=0

  

There are also a few changes we need to make to the amavisd configuration so that it will store quarantined messages into the database as follows


Code: /etc/amavisd.conf
 
# nano /etc/amavisd.conf

# Location to put infected mail into: (applies to 'local:' quarantine method)
#   empty for not quarantining, may be a file (Unix-style mailbox),
#   or a directory (no trailing slash)
#   (the default value is undef, meaning no quarantine)
#
#$QUARANTINEDIR = "$MYHOME/quarantine";

#$quarantine_subdir_levels = 1;  # add level of subdirs to disperse quarantine

#$virus_quarantine_method          = 'local:virus-%m';     # default
#$spam_quarantine_method           = 'local:spam-%m.gz';   # default
#$banned_files_quarantine_method   = 'local:banned-%m';    # default
#$bad_header_quarantine_method     = 'local:badh-%m';      # default

# Separate quarantine subdirectories virus, spam, banned and badh within
# the directory $QUARANTINEDIR may be specified by the following settings
# (the subdirectories need to exist - must be created manually):
#$virus_quarantine_method          = 'local:virus/virus-%m';
#$spam_quarantine_method           = 'local:spam/spam-%m.gz';
#$banned_files_quarantine_method   = 'local:banned/banned-%m';
#$bad_header_quarantine_method     = 'local:badh/badh-%m';


#use the 'bsmtp:' method as an alternative to the default 'local:'
#$virus_quarantine_method = "bsmtp:$QUARANTINEDIR/virus-%m.bsmtp";
#$spam_quarantine_method  = "bsmtp:$QUARANTINEDIR/spam-%m.bsmtp";


#$virus_quarantine_to     = 'virus-quarantine';      # local quarantine
#$banned_quarantine_to    = 'banned-quarantine';     # local quarantine
#$bad_header_quarantine_to= 'bad-header-quarantine'; # local quarantine
#$spam_quarantine_to      = 'spam-quarantine';       # local quarantine


#
#using the 'sql:' method to store quarantined message to a SQL database:
$virus_quarantine_method = $spam_quarantine_method =
  $banned_files_quarantine_method = $bad_header_quarantine_method = 'sql:';



#@lookup_sql_dsn =
#   ( ['DBI:mysql:database=mail;host=127.0.0.1;port=3306', 'user1', 'passwd1'],
#     ['DBI:mysql:database=mail;host=host2', 'username2', 'password2'],
#     ["DBI:SQLite:dbname=$MYHOME/sql/mail_prefs.sqlite", '', ''] );
@lookup_sql_dsn = ( ['DBI:Pg:dbname=amavis;host=martin;port=5432'] );
@storage_sql_dsn = @lookup_sql_dsn;  # none, same, or separate database
  

Ensure you comment out all of the quarantine settings above and uncomment the “sql:” method parameters and the lookup_sql_dsn and storage_sql_dsn.


Note: There is a problem in the perl DBI module for Postgres with the Taint functionality. I spent a fair bit to time trying to resolve the issue, but wasn’t able to find a root cause. In the meantime there is a workaround by making a small change in the amavisd code so that Perl’s Taint functionality doesn’t interfere.


We will adjust Taint in the DBI->connect call so that it reads Taint => 0 rather than => 1 as originally coded.

// The above seems to be not correct (at least) for amavisd-new-2.4.1! Changing the above actually prevented the whole thing from working.


Code: /usr/sbin/amavisd
 
# nano /usr/sbin/amavisd

sub connect_to_sql {
  my($self) = shift;  # a list of DSNs to try connecting to sequentially
  my($dbh); my(@dsns) = @{$self->{dsn_list}};
  do_log(3,"Connecting to SQL database server");
  for my $tmpdsn (@dsns) {
    my($dsn, $username, $password) = @$tmpdsn;
    do_log(4,"connect_to_sql: trying '$dsn'");
    $dbh = DBI->connect($dsn, $username, $password,
             {PrintError => 0, RaiseError => 0, Taint => 0, AutoCommit => 1} );
    if ($dbh) { do_log(3,"connect_to_sql: '$dsn' succeeded"); last }
    do_log(-1,"connect_to_sql: unable to connect to DSN '$dsn': ".$DBI::errstr);
  

Restart amavisd and run a quick test by sending a clean message that should go through and then the test spam message (mentioned earlier) which should get stored into the database. To verify the test worked, use webmin to go look at the quarantine table in your database and confirm that a mail did show up.

Per-User Settings

Obviously when we created the database above, we included many tables beyond what was required simply for storing than what was required for just quaranting messages. Not all users will be satisfied with the default values you have set for amavisd.conf. Using the database, we can allow individual settings for users or groups of users.

With the tables setup, we simply need to tell amavisd how to use the database to read user preferences/


Code: /etc/amavisd.conf
 
# nano /etc/amavisd.conf

$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';
  

Now using webmin, I will setup some test data that will allow us to verify the settings are in fact working. Substitute the test email with your own so that the policy will get picked up when you send your test messages through.


File: Test Data
 
  users Table
     id:         	1
     priority:  	9
     policy_id:   	1
     email:     	user@example.com
     fullname:  	Test User
     local:	        Y


  policy Table
     id:         	     1
     policy_name:  	     Test Policy
     virus_lover:   	     N
     banned_files_lover:     N
     bad_header_lover: 	     N
     bypass_virus_checks:    Y
     bypass_spam_checks:     N
     bypass_banned_checks:   N
     bypass_header_checks:   N
     spam_modifies_subj:     N

* Leave the remainder of the settings NULL
  

Per-User Testing

Based on the initial test settings, when we submit a message, then the system should not act any differently. Be sure to restart amavisd before testing or else your changes will not take effect.

Send through your spam message and ensure that it gets shunted off to the quarantine database. Yes? Great, now go back and make a minor change to your test data in the policy table by setting bypass_spam_checks to “Y”. Now when you resend your spam message, it should just pass straight through to your mailbox.

Before moving on, don’t forget to reset the value we changed back to “N”.

Retrieved from "http://www.gentoo-wiki.info/Complete_Virtual_Mail_Server/Quarantine_and_Spam_Management"

Last modified: Mon, 06 Oct 2008 16:22:00 +0000 Hits: 16,771