Zurück

Fighting Spam/Viruses with Amavisd-New, Maia and Postfix
 

More Tips: Fighting Spam with SpamAssassin and Postfix
  Postfix -- the Sendmail Replacement

Content

Overview
Integrating Amavisd-New with Postfix
Installing Amavisd-New
Configuring Amavisd-New
Quarantine Handling
Globally Sender whitelists and blacklists
Per-Recipient Sender Whitelists and Blacklists (Maia Mailguard)
Maia Installing and Configuration
Configure Postfix for Amavisd-New
ClamAV Virus Scanning
Sender Policy Framework SPF

Overview

Postfix is a mail transport agent written by security researcher Wietse Venema. Not surprisingly, Postfix is designed from the ground up to be a highly secure system. It consists of several components, each of which runs with least privilege and none of which trust data from the other without validating it themselves. Despite the extensive security emphasis in the system's architecture, Postfix is capable of very good performance in normal conditions; because of architectural decisions, it is also fault tolerant and capable of good performance under adverse conditions such as resource starvation.

Postfix supports three content inspection methods, ranging from light-weight one-line-at-a-time scanning before mail is queued, to heavy duty machinery that does sophisticated content analysis after mail is queued. Each approach serves a different purpose.

The method shown in this article inspects mail AFTER it is stored in the queue, and uses standard protocols such as SMTP. After-queue inspection allows you to use content filters of arbitrary complexity without causing timeouts while receiving mail, and without running out of memory resources under a peak load.

The SpamAssassin system is software for analyzing email messages, determining how likely they are to be spam, and reporting its conclusions. It is a rule-based system that compares different parts of email messages with a large set of rules. Each rule adds or removes points from a message's spam score. A message with a high enough score is reported to be spam.

Amavisd-New is a high-performance and reliable interface between mailer (MTA) and one or more content checkers: virus scanners, and SpamAssassin. It is written in Perl, assuring high reliability, portability and maintainability. It talks to MTA via (E)SMTP or LMTP, or by using helper programs. No timing gaps exist in the design, which could cause a mail loss.

It is normally positioned at or near a central mailer, not necessarily where user's mailboxes and final delivery takes place. When calling of Mail::SpamAssassin (SA) is enabled, it calls SA only once per message (regardless of the number of recipients), and tries very hard to correctly honour per-recipient preferences, such as pass/reject, and inserting spam-related mail header fields.

Integrating Amavisd-New with Postfix

This article does not explain how to install and setup Postfix / SpamAssassin, this is explained here:

Postfix -- the Sendmail Replacement
Fighting Spam with SpamAssassin and Postfix

If you want to set up a spam-checking gateway for all recipients, local or not, you need a way to perform spam-checking as mail is received, before final delivery. Postfix provides a general-purpose filtering directive called content_filter.

The content_filter directive specifies a mail transport that Postfix will invoke after receiving a message. The mail transport hands the message to a filtering program. The filter checks the message and then either refuses it (which will cause Postfix to generate a bounce message), discards it, or reinjects the modified message into Postfix for further delivery. Messages that pass the filter are reinjected so that Postfix can operate on them almost as if they were new messages; this allows Postfix to behave properly if the content filter rewrites message headers.

Installing Amavisd-New (2.4.5)

Several content-filtering daemons that call SpamAssassin are available for Postfix. This article provides a complete sample installation of Amavisd-New, a particularly efficient filter that supports both spam-checking and virus-checking. Amavisd-New is written in Perl. Some of Amavisd-New's features include:

  • Messages can be rejected based on MIME type or extensions of attached filenames.
  • Messages can be checked with multiple virus scanners, and messages carrying viruses can be refused, discarded, or quarantined.
  • SpamAssassin can be invoked on a message, and spam can be refused, discarded, quarantined, or tagged.
  • Per-user configuration of Amavisd-New is possible through an SQL or LDAP database.

The rest of this article details the installation, configuration, and operation of Amavisd-New as an example of a full-scale, daemonized, content filter approach to using SpamAssassin with Postfix. Amavisd-New's other functions, such as virus-checking, are not covered; read the documentation to learn more about these other Amavisd-New features.

  1. Download Amavisd-New from: http://www.ijs.si/software/amavisd/
     
  2. Download the following modules from http://www.cpan.org/, if you search a single module, http://search.cpan.org/ can be very helpful.

    AppConfig-1.64.tar.gz
    Archive-Tar-1.30.tar.gz
    Archive-Zip-1.18.tar.gz
    BerkeleyDB-0.31.tar.gz
    Compress-Raw-Zlib-2.004.tar.gz
    Compress-Zlib-2.004.tar.gz
    Convert-TNEF-0.17.tar.gz
    Convert-UUlib-1.08.tar.gz
    Crypt-Blowfish-2.10.tar.gz
    Crypt-CBC-2.22.tar.gz
    Crypt-OpenSSL-RSA-0.24.tar.gz
    Data-UUID-1.148.tar.gz
    DBD-mysql-4.004.tar.gz
    DB_File-1.815.tar.gz
    DBI-1.54.tar.gz
    Digest-1.15.tar.gz
    Digest-HMAC-1.01.tar.gz
    Digest-MD5-2.36.tar.gz
    Digest-SHA1-2.11.tar.gz
    Digest-SHA-5.44.tar.gz
    Error-0.17008.tar.gz
    File-HomeDir-0.64.tar.gz
    File-Which-0.05.tar.gz
    Geography-Countries-1.4.tar.gz
    HTML-Parser-3.56.tar.gz
    IO-Compress-Base-2.004.tar.gz
    IO-Compress-Zlib-2.004.tar.gz
    IO-stringy-2.110.tar.gz
    IO-Zlib-1.05.tar.gz
    IP-Country-2.23.tar.gz
    libnet-1.19.tar.gz
    libwww-perl-5.805.tar.gz
    Mail-DKIM-0.24.tar.gz
    Mail-DomainKeys-1.0.tar.gz
    Mail-SPF-Query-1.999.1.tar.gz
    MailTools-1.76.tar.gz
    MIME-Base64-3.07.tar.gz
    MIME-tools-5.420.tar.gz
    Net-CIDR-Lite-0.20.tar.gz
    Net-DNS-0.59.tar.gz
    Net-Ident-1.20.tar.gz
    Net-Server-0.96.tar.gz
    Storable-2.16.tar.gz
    Sys-Hostname-Long-1.4.tar.gz
    Template-Toolkit-2.18.tar.gz
    Text-Diff-0.35.tar.gz
    Time-HiRes-1.9707.tar.gz
    Unix-Syslog-0.100.tar.gz
     
  3. Install the modules as follows:

    tar xzvf <file-from-above>
    cd <extracted module>
    perl Makefile.PL
    make
    make test
    make install

If you plan to do per-user configuration of Amavisd-New through SQL, you'll need appropriate Perl modules for database access (DBI and a DBD:: module for SQL).

Begin the install process by creating a new user account and group for running Amavisd-New; the usual name for both the user and group is «amavis». This user will own Amavisd-New's files, and the user (or group) must have access to SpamAssassin's configuration and database files as well.

Amavisd-New uses several important directories. It keeps two files in its home directory, one containing its current process ID, and the other used for locking. It uses a working directory for unpacking email messages and scanning them; by default, this is the home directory or the tmp subdirectory of the home directory. For optimal performance, this directory should be on a fast disk—even a RAM disk if your operating system supports it and you have enough memory to spare. Amavisd-New stores quarantined email messages in /var/virusmails by default, but you can select any directory for this purpose.

As root, copy the amavisd script to a suitable directory for executable daemons (e.g. /usr/local/bin), chown it to root, and use chmod to set its permissions to 0755 (readable and executable by all users, writable only by root).

Copy the amavisd.conf file to a suitable directory for configuration files (e.g /etc). By default, amavisd expects to find this file in /etc, and if you locate it anywhere else, you will have to add an extra command-line option (-c filename) when invoking amavisd to tell it the new location. The amavisd.conf file should also be owned by root and should have permissions 0644 (readable by all, writable only by root).

Configuring Amavisd-New

Amavisd-New is configured through the amavisd.conf file. amavisd.conf is parsed as a Perl script and can contain any legal Perl code. Because it is parsed as Perl, you must escape any at sign (@), question mark ($), or backslash (\) characters that appear in double-quoted strings by prepending a backslash.

Edit amavisd.conf to set the (many) available configuration options to control amavisd. The file is organized in logical sections; the most important options are in Section I, but you'll need to read through the entire file to customize the system completely.

Once you've configured the options in amavisd.conf, you're ready to test amavisd. During your first test, start amavisd with the debug argument. This causes amavisd to run in the foreground and produce debugging information that you can watch to be sure that it's working correctly.

Here is an example of amavisd.conf

# /usr/local/bin/amavisd -u amavis debug

