HOWTO Email: A Complete Virtual System - Quarantine and Spam Management
From Gentoo Linux Wiki
Please format this article according to the guidelines and Wikification suggestions, then remove this notice {{Wikify}} from the article
|
Email: A Complete Virtual System |
|
Getting Started Basic Mail Setup Enhanced Mail Services
Anti-Spam Configuration
Anti-Virus Configuration Log Analyzer Wrapping it Up |
| edit |
[edit] Quarantine and Spam Management
This section has turned out to be much tougher than originally anticipated. Amavisd is a great start but was lacking a web-based interface. When I started searching for a package to cover this off, I found a real mixed bag of solutions with no clear winner. I have actually installed a number of packages only to find they were lacking some necessary component or didn’t quite do what I was looking for.
I was not able to get the functionality I wanted out of a single package. Either they were missing functionality, wouldn’t run across multiple servers, required a bunch of custom scripts, etc. etc. etc. I eventually decided the right thing to do was to install the base database schema supported by amavisd and have quarantined messages stored in there for now. From there, you can either write a script, or use a package such as MailZu (www.mailzu.net) to provide an interface. Unfortunately this solution is not nicely tied into the other components (as say a per-user mail folder or integrated with Squirrelmail), but better than a kick in the teeth.
I know that many of you will say there are ready made plugins for squirrelmail that will do the job. Well yes and no. There are some ready made plugins, but they either require different database schemas, their own special scripts to move things into the database and/or will not operate across multiple servers without the need to install a mess of additional support, which I am not prepared to do.
[edit] Creating the Database
Quarantined messages and all of our amavisd/spamassassin settings are going to be stored in our database. We will start by creating the database and database user we will need.
| Code: Creating the Database |
# createdb -E SQL_ASCII -U postgres amavis CREATE DATABASE # createuser -U postgres -P Enter name of user to add: amavis Enter password for new user: $password Enter it again: $password Shall the new user be allowed to create databases? (y/n) n Shall the new user be allowed to create more new users? (y/n) n CREATE USER |
Don’t forget that before the amavis user is going to be able to connect to your new database, you will need to give the amavis user the appropriate rights. As we did way back in this document for the Postgres and Postfix users, I used Webmin to setup the Postgres access using the “Allowed hosts” feature.
The reason for SQL_ASCII-encoding of the database is to allow spamassassin to store mails with strange mail encodings too. We can't use UNICODE, because we have no way to specify a client-encoding in the spamassassin configs. SQL_ASCII means to ignore conversions and store everything as-is.
(This is not a great solution, but the only one i could find, that seems to work. Pay attention to UNICODE-conversion-errors your logs if you're not using this setting! I got bad bayes learning for some months before i could solve this! And I'm personally still not convinced that postgres spits out the data in the same format as spamassassin expects it... [Could someone who knows SA better than me please verify this.] -- 212.100.47.100 05:09, 4 October 2006 (UTC) )
The tables for our database are gong to be created from a number of sources. We will step through this one plugin at a time, ensuring that they are tested before moving on.
[edit] Create the Tables
We will use the amavisd schema taken from the README.sql.txt document found at http://www.ijs.si/software/amavisd/ README.sql.txt. Have a read through the document before you carry on (along with some of the other readme’s here) as there is a lot of good information that will increase your understanding of amavisd.
To keep things easy for the reader, I have included the entire schema here, adjusted for Postrgres with the necessary Grant statements for the amavis user we created above. You should be able to copy the text below into a file and use it to create the required tables.
{pderbyshire - 7-nov-2006: note db schema has changed since versions 2.4.1 and above so best copy the SQL from http://www.ijs.si/software/amavisd/README.sql.txt making appropriate changes for PostgreSQL. Don't forget to include the GRANTs (SELECT, INSERT, UPDATE, DELETE) on all the amavis tables and indexes.}
...{sbavalis - 9-nov-2007: I don't know if this is another change or not but there is a readme at http://ijs.si/software/amavisd for generic sql, postgres, and mysql.}...
{Edit by michrech@yahoo.com -- the bad_header_lover field should be larger than 1 character to be able to hold a username as the sample data suggests. 1 character just isn't enough as I discovered while I tried to enter some data into the table. If this was meant to be a "y" or "n" like the bypass_* fields, then "Test User" in the "Test Data" box in the 'policy' table should be replaced with "y" or "n".}
{This was my problem for a few hours, i couldn't store corectly incomming spam in database. Solution from amavis readme: Upgrading from pre 2.4.0 amavisd-new SQL schema to the 2.4.0 schema requires adding column 'quar_loc' to table msgs, and creating FOREIGN KEY constraint to facilitate deletion of expired records. The following clauses should be executed for upgrading pre-2.4.0 amavisd-new SQL schema to the 2.4.0 schema}
ALTER TABLE msgs ADD quar_loc varchar(255) DEFAULT ' ';
{Edit by kardasa@kardasa.pl for me above wasn't enough as I'm using amavisd-new-2.5.2 according to documentation [1] you have to change data type of mail_text field
ALTER TABLE quarantine ALTER mail_text TYPE bytea USING decode(replace(mail_text,'\\','\\\\'),'escape');
This will also require to change an option in MailZu config.php file.
Sugestion: /var/amavis/amavis.log - is yours friend.
{Edit by Alphacube 2008-05-08 for MySql users see http://www.ijs.si/software/amavisd/README.sql-mysql.txt }
| Code: Database Tables |
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- unique id, not necessarily auto-assigned
priority integer NOT NULL DEFAULT '7', -- sort field, 0 is low prior.
policy_id integer NOT NULL DEFAULT '1', -- JOINs with policy.id
email varchar(255) NOT NULL,
fullname varchar(255) DEFAULT NULL, -- not used by amavisd-new
local char(1) -- Y/N (optional field, see note further down)
);
CREATE UNIQUE INDEX users_idx_email ON users (email);
GRANT SELECT, INSERT, UPDATE, DELETE on users, users_id_seq to amavis;
-- any e-mail address (non- rfc2822-quoted), external or local,
-- used as senders in wblist
CREATE TABLE mailaddr (
id SERIAL PRIMARY KEY,
priority integer NOT NULL DEFAULT '7', -- 0 is low priority
email varchar(255) NOT NULL
);
CREATE UNIQUE INDEX mailaddr_idx_email ON mailaddr (email);
GRANT SELECT, INSERT, UPDATE, DELETE on mailaddr, mailaddr_id_seq to amavis;
-- per-recipient whitelist and/or blacklist,
-- puts sender and recipient in relation wb (white or blacklisted sender)
CREATE TABLE wblist (
rid integer NOT NULL, -- recipient: users.id
sid integer NOT NULL, -- sender: mailaddr.id
wb varchar(10) NOT NULL, -- W or Y / B or N / space=neutral / score
PRIMARY KEY (rid,sid)
);
GRANT SELECT, INSERT, UPDATE, DELETE on wblist to amavis;
CREATE TABLE policy (
id SERIAL PRIMARY KEY, -- this is the _only_ required field
policy_name varchar(32), -- not used by amavisd-new
virus_lover char(1) default NULL, -- Y/N
spam_lover char(1) default NULL, -- Y/N
banned_files_lover char(1) default NULL, -- Y/N
bad_header_lover char(1) default NULL, -- Y/N
bypass_virus_checks char(1) default NULL, -- Y/N
bypass_spam_checks char(1) default NULL, -- Y/N
bypass_banned_checks char(1) default NULL, -- Y/N
bypass_header_checks char(1) default NULL, -- Y/N
spam_modifies_subj char(1) default NULL, -- Y/N
virus_quarantine_to varchar(64) default NULL,
spam_quarantine_to varchar(64) default NULL,
banned_quarantine_to varchar(64) default NULL,
bad_header_quarantine_to varchar(64) default NULL,
spam_tag_level float default NULL, -- higher score inserts spam info headers
spam_tag2_level float default NULL, -- inserts 'declared spam' header fields
spam_kill_level float default NULL, -- higher score activates evasive actions, e.g.
-- reject/drop, quarantine, ...
-- (subject to final_spam_destiny setting)
spam_dsn_cutoff_level float default NULL,
addr_extension_virus varchar(64) default NULL,
addr_extension_spam varchar(64) default NULL,
addr_extension_banned varchar(64) default NULL,
addr_extension_bad_header varchar(64) default NULL,
warnvirusrecip char(1) default NULL, -- Y/N
warnbannedrecip char(1) default NULL, -- Y/N
warnbadhrecip char(1) default NULL, -- Y/N
newvirus_admin varchar(64) default NULL,
virus_admin varchar(64) default NULL,
banned_admin varchar(64) default NULL,
bad_header_admin varchar(64) default NULL,
spam_admin varchar(64) default NULL,
spam_subject_tag varchar(64) default NULL,
spam_subject_tag2 varchar(64) default NULL,
message_size_limit integer default NULL, -- max size in bytes, 0 disable
banned_rulenames varchar(64) default NULL -- comma-separated list of ...
-- names mapped through %banned_rules to actual banned_filename tables
);
GRANT SELECT, INSERT, UPDATE, DELETE on policy, policy_id_seq to amavis;
-- R/W part of the dataset (optional)
-- May reside in the same or in a separate database as lookups database;
-- requires support for transactions; specified in @storage_sql_dsn
--
-- Please create additional indexes on keys when needed, or drop suggested
-- ones as appropriate to optimize queries needed by a management application.
-- See your database documentation for further optimization hints.
-- provide unique id for each e-mail address, avoids storing copies
CREATE TABLE maddr (
id SERIAL PRIMARY KEY,
email varchar(255) NOT NULL, -- full mail address
domain varchar(255) NOT NULL -- only domain part of the email address
-- with subdomain fields in reverse
);
CREATE UNIQUE INDEX maddr_idx_email ON maddr (email);
CREATE INDEX maddr_idx_domain ON maddr (domain);
GRANT SELECT, INSERT, UPDATE, DELETE on maddr, maddr_id_seq to amavis;
-- information pertaining to each processed message as a whole;
-- NOTE: records with NULL msgs.content should be ignored by utilities,
-- as such records correspond to messages just being processes, or were lost
CREATE TABLE msgs (
mail_id varchar(12) NOT NULL, -- long-term unique mail id
secret_id varchar(12) DEFAULT '', -- authorizes release of mail_id
am_id varchar(20) NOT NULL, -- id used in the log
time_num integer NOT NULL, -- rx_time: second since Unix epoch
time_iso char(16) NOT NULL, -- rx_time: ISO8601 UTC ascii time
sid integer NOT NULL, -- sender: maddr.id
policy varchar(255) DEFAULT '', -- policy bank path (like macro %p)
client_addr varchar(255) DEFAULT '', -- SMTP client IP address (IPv4 or v6)
size integer NOT NULL, -- message size in bytes
content char(1), -- content type: V/B/S/H/O/C, is NULL
-- ...on partially processed mail
quar_type char(1), -- quarantined as: ' '/F/Z/B/Q/M
-- none/file/zipfile/bsmtp/sql/mailbox
dsn_sent char(1), -- was DSN sent? Y/N/q (q=quenched)
spam_level float, -- base message spam level (no boosts)
message_id varchar(255) DEFAULT '', -- mail Message-ID header field
from_addr varchar(255) DEFAULT '', -- mail From header field, UTF8
subject varchar(255) DEFAULT '', -- mail Subject header field, UTF8
host varchar(255) NOT NULL, -- hostname where amavisd is running
PRIMARY KEY (mail_id)
);
CREATE INDEX msgs_idx_sid ON msgs (sid);
GRANT SELECT, INSERT, UPDATE, DELETE on msgs to amavis;
-- per-recipient information related to each processed message;
-- NOTE: records in msgrcpt without corresponding msgs.mail_id record are
-- orphaned and should be ignored and eventually deleted by external utilities
CREATE TABLE msgrcpt (
mail_id varchar(12) NOT NULL, -- (must allow duplicates)
rid integer NOT NULL, -- recipient: maddr.id (dupl. allowed)
ds char(1) NOT NULL, -- delivery status: P/R/B/D/T
-- pass/reject/bounce/discard/tempfail
rs char(1) NOT NULL, -- release status: initialized to ' '
bl char(1) DEFAULT ' ', -- sender blacklisted by this recip
wl char(1) DEFAULT ' ', -- sender whitelisted by this recip
bspam_level float, -- spam level + per-recip boost
smtp_resp varchar(255) DEFAULT ''
);
CREATE INDEX msgrcpt_idx_mail_id ON msgrcpt (mail_id);
CREATE INDEX msgrcpt_idx_rid ON msgrcpt (rid);
GRANT SELECT, INSERT, UPDATE, DELETE on msgrcpt to amavis;
-- mail quarantine in SQL, enabled by $*_quarantine_method='sql:'
-- NOTE: records in quarantine without corresponding msgs.mail_id record are
-- orphaned and should be ignored and eventually deleted by external utilities
CREATE TABLE quarantine (
mail_id varchar(12) NOT NULL, -- long-term unique mail id
chunk_ind integer NOT NULL,-- chunk number, starting with 1
mail_text text NOT NULL, -- store mail as chunks up to 16 kB
PRIMARY KEY (mail_id,chunk_ind)
);
GRANT SELECT, INSERT, UPDATE, DELETE on quarantine to amavis;
-- field msgrcpt.rs is primarily intended for use by quarantine management
-- software; the value initially assigned by amavisd is a space;
-- a short _preliminary_ list of possible values:
-- 'V' => viewed (marked as read)
-- 'R' => released (delivered) to this recipient
-- 'p' => pending (a status given to messages when the admin received the
-- request but not yet released; targeted to banned parts)
-- 'D' => marked for deletion; a cleanup script may delete it
|
[edit] Database Connections
Here we give amavisd the information it needs to connect with our database for reading per-user settings. Back to the mail sever to adjust the settings in amavisd.conf for its lookups.
| Code: /etc/amavisd.conf |
# nano /etc/amavisd.conf @lookup_sql_dsn = ( ['DBI:Pg:dbname=amavis;host=dbServerhostname;port=5432', 'dbuser', 'dbpass']); @storage_sql_dsn = @lookup_sql_dsn; # none, same, or separate database. |
While debug testing amavis i discovered that unless a username and password is provided in the sql dsn string pg login was denied.
Fixed the example to represent functional values - ali3nx 27.11.06
| Code: /etc/amavisd.conf @lookup_sql_dsn notes |
amavis[11790]: (11790-01) sql begin, nontransaction amavis[11790]: (11790-01) Connecting to SQL database server amavis[11790]: (11790-01) connect_to_sql: trying 'DBI:Pg:dbname=amavis;host=127.0.0.1;port=5432' amavis[11790]: (11790-01) (!) connect_to_sql: unable to connect to DSN 'DBI:Pg:dbname=amavis;host=127.0.0.1;port=5432': fe_sendauth: no password supplied\n amavis[11790]: (11790-01) (!!) TROUBLE in process_request: connect_to_sql: unable to connect to any dataset at (eval 52) line 196, <GEN5> line 5. amavis[11790]: (11790-01) (!) Requesting process rundown after fatal error amavis[11790]: (11790-01) Amavis::In::SMTP DESTROY called, sock=Net::Server::Proto::TCP=GLOB(0x40a1990), normal=0 |
There are also a few changes we need to make to the amavisd configuration so that it will store quarantined messages into the database as follows
| Code: /etc/amavisd.conf |
# nano /etc/amavisd.conf # Location to put infected mail into: (applies to 'local:' quarantine method) # empty for not quarantining, may be a file (Unix-style mailbox), # or a directory (no trailing slash) # (the default value is undef, meaning no quarantine) # #$QUARANTINEDIR = "$MYHOME/quarantine"; #$quarantine_subdir_levels = 1; # add level of subdirs to disperse quarantine #$virus_quarantine_method = 'local:virus-%m'; # default #$spam_quarantine_method = 'local:spam-%m.gz'; # default #$banned_files_quarantine_method = 'local:banned-%m'; # default #$bad_header_quarantine_method = 'local:badh-%m'; # default # Separate quarantine subdirectories virus, spam, banned and badh within # the directory $QUARANTINEDIR may be specified by the following settings # (the subdirectories need to exist - must be created manually): #$virus_quarantine_method = 'local:virus/virus-%m'; #$spam_quarantine_method = 'local:spam/spam-%m.gz'; #$banned_files_quarantine_method = 'local:banned/banned-%m'; #$bad_header_quarantine_method = 'local:badh/badh-%m'; #use the 'bsmtp:' method as an alternative to the default 'local:' #$virus_quarantine_method = "bsmtp:$QUARANTINEDIR/virus-%m.bsmtp"; #$spam_quarantine_method = "bsmtp:$QUARANTINEDIR/spam-%m.bsmtp"; #$virus_quarantine_to = 'virus-quarantine'; # local quarantine #$banned_quarantine_to = 'banned-quarantine'; # local quarantine #$bad_header_quarantine_to= 'bad-header-quarantine'; # local quarantine #$spam_quarantine_to = 'spam-quarantine'; # local quarantine # #using the 'sql:' method to store quarantined message to a SQL database: $virus_quarantine_method = $spam_quarantine_method = $banned_files_quarantine_method = $bad_header_quarantine_method = 'sql:'; #@lookup_sql_dsn = # ( ['DBI:mysql:database=mail;host=127.0.0.1;port=3306', 'user1', 'passwd1'], # ['DBI:mysql:database=mail;host=host2', 'username2', 'password2'], # ["DBI:SQLite:dbname=$MYHOME/sql/mail_prefs.sqlite", '', ''] ); @lookup_sql_dsn = ( ['DBI:Pg:dbname=amavis;host=martin;port=5432'] ); @storage_sql_dsn = @lookup_sql_dsn; # none, same, or separate database |
Ensure you comment out all of the quarantine settings above and uncomment the “sql:” method parameters and the lookup_sql_dsn and storage_sql_dsn.
We will adjust Taint in the DBI->connect call so that it reads Taint => 0 rather than => 1 as originally coded.
// The above seems to be not correct (at least) for amavisd-new-2.4.1! Changing the above actually prevented the whole thing from working.
| Code: /usr/sbin/amavisd |
# nano /usr/sbin/amavisd
sub connect_to_sql {
my($self) = shift; # a list of DSNs to try connecting to sequentially
my($dbh); my(@dsns) = @{$self->{dsn_list}};
do_log(3,"Connecting to SQL database server");
for my $tmpdsn (@dsns) {
my($dsn, $username, $password) = @$tmpdsn;
do_log(4,"connect_to_sql: trying '$dsn'");
$dbh = DBI->connect($dsn, $username, $password,
{PrintError => 0, RaiseError => 0, Taint => 0, AutoCommit => 1} );
if ($dbh) { do_log(3,"connect_to_sql: '$dsn' succeeded"); last }
do_log(-1,"connect_to_sql: unable to connect to DSN '$dsn': ".$DBI::errstr);
|
Restart amavisd and run a quick test by sending a clean message that should go through and then the test spam message (mentioned earlier) which should get stored into the database. To verify the test worked, use webmin to go look at the quarantine table in your database and confirm that a mail did show up.
[edit] Per-User Settings
Obviously when we created the database above, we included many tables beyond what was required simply for storing than what was required for just quaranting messages. Not all users will be satisfied with the default values you have set for amavisd.conf. Using the database, we can allow individual settings for users or groups of users.
With the tables setup, we simply need to tell amavisd how to use the database to read user preferences/
| Code: /etc/amavisd.conf |
# nano /etc/amavisd.conf
$sql_select_policy = 'SELECT *,users.id FROM users,policy'.
' WHERE (users.policy_id=policy.id) AND (users.email IN (%k))'.
' ORDER BY users.priority DESC';
|
Now using webmin, I will setup some test data that will allow us to verify the settings are in fact working. Substitute the test email with your own so that the policy will get picked up when you send your test messages through.
| File: Test Data |
users Table
id: 1
priority: 9
policy_id: 1
email: user@example.com
fullname: Test User
local: Y
policy Table
id: 1
policy_name: Test Policy
virus_lover: N
banned_files_lover: N
bad_header_lover: N
bypass_virus_checks: Y
bypass_spam_checks: N
bypass_banned_checks: N
bypass_header_checks: N
spam_modifies_subj: N
* Leave the remainder of the settings NULL
|
[edit] Per-User Testing
Based on the initial test settings, when we submit a message, then the system should not act any differently. Be sure to restart amavisd before testing or else your changes will not take effect.
Send through your spam message and ensure that it gets shunted off to the quarantine database. Yes? Great, now go back and make a minor change to your test data in the policy table by setting bypass_spam_checks to “Y”. Now when you resend your spam message, it should just pass straight through to your mailbox.
Before moving on, don’t forget to reset the value we changed back to “N”.
