Guardian Digital Amavis Mailing List Archive

....

Guardian Digital Inc. > InfoCenter > Mailing List Archives > Amavis

Amavis Mailing List Archive

About this list	Date view	Thread view	Subject view	Author view	Attachment view

From: CRivera@checkfree.com
Date: Thu Dec 30 2004 - 12:56:56 EST

Next message: Matthias Keller: "Re: [AMaViS-user] Manually Re-Injecting Quarantined Messages"
Previous message: Jefferson Cowart: "[AMaViS-user] Manually Re-Injecting Quarantined Messages"
In reply to: Daniel Luttermann: "[AMaViS-user] Re: MySQL [OT]"

That was a great write-up, A+.

How about some maintenance scripts to keep this database tidy?

Here are my docs on dspam and bayes using mySQL --> environment this runs
on --> amavisd-new 2.2.1, dspam 3.2.3 and SA 3.0.1 and PostFix 2.1.5 I
hope this helps, if someone needs the SQL scripts i have in my
documentation I can provide those.

#############################
### DSPAM MySQL procedure ###
#############################

# Change default root password
/usr/bin/mysqladmin -u root password '(SOME PASSWORD)'

# Copy over purge script
cp -p ~crivera/purge.sql /usr/local/sbin

# Create DSPAM database
mysql -u root -p(SOME PASSWORD)
create database dspam;

# Import SQL scripts
mysql dspam -u root -p(SOME PASSWORD)< ~crivera/mysql_objects-speed.sql
mysql dspam -u root -p(SOME PASSWORD)< ~crivera/virtual_users.sql

# Modify databse priviliges
mysql -u root -p(SOME PASSWORD)
use dspam;
GRANT ALL PRIVILEGES ON *.* TO 'dspam'@'localhost'
IDENTIFIED BY '(SOME PASSWORD)' WITH GRANT OPTION;

# Modify /etc/dspam.conf for SQL use
echo "MySQLServer /var/lib/mysql/mysql.sock" >> /etc/dspam.conf
echo "MySQLPort" >> /etc/dspam.conf
echo "MySQLUser" dspam >> /etc/dspam.conf
echo "MySQLPass" (SOME PASSWORD) >> /etc/dspam.conf
echo "MySQLDb" dspam >> /etc/dspam.conf
echo "MySQLCompress true" >> /etc/dspam.conf

# Add crontab entry
# Clean up Dspam Metadata - nightly
0 0 * * * /usr/bin/mysql dspam -u root -p(SOME PASSWORD)<
/usr/local/sbin/purge.sql

# Reload amavisd-new
amavisd reload

#############################
### BAYES MySQL procedure ###
#############################

# Create BAYES database
mysql -u root -p(SOME PASSWORD)
create database bayes;

# Import SQL scripts
mysql bayes -u root -p(SOME PASSWORD) < ~crivera/userpref_mysql.sql
mysql bayes -u root -p(SOME PASSWORD)< ~crivera/awl_mysql.sql
mysql bayes -u root -p(SOME PASSWORD)< ~crivera/bayes_mysql.sql

# Modify databse priviliges
mysql -u root -p (SOME PASSWORD)
use bayes;
GRANT ALL PRIVILEGES ON *.* TO 'bayes'@'localhost'
IDENTIFIED BY ' (SOME PASSWORD)' WITH GRANT OPTION;

# Modify /etc/mail/spamassassin/local/cf for SQL use
# Bayes learning tool
use_bayes 1
#bayes_path /var/bayes/bayes <-- commented out
#bayes_learn_to_journal 1 <-- commented out
#bayes_learn_during_report 1 <-- commented out
bayes_min_spam_num 100
bayes_min_ham_num 50
bayes_auto_expire 1
bayes_auto_learn_threshold_spam 12.0
bayes_auto_learn_threshold_nonspam 2.0

# mysql
echo "bayes_store_module Mail::SpamAssassin::BayesStore::SQL" >>
/etc/mail/spamassassin/local.cf
echo "bayes_sql_dsn DBI:mysql:bayes" >>
/etc/mail/spamassassin/local.cf
echo "bayes_sql_username bayes" >> /etc/mail/spamassassin/local.cf
echo "bayes_sql_password (SOME PASSWORD)" >>
/etc/mail/spamassassin/local.cf
echo "auto_whitelist_factory Mail::SpamAssassin::SQLBasedAddrList" >>
/etc/mail/spamassassin/local.cf
echo "user_awl_dsn DBI:mysql:bayes" >>
/etc/mail/spamassassin/local.cf
echo "user_awl_sql_username bayes" >> /etc/mail/spamassassin/local.cf
echo "user_awl_sql_password baye\$" >> /etc/mail/spamassassin/local.cf