starting.  /usr/local/bin/amavisd at rabbit.akadia.com Amavisd-New-2.2.0 (20041102), Unicode aware, LANG=en_US
user=amavis, EUID: 502 (502);  group=502, EGID: 502 502 (502 502)
Perl version               5.008
Net::Server: 2004/12/10-09:18:19 Amavis (type Net::Server::PreForkSimple)
Net::Server: Binding to TCP port 10024 on host 127.0.0.1
Net::Server: Group Not Defined.  Defaulting to EGID '502 502'
Net::Server: User Not Defined.  Defaulting to EUID '502'
Net::Server: Setting up serialization via flock
Module Amavis::Conf        2.033
Module Archive::Tar        1.23
Module Archive::Zip        1.14
Module BerkeleyDB          0.26
Module Compress::Zlib      1.33
Module Convert::TNEF       0.17
Module Convert::UUlib      1.03
Module DBI                 1.46
Module DB_File             1.810
Module MIME::Entity        5.415
Module MIME::Parser        5.415
Module MIME::Tools         5.415
Module Mail::Header        1.65
Module Mail::Internet      1.65
Module Mail::SPF::Query    1.997
Module Mail::SpamAssassin  3.000001
Module Net::Cmd            2.26
Module Net::DNS            0.48
Module Net::SMTP           2.29
Module Net::Server         0.87
Module Time::HiRes         1.65
Module Unix::Syslog        0.100
Amavis::DB code        loaded
Amavis::Cache code     loaded
Lookup::SQL code       NOT loaded
Lookup::LDAP code      NOT loaded
AMCL-in protocol code  NOT loaded
SMTP-in protocol code  loaded
ANTI-VIRUS code        NOT loaded
ANTI-SPAM  code        loaded
Unpackers  code        loaded
Found $file       at /usr/bin/file
No $arc,          not using it
Found $gzip       at /usr/bin/gzip
Found $bzip2      at /usr/bin/bzip2
No $lzop,         not using it
Found $lha        at /usr/bin/lha
Found $unarj      at /usr/bin/unarj
Found $uncompress at /usr/bin/uncompress
No $unfreeze,     not using it
No $unrar,        not using it
No $zoo,          not using it
Found $cpio       at /bin/cpio
Found $ar         at /usr/bin/ar
Found $rpm2cpio   at /usr/bin/rpm2cpio
No $cabextract,   not using it
No $dspam,        not using it
Creating db in /home/amavis/db/; BerkeleyDB 0.26, libdb 4.0
SpamControl: initializing Mail::SpamAssassin
debug: SpamAssassin version 3.0.1
debug: Score set 0 chosen.
debug: running in taint mode? yes
debug: Running in taint mode, removing unsafe env vars, and resetting PATH
debug: PATH included '/usr/local/sbin', keeping.
debug: PATH included '/usr/local/bin', keeping.
debug: PATH included '/usr/sbin', keeping.
debug: PATH included '/sbin', keeping.
debug: PATH included '/usr/bin', keeping.
debug: PATH included '/bin', keeping.
debug: Final PATH set to: /usr/local/sbin:/usr/local/bin:/usr/sbin:/sbin:/usr/bin:/bin
debug: ignore: test message to precompile patterns and load modules
debug: using "/etc/mail/spamassassin/init.pre" for site rules init.pre
debug: config: read file /etc/mail/spamassassin/init.pre
debug: using "/usr/share/spamassassin" for default rules dir
debug: config: read file /usr/share/spamassassin/10_misc.cf
debug: config: read file /usr/share/spamassassin/20_anti_ratware.cf
debug: config: read file /usr/share/spamassassin/20_body_tests.cf
debug: config: read file /usr/share/spamassassin/20_compensate.cf
debug: config: read file /usr/share/spamassassin/20_dnsbl_tests.cf
debug: config: read file /usr/share/spamassassin/20_drugs.cf
debug: config: read file /usr/share/spamassassin/20_fake_helo_tests.cf
debug: config: read file /usr/share/spamassassin/20_head_tests.cf
debug: config: read file /usr/share/spamassassin/20_html_tests.cf
debug: config: read file /usr/share/spamassassin/20_meta_tests.cf
debug: config: read file /usr/share/spamassassin/20_phrases.cf
debug: config: read file /usr/share/spamassassin/20_porn.cf
debug: config: read file /usr/share/spamassassin/20_ratware.cf
debug: config: read file /usr/share/spamassassin/20_uri_tests.cf
debug: config: read file /usr/share/spamassassin/23_bayes.cf
debug: config: read file /usr/share/spamassassin/25_body_tests_es.cf
debug: config: read file /usr/share/spamassassin/25_hashcash.cf
debug: config: read file /usr/share/spamassassin/25_spf.cf
debug: config: read file /usr/share/spamassassin/25_uribl.cf
debug: config: read file /usr/share/spamassassin/30_text_de.cf
debug: config: read file /usr/share/spamassassin/30_text_fr.cf
debug: config: read file /usr/share/spamassassin/30_text_nl.cf
debug: config: read file /usr/share/spamassassin/30_text_pl.cf
debug: config: read file /usr/share/spamassassin/50_scores.cf
debug: config: read file /usr/share/spamassassin/60_whitelist.cf
debug: using "/etc/mail/spamassassin" for site rules dir
debug: config: read file /etc/mail/spamassassin/local.cf
debug: using "/home/amavis/var/.spamassassin/user_prefs" for user prefs file
debug: plugin: loading Mail::SpamAssassin::Plugin::URIDNSBL from @INC
debug: plugin: registered Mail::SpamAssassin::Plugin::URIDNSBL=HASH(0x993df48)
debug: plugin: loading Mail::SpamAssassin::Plugin::Hashcash from @INC
debug: plugin: registered Mail::SpamAssassin::Plugin::Hashcash=HASH(0x9941c2c)
debug: plugin: loading Mail::SpamAssassin::Plugin::SPF from @INC
debug: plugin: registered Mail::SpamAssassin::Plugin::SPF=HASH(0x9952454)
debug: plugin: Mail::SpamAssassin::Plugin::URIDNSBL=HASH(0x993df48)
debug: bayes: 11157 tie-ing to DB file R/O /home/spamd/_toks
debug: bayes: 11157 tie-ing to DB file R/O /home/spamd/_seen

debug: bayes: found bayes db version 3
debug: bayes: Not available for scanning, only 198 spam(s) in Bayes DB < 200
debug: bayes: 11157 untie-ing
debug: bayes: 11157 untie-ing db_toks
debug: bayes: 11157 untie-ing db_seen
debug: Score set 1 chosen.
debug: ---- MIME PARSER START ----
debug: main message type: text/plain
debug: parsing normal part
debug: added part, type: text/plain
debug: ---- MIME PARSER END ----
....
....
debug: is spam? score=-1.053 required=3
debug: tests=ALL_TRUSTED,MISSING_DATE,MISSING_SUBJECT,NO_REAL_NAME
debug: subtests=__HAS_MSGID,__MSGID_OK_DIGITS,__MSGID_OK_HOST,__SANE_MSGID
SpamControl: done
Net::Server: Beginning prefork (5 processes)
Net::Server: Starting "5" children

Quarantine Handling

When amavisd detects a spam email, it logs a message to its log file by default. It can also quarantine the email and/or notify an administrator. It can then generate a bounce message to the sender. Finally, it can either accept and deliver the message, or discard the message. Many different configuration variables are involved in these decisions.

Enable a spam quarantine by setting the following variables:

$QUARANTINEDIR = '/var/virusmails';              # Quarantine Directory
$spam_quarantine_method = 'local:spam-%b-%i-%n';
# Filename in $QUARANTINEDIR
$spam_quarantine_to = 'spam-quarantine';        
# Put Spam in Quarantine Directory
# $spam_quarantine_to = "postmaster\@$mydomain"; # Send Spam to Adminstrator
# $spam_quarantine_to = undef;                   # Do nothing with Spam
$final_spam_destiny = D_DISCARD;
$spam_admin = "martin.zahn\@$mydomain";         
# Where to send Notification

The following symbolic constants can be used in $final_spam_destiny:

  • D_DISCARD: Mail will not be delivered to its recipients, sender will NOT be notified. Effectively we lose mail (but will be quarantined unless disabled). Losing mail is not decent for a mailer, but might be desired.
     
  • D_BOUNCE: Mail will not be delivered to its recipients, a non-delivery notification (bounce) will be sent to the sender by amavisd-new; Exception: bounce (DSN) will not be sent if a virus name matches $viruses_that_fake_sender_re, or to messages from mailing lists, or for spam level that exceeds the $sa_dsn_cutoff_level.
     
  • D_REJECT: mail will not be delivered to its recipients, sender should preferably get a reject, e.g. SMTP permanent reject response.

Globally Sender Whitelists and Blacklists

Hard sender whitelists and blacklists

amavisd can maintain whitelists and blacklists of message senders. It uses a message's envelope address (the one provided in the SMTP MAIL FROM command) as the sender address. Whitelisting ensures that amavisd will allow mail from a whitelisted sender to continue to its intended recipients; blacklisting ensures that amavisd will treat mail from a blacklisted sender as spam.

As with other amavisd address-matching features, you can specify addresses to globally whitelist:

  1. by an array

    Set the @whitelist_sender_maps array to a list of sender addresses to whitelist.

    @whitelist_sender_maps = (['.example.org', '.example.net']);
    @whitelist_sender_maps = ([qw(.example.org .example.net)]); # same thing
    @whitelist_sender_maps = ( [".$mydomain"] ); # $mydomain and its subdomains

     
  2. by keys of a hash

    Create a hash lookup table constructed in-line, with keys lowercased

    map { $whitelist_sender{lc($_)}=1 } (qw(
       cert-advisory-owner@cert.org
       owner-alert@iss.net
       slashdot@slashdot.org
      
    bugtraq@securityfocus.com
    ));


     
  3. by a set of regular expressions

    Finally, you can specify senders to whitelist by providing a list of regular expressions that match the sender addresses to the new_RE function and assigning the result to $whitelist_sender_re. You may use domain names instead of sender addresses to whitelist all mail sent from a given domain.

    @whitelist_sender_maps = ( new_RE(
       qr'.*@akadia.com$'i,
       qr'.*@swisscom\.com$'i,
       qr'.*@sicap\.com$'i,
       qr'.*@glue\.ch$'i,
      
    qr'.*@\.*\.admin\.ch$'i
    ));

You can use a similar set of variables for globally blacklisting senders. The array is @blacklist_sender_maps.

The default amavisd.conf defines $blacklist_sender_maps as shown below. Many username patterns typical of spammers are blacklisted, such as investments; many addresses of well-known security and vendor mailing lists are whitelisted.

Default blacklist entries in amavisd.conf

$blacklist_sender_re = new_RE(
   qr'^(bulkmail|offers|cheapbenefits|earnmoney|foryou|greatcasino)@'i,
   qr'^(investments|lose_weight_today|market.alert|money2you|MyGreenCard)@'i,
   qr'^(new\.tld\.registry|opt-out|opt-in|optin|saveonlsmoking2002k)@'i,
   qr'^(specialoffer|specialoffers|stockalert|stopsnoring|wantsome)@'i,
   qr'^(workathome|yesitsfree|your_friend|greatoffers)@'i,
   qr'^(inkjetplanet|marketopt|MakeMoney)\d*@'i,
);

Soft sender whitelists and blacklists

Instead of hard black- or whitelisting, a softer approach is to add score points (penalties) to the SpamAssassin score for mail from certain senders. Positive points lean towards blacklisting, negative towards whitelisting. This is much like adding SpamAssassin rules or using its white/blacklisting, except that here only envelope sender addresses are considered (not addresses in a mail header), and that score points can be assigned per-recipient (or globally), and the assigned penalties are customarily much lower than the default SpamAssassin white/blacklisting score.

@score_sender_maps = ({
  '.' => [  # the '.' matches any recipient
   new_RE (
      [qr'.*@akadia.com$'i         => -10.0],
      [qr'.*@swisscom\.com$'i      => -10.0],
      [qr'.*@sicap\.com$'i         => -10.0],
      [qr'.*@glue\.ch$'i           => -10.0],
      [qr'.*@\.*\.admin\.ch$'i     => -10.0]
   )
  ]
});

Per-Recipient Sender Whitelists and Blacklists (Maia Mailguard)

Maia Mailguard (1.0.2) is a web-based interface and management system for the popular amavisd-new e-mail scanner and SpamAssassin. Written in Perl and PHP, Maia Mailguard gives end-users control over how their mail is processed by virus scanners and spam filters, while giving mail administrators the power to configure site-wide defaults and limits.

Original Installation Guide can be found here.

Maia Features

  • Users can manage their own content filtering settings
  • Users can maintain their own whitelists and blacklists
  • Lets users see a list of their quarantined spam, sorted by score
  • Lets users see a list of their quarantined viruses, sorted by date
  • Users can view text or HTML contents of quarantined mail in decoded or raw form in a "safe" mail viewer
  • Users can rescue quarantined items to have them redelivered, delete them, or confirm them as spam
  • Users can report "false negatives" (i.e. spam that gets past the filters)
  • Optional auto-whitelisting for senders of rescued quarantine items

Entity Relationship Diagram (ERM)

Click here for the Entity Relationship Diagram (ERM)

Users

Contains amavisd-new's per-address settings, and links e-mail addresses to Maia users.

+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| id             | int(10) unsigned |      | PRI | NULL    | auto_increment |
| priority       | int(11)          |      |     | 7       |                |
| policy_id      | int(10) unsigned |      |     | 1       |                |
| email          | varchar(255)     |      | UNI |         |                |
| maia_user_id   | int(10) unsigned |      |     | 0       |                | # maia_users.id
| maia_domain_id | int(10) unsigned |      |     | 0       |                | # maia_domains.id
+----------------+------------------+------+-----+---------+----------------+

Priority

SQL lookups (e.g. for user+foo@example.com) are performed in order which is normally requested by:

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

ORDER is used, if there is a chance that multiple records will match - the first match wins.

The following order (implemented by sorting on the 'priority' field in DESCending order, zero is low priority) is recommended, to follow the same specific-to-general principle as in other lookup tables:

- lookup for user+foo@example.com
- lookup for user@example.com         # (only if $recipient_delimiter nonempty)
- lookup for user+foo                 # (only if domain part is local)
- lookup for user                     # (only local; only if $recipient_delimiter is nonempty)
- lookup for @example.com
- lookup for @.                       # (catchall)

POLICY

POLICY contains amavisd-new's policy settings, which Maia applies on a per-address basis (i.e. each e-mail address is assigned its own unique policy record. Each domain also has a set of policy defaults, and the system default policy is stored as the '@.' user's policy record.

+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| id                   | int(10) unsigned |      | PRI | NULL    | auto_increment |
| policy_name          | varchar(255)     | YES  |     | NULL    |                |
| virus_lover          | char(1)          | YES  |     | Y       |                |
| spam_lover           | char(1)          | YES  |     | Y       |                |
| banned_files_lover   | char(1)          | YES  |     | Y       |                |
| bad_header_lover     | char(1)          | YES  |     | Y       |                |
| bypass_virus_checks  | char(1)          | YES  |     | Y       |                |
| bypass_spam_checks   | char(1)          | YES  |     | Y       |                |
| bypass_banned_checks | char(1)          | YES  |     | Y       |                |
| bypass_header_checks | char(1)          | YES  |     | Y       |                |
| discard_viruses      | char(1)          | YES  |     | N       |                |
| discard_spam         | char(1)          | YES  |     | N       |                |
| discard_banned_files | char(1)          | YES  |     | N       |                |
| discard_bad_headers  | char(1)          | YES  |     | N       |                |
| spam_modifies_subj   | char(1)          | YES  |     | N       |                |
| spam_quarantine_to   | varchar(64)      | YES  |     | NULL    |                |
| spam_tag_level       | float            | YES  |     | 999     |                |
| spam_tag2_level      | float            | YES  |     | 999     |                |
| spam_kill_level      | float            | YES  |     | 999     |                |
+----------------------+------------------+------+-----+---------+----------------+

MAILADDR

MAILADDR contains a list of sender e-mail addresses referenced by users' whitelists and blacklists.

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned |      | PRI | NULL    | auto_increment |
| priority | int(11)          |      |     | 7       |                |
| email    | varchar(255)     |      | UNI |         |                |
+----------+------------------+------+-----+---------+----------------+

Priority

The SQL select clause to check sender in per-recipient whitelist/blacklist. The first SELECT argument '?' will be users.id from recipient SQL lookup, the %k will be sender addresses (e.g. full address, domain only, catchall).

$sql_select_white_black_list =
  'SELECT wblist.wb FROM wblist,mailaddr,users'
  . ' WHERE (users.id=?)'
  . ' AND (wblist.rid=users.maia_user_id)'
  . ' AND (wblist.sid=mailaddr.id)'
  . ' AND (mailaddr.email IN (%k))'
  . ' ORDER BY mailaddr.priority DESC';


  . ' ORDER BY mailaddr.priority DESC';

WBLIST

WBLIST contains the whitelist and blacklist records, on a per-user (not per-address) basis.

+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| rid   | int(10) unsigned |      | PRI | 0       |       | # maia_users.id
| sid   | int(10) unsigned |      | PRI | 0       |       | # mailaddr.id
| wb    | char(1)          |      |     |         |       |
+-------+------------------+------+-----+---------+-------+

MAIA_USERS

MAIA_USERS contains mail filter settings that apply to e-mail recipients who have registered with Maia. The user_level is stored as one of (U)ser, (A)dministrator, or (S)uper-Administrator.

+----------------------------+------------------+------+-----+------------+----------------+
| Field                      | Type             | Null | Key | Default    | Extra          |
+----------------------------+------------------+------+-----+------------+----------------+
| id                         | int(10) unsigned |      | PRI | NULL       | auto_increment |
| user_name                  | varchar(255)     |      | UNI |            |                |
| user_level                 | char(1)          |      |     | U          |                |
| reminders                  | char(1)          |      |     | Y          |                |
| charts                     | char(1)          |      |     | N          |                |
| primary_email_id           | int(10) unsigned |      |     | 0          |                |
| language                   | varchar(10)      |      |     | en         |                | # users.id
| charset                    | varchar(20)      |      |     | ISO-8859-1 |                |
| spamtrap                   | char(1)          |      |     | N          |                |
| password                   | varchar(32)      | YES  |     | NULL       |                |
| auto_whitelist             | char(1)          |      |     | Y          |                |
| items_per_page             | int(10) unsigned |      |     | 50         |                |
| spam_quarantine_sort       | char(2)          |      |     | XA         |                |
| virus_quarantine_sort      | char(2)          |      |     | DA         |                |
| header_quarantine_sort     | char(2)          |      |     | DA         |                |
| attachment_quarantine_sort | char(2)          |      |     | DA         |                |
| ham_cache_sort             | char(2)          |      |     | XD         |                |
| discard_ham                | char(1)          |      |     | N          |                |
| theme_id                   | int(10) unsigned |      | MUL | 1          |                | # maia_themes_id
+----------------------------+------------------+------+-----+------------+----------------+

MAIA_DOMAINS

MAIA_DOMAINS contains mail filter settings that apply to entire e-mail domains as defaults for users who are not registered with Maia.

+--------------------------+------------------+------+-----+------------+----------------+
| Field                    | Type             | Null | Key | Default    | Extra          |
+--------------------------+------------------+------+-----+------------+----------------+
| id                       | int(10) unsigned |      | PRI | NULL       | auto_increment |
| domain                   | varchar(255)     |      | UNI |            |                |
| reminders                | char(1)          |      |     | N          |                |
| charts                   | char(1)          |      |     | N          |                |
| enable_user_autocreation | char(1)          |      |     | Y          |                |
| language                 | varchar(10)      |      |     | en         |                |
| charset                  | varchar(20)      |      |     | ISO-8859-1 |                |
+--------------------------+------------------+------+-----+------------+----------------+

MAIA_DOMAIN_ADMINS

MAIA_DOMAIN_ADMINS is a one-to-many mapping of domains to users with administrator privileges (domains can have multiple administrators).

+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| domain_id | int(10) unsigned |      | PRI | 0       |       | # maia_domains.id
| admin_id  | int(10) unsigned |      | PRI | 0       |       | # maia_users.id
+-----------+------------------+------+-----+---------+-------+

MAIA_STATS

MAIA_STATS is a table of per-user statistics that keeps track of the total number of mail items of each type, along with total sizes and superlatives (e.g. largest, smallest, oldest, newest, etc.).

+------------------------------+------------------+------+-----+---------+-------+
| Field                        | Type             | Null | Key | Default | Extra |
+------------------------------+------------------+------+-----+---------+-------+
| user_id                      | int(10) unsigned |      | PRI | 0       |       | # maia_users.id
| oldest_suspected_ham_date    | datetime         | YES  |     | NULL    |       |
| newest_suspected_ham_date    | datetime         | YES  |     | NULL    |       |
| smallest_suspected_ham_size  | int(10) unsigned |      |     | 0       |       |
| largest_suspected_ham_size   | int(10) unsigned |      |     | 0       |       |
| total_suspected_ham_size     | int(10) unsigned |      |     | 0       |       |
| lowest_suspected_ham_score   | float            |      |     | 0       |       |
| highest_suspected_ham_score  | float            |      |     | 0       |       |
| total_suspected_ham_score    | float            |      |     | 0       |       |
| total_suspected_ham_items    | int(10) unsigned |      |     | 0       |       |
| oldest_ham_date              | datetime         | YES  |     | NULL    |       |
| newest_ham_date              | datetime         | YES  |     | NULL    |       |
| smallest_ham_size            | int(10) unsigned |      |     | 0       |       |
| largest_ham_size             | int(10) unsigned |      |     | 0       |       |
| total_ham_size               | int(10) unsigned |      |     | 0       |       |
| lowest_ham_score             | float            |      |     | 0       |       |
| highest_ham_score            | float            |      |     | 0       |       |
| total_ham_score              | float            |      |     | 0       |       |
| total_ham_items              | int(10) unsigned |      |     | 0       |       |
| oldest_wl_date               | datetime         | YES  |     | NULL    |       |
| newest_wl_date               | datetime         | YES  |     | NULL    |       |
| smallest_wl_size             | int(10) unsigned |      |     | 0       |       |
| largest_wl_size              | int(10) unsigned |      |     | 0       |       |
| total_wl_size                | int(10) unsigned |      |     | 0       |       |
| total_wl_items               | int(10) unsigned |      |     | 0       |       |
| oldest_bl_date               | datetime         | YES  |     | NULL    |       |
| newest_bl_date               | datetime         | YES  |     | NULL    |       |
| smallest_bl_size             | int(10) unsigned |      |     | 0       |       |
| largest_bl_size              | int(10) unsigned |      |     | 0       |       |
| total_bl_size                | int(10) unsigned |      |     | 0       |       |
| total_bl_items               | int(10) unsigned |      |     | 0       |       |
| oldest_suspected_spam_date   | datetime         | YES  |     | NULL    |       |
| newest_suspected_spam_date   | datetime         | YES  |     | NULL    |       |
| smallest_suspected_spam_size | int(10) unsigned |      |     | 0       |       |
| largest_suspected_spam_size  | int(10) unsigned |      |     | 0       |       |
| total_suspected_spam_size    | int(10) unsigned |      |     | 0       |       |
| lowest_suspected_spam_score  | float            |      |     | 0       |       |
| highest_suspected_spam_score | float            |      |     | 0       |       |
| total_suspected_spam_score   | float            |      |     | 0       |       |
| total_suspected_spam_items   | int(10) unsigned |      |     | 0       |       |
| oldest_fp_date               | datetime         | YES  |     | NULL    |       |
| newest_fp_date               | datetime         | YES  |     | NULL    |       |
| smallest_fp_size             | int(10) unsigned |      |     | 0       |       |
| largest_fp_size              | int(10) unsigned |      |     | 0       |       |
| total_fp_size                | int(10) unsigned |      |     | 0       |       |
| lowest_fp_score              | float            |      |     | 0       |       |
| highest_fp_score             | float            |      |     | 0       |       |
| total_fp_score               | float            |      |     | 0       |       |
| total_fp_items               | int(10) unsigned |      |     | 0       |       |
| oldest_fn_date               | datetime         | YES  |     | NULL    |       |
| newest_fn_date               | datetime         | YES  |     | NULL    |       |
| smallest_fn_size             | int(10) unsigned |      |     | 0       |       |
| largest_fn_size              | int(10) unsigned |      |     | 0       |       |
| total_fn_size                | int(10) unsigned |      |     | 0       |       |
| lowest_fn_score              | float            |      |     | 0       |       |
| highest_fn_score             | float            |      |     | 0       |       |
| total_fn_score               | float            |      |     | 0       |       |
| total_fn_items               | int(10) unsigned |      |     | 0       |       |
| oldest_spam_date             | datetime         | YES  |     | NULL    |       |
| newest_spam_date             | datetime         | YES  |     | NULL    |       |
| smallest_spam_size           | int(10) unsigned |      |     | 0       |       |
| largest_spam_size            | int(10) unsigned |      |     | 0       |       |
| total_spam_size              | int(10) unsigned |      |     | 0       |       |
| lowest_spam_score            | float            |      |     | 0       |       |
| highest_spam_score           | float            |      |     | 0       |       |
| total_spam_score             | float            |      |     | 0       |       |
| total_spam_items             | int(10) unsigned |      |     | 0       |       |
| oldest_virus_date            | datetime         | YES  |     | NULL    |       |
| newest_virus_date            | datetime         | YES  |     | NULL    |       |
| smallest_virus_size          | int(10) unsigned |      |     | 0       |       |
| largest_virus_size           | int(10) unsigned |      |     | 0       |       |
| total_virus_size             | int(10) unsigned |      |     | 0       |       |
| total_virus_items            | int(10) unsigned |      |     | 0       |       |
| oldest_bad_header_date       | datetime         | YES  |     | NULL    |       |
| newest_bad_header_date       | datetime         | YES  |     | NULL    |       |
| smallest_bad_header_size     | int(10) unsigned |      |     | 0       |       |
| largest_bad_header_size      | int(10) unsigned |      |     | 0       |       |
| total_bad_header_size        | int(10) unsigned |      |     | 0       |       |
| total_bad_header_items       | int(10) unsigned |      |     | 0       |       |
| oldest_banned_file_date      | datetime         | YES  |     | NULL    |       |
| newest_banned_file_date      | datetime         | YES  |     | NULL    |       |
| smallest_banned_file_size    | int(10) unsigned |      |     | 0       |       |
| largest_banned_file_size     | int(10) unsigned |      |     | 0       |       |
| total_banned_file_size       | int(10) unsigned |      |     | 0       |       |
| total_banned_file_items      | int(10) unsigned |      |     | 0       |       |
| oldest_oversized_date        | datetime         | YES  |     | NULL    |       |
| newest_oversized_date        | datetime         | YES  |     | NULL    |       |
| smallest_oversized_size      | int(10) unsigned |      |     | 0       |       |
| largest_oversized_size       | int(10) unsigned |      |     | 0       |       |
| total_oversized_size         | int(10) unsigned |      |     | 0       |       |
| total_oversized_items        | int(10) unsigned |      |     | 0       |       |
+------------------------------+------------------+------+-----+---------+-------+

MAIA_STATS_HISTORY

MAIA_STATS_HISTORY is a table used to store snapshots of the more relevant items from the MAIA_STATS table at (H)ourly, (D)aily, (M)onthly, and (Y)early intervals. Entries are automatically expired, so this table has a more or less fixed size of ((24 + 31 + 12 + 1/year) * users) rows.

+-------------------------+------------------+------+-----+---------------------+----------------+
| Field                   | Type             | Null | Key | Default             | Extra          |
+-------------------------+------------------+------+-----+---------------------+----------------+
| id                      | int(10) unsigned |      | PRI | NULL                | auto_increment |
| user_id                 | int(10) unsigned |      |     | 0                   |                | # maia_users.id
| type                    | char(1)          |      |     | H                   |                |
| taken_at                | datetime         |      |     | 0000-00-00 00:00:00 |                |
| total_ham_items         | int(10) unsigned |      |     | 0                   |                |
| total_ham_size          | int(10) unsigned |      |     | 0                   |                |
| total_spam_items        | int(10) unsigned |      |     | 0                   |                |
| total_spam_size         | int(10) unsigned |      |     | 0                   |                |
| total_virus_items       | int(10) unsigned |      |     | 0                   |                |
| total_virus_size        | int(10) unsigned |      |     | 0                   |                |
| total_fp_items          | int(10) unsigned |      |     | 0                   |                |
| total_fp_size           | int(10) unsigned |      |     | 0                   |                |
| total_fn_items          | int(10) unsigned |      |     | 0                   |                |
| total_fn_size           | int(10) unsigned |      |     | 0                   |                |
| total_banned_file_items | int(10) unsigned |      |     | 0                   |                |
| total_banned_file_size  | int(10) unsigned |      |     | 0                   |                |
| total_bad_header_items  | int(10) unsigned |      |     | 0                   |                |
| total_bad_header_size   | int(10) unsigned |      |     | 0                   |                |
| total_wl_items          | int(10) unsigned |      |     | 0                   |                |
| total_wl_size           | int(10) unsigned |      |     | 0                   |                |
| total_bl_items          | int(10) unsigned |      |     | 0                   |                |
| total_bl_size           | int(10) unsigned |      |     | 0                   |                |
| total_oversized_items   | int(10) unsigned |      |     | 0                   |                |
| total_oversized_size    | int(10) unsigned |      |     | 0                   |                |
+-------------------------+------------------+------+-----+---------------------+----------------+

MAIA_MAIL

MAIA_MAIL stores mail items of five types: Suspected (S)pam, (V)iruses, Banned (F)ile Attachments, (B)ad Headers, and Suspected (H)am.

+---------------+------------------+------+-----+---------------------+----------------+
| Field         | Type             | Null | Key | Default             | Extra          |
+---------------+------------------+------+-----+---------------------+----------------+
| id            | int(10) unsigned |      | PRI | NULL                | auto_increment |
| received_date | datetime         |      | MUL | 0000-00-00 00:00:00 |                |
| size          | int(10) unsigned |      |     | 0                   |                |
| sender_email  | varchar(255)     |      |     |                     |                |
| envelope_to   | text             |      |     |                     |                |
| subject       | varchar(255)     |      |     |                     |                |
| contents      | longtext         |      |     |                     |                |
| score         | float            | YES  |     | NULL                |                |
+---------------+------------------+------+-----+---------------------+----------------+

MAIA_MAIL_RECIPIENTS

MAIA_MAIL_RECIPIENTS is a one-to-many mapping of mail items to recipients. These records are deleted when a recipient rescues an item, or the item is deleted.

+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| mail_id      | int(10) unsigned |      | PRI | 0       |       | # maia_mail.id
| recipient_id | int(10) unsigned |      | PRI | 0       |       | # maia_users.id
| type         | char(1)          |      | MUL |         |       |
+--------------+------------------+------+-----+---------+-------+

MAIA_VIRUSES

MAIA_VIRUSES contains a list of the "official" names of viruses that have been detected by the virus scanners. These are the names that will be displayed in stats tables and charts.

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned |      | PRI | NULL    | auto_increment |
| virus_name | varchar(255)     |      | UNI |         |                |
| count      | int(10) unsigned |      |     | 0       |                |
+------------+------------------+------+-----+---------+----------------+

MAIA_VIRUS_ALIASES

MAIA_VIRUS_ALIASES is a one-to-many mapping of viruses to aliases for those viruses, as detected by other virus scanners.

+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| virus_id    | int(10) unsigned |      | PRI | 0       |       | # maia_viruses.id
| virus_alias | varchar(255)     |      | PRI |         |       |
+-------------+------------------+------+-----+---------+-------+

MAIA_VIRUS_DETECTED

MAIA_VIRUS_DETECTED is a one-to-many mapping of mail items to viruses found. These entries are deleted when a mail item is rescued or deleted.

+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| mail_id  | int(10) unsigned |      | PRI | 0       |       | # maia_mail.id
| virus_id | int(10) unsigned |      | PRI | 0       |       | # maia_viruses.id
+----------+------------------+------+-----+---------+-------+

MAIA_SA_RULES

MAIA_SA_RULES contains a list of all the SpamAssassin rules installed on the system, along with their text descriptions and score values.

+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned |      | PRI | NULL    | auto_increment |
| rule_name        | varchar(255)     |      | UNI |         |                |
| rule_description | varchar(255)     |      |     |         |                |
| rule_score_0     | float            |      |     | 1       |                |
| rule_score_1     | float            |      |     | 1       |                |
| rule_score_2     | float            |      |     | 1       |                |
| rule_score_3     | float            |      |     | 1       |                |
| rule_count       | int(10) unsigned |      |     | 0       |                |
+------------------+------------------+------+-----+---------+----------------+

MAIA_SA_RULES_TRIGGERED

MAIA_SA_RULES_TRIGGERED is a one-to-many mapping of mail items to SpamAssassin rules triggered by that mail item. These entries are deleted when a mail item is rescued or deleted.

+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| mail_id    | int(10) unsigned |      | PRI | 0       |       | # maia_mail.id
| rule_id    | int(10) unsigned |      | PRI | 0       |       | # maia_sa_rules.id
| rule_score | float            |      |     | 0       |       |
+------------+------------------+------+-----+---------+-------+

MAIA_BANNED_ATTACHMENTS_FOUND

MAIA_BANNED_ATTACHMENTS_FOUND is a one-to-many mapping of mail items to file attachments that were found in that mail item. These entries are deleted when a mail item is rescued or deleted.

+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| mail_id   | int(10) unsigned |      | PRI | 0       |       | # maia_mail.id
| file_name | varchar(255)     |      | PRI |         |       |
| file_type | varchar(20)      |      |     | Unknown |       |
+-----------+------------------+------+-----+---------+-------+

MAIA_THEMES

MAIA_THEMES stores information about each of the installed user-selectable themes.

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned |      | PRI | NULL    | auto_increment |
| name  | varchar(30)      |      |     |         |                |
| path  | varchar(30)      |      |     |         |                |
+-------+------------------+------+-----+---------+----------------+

MAIA_CONFIG

MAIA_CONFIG contains Maia's configuration settings, as set and modified by the super-administrator.

+----------------------------------+------------------+------+-----+---------------------------------------+
| Field                            | Type             | Null | Key | Default                               |
+----------------------------------+------------------+------+-----+---------------------------------------+
| id                               | int(10) unsigned |      | PRI | 0                                     |
| enable_user_autocreation         | char(1)          |      |     | N                                     |
| enable_false_negative_management | char(1)          |      |     | Y                                     |
| enable_stats_tracking            | char(1)          |      |     | Y                                     |
| enable_virus_scanning            | char(1)          |      |     | Y                                     |
| enable_spam_filtering            | char(1)          |      |     | Y                                     |
| enable_banned_files_checking     | char(1)          |      |     | Y                                     |
| enable_bad_header_checking       | char(1)          |      |     | Y                                     |
| enable_charts                    | char(1)          |      |     | N                                     |
| enable_spamtraps                 | char(1)          |      |     | N                                     |
| enable_stats_reporting           | char(1)          |      |     | N                                     |
| enable_address_linking           | char(1)          |      |     | Y                                     |
| enable_privacy_invasion          | char(1)          |      |     | N                                     |
| enable_username_changes          | char(1)          |      |     | Y                                     |
| internal_auth                    | char(1)          |      |     | N                                     |
| system_default_user_is_local     | char(1)          |      |     | Y                                     |
| user_virus_scanning              | char(1)          |      |     | Y                                     |
| user_spam_filtering              | char(1)          |      |     | Y                                     |
| user_banned_files_checking       | char(1)          |      |     | Y                                     |
| user_bad_header_checking         | char(1)          |      |     | Y                                     |
| admin_email                      | varchar(255)     | YES  |     | NULL                                  |
| expiry_period                    | int(10) unsigned | YES  |     | 30                                    |
| ham_cache_expiry_period          | int(10) unsigned | YES  |     | 5                                     |
| reminder_threshold_count         | int(10) unsigned | YES  |     | 100                                   |
| reminder_threshold_size          | int(10) unsigned | YES  |     | 500000                                |
| reminder_template_file           | varchar(255)     | YES  |     | reminder.tpl                          |
| reminder_login_url               | varchar(255)     | YES  |     | NULL                                  |
| newuser_template_file            | varchar(255)     | YES  |     | newuser.tpl                           |
| smtp_server                      | varchar(255)     | YES  |     | localhost                             |
| smtp_port                        | int(10) unsigned | YES  |     | 10025                                 |
| currency_label                   | varchar(15)      | YES  |     | $                                     |
| bandwidth_cost                   | float            |      |     | 0                                     |
| chart_ham_colour                 | varchar(32)      | YES  |     | #DDDDB7                               |
| chart_spam_colour                | varchar(32)      | YES  |     | #FFAAAA                               |
| chart_virus_colour               | varchar(32)      | YES  |     | #CCFFCC                               |
| chart_fp_colour                  | varchar(32)      | YES  |     | #C4CA73                               |
| chart_fn_colour                  | varchar(32)      | YES  |     | #FF7575                               |
| chart_suspected_ham_colour       | varchar(32)      | YES  |     | #FFFFB7                               |
| chart_suspected_spam_colour      | varchar(32)      | YES  |     | #FFCCCC                               |
| chart_wl_colour                  | varchar(32)      | YES  |     | #eeeeee                               |
| chart_bl_colour                  | varchar(32)      | YES  |     | #888888                               |
| chart_background_colour          | varchar(32)      | YES  |     | #B0ECFF                               |
| chart_font_colour                | varchar(32)      | YES  |     | #3D3D50                               |
| chart_autogeneration_interval    | int(10) unsigned | YES  |     | 60                                    |
| banner_title                     | varchar(255)     | YES  |     | Maia Mailguard                        |
| use_icons                        | char(1)          |      |     | Y                                     |
| use_logo                         | char(1)          |      |     | Y                                     |
| logo_url                         | varchar(255)     | YES  |     | http://www.renaissoft.com/maia/       |
| logo_file                        | varchar(255)     | YES  |     | images/maia-logotoolbar.gif           |
| logo_alt_text                    | varchar(255)     | YES  |     | Maia Mailguard Home Page              |
| virus_info_url                   | varchar(255)     | YES  |     | http://www.google.com/search          |
| virus_lookup                     | varchar(20)      | YES  |     | google                                |
| primary_report_server            | varchar(255)     | YES  |     | maia.renaissoft.com                   |
| primary_report_port              | int(10) unsigned | YES  |     | 443                                   |
| secondary_report_server          | varchar(255)     | YES  |     | NULL                                  |
| secondary_report_port            | int(10) unsigned | YES  |     | 443                                   |
| reporter_sitename                | varchar(255)     | YES  |     | NULL                                  |
| reporter_username                | varchar(50)      | YES  |     | NULL                                  |
| reporter_password                | varchar(50)      | YES  |     | NULL                                  |
| size_limit                       | int(10) unsigned | YES  |     | 1000000                               |
| oversize_policy                  | char(1)          |      |     | B                                     |
| sa_score_set                     | int(10) unsigned |      |     | 0                                     |
| key_file                         | varchar(255)     | YES  |     | blowfish.key                          |
+----------------------------------+------------------+------+-----+-------------------------------+-------+