# Reload amavisd-new
amavisd reload

"Daniel Luttermann" <Daniel.Luttermann@t-online.de>
Sent by: amavis-user-admin@lists.sourceforge.net
12/30/2004 12:21 PM
Please respond to
Daniel Luttermann <daniel.luttermann@t-online.de>

To
amavis-user@lists.sourceforge.net
cc

Subject
[AMaViS-user] Re: MySQL [OT]

Hi Dale,

> Would someone be so kind as to provide instructions to enable mysql
> with amavisd-new
>
> I"m looking for simple, easy to follow instructions, please include any

> and all relevant information.
>
> Thank you
>
> -- Dale

This is my "documentation".... needs more descriptions....
-------------

Amavisd-new with MySQL

What you will need:

A recent version of amavisd-new (current:2.1.1)
http://www.ijs.si/software/amavisd/

Recommend: MySQL 4.1.x (current:4.1.8)
http://www.mysql.com

Perl DBI (current:1.46)
http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.46.tar.gz

Perl DBD MySQL
http://search.cpan.org/CPAN/authors/id/R/RU/RUDY/DBD-mysql-2.9004.tar.gz

Install steps

First you should compile and install MySQL if you don't have a working
installation. Then you should create an user in your mysql user table.
This can be done with this sql statement:

USE mysql;
REPLACE INTO user (host, user, password)
    VALUES (
        'localhost',
        'amavisd',
-- IMPORTANT: Change this password!
        PASSWORD('password')
);

REPLACE INTO db (host, db, user, select_priv, insert_priv, update_priv,
                 delete_priv, create_priv, drop_priv)
    VALUES (
        'localhost',
        'amavisd',
        'amavisd',
        'Y', 'Y', 'Y', 'Y',
        'N', 'N'
);
FLUSH PRIVILEGES;
CREATE DATABASE amavisd;

This user (amavisd) is needed by amavisd-new to connect to the amavisd-new
database.
The amavisd-new user needs only these MySQL permissions:

SELECT,INSERT,UPDATE,DELETE

After you've created an user for connecting to the amavisd-new MySQL
database
you can create the amavisd-new database and necassary tables.

This can be done with this sql statement:

USE amavisd;

-- 
-- Database: `amavisd`
-- 
-- --------------------------------------------------------
-- 
-- `mailaddr`
-- 
CREATE TABLE `mailaddr` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `priority` int(11) NOT NULL default '7',
  `email` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `mailaddr_idx_email` (`email`),
  KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
-- --------------------------------------------------------
-- 
-- `policy`
-- 
CREATE TABLE `policy` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `policy_name` varchar(32) default NULL,
  `virus_lover` char(1) default NULL,
  `spam_lover` char(1) default NULL,
  `banned_files_lover` char(1) default NULL,
  `bad_header_lover` char(1) default NULL,
  `bypass_virus_checks` char(1) default NULL,
  `bypass_spam_checks` char(1) default NULL,
  `bypass_banned_checks` char(1) default NULL,
  `bypass_header_checks` char(1) default NULL,
  `spam_modifies_subj` char(1) default NULL,
  `virus_quarantine_to` varchar(64) default NULL,
  `spam_quarantine_to` varchar(64) default NULL,
  `message_size_limit` float default NULL,
  `banned_quarantine_to` varchar(64) default NULL,
  `bad_header_quarantine_to` varchar(64) default NULL,
  `spam_tag_level` float default NULL,
  `spam_tag2_level` float default NULL,
  `spam_kill_level` float default NULL,
  `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 'N',
  `warnbannedrecip` char(1) default 'N',
  `warnbadhrecip` char(1) default '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,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;
-- --------------------------------------------------------
-- 
-- `users`
-- 
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `priority` int(11) NOT NULL default '7',
  `policy_id` int(10) unsigned NOT NULL default '1',
  `email` varchar(255) NOT NULL default '',
  `fullname` varchar(255) default NULL,
  `local` char(1) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `users_idx_email` (`email`),
  KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;
-- --------------------------------------------------------
-- 
-- `wblist`
-- 
CREATE TABLE `wblist` (
  `rid` int(10) unsigned NOT NULL default '0',
  `sid` int(10) unsigned NOT NULL default '0',
  `wb` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`rid`,`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