MAIA_LANGUAGES

MAIA_LANGUAGES contains a list of the installed languages and their ISO-639 two-letter abbreviations.

+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned |      | PRI | NULL    | auto_increment |
| language_name | varchar(100)     |      |     |         |                |
| abbreviation  | char(2)          |      | UNI |         |                |
| installed     | char(1)          |      |     | N       |                |
+---------------+------------------+------+-----+---------+----------------+

Maia Installing and Configuration

The Setup of MySQL is not shown, more information can be found here. For the original install documentation click here.

Create a Database, DB-User

mysql> CREATE DATABASE maia;
mysql> GRANT ALL PRIVILEGES ON *.* TO amavis@localhost
       IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO
amavis@'%'
       IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

Create Schema

$ mysql --user=amavis --password=some_pass amavisd
$ source maia-mysql.sql

Test the Connection from Perl/DBI

You can now test the connection to the created database with the following simple perl script.

#!/usr/bin/perl -w

use strict;
use DBI;  # Here's how to include the DBI module

# Database handle
my $dbh;

# Open Database
$dbh = open_dbi();

# Fetch Rows from Table
my $sth = $dbh->prepare( 'SELECT * FROM mailaddr')
   or err_trap("Prepare Statement failed");

$sth->execute or err_trap("Cannot execute Query");

while (my @result = $sth->fetchrow_array ){
        print $result[0] . " " . $result[1] . " " . $result[2] . "\n";
}

# Cleanup
$sth->finish;
close_dbi($dbh);

sub open_dbi
{
   # Declare and initialize variables
   my $host = 'localhost';
   my $db = 'amavisd';
   my $db_user = 'amavis';
   my $db_password = 'some_pass';

   # Connect to the requested server

   my $dbh = DBI->connect("dbi:mysql:$db:$host","$db_user","$db_password",
   {RaiseError => 0,PrintError => 0})

   or err_trap("Cannot connect to the database");
   return $dbh;
}

sub err_trap
{
   my $error_message = shift(@_);
   die "$error_message\n
   ERROR: $DBI::err ($DBI::errstr)\n";
}

sub close_dbi
{
   my $dbh = shift(@_);
   $dbh->disconnect or err_trap("Cannot disconnect from the database");
}

$ ./test_connection.perl

Setup /etc/my.conf

You'll need to add (or increase) the max_allowed_packet setting in your my.cnf file. This setting determines the size of the largest mail item you'll be able to process with Maia, so make sure to set this value large enough to accommodate your needs.

max_allowed_packet = 5M

Install the Maintenance Scripts and Templates

Maia Mailguard comes with a collection of Perl scripts in the scripts subdirectory. Install these files in a place that your web server can't access (i.e. outside of your document root), so that they can't be triggered by web visitors.

# mkdir /home/amavis/maia
# mkdir /home/amavis/maia/scripts
# mkdir /home/amavis/maia/templates

Now copy the contents of Maia's scripts subdirectory into /home/amavis/maia/scripts, and put the contents of the templates subdirectory into /home/amavis/maia/templates. Set the ownership of all of these files to the amavis user and group, and tighten up the permissions on these files so that they're accessible only to the amavis user:

# chown -R amavis /home/amavis/maia
# chgrp -R amavis /home/amavis/maia
# chmod 640 /home/amavis/maia/templates/*.tpl
# chmod 750 /home/amavis/maia/scripts/*.pl
# chmod 640 /home/amavis/maia/database.cfg

Before any of the supplied scripts can be used, you need to edit the database.cfg file in the scripts subdirectory that you just installed. This is a simple file with just three items to configure--the username, password, and connection string that Perl needs in order to access the Maia Mailguard database:

# Configure your database DSN here
dsn = "DBI:mysql:maia:localhost:3306"

# Your database user's login name
username = "amavis"

# Your database user's password
password = "some_pass"

Finally, you'll want to edit the Perl scripts themselves to make sure they know where to find the database.cfg file, since they rely on it to be able to connect to the Maia Mailguard database. You'll find the configurable portion of the scripts near the top of each file, e.g.:

# CONFIGURE THIS: Location of your database.cfg file
my $cfg = "/home/amavis/maia/scripts/database.cfg";

Test your amavisd and SpamAssassin configuration

SpamAssassin and amavisd both rely on a number of Perl modules and system utilities to function properly. Before going any further, you can use the configtest.pl script to verify that you have all of the necessary modules and utilities installed, and that their versions are new enough to be useful. You'll want to run this script on all of the machines where you run SpamAssassin and amavisd.

If the script tells you that you need to upgrade or install a Perl module or utility, do so now, and then re-run the script to make sure. The output should look something like this:

# ./configtest.pl

amavisd-new:

file(1)              :     4.06 : OK
Archive::Tar         :     1.23 : OK
Archive::Zip         :     1.14 : OK
Compress::Zlib       :     1.33 : OK
Convert::TNEF        :     0.17 : OK
Convert::UUlib       :     1.03 : OK
MIME::Base64         :     3.05 : OK
MIME::Parser         :    5.415 : OK
Mail::Internet       :     1.65 : OK
Net::Server          :     0.87 : OK
Net::SMTP            :     2.29 : OK
Digest::MD5          :     2.33 : OK
IO::Stringy          :    2.109 : OK
Time::HiRes          :     1.65 : OK
Unix::Syslog         :    0.100 : OK
DBI                  :     1.46 : OK
DBD::mysql           :   2.9004 : OK
DBD::Pg              :      N/A : NOT INSTALLED (required if you want to use PostgreSQL)

SpamAssassin:

Mail::SpamAssassin   : 3.000001 : OK
File::Spec           :     0.86 : OK
Pod::Usage           :     1.16 : OK
HTML::Parser         :     3.37 : OK
DB_File              :    1.810 : OK
Net::DNS             :     0.48 : OK
Digest::SHA1         :     2.10 : OK

Maia Mailguard:

Crypt::Blowfish      :     2.09 : OK
Crypt::CBC           :     2.12 : OK

Database DSN test    : PASSED

If there are any missing perl modules, download them from http://search.cpan.org and install them.

# spamassassin -D --lint

Install SQL based Bayesian Storage

SpamAssassin can now store users' bayesian filter data in a SQL database. The most common use for a system like this would be for users to be able to have per user bayesian filter data on systems where users may not have a home directory to store the data. In order to activate the SQL based bayesian storage you have to configure spamassassin and spamd to use a different bayes storage module. This can be done via a setting in the global configuration file.

wget http://spamassassin.apache.org/full/3.0.x/dist/sql/awl_mysql.sql
wget http://spamassassin.apache.org/full/3.0.x/dist/sql/bayes_mysql.sql
mysql --user=amavis --password=xxxxx maia
mysql> source /var/amavisd/download/awl_mysql.sql;
mysql> source /var/amavisd/download/bayes_mysql.sql;
mysql> quit;

Edit /etc/mail/spamassassin/local.cf  to setup the SQL based bayesian storage:

# Enable Bayes auto-learning

# Sitewide in File
# bayes_path              /usr/share/spamassassin/bayes/
# bayes_file_mode         0666

# Per-User in MySQL
bayes_store_module                Mail::SpamAssassin::BayesStore::SQL
bayes_sql_dsn                     DBI:mysql:maia:opal.akadia.com:3306
bayes_sql_username                amavis
bayes_sql_password                xxxxxx

Testing SQL/Bayesian

To test your SQL setup, and debug any possible problems, you should start spamd with the -D option, which will keep spamd in the foreground, and will output debug message to the terminal. You should then test spamd with a message by calling spamc. You can use the sample-spam.txt file with the following command:

spamd -D
cat sample-spam.txt | spamc

Watch the debug output from spamd and look for the following debug line:

debug: bayes: Database connection established
debug: bayes: Using username: <username>

If you do not see the above text, then the SQL query was not successful, and you should see any error messages reported.

Install SQL based Auto Whitelists

SpamAssassin can now load users' auto-whitelists from a SQL database. The most common use for a system like this would be for users to be able to have per user auto-whitelists on systems where users may not have a home directory to store the whitelist DB files. In order to activate the SQL based auto-whitelist you have to configure spamassassin and spamd to use a different whitelist factory.

# Autowhitelists

auto_whitelist_factory            Mail::SpamAssassin::SQLBasedAddrList
user_awl_dsn                      DBI:mysql:maia:opal.akadia.com:3306
user_awl_sql_username             amavis
user_awl_sql_password             xxxxxx

Testing Auto Whitelists

To test your SQL setup, and debug any possible problems, you should start spamd with the -D option, which will keep spamd in the foreground, and will output debug message to the terminal. You should then test spamd with a message by calling spamc. You can use the sample-spam.txt file with the following command:

spamd -D
cat sample-spam.txt | spamc

Watch the debug output from spamd and look for the following debug line:

SQL Based AWL: Connected to <your dsn>

If you do not see the above text, then the SQL query was not successful, and
you should consult any error messages reported.

Load your SpamAssassin Rules and setup Cron Jobs

Maia Mailguard needs to index all of the SpamAssassin rules you have installed on your system, so that these rules, their descriptions and scores can be loaded into a database table. To do this, use one of the scripts in the scripts subdirectory called load-sa-rules.pl.

Before you run this script, edit it to make sure that the SpamAssassin rule directories are set properly, e.g.:

# CONFIGURE THIS: SpamAssassin directories to search for rules
# files (*.cf and user_prefs)

my $local_cf_dir = "/etc/mail/spamassassin";
my $system_rules_dir = "/usr/share/spamassassin";
my $user_rules_dir = "/home/amavis/.spamassassin";

$local_cf_dir should be set to the directory that contains your site's local.cf file.
$system_rules_dir should be set to the directory that contains SpamAssassin's own *.cf files.
$user_rules_dir should be set to the amavis user's SpamAssassin directory, where its (optional) user_prefs file may be found.

Run it without arguments, e.g.

# ./load-sa-rules.pl

If all goes well, this script will scan the three specified subdirectories for *.cf and user_prefs files, reading any rule names, descriptions and scores it finds and storing them in the Maia Mailguard database. You can safely run this script anytime you add new SpamAssassin rules or update your SpamAssassin rule files with new scores. The script will not add the same rule twice, but it will update the score value of a rule that it has seen before. If you use a scheduled job to fetch updated versions of popular SpamAssassin rule sets, for example, add this script to the end of your update job to make sure the changes are picked up by Maia Mailguard.

Setup Cron for the "amavis" User as follows:

# Maia Scripts
0 * * * * /home/amavis/maia/scripts/process-quarantine.pl --learn --limit=2 > /var/log/process-quarantine.log 2>&1
0 1 * * * /home/amavis/maia/scripts/expire-quarantine-cache.pl > /var/log/expire-quarantine-cache.log 2>&1
0 2 * * * /home/amavis/maia/scripts/send-quarantine-reminders.pl > /var/log/send-quarantine-reminders.log 2>&1
0 3 * * * /home/amavis/maia/scripts/load-sa-rules.pl > /var/log/load-sa-rules.log 2>&1

Maintenance Scripts

Maia Mailguard is mostly PHP-based, but it does rely on a few Perl scripts to run on the machine(s) where amavisd and SpamAssassin are installed.

Note: Before using any of these scripts, be sure to edit them to set a handful of configuration options at the beginning of each script.

process-quarantine.pl

When users "confirm" spam or ham using Maia Mailguard, the mail items are marked appropriately as such to await processing by this script. With confirmed spam and ham distinguished in this way, you can use this script to post-process these items at regular intervals (using a scheduler like cron). At the very least you can use this to train the site-wide Bayes engine with specific examples of known spam and known ham. Optionally, if you have Razor2, DCC, and/or Pyzor configured in SpamAssassin, you can use this script to also report spam to these networks. Note that you must configure Razor2/DCC/Pyzor specifically for reporting, as these services often require reporters to be registered with them. When the script is done training the Bayes engine and reporting the spam items, the spam and ham items are deleted, which helps to keep the database from getting filled up.

This script should be scheduled to run on an hourly basis, to make sure that learning and reporting takes place frequently, since these are important steps in maintaining/improving the effectiveness of your spam filter.

expire-quarantine-cache.pl

Maia Mailguard system encourages the end-users to manage their own quarantine areas and ham caches on a regular basis, to make sure no legitimate mail has been held up in quarantine, and so on. That said, not all users will be diligent about checking their quarantine areas in a timely manner, and if left untended, this can result in a lot of wasted storage space.

By setting the expiry period for quarantined items and cached ham from Maia Mailguard's System Configuration page, and running this script from a daily cron job, you can force the expiry of quarantined items that have not been dealt with in a certain amount of time, to make sure they don't sit in your database indefinitely.

Schedule this script to run once per day, preferably at an off-peak time. It will check the ages of all unconfirmed spam and ham in the quarantine/cache, and delete items that are older than your specified thresholds. The thresholds themselves are set by the superadministrator from the System Configuration page.

send-quarantine-reminders.pl

If you set a reminder threshold count or size from Maia Mailguard's System Configuration page, you'll want to add this script to your cron schedule to ensure that reminder e-mails are sent out to users at regular intervals (e.g. once per week) when the number of items in their quarantine area exceeds this threshold count, or when the total size of the items in their quarantine area exceeds the threshold size. Users can individually opt out of these reminders, of course, and for some types of users (e.g. system accounts, spam-traps, etc.) you'll want to disable reminder notices, but for most regular users this setting should probably be enabled by default, to ensure that users are aware of Maia Mailguard and the status of their quarantine area.

load-sa-rules.pl

This script scans certain specified directories on your server, looking for SpamAssassin rules to add to the Maia Mailguard database. If it finds a rule it doesn't know about yet, the rule gets added to the database. If it finds a known rule, it updates the rule's score as necessary, in the event it may have changed. You should run this script as a "finishing step" every time you update your SpamAssassin rules, to ensure that Maia Mailguard always has the most up-to-date set of SpamAssassin rules and scores in its database.

stats-snapshot.pl

This script takes a "snapshot" of your current Maia stats table, so that it can be stored and referenced later by scripts and processes that generate interval-based graphical charts. The data is recorded in the maia_stats_history table, with one row per hour, one row per day, one row per month, and one row per year. This script needs to be scheduled to run at the beginning of every hour.

generate-key.pl

If you want to take advantage of Maia's Blowfish encryption features, you'll need to use this script to generate your site's encryption key. This is a one-time task, which you can do by running this script and redirecting the output to a file. Be sure to create a backup copy of the key file you generate, and keep it in a safe place in case you lose the original.

Install the PHP scripts

Decide where you want to install the PHP scripts. This should be a subdirectory somewhere within your web tree. There should be two subdirectories beneath the directory with your PHP scripts--one called images, which contains the logos, icons, and other graphics, and the other called locale, which contains the language-specific text used by the rest of the scripts. For security reasons set the permissions for configtest.php and internal-init.php to "----------".

# cd /home/amavis/maia/mail
# ls  -l

-rw-r--r--    1 root     root         5941 Jan 21  2004 admindex.php
-rw-r--r--    1 root     root         8429 Jan 31  2004 admindomains.php
-rw-r--r--    1 root     root        15701 May  1  2004 adminhelp.php
-rw-r--r--    1 root     root         9428 Jun 30  2004 adminlanguages.php
-rw-r--r--    1 root     root         6169 Jan 21  2004 adminstats.php
-rw-r--r--    1 root     root        46565 Jun 30  2004 adminsystem.php
-rw-r--r--    1 root     root        16399 Jan 31  2004 adminusers.php
-rw-r--r--    1 root     root         8242 Jan 21  2004 adminviruses.php
-rw-r--r--    1 root     root        15188 Jul  5  2004 attachment-quarantine.php
-rw-r--r--    1 root     root         4147 Jan 21  2004 authcheck.php
-rw-r--r--    1 root     root        12066 Mar  3  2004 auth.php
-rw-r--r--    1 root     root        14189 Dec 28 12:27 config.php
----------    1 root     root       159346 Jul  5  2004 configtest.php
-rw-r--r--    1 root     root         4402 Jan 21  2004 confirmspam.php
-rw-r--r--    1 root     root         3654 Jul  2  2004 constants.php
-rw-r--r--    1 root     root       121301 Jul  1  2004 db.php
-rw-r--r--    1 root     root         4455 Jan 21  2004 deleteitem.php
-rw-r--r--    1 root     root         7672 Jan 23  2004 deliver.php
-rw-r--r--    1 root     root        44675 Jul  1  2004 display.php
-rw-r--r--    1 root     root        21876 Jun 24  2004 domainsettings.php
-rw-r--r--    1 root     root         5609 Feb 15  2004 encrypt.php
-rw-r--r--    1 root     root         4625 Jan 21  2004 falsenegative.php
-rw-r--r--    1 root     root         1406 Jan 21  2004 favicon.ico
-rw-r--r--    1 root     root        16384 Jul  5  2004 ham-cache.php
-rw-r--r--    1 root     root        14593 Jul  5  2004 header-quarantine.php
-rw-r--r--    1 root     root        16931 Jan 21  2004 help.php
drwxr-xr-x    2 root     root         4096 Jul  5  2004 images/
-rw-r--r--    1 root     root         4136 Jan 21  2004 index.php
----------    1 root     root         9646 Mar  8  2004 internal-init.php
drwxr-xr-x    7 root     root         4096 Jul  5  2004 locale/
-rw-r--r--    1 root     root         7045 Mar  3  2004 login.php
-rw-r--r--    1 root     root         4427 Jan 21  2004 logout.php
-rw-r--r--    1 root     root         7744 May 19  2004 mailtools.php
-rw-r--r--    1 root     root        10517 Feb  1  2004 mime.php
-rw-r--r--    1 root     root         4628 Jan 21  2004 public.php
-rw-r--r--    1 root     root         9424 Feb  2  2004 quarantine.php
-rw-r--r--    1 root     root         6202 Jan 21  2004 reportspam.php
-rw-r--r--    1 root     root         4254 Jan 21  2004 rulestats.php
-rw-r--r--    1 root     root        28248 May 19  2004 settings.php
-rw-r--r--    1 root     root         8017 Jan 21  2004 smtp.php
-rw-r--r--    1 root     root        16537 Jul  5  2004 spam-quarantine.php
-rw-r--r--    1 root     root         4531 Jan 21  2004 stats.php
-rw-r--r--    1 root     root         5060 Jan 21  2004 tooltips.js
-rw-r--r--    1 root     root        23093 Feb 19  2004 viewmail.php
-rw-r--r--    1 root     root         8319 Jan 21  2004 virus.php
-rw-r--r--    1 root     root        15250 Jul  5  2004 virus-quarantine.php
-rw-r--r--    1 root     root         4253 Jan 21  2004 virusstats.php
-rw-r--r--    1 root     root         6632 Jan 31  2004 wblist.php
-rw-r--r--    1 root     root         9861 Jan 29  2004 welcome.php
-rw-r--r--    1 root     root        10445 Jan 31  2004 xadmindomains.php
-rw-r--r--    1 root     root         6002 Jul  1  2004 xadminlanguages.php
-rw-r--r--    1 root     root         6209 Jan 21  2004 xadminstats.php
-rw-r--r--    1 root     root        12099 Jun 30  2004 xadminsystem.php
-rw-r--r--    1 root     root        18368 Mar  3  2004 xadminusers.php
-rw-r--r--    1 root     root         6806 Jan 21  2004 xadminviruses.php
-rw-r--r--    1 root     root        18520 Jun 24  2004 xdomainsettings.php
-rw-r--r--    1 root     root         4186 Jan 21  2004 ximpersonate.php
-rw-r--r--    1 root     root         7171 Mar  3  2004 xlogin.php
-rw-r--r--    1 root     root         5310 Jan 21  2004 xquarantine.php
-rw-r--r--    1 root     root         5200 Jan 22  2004 xreportspam.php
-rw-r--r--    1 root     root        22243 Jun 24  2004 xsettings.php
-rw-r--r--    1 root     root         6663 Jan 31  2004 xwblist.php

Setup Apache, MySQL and PHP

The installation and configuration of Apache / PHP is not explained in detail here, see original documentation:

Apache (2.2.4)

./configure --prefix=/usr/local/apache \
--enable-mods-shared=most \
--enable-ssl=shared \
--enable-mime-magic \
--enable-cern-meta \
--enable-proxy \
--enable-proxy-connect \
--enable-proxy-ftp \
--enable-proxy-http \
--enable-usertrack \
--enable-unique-id \
--enable-deflate