All fields that are supported with amavisd-new 2.1.1 lookup are included.
After that you should fill the fields with sample informations for your
domain. You must fill in some data in the table "users" and "policys". If 
not,
your sql queries will not be successful.
If you like you can insert some data with this sql statement (from 
amavisd-new
documentation readme.lookups):
INSERT INTO users VALUES ( 1, 9, 5, 'user1+foo@y.example.com','Name1 
Surname1', 'Y');
INSERT INTO users VALUES ( 2, 7, 5, 'user1@y.example.com', 'Name1 
Surname1', 'Y');
INSERT INTO policy VALUES (1, 'Non-paying',    'N','N','N','N', 
'Y','Y','Y','N',
'Y',NULL,NULL,NULL,NULL, 3.0,   7, 10,   NULL, NULL,NULL,NULL,NULL);
INSERT INTO policy VALUES (2, 'Uncensored',    'Y','Y','Y','Y', 
'N','N','N','N',
'N',NULL,NULL,NULL,NULL, 3.0, 999, 999,  NULL, NULL,NULL,NULL,NULL);
INSERT INTO mailaddr VALUES ( 1, 5, '@example.com');
INSERT INTO mailaddr VALUES ( 2, 9, 'owner-postfix-users@postfix.org');
INSERT INTO wblist VALUES (14, 1, 'W');
INSERT INTO wblist VALUES (17, 2, 'W');
These are only examples so insert data that match your current 
environment.
If you've finished your MySQL setup you should install all needed Perl
modules from CPAN that are described in amavisd-new installation.
These modules are (from amavisd-new installation documentation):
Archive::Tar
Archive::Zip
Compress::Zlib
Convert::TNEF
Convert::UUlib
MIME::Base64
MIME::Parser
Mail::Internet
Net::Server
Net::SMTP
Digest::MD5
IO::Stringy
Time::HiRes
Unix::Syslog
BerkeleyDB
Optional Perl modules:
Mail::SpamAssassin
Net::LDAP
Authen::SASL
Mail::ClamAV
SAVI
And for MySQL support you need the Perl modules described in
"What you will need" at the top of this document.
If the installation of these Perl modules were successful you can install
amavisd-new as it is described in the installation document of 
amavisd-new.
In amavisd.conf you must configure your settings for your MySQL database. 
Look
at this line in amavisd.conf.
@lookup_sql_dsn =
( 
['DBI:mysql:database=database;host=localhost;mysql_socket=/var/lib/mysql/mysql.sock', 
'username', 'password'] );
#     ['DBI:mysql:database=mail;host=host2', 'username2', 'password2'] );
I prefer that you use the connect over MySQL sockets rather than port 
3306.
Then you can start the amavisd daemon with the option "debug". Look at the 
output if
MySQL support has been loaded. If so you can send you the first message.
With the debug switch you can see if sql lookups were successful or not. 
The mostly common
error is that amavisd-new fails to connect to the database.
---------------
-- 
Best Regards
Daniel Luttermann
mailto:daniel.luttermann@t-online.de
-------------------------------------------------------
The SF.Net email is sponsored by: Beat the post-holiday blues
Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
-------------------------------------------------------
The SF.Net email is sponsored by: Beat the post-holiday blues
Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
_______________________________________________
AMaViS-user mailing list
AMaViS-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/

Next message: Matthias Keller: "Re: [AMaViS-user] Manually Re-Injecting Quarantined Messages"
Previous message: Jefferson Cowart: "[AMaViS-user] Manually Re-Injecting Quarantined Messages"
In reply to: Daniel Luttermann: "[AMaViS-user] Re: MySQL [OT]"

About this list	Date view	Thread view	Subject view	Author view	Attachment view

[ About Guardian Digital ] - [ Press Center ] - [ Contact Us ] - [ System Activation ] - [ Reseller Info ] - [ Online Store ] - [ Site Map ]
Copyright (c) 2000 - 2004 Guardian Digital, Inc. Linux Lockbox and EnGarde are Trademarks of Guardian Digital, Inc.