make
make install

MySQL (5.0.37)

groupadd mysql
useradd -g mysql mysql

gunzip < mysql-VERSION.tar.gz | tar -xvf -

cd mysql-VERSION
./configure --prefix=/usr/local/mysql \
--localstatedir=/usr/local/mysql/data \
--enable-thread-safe-client \
--enable-local-infile \
--with-unix-socket-path=/tmp/mysql.sock \
--with-extra-charsets=all \
--with-big-tables
make
make install


cp support-files/my-medium.cnf /etc/my.cnf
cd /usr/local/mysql
bin/mysql_install_db --user=mysql

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h dorint password 'new-password'

PHP4 (5.2.1)

./configure --with-apxs2=/usr/local/apache/bin/apxs \
--with-mysql=/usr/local/mysql \
--with-mysql-sock=/tmp/mysql.sock \
--enable-ftp \
--enable-wddx \
--enable-bcmath \
--with-gd

make
make install
cp php.ini-dist /usr/local/lib/php.ini

Edit your httpd.conf to load the PHP module. The path on the right hand side of the LoadModule statement must point to the path of the PHP module on your system. The make install from above may have already added this for you, but be sure to check.

LoadModule php5_module modules/libphp5.so

Tell Apache to parse certain extensions as PHP.

AddType application/x-httpd-php .php .phtml
AddType application/x-httpd-php-source .phps

Use your normal procedure for starting the Apache server, e.g.:

/usr/local/apache/bin/httpd -k start -D SSL -D PHP4

Setup Document Root

<Directory />
   Options -All -Multiviews FollowSymLinks
   AllowOverride None
   <IfModule mod_access.c>
     Order deny,allow
     Deny from all
   </IfModule>
</Directory>

DocumentRoot /home/amavis/maia/php
<Directory   /home/amavis/maia/php>
   Options -All -Multiviews
   AllowOverride None
   <IfModule mod_access.c>
      Order allow,deny
      Allow from all
   </IfModule>
</Directory>

Authenticating with Maia's Internal SQL Databasen

The config.php file contains a few basic configuration settings that must be adjusted for your site before you can configure the rest of Maia Mailguard's settings. In particular, the language defaults, database connection information and user authentication parameters must be hard-coded here.

If you want Maia Mailguard to handle its own user database, rather than authenticate against an external source, select the internal method. With this method, Maia stores usernames and passwords in its own database.

When new accounts are created, Maia automatically sends the new user an e-mail containing a temporary username and password, which she can change once she logs in (and at any time thereafter).

This authentication method is handy for situations where the other external authentication methods would be impractical, such as hosting mail filtering services for other companies downstream, where there would otherwise be many different sources to authenticate against.

$maia_sql_dsn = "mysql://amavis:some_pass@tcp(localhost:3306)/maia";
$auth_method = "internal";

Test your PHP and database configuration

At this point, you'll want to verify that your web server has its PHP correctly compiled, all the necessary PEAR modules installed, and the database DSN correctly configured in config.php. You can do this by loading the configtest.php page in your browser.

This script will verify that the prerequisites are properly installed on your web server, and that PHP scripts can successfully communicate with your database server. It will point out any missing components or outdated version numbers and offer some advice for fixing the problem. Fix any problems that get highlighted at this stage and reload the script to verify that they've been properly fixed before proceeding.

http://<your-host>/configtest.php

Patch amavisd-new

You need a patched version of the /usr/local/bin/amavisd perl script. This can be downloaded from https://secure.renaissoft.com/cgi-bin/trac.cgi/browser/trunk. The perl script is called: amavisda-maia for amavisd-new-2.2.0. Download amavisda-maia and copy it to /usr/local/bin/amavisd.

# cp amavisda-maia  /usr/local/bin/amavisd

Setup /etc/amavisd.conf

# Setup Connection
@lookup_sql_dsn = (
   ['DBI:mysql:database=amavisd;host=127.0.0.1;port=3306','amavis','some_pass']
);

# To disable SQL white/black list, set to undef (otherwise comment-out
# the following statement, leaving it at the default value).

# $sql_select_white_black_list = undef;

Test it!

/usr/local/bin/amavisd -u amavis debug

....
....
SPAM-KILL, <martin dot zahn at akadia dot ch> -> <zahn@akadia.dyndns.org>,
           hits=x, kill=3, BLACKLISTED
header: X-Spam-Status: No, hits=x tag=2 tag2=3 kill=3 BLACKLISTED \n
header: X-Spam-Level: \n
header: X-Envelope-To: <zahn@akadia.dyndns.org>\n
header: X-Envelope-From: <martin dot zahn at akadia dot ch>\n
DO_QUARANTINE, sender:
local delivery: <> -> <spam-quarantine>,
mbx=/var/virusmails/spam-f9ecad0e5e3185eebe68-20041217-145953-17253-01.gz
....
....

Configure Postfix for Amavisd-New

In the daemonized approach, amavisd listens on a TCP port bound to the loopback address (127.0.0.1). On receiving a message from the Internet, Postfix connects to amavisd and relays the message using the SMTP or LMTP protocol.

amavisd can reject the message during the SMTP/LMTP transaction, which will cause Postfix to bounce the message, or amavisd can accept the message, modify it, and reinject it by SMTP. To prevent mail loops, Postfix must run a second smtpd daemon, bound to another TCP port on the loopback address. The second smtpd is configured to accept messages without rerunning the filter (or performing the checks that would normally be performed on a message received from the Internet).

Define a new mail transport

Define a new mail transport in master.cf that sends mail to amavisd. In the following example, the transport is called scan and is defined as a Unix service that will use Postfix's smtp command. You can use the disable_dns_lookups option to save overhead, as you know that the transport will be configured to relay mail to your loopback IP address, so amavisd will never need to perform a DNS MX lookup. The example uses the maxproc feature in master.cf to limit the number of messages that can use this mail transport at one time to two.

# ==========================================================================
# service type  private unpriv  chroot  wakeup  maxproc command + args
#               (yes)   (yes)   (yes)   (never) (100)
# ==========================================================================

# Replaced by M.Zahn for After Queue Content Filter Checking
# smtp      inet  n       -       n       -       -       smtpd

# Instruct smtpd on Port 25 to deliver mail to amavisd on 127.0.0.1 Port 10024
smtp      inet  n       -       n       -       2       smtpd
    -o content_filter=scan:[127.0.0.1]:10024
    -o receive_override_options=no_address_mappings

# This is the mail transport used above. It is in fact an instance of the
# Postfix SMTP client with slightly different configuration parameters
scan      unix  -       -       n       -       2       lmtp
    -o disable_dns_lookups=yes
    -o lmtp_send_xforward_command=yes
    -o lmtp_data_done_timeout=1200

If you are using Postfix 2.0 or later, you can define the spamcheck transport to use Postfix's lmtp command instead of smtp. The LMTP protocol has some advantages over SMTP—notably, LMTP servers (including amavisd) can return individual accept/refuse codes for each message recipient during an LMTP transaction. Postfix's lmtp client can also cache connections to an LMTP server for greater performance.

Define a new mail transport that receives mail from the daemon in master.cf. This transport will use Postfix's smtpd daemon and is defined by the IP address and port number on which it will listen (127.0.0.1 and 10025, respectively). smtpd is an inet service, and many option parameters are provided to prevent further filtering and to restrict access to this mail transport to the local host only. Here is an example of such a definition in master.cf:

# Injecting mail back into Postfix after content filter
localhost:10025 inet n  -       n       -       2       smtpd
    -o content_filter=
    -o myhostname=localhost.akadia.com
    -o local_recipient_maps=
    -o relay_recipient_maps=
    -o smtpd_restriction_classes=
    -o smtpd_client_restrictions=
    -o smtpd_helo_restrictions=
    -o smtpd_sender_restrictions=
    -o smtpd_recipient_restrictions=permit_mynetworks,reject
    -o mynetworks=127.0.0.0/8
    -o strict_rfc821_envelopes=yes
    -o smtpd_error_sleep_time=0
    -o smtpd_soft_error_limit=1001
    -o smtpd_hard_error_limit=1000
    -o smtpd_client_connection_count_limit=0
    -o smtpd_client_connection_rate_limit=0
    -o receive_override_options=no_unknown_recipient_checks,no_header_body_checks
    -o smtpd_authorized_xforward_hosts=127.0.0.0/8

The -o myhostname=localhost.yourdomain option is important if the content filter issues the SMTP HELO command with the same hostname that it originally received from Postfix. If Postfix sees a HELO from itself, it rejects the connection to avoid a mail loop. By telling the new smtpd that its hostname is something else, you prevent this problem.

Filtering Before Address-Rewriting

The Postfix queue manager invokes amavisd once it has queued a message. A potential problem is that the messages to be filtered have passed through the cleanup service on their way to the queue, and cleanup performs virtual address lookups and address canonicalization—that is, cleanup may rewrite addresses in message headers. Accordingly, the message that Postfix sends to amavisd  (and thus to SpamAssassin) to check is not exactly the same as the message that Postfix received.

Since Postfix snapshot 2.0.13-20030706 there is a new parameter receive_override_options that eliminates the need for two cleanup services in some more straightforward cases (not all features of having two cleanup services are available).

The idea is to use:

-o receive_override_options=no_address_mappings

for main incoming smtpd service, and the:

-o receive_override_options=no_header_body_checks,no_unknown_recipient_checks

for the post-content-filter smtpd service on port 10025.

ClamAV Virus Scanning

Clam AntiVirus is an anti-virus toolkit for UNIX, designed for e-mail scanning on mail gateways. It provides a flexible and scalable multi-threaded daemon, a command line scanner, and an advanced tool for automatic database updating via Internet. The package also includes a virus scanner shared library.

clamd - is the anti-virus daemon, it listens for incoming connections on Unix or TCP socket and scans files or directories on demand. It's a part of the Clam AntiVirus package. It reads its configuration information from a clamd.conf file. It provides on-access scanning feature (clamuko) under Linux.

clamdscan - scan files and directories against viruses using Clam AntiVirus Daemon clamd. clamdscan is a simple clamd client which may be used as a clamscan replacement. clamdscan accepts all the options implemented in clamscan but most of them will be ignored because its scanning abilities only depend on clamd.

clamscan - scan files and directories against viruses. clamscan is a command line anti-virus scanner.

freshclam - update the virus databases over the Internet.

Features

  • Licensed under the GNU General Public License, Version 2
  • POSIX compliant, portable
  • Fast scanning
  • Supports on-access scanning (Linux and FreeBSD only)
  • Detects over 20000 viruses, worms, and trojans, including Microsoft Office and MacOffice macro viruses
  • Scans within archives and compressed files (also protects against archive bombs), built-in support includes: Zip, RAR (2.0), Tar, Gzip, Bzip2, MS OLE2, MS Cabinet Files, MS CHM (Compiled HTML), MS SZDD compression format
  • Powerful mail scanner
  • Advanced database updater with support for digital signatures and DNS based database version queries.

Installation

Download the sources

tar xzvf clamav-0.81rc1.tar.gz
./configure --prefix=/usr/local/clamav --disable-clamav
make
make check
make install
cd /usr/local
mv clamav clamav-0.81rc1
ln -s clamav-0.81rc1 clamav
groupadd -g 503 clamav
useradd -u 503 -g 503 -d /home/amavis -c "Clam AntiVirus Owner" -s /sbin/nologin clamav

Add the user «clamav» to the «amavis» group in /etc/group:

amavis:x:502:clamav

Test the Installation

cd /usr/local/clamav/bin
./clamscan


/usr/local/clamav-0.81rc1/bin/clamscan: OK
/usr/local/clamav-0.81rc1/bin/clamdscan: OK
/usr/local/clamav-0.81rc1/bin/freshclam: OK
/usr/local/clamav-0.81rc1/bin/sigtool: OK
/usr/local/clamav-0.81rc1/bin/clamav-config: OK

----------- SCAN SUMMARY -----------
Known viruses: 29545
Scanned directories: 1
Scanned files: 5
Infected files: 0
Data scanned: 0.59 MB
I/O buffer size: 131072 bytes
Time: 0.641 sec (0 m 0 s)

Clamd Configuration ( /usr/local/clamav/etc/clamd.conf )

LogFile /var/log/clamd/clamd.log
LogFileMaxSize 2M
LogTime

LogSyslog
LogFacility LOG_MAIL
LogVerbose
PidFile /home/amavis/var/clamd.pid
TemporaryDirectory /var/tempfs
LocalSocket /home/amavis/var/amavisd.sock

FixStaleSocket
MaxThreads 20
SelfCheck 3600

User amavis
AllowSupplementaryGroups
ScanPE
DetectBrokenExecutables
ScanOLE2
ScanHTML
ScanArchive
ArchiveBlockMax
ArchiveMaxFileSize 10M

Logfile Permissions

cd /var/log
mkdir clamd
chown amavis:amavis clamd
chmod 770 clamd

drwxrwx--- 2 amavis amavis 4096 Jan 26 05:00 clamd

Pidfile Permissions

cd /home/amavis
chown amavis:amavis var
chmod 770 var


drwxrwx--- 3 amavis amavis 4096 Jan 26 08:57 var

Test the Daemon

/usr/local/clamav/sbin/clamd --config-file=/usr/local/clamav/etc/clamd.conf

cat /var/log/clamd/clamd.log
Wed Jan 26 09:37:42 2005 -> +++ Started at Wed Jan 26 09:37:42 2005
Wed Jan 26 09:37:42 2005 -> clamd daemon 0.81rc1 (OS: linux-gnu, ARCH: i386, CPU: i686)
Wed Jan 26 09:37:42 2005 -> Log file size limited to 2097152 bytes.
Wed Jan 26 09:37:42 2005 -> Verbose logging activated.
Wed Jan 26 09:37:42 2005 -> Running as user amavis (UID 502, GID 502)
Wed Jan 26 09:37:42 2005 -> Reading databases from /usr/local/clamav/share/clamav
Wed Jan 26 09:37:42 2005 -> Protecting against 29810 viruses.
Wed Jan 26 09:37:42 2005 -> Unix socket file /home/amavis/var/amavisd.sock
Wed Jan 26 09:37:42 2005 -> Setting connection queue length to 15
Wed Jan 26 09:37:42 2005 -> Listening daemon: PID: 18474
Wed Jan 26 09:37:42 2005 -> Archive: Archived file size limit set to 10485760 bytes.
Wed Jan 26 09:37:42 2005 -> Archive: Recursion level limit set to 8.
Wed Jan 26 09:37:42 2005 -> Archive: Files limit set to 1000.
Wed Jan 26 09:37:42 2005 -> Archive: Compression ratio limit set to 250.
Wed Jan 26 09:37:42 2005 -> Archive support enabled.
Wed Jan 26 09:37:42 2005 -> Archive: RAR support disabled.
Wed Jan 26 09:37:42 2005 -> Archive: Blocking archives that exceed limits.
Wed Jan 26 09:37:42 2005 -> Portable Executable support enabled.
Wed Jan 26 09:37:42 2005 -> Detection of broken executables enabled.
Wed Jan 26 09:37:42 2005 -> Mail files support enabled.
Wed Jan 26 09:37:42 2005 -> OLE2 support enabled.
Wed Jan 26 09:37:42 2005 -> HTML support enabled.
Wed Jan 26 09:37:42 2005 -> Self checking every 3600 seconds.

telnet localhost 3310
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.

PING
PONG
Connection closed by foreign host.

telnet localhost 3310
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.

VERSION
ClamAV 0.81rc1/674/Tue Jan 18 21:27:28 2005
Connection closed by foreign host.

Integrate the Daemon into Amavisd-New

The communication between Amavisd-New and ClamAV is handled by a Unix Socket. Therefore the name of the Unix Socket must correspond in /etc/amavisd.conf for Amavisd-New and in /usr/local/clamav/etc/clamd.conf for ClamAV.

/etc/amavisd.conf

## ANTI-VIRUS AND INVALID/FORBIDDEN CONTENTS CONTROLS
# Make sure that the name of the unix socket in the av_scanners entry
# (/home/amavis/var/amavisd.sock) correspond with the LocalSocket
# entry in /usr/local/clamav/etc/clamd.con.
# (LocalSocket /home/amavis/var/amavisd.sock)

# ***** uncomment to DISABLE anti-virus code ******
# @bypass_virus_checks_maps = (1);
@av_scanners = (
  ['ClamAV-clamd',
  \&ask_daemon, ["CONTSCAN {}\n", "/home/amavis/var/amavisd.sock"],
  qr/\bOK$/, qr/\bFOUND$/,
  qr/^.*?: (?!Infected Archive)(.*) FOUND$/ ]
);

/usr/local/clamav/etc/clamd.conf

# Path to a local socket file the daemon will listen on.
LocalSocket /home/amavis/var/amavisd.sock

Restart Amavisd-New and check the Logfile for:

Jan 25 14:29:14 rabbit amavis[29332]: ANTI-VIRUS code loaded
Jan 25 14:29:14 rabbit amavis[29332]: Using internal av scanner code for (primary) ClamAV-clamd

Automatically Start/Stop Daemon

#!/sbin/runscript
# Akadia AG, Arvenweg 4, CH-3604 Thun
# ----------------------------------------------------------------------
# File:       clamd
#
# Autor:      Martin Zahn / 26.01.2005
#
# Purpose:    Start / Stop clamd (Clam AntiVirus Daeomn)
#
# Platform:   GENTOO Linux
# ----------------------------------------------------------------------

depend() {
    need postfix mysql
}

start() {
    ebegin "Try to start Clam AntiVirus daemon ..."
    if [ -x /usr/local/clamav/sbin/clamd ]
    then
        /usr/local/clamav/sbin/clamd --config-file=/usr/local/clamav/etc/clamd.conf
    else
    echo "Clam AntiVirus daemon does not exist"
    fi
    eend $? "Failed to start Clam AntiVirus daemon"
}

stop() {
    ebegin "Try to stop Amavisd-New daemon ..."
    kill -SIGTERM `cat /home/amavis/var/amavisd.pid`
    eend $? "Failed to stop Amavisd-New daemon"
}

Setting up auto-updating

freshclam is the default database updater for Clam AntiVirus. It can work in two modes:

  • interactive - from command line, verbosely
  • daemon - alone, silently

When started by a superuser it drops privileges and switches to the clamav user. freshclam uses the database.clamav.net round-robin DNS which automatically selects a database mirror. freshclam is an advanced tool: it supports database version verification through DNS, proxy servers (with authentication), digital signatures and various error scenarios.

Configurationfile: /usr/local/clamav/etc/freshclam.conff

UpdateLogFile /var/log/clamd/freshclam.log
LogVerbose
LogSyslog
LogFacility LOG_MAIL
PidFile /home/amavis/var/freshclam.pid
DNSDatabaseInfo current.cvd.clamav.net
DatabaseMirror db.ch.clamav.net
DatabaseMirror database.clamav.net
Checks 4

Cron Setup for user root

# Refresh Clam AntiVirus
0 5,12,20 * * * /usr/local/clamav/bin/freshclam > /var/log/clamd/freshclam-cron.log 2>&1

Sender Policy Framework SPF

Overview

Much of this article can be found on http://spf.pobox.com. Have you ever gotten spam from yourself? We have, and we have been thinking hard about how to stop it! We didn't send it. It came from a spammer. If we could stop spammers from forging mail, we could easily tell spam from ham and block the bad stuff.

Sender Policy Framework (SPF) makes it easy for a domain, whether it's an ISP, a business, a school or a vanity domain, to say, «I only send mail from these machines. If any other machine claims that I'm sending mail from there, they're lying.»

As an example, akadia.com is the sending domain, and arkum.ch is the receiver. Akadia.com publishes an SPF record, specifying which computers on the Internet can send mail as user@akadia.com.

  1. When a real Akadia user sends mail, arkum.ch receives the message from an Akadia server.
     
  2. Arkum checks Akadia's SPF record, to make sure the server is allowed to send mail from Akadia.
     
  3. If the server is listed, so Arkum gives the message a pass. If the server is not listed, so Arkum gives the message a fail. When a spammer forges mail from Akadia, Arkum receives the messages from an outside server.

DNS Setup (Publishing SPF)

Suppose akadia.com wants to publish SPF, so it adds the following line to its DNS zone file:

akadia.com. IN TXT "v=spf1 a mx ptr -all"

The v=spf1 version string identifies this as an SPF record. The -all means reject all mail by default. Domains that don't send any mail,  can get by with simply v=spf1 -all. But if the domain does send mail, it declares mechanisms that describe how legitimate mail should look. Mechanisms go in the middle, before
 -all. The first mechanism to match provides a result for the SPF query. -all always matches and so belongs at the end.

  • A: the A mechanism means the IP address of akadia.com is permitted to send mail from akadia.com. If you want to say the IP address of some-other.com is permitted, you can say a:some-other.com. You can use as many A mechanisms as you want.
     
  • MX: the MX mechanism means the MX servers for akadia.com all are permitted to send mail from akadia.com. If you want to say the MX servers for some-other.com are permitted, you can say mx:some-other.com. You can use as many MX mechanisms as you want.
     
  • PTR: the PTR mechanism says if a host has a PTR record that ends in akadia.com, it is permitted to send mail from akadia.com. If you want to say servers whose names end in some-other.com are permitted to send mail from akadia.com, you can say ptr:some-other.com. You can use as many PTR mechanisms as you want.
     
  • IP4: to say the network of 62.2.210.208/28 is permitted to send mail from akadia.com, you would write ip4:62.2.210.208/28.

Mechanisms are interpreted left-to-right. Using v=spf1 a mx ptr -all first would check whether the connecting client was found in the A record for the domain or, failing that, in its list of MX servers. Then the MTA would check to see whether the hostname of the client matched the domain. If none of the mechanisms matched, -all would be evaluated, the result would be fail and the MTA would be justified in rejecting the mail.

You can query the SPF record with the host command:

host -tTXT akadia.com
akadia.com text "v=spf1 ip4:62.2.210.208/28 -all"

Checking SPF (with SpamAssassin)

SpamAssassin 3.0 supports SPF to detect and penalize header forgery. This requires Mail::SPF::Query, a relatively new package that's not yet installed on most machines. You can confirm whether you have it by entering:

perl -e 'require Mail::SPF::Query'

If you get the error "Can't locate Mail/SPF/Query.pm in @INC..." you need it.

To install Mail::SPF::Query, do the following:

Get it from http://search.cpan.org

perl Makefile.PL
make
make test
make install

Test SpamAssassin installation

You can test SPF by entering:

spamassassin -D < sample-nonspam.txt

and carefully reviewing the output. Specifically, look for the following lines:

....
debug: registering glue method for check_for_spf_helo_pass
(Mail::SpamAssassin::Plugin::SPF=HASH(0x8d21990))

....