Postfix, Dovecot, spamassassin, mysql and maildrop

[hamba]

Hi,

I while back I created a small how-to that I used to setup a mail server at work, I have also used the same how-to on my own server.

This how-to will show you in a few quick step how to setup a Postfix mail server using Dovecot for imap and pop3, clamav and spamassassin to combat viruses and spam and maildrop for delivering the mail to your users.

I will assume that you have MySQL, Apache and PHP already installed and working and that you have updated your ports tree.

First we will install Dovecot

# cd /usr/ports/mail/dovecot; make install clean

Make sure that you have the following selected, "SSL", "IPV6", "POP3" and "MYSQL"

Add dovecot to your rc.conf file so that it start automatically when you boot the system

# vim /etc/rc.conf
dovecot_enable="YES"

Next will be to copy the sample.conf files to proper conf files

# cd /usr/local/etc/
# cp dovecot-example.conf dovecot.conf
# cp dovecot-sql-example.conf dovecot-sql.conf

Next we will go and create the ssl cert for Dovecot, this will be a self sighned cert.
Edit /usr/local/share/dovecot/dovecot-openssl.cnf

# vim /usr/local/share/dovecot/dovecot-openssl.cnf
[ req ]
default_bits = 1024
encrypt_key = yes
distinguished_name = req_dn
x509_extensions = cert_type
prompt = no

[ req_dn ]
## country (2 letter code)
C=DE

## State or Province Name (full name)
ST=

## Locality Name (eg. city)
L=Berlin

## Organization (eg. company)
O=Example

## Organizational Unit Name (eg. section)
OU=IMAP server

## Common Name (*.example.com is also possible)
## NOTE: must be a FQDN
CN=imap.example.com

## E-mail contact
emailAddress=postmaster@example.com

[ cert_type ]
nsCertType = server

Create a ssl directory for your cert

# mkdir /etc/ssl/certs /etc/ssl/private

Now create the cert by running the following command

# /usr/local/share/dovecot/mkcert.sh

Next we will configure Dovecot
Edit the following file

# vim /usr/local/etc/dovecot.conf
base_dir = /var/run/dovecot/
protocols = imap imaps pop3 pop3s
listen = *
disable_plaintext_auth = no
shutdown_clients = yes
ssl_disable = no
ssl_cert_file = /etc/ssl/certs/dovecot.pem
ssl_key_file = /etc/ssl/private/dovecot.pem
login_greeting = Imap server ready.
mail_location = maildir:/usr/local/virtual/%d/%n
mail_extra_groups = mail
verbose_proctitle = yes
## This is the same uid and gid as postfix
first_valid_uid = 125
first_valid_gid = 125
protocol imap {
  mail_plugins = quota imap_quota
  imap_client_workarounds = delay-newmail outlook-idle netscape-eoh tb-extra-mailbox-sep
}
protocol pop3 {
  pop3_uidl_format = %08Xu%08Xv
  mail_plugins = quota
  pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
}
protocol lda {
  postmaster_address = postmaster@example.com
  sendmail_path = /usr/sbin/sendmail
}
auth default {
  mechanisms = plain login
  passdb sql {
    args = /usr/local/etc/dovecot-sql.conf
  }
  userdb sql {
    args = /usr/local/etc/dovecot-sql.conf
  }
  user = root
  socket listen {
    client {
      path = /var/spool/postfix/private/auth
      user = postfix
      group = postfix
      mode = 0660
    }
  }
}
dict {
}
plugin {
}

Next file to edit is the sql file

# vim /usr/local/etc/dovecot-sql.conf
driver = mysql
connect = host=localhost dbname=postfix user=postfix password=ch@ngeMe
default_pass_scheme = MD5
password_query = SELECT password FROM mailbox WHERE username = '%u'
user_query = SELECT maildir, 125 AS uid, 125 AS gid, CONCAT('dirsize:storage=', ROUND( mailbox.quota / 1024 ) ) AS quota FROM mailbox WHERE username = '%u' AND active = '1'

NOTE: make sure that all the MySQL queries are on one line

Thats all you have to do to get Dovecot up and running

Next install Postfix

# cd /usr/ports/mail/postfix; make install clean

Make sure that you select "DOVECOT", "TLS", "BDB", "MySQL", and "VDA"
After the Postfix application is finished building and preparing to be finished installing, it will prompt you with a question similar to:
Would you like to activate Postfix in /etc/mail/mailer.conf [n]?
Press "y" and then continue with the install

Stop sendmail and add Postfix to your rc.conf file so that it start automatically when you boot the system

# vim /etc/rc.conf
sendmail_enable="NO"
sendmail_submit_enable="NO"
sendmail_outbound_enable="NO"
sendmail_msp_queue_enable="NO"
postfix_enable="YES"

Also edit /etc/periodic.conf and add the following

# vim  /etc/periodic.conf
daily_clean_hoststat_enable="NO"
daily_status_mail_rejects_enable="NO"
daily_status_include_submit_mailq="NO"
daily_submit_queuerun="NO"

Verify the linked libraries for Postfix smtpd

# ldd /usr/local/libexec/postfix/smtpd
/usr/local/libexec/postfix/smtpd:
        libpcre.so.0 => /usr/local/lib/libpcre.so.0 (0x280c3000)
        libssl.so.4 => /usr/lib/libssl.so.4 (0x280e9000)
        libcrypto.so.4 => /lib/libcrypto.so.4 (0x28117000)
        libdb41.so.1 => /usr/local/lib/libdb41.so.1 (0x2820a000)
        libmysqlclient.so.15 => /usr/local/lib/mysql/libmysqlclient.so.15 (0x282a7000)
        libz.so.3 => /lib/libz.so.3 (0x28302000)
        libcrypt.so.3 => /lib/libcrypt.so.3 (0x28313000)
        libm.so.4 => /lib/libm.so.4 (0x2832b000)
        libc.so.6 => /lib/libc.so.6 (0x28341000)

Now create the ssl cert for SMTP

# mkdir /usr/local/etc/postfix/ssl
# cd /usr/local/etc/postfix/ssl
# openssl req -new -x509 -nodes -out smtpd.pem -keyout smtpd.pem -days 3650
# chmod 750 /usr/local/etc/postfix/ssl
# chmod 640 /usr/local/etc/postfix/ssl/smtpd.pem
# chgrp -R postfix /usr/local/etc/postfix/ssl

Now we can go and configure postfix

# vim /usr/local/etc/postfix/main.cf
## SASL CONFIG
broken_sasl_auth_clients = yes
smtpd_sender_restrictions = permit_sasl_authenticated, permit_mynetworks
smtpd_recipient_restrictions =
  permit_mynetworks,
  permit_sasl_authenticated,
  reject_non_fqdn_hostname,
  reject_non_fqdn_sender,
  reject_non_fqdn_recipient,
  reject_unauth_destination,
  reject_unauth_pipelining,
  reject_invalid_hostname,
  reject_rbl_client list.dsbl.org,
  reject_rbl_client bl.spamcop.net,
  reject_rbl_client sbl-xbl.spamhaus.org
smtpd_sasl_auth_enable = yes
smtpd_sasl_authenticated_header = yes
smtpd_sasl_local_domain = $myhostname
smtpd_sasl_security_options = noanonymous
smtpd_sasl_type = dovecot
smtpd_sasl_path = /var/spool/postfix/private/auth

## TLS CONFIG
##
smtp_use_tls = yes
smtpd_use_tls = yes
smtp_tls_note_starttls_offer = yes
smtpd_tls_key_file = /usr/local/etc/postfix/ssl/smtpd.pem
smtpd_tls_cert_file = /usr/local/etc/postfix/ssl/smtpd.pem
smtpd_tls_CAfile = /usr/local/etc/postfix/ssl/smtpd.pem
smtpd_tls_loglevel = 2
smtpd_tls_received_header = yes
smtpd_tls_session_cache_timeout = 3600s
tls_random_source = dev:/dev/urandom

## MySQL Configuration
virtual_alias_maps = proxy:mysql:/usr/local/etc/postfix/mysql_virtual_alias_maps.cf
virtual_gid_maps = static:125
virtual_mailbox_base = /usr/local/virtual
virtual_mailbox_domains = proxy:mysql:/usr/local/etc/postfix/mysql_virtual_domains_maps.cf
virtual_mailbox_limit = 51200000
virtual_mailbox_maps = proxy:mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_maps.cf
virtual_minimum_uid = 125
virtual_transport = virtual
virtual_uid_maps = static:125
relay_domains = proxy:mysql:/usr/local/etc/postfix/mysql_relay_domains_maps.cf

## Additional for quota support
virtual_create_maildirsize = yes
virtual_mailbox_extended = yes
virtual_mailbox_limit_maps = proxy:mysql:/usr/local/etc/postfix/mysql_virtual_mailbox_limit_maps.cf
proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps
  $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains
  $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps
  $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks
  $virtual_mailbox_limit_maps
virtual_mailbox_limit_override = yes
virtual_maildir_limit_message = Sorry, this user has overdrawn their diskspace quota. Please try again later.
virtual_overquota_bounce = yes

spamassassin_destination_recipient_limit = 1

queue_directory = /var/spool/postfix
command_directory = /usr/local/sbin
daemon_directory = /usr/local/libexec/postfix
mail_owner = postfix
myhostname = mail.example.com
mydomain = example.com
myorigin = $myhostname
inet_interfaces = all
unknown_local_recipient_reject_code = 550
mynetworks_style = host
mynetworks = 10.0.0.0/8, 127.0.0.0/8
transport_maps = hash:/usr/local/etc/postfix/transport
vacation_destination_recipient_limit = 1
debug_peer_level = 2
debugger_command =
     PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin
     xxgdb $daemon_directory/$process_name $process_id & sleep 5
sendmail_path = /usr/local/sbin/sendmail
newaliases_path = /usr/local/bin/newaliases
mailq_path = /usr/local/bin/mailq
setgid_group = maildrop
html_directory = no
manpage_directory = /usr/local/man
sample_directory = /usr/local/etc/postfix
readme_directory = no

## clamd
content_filter = scan:localhost:10025
receive_override_options = no_address_mappings

Next edit /usr/local/etc/postfix/master.cf

# vim /usr/local/etc/postfix/master.cf
smtp      inet  n       -       n       -       -       smtpd
    -o content_filter=spamassassin
smtps     inet  n       -       n       -       -       smtpd
    -o smtpd_tls_wrappermode=yes
    -o smtpd_sasl_auth_enable=yes
    -o smtpd_client_restrictions=permit_sasl_authenticated,reject
pickup    fifo  n       -       n       60      1       pickup
cleanup   unix  n       -       n       -       0       cleanup
qmgr      fifo  n       -       n       300     1       qmgr
tlsmgr    unix  -       -       n       1000?   1       tlsmgr
rewrite   unix  -       -       n       -       -       trivial-rewrite
bounce    unix  -       -       n       -       0       bounce
defer     unix  -       -       n       -       0       bounce
trace     unix  -       -       n       -       0       bounce
verify    unix  -       -       n       -       1       verify
flush     unix  n       -       n       1000?   0       flush
proxymap  unix  -       -       n       -       -       proxymap
smtp      unix  -       -       n       -       -       smtp
relay     unix  -       -       n       -       -       smtp
    -o fallback_relay=
showq     unix  n       -       n       -       -       showq
error     unix  -       -       n       -       -       error
retry     unix  -       -       n       -       -       error
discard   unix  -       -       n       -       -       discard
local     unix  -       n       n       -       -       local
virtual   unix  -       n       n       -       -       virtual
lmtp      unix  -       -       n       -       -       lmtp
anvil     unix  -       -       n       -       1       anvil
scache    unix  -   -   n   -   1   scache
vacation  unix  -       n       n       -       -       pipe
    flags=DRhu user=vacation argv=/var/spool/vacation/vacation.pl
## AV scan filter (used by content_filter)
scan      unix  -       -       n       -       16      smtp
    -o smtp_send_xforward_command=yes
## For injecting mail back into postfix from the filter
localhost:10026 inet  n -       n       -       16      smtpd
    -o content_filter=
    -o receive_override_options=no_unknown_recipient_checks,no_header_body_checks
    -o smtpd_helo_restrictions=
    -o smtpd_client_restrictions=
    -o smtpd_sender_restrictions=
    -o smtpd_recipient_restrictions=permit_mynetworks,reject
    -o mynetworks_style=host
    -o smtpd_authorized_xforward_hosts=127.0.0.0/8
## SpamAssassin
spamassassin unix -     n       n       -       -       pipe
        user=nobody argv=/usr/local/bin/spamc -u ${recipient} -f -e
        /usr/sbin/sendmail -oi -f ${sender} ${recipient}

Next create and edit /usr/local/etc/postfix/mysql_virtual_alias_maps.cf

# touch /usr/local/etc/postfix/mysql_virtual_alias_maps.cf
# vim /usr/local/etc/postfix/mysql_virtual_alias_maps.cf
user = postfix
password = ch@ngeMe
hosts = localhost
dbname = postfix
query = SELECT goto FROM alias WHERE address='%s' AND active = '1'

Then create and edit /usr/local/etc/postfix/mysql_virtual_domains_maps.cf

# touch /usr/local/etc/postfix/mysql_virtual_domains_maps.cf
# vim /usr/local/etc/postfix/mysql_virtual_domains_maps.cf
user = postfix
password = ch@ngeMe
hosts = localhost
dbname = postfix
query = SELECT domain FROM domain WHERE domain='%s'
##optional query to use when relaying for backup MX
##query = SELECT domain FROM domain WHERE domain='%s' and backupmx = '0' and active = '1'

Then create and edit /usr/local/etc/postfix/mysql_virtual_mailbox_maps.cf

# touch /usr/local/etc/postfix/mysql_virtual_mailbox_maps.cf
# vim /usr/local/etc/postfix/mysql_virtual_mailbox_maps.cf
user = postfix
password = ch@ngeMe
hosts = localhost
dbname = postfix
query = SELECT maildir FROM mailbox WHERE username='%s' AND active = '1'

Then create and edit /usr/local/etc/postfix/mysql_virtual_mailbox_limit_maps.cf

# touch /usr/local/etc/postfix/mysql_virtual_mailbox_limit_maps.cf
# vim /usr/local/etc/postfix/mysql_virtual_mailbox_limit_maps.cf
user = postfix
password = ch@ngeMe
hosts = localhost
dbname = postfix
query = SELECT quota FROM mailbox WHERE username='%s'

And lastly create and edit /usr/local/etc/postfix/mysql_relay_domains_maps.cf

# touch /usr/local/etc/postfix/mysql_relay_domains_maps.cf
# vim /usr/local/etc/postfix/mysql_relay_domains_maps.cf
user = postfix
password = ch@ngeMe
hosts = localhost
dbname = postfix
query = SELECT domain FROM domain WHERE domain='%s' and backupmx = '1'

Make sure you secure these files

# chmod 640 /usr/local/etc/postfix/mysql_*
# chgrp postfix /usr/local/etc/postfix/mysql_*

Update the transport map database

# postmap /usr/local/etc/postfix/transport

Edit the file in /etc/aliases and add your email address to recieve root emails

# /etc/aliases
root: you@example.com

Then create the new aliases.db file

# /usr/bin/newaliases

Now create your virual directory where all your mail will be stored

# mkdir /usr/local/virtual
'The lay out will be as you\'ve specified in your Dovecot config file %d/%n'
'This will give you something like "/usr/local/virtual/example.com/you/"'
# chown -R postfix:postfix /usr/local/virtual
# chmod -R 700 /usr/local/virtual

That should be it for configuring Postfix
The next step will be to install postfixadmin
Here you have two options, one install it from ports or go and download the latest version, I'm using the latest one.
Go to http://postfixadmin.sourceforge.net/ and download the latest version

Untar it some where in your home directory and then copy it to /usr/local/www/

Next we need to setup the database for Postfix, Dovecot and Postfixadmin, they all use one database called postfix
There is a nice file called "DATABASE_MYSQL.TXT", edit it to suite your needs, I used this file to create the postfix database, the postfix user and the postfixadmin user as well as the tables.
After editing the file all you need to do is insert it into MySQL

# mysql -u root -p < DATABASE_MYSQL.TXT

Once thats done we can go and add an alias to Apache

# vim /usr/local/etc/apache22/extra/httpd-autoindex.conf
Alias /postfixadmin "/usr/local/www/postfixadmin/"
<Directory "/usr/local/www/postfixadmin">
    Options Indexes
    AllowOverride AuthConfig
    Allow from all
</Directory>

Go to /usr/local/www/postfixadmin/config.inc.php and make the needed changes
I will only list the changes I've made here

# vim /usr/local/www/postfixadmin/config.inc.php
$CONF['configured'] = true;
$CONF['postfix_admin_url'] = 'http://www.example.com/postfixadmin/';
$CONF['database_type'] = 'mysqli';
$CONF['database_host'] = 'localhost';
$CONF['database_user'] = 'postfixadmin';
$CONF['database_password'] = 'Ple@seCh@ngeMe';
$CONF['database_name'] = 'postfix';
$CONF['database_prefix'] = '';
$CONF['database_prefix'] = '';
$CONF['admin_email'] = 'me@example.com';
$CONF['smtp_server'] = 'mail.example.com';
$CONF['generate_password'] = 'YES';
$CONF['show_password'] = 'YES';
$CONF['page_size'] = '15';
$CONF['default_aliases'] = array (
    'abuse' => 'abuse@example.com',
    'hostmaster' => 'hostmaster@example.com',
    'postmaster' => 'postmaster@example.com',
    'webmaster' => 'webmaster@example.com'
);
$CONF['domain_path'] = 'YES';
$CONF['domain_in_mailbox'] = 'NO';
$CONF['vacation'] = 'YES';
$CONF['vacation_domain'] = 'autoreply.example.com';
$CONF['alias_control'] = 'YES';
$CONF['alias_control_admin'] = 'YES';
$CONF['special_alias_control'] = 'YES';
$CONF['fetchmail'] = 'NO';
$CONF['fetchmail_extra_options'] = 'NO';
$CONF['user_footer_link'] = "http://www.example.com/webmail/";
$CONF['show_footer_text'] = 'YES';
$CONF['footer_text'] = 'Return to example.com';
$CONF['footer_link'] = 'http://www.example.com';
$CONF['welcome_text'] = <<<EOM
Hi, Welcome to your new account. If you have any questions please email me at you@example.com
EOM;

Now create Vacation user and group accounts

# pw groupadd vacation
# pw useradd vacation -c Virtual\ Vacation -d /nonexistent -g vacation -s /sbin/nologin

Then create, populate and secure vacation directory

# mkdir /var/spool/vacation
# cp /usr/local/www/postfixadmin/VIRTUAL_VACATION/vacation.pl /var/spool/vacation/
# chown -R vacation:vacation /var/spool/vacation/
# chmod -R 700 /var/spool/vacation/
# touch /var/log/vacation.log /var/log/vacation.debug
# chown vacation:vacation /var/log/vacation.*

You still need to edit the /var/spool/vacation/vacation.pl

# vim /var/spool/vacation/vacation.pl
my $db_type = 'mysql';
my $db_host = 'localhost';
my $db_username = 'postfixadmin';
my $db_password = 'Ple@seCh@ngeMe';
my $db_name     = 'postfix';
my $logfile = "/var/log/vacation.log";
my $debugfile = "/var/log/vacation.debug";

NOTE: Please make sure that "MIME-EncWords", "MIME-Charset", "Email-Valid" and "Mail-Sendmail" perl modules are installed to make this file work. If they are not installed you can find them in the ports tree.

At this stage of the setup you can go and comment out all the ClamAV and SpamAssassin stuff in the "/usr/local/etc/postfix/main.cf" and "/usr/local/etc/postfix/master.cf" files

After you've commented them out we can go and start Postfix and Dovecot

# /usr/local/etc/rc.d/postfix start
# /usr/local/etc/rc.d/dovecot start

Have a look in your "/var/log/maillog" file for errors

# tail -1000 /var/log/maillog

[hamba]

And we have still more todo



If all looks good go to your Postfixadmin page
In a browser type in http://www.example.com/postfixadmin/setup.php and follow the on screen instructions. After your done go and remove the setup.php file

# rm /usr/local/www/postfixadmin/setup.php

Now go back to the postfixadmin page and login
After you've loged in go to the New Domain page and create your mail domain.
Then go and create an email account for yourself
If all goes as planned you will see the domain and email accounts if you go to "/usr/local/virtual"

# ls -alh /usr/local/virtual
drwx------  49 postfix postfix  1.3K 2008-01-19 15:04 example.com
# ls -alh /usr/local/virtual/example.com
drwx------  49 postfix postfix  1.3K 2008-01-19 15:04 you
drwx------  49 postfix postfix  1.3K 2008-01-19 15:04 me
drwx------  49 postfix postfix  1.3K 2008-01-19 15:04 someone

Next thing to do is getting webmail up and running, I'm using Squirrelmail because there is a nice plugin for postfixadmin so that users can set there out of office replies and change there password.
I'm using the squirrelmail-dev version from the ports tree

# cd /usr/ports/mail/squirrelmail-devel; make install clean

'After the installation you need to go and edit Apache to add Squirrelmail'
# vim vim /usr/local/etc/apache22/extra/httpd-autoindex.conf
Alias /sqmail "/usr/local/www/squirrelmail-devel/"

<Directory "/usr/local/www/squirrelmail-devel/">
    Options all -Indexes MultiViews
    AllowOverride None
    Order allow,deny
    Allow from all
</Directory>

Next go and configure Squirrelmail

# /usr/local/www/squirrelmail-devel/configure

Press "2" for "Server Settings" and me the following changes
Server Settings

General
-------
1.  Domain                 : example.com
2.  Invert Time            : false
3.  Sendmail or SMTP       : SMTP

A.  Update IMAP Settings   : mail.example.com:993 (dovecot)
B.  Update SMTP Settings   : mail.example.com:25

Press "a" for editing the "imap settings"
IMAP Settings
--------------
4.  IMAP Server            : mail.example.com
5.  IMAP Port              : 993
6.  Authentication type    : login
7.  Secure IMAP (TLS)      : 1
8.  Server software        : dovecot
9.  Delimiter              : detect

Press "b" for editing the "smtp settings"
SMTP Settings
-------------
4.   SMTP Server           : 10.0.100.111
5.   SMTP Port             : 25
6.   POP before SMTP       : false
7.   SMTP Authentication   : none
8.   Secure SMTP (TLS)     : 0
9.   Header encryption key :

Press "s" to save the changes and then "r" to return to the main config page
Press "3" to change the "Folder Defaults"
Folder Defaults
1.  Default Folder Prefix         :
2.  Show Folder Prefix Option     : false
3.  Trash Folder                  : Trash
4.  Sent Folder                   : Sent
5.  Drafts Folder                 : Drafts
6.  By default, move to trash     : true
7.  By default, move to sent      : true
8.  By default, save as draft     : true
9.  List Special Folders First    : true
10. Show Special Folders Color    : true
11. Auto Expunge                  : true
12. Default Sub. of INBOX         : false
13. Show 'Contain Sub.' Option    : false
14. Default Unseen Notify         : 2
15. Default Unseen Type           : 1
16. Auto Create Special Folders   : true
17. Folder Delete Bypasses Trash  : false
18. Enable /NoSelect folder fix   : false

Press "s" to save the changes and then "r" to return to the main config page
Press "4" to change to "General Options"
1.  Data Directory              : /var/spool/squirrelmail/pref/
2.  Attachment Directory        : /var/spool/squirrelmail/attach/
3.  Directory Hash Level        : 0
4.  Default Left Size           : 150
5.  Usernames in Lowercase      : true
6.  Allow use of priority       : true
7.  Hide SM attributions        : false
8.  Allow use of receipts       : true
9.  Allow editing of identity   : true
    Allow editing of name       : true
    Remove username from header : false
10. Disable server thread sort  : false
11. Disable server-side sorting : false
12. Allow server charset search : true
13. Allow advanced search       : 2
14. PHP session name            : SQMSESSID
15. Time zone configuration     : 0

Press "s" to save the changes and then "r" to return to the main config page
Press "9" to change the database settings
Database
1.  DSN for Address Book   : mysql://sqmail:Ch@ngeMe@localhost/squirrelmail
2.  Table for Address Book : address

3.  DSN for Preferences    : mysql://sqmail:Ch@ngeMe@localhost/squirrelmail
4.  Table for Preferences  : userprefs
5.  Field for username     : user (128)
6.  Field for prefs key    : prefkey (64)
7.  Field for prefs value  : prefval (65536)

8.  DSN for Global Address Book            : mysql://sqmail:Ch@ngeMe@localhost/squirrelmail
9.  Table for Global Address Book          : global_abook
10. Allow writing into Global Address Book : false
11. Allow listing of Global Address Book   : true

Press "q" to quit and save the changes

Now go and create the database and tables for Squirrelmail

# mysql -u root -p
mysql> CREATE DATABASE squirrelmail;
mysql> USE mysql;
mysql> GRANT ALL ON squirrelmail.* TO 'sqmail'@'localhost' IDENTIFIED BY 'Ch@ngeMe';
mysql> FLUSH PRIVILEGES;
mysql> USE squirrelmail;
mysql> CREATE TABLE address (
       owner varchar(128) DEFAULT '' NOT NULL,
       nickname varchar(16) DEFAULT '' NOT NULL,
       firstname varchar(128) DEFAULT '' NOT NULL,
       lastname varchar(128) DEFAULT '' NOT NULL,
       email varchar(128) DEFAULT '' NOT NULL,
       label varchar(255),
       PRIMARY KEY (owner,nickname),
       KEY firstname (firstname,lastname)
       );
mysql> CREATE TABLE global_abook (
       owner varchar(128) DEFAULT '' NOT NULL,
       nickname varchar(16) DEFAULT '' NOT NULL,
       firstname varchar(128) DEFAULT '' NOT NULL,
       lastname varchar(128) DEFAULT '' NOT NULL,
       email varchar(128) DEFAULT '' NOT NULL,
       label varchar(255),
       PRIMARY KEY (owner,nickname),
       KEY firstname (firstname,lastname)
       );
mysql> CREATE TABLE userprefs (
       user varchar(128) DEFAULT '' NOT NULL,
       prefkey varchar(64) DEFAULT '' NOT NULL,
       prefval BLOB DEFAULT '' NOT NULL,
       PRIMARY KEY (user,prefkey)
       );
mysql> exit;

Next go and download the postfixadmin plugin for squirrelmail and copy it into your squirrelmail/plugins directory
Make the needed changes in the postfixadmin plugin config file.
After all that is done go to "/usr/local/www/squirrelmail-devel/configure" press "8" and then the number next to the postfixadmin plugin to enable it
Now login to Squirrelmail and go to the options page and test the password change and out of office response

By now you should have a fully working email server witha nice webmail interface for your users.

After all this is working and you have no problems you can go and install SpamAssassin and ClamAV
First install SpamAssassin from ports

# cd /usr/ports/mail/p5-Mail-SpamAssassin/; make install clean

NOTE: make sure that "AS_ROOT", "SSL", "MYSQL" and "RAZOR" are selected

Create a user that will use SpamAssassin

# pw groupadd vscan
# pw useradd -n vscan -c Amavisd\ User -d /var/amavisd -g vscan -m
# passwd vscan

Edit the "/usr/local/etc/mail/spamassassin/local.cf" file

# vim /usr/local/etc/mail/spamassassin/local.cf
rewrite_header Subject *****SPAM*****
report_safe 1
report_contact you@example.com

use_bayes 1
bayes_store_module                  Mail::SpamAssassin::BayesStore::MySQL
bayes_sql_dsn                       DBI:mysql:spamassassin:localhost
bayes_sql_username                  vscan
bayes_sql_password                  ChangeMe
bayes_sql_override_username         vscan
bayes_auto_learn                    1
bayes_auto_learn_threshold_nonspam  -0.001
bayes_auto_learn_threshold_spam     10.0
bayes_use_hapaxes                   1
bayes_ignore_header                 ReSent-Date
bayes_ignore_header                 ReSent-From
bayes_ignore_header                 ReSent-Message-ID
bayes_ignore_header                 ReSent-Subject
bayes_ignore_header                 ReSent-To
bayes_ignore_header                 Resent-Date
bayes_ignore_header                 Resent-From
bayes_ignore_header                 Resent-Message-ID
bayes_ignore_header                 Resent-Subject
bayes_ignore_header                 Resent-To
bayes_ignore_header                 X-Received-From-IP
bayes_ignore_header                 X-Virus-Scanned
bayes_ignore_header                 X-Spam-Status
bayes_ignore_header                 X-Spam-Level
bayes_ignore_header                 X-Sender
bayes_ignore_header                 X-Mailer

user_scores_dsn                 DBI:mysql:spamassassin:localhost
user_scores_sql_username        vscan
user_scores_sql_password        ChangeMe
user_scores_sql_custom_query    SELECT preference, value FROM userpref WHERE username = _USERNAME_ OR username = '@GLOBAL' OR username = '@~'||_DOMAIN_ ORDER BY username ASC

auto_whitelist_factory          Mail::SpamAssassin::SQLBasedAddrList
user_awl_dsn                    DBI:mysql:spamassassin:localhost
user_awl_sql_username           vscan
user_awl_sql_password           ChangeMe
user_awl_sql_table              awl

skip_rbl_checks                     0
rbl_timeout                         10
dns_available                       yes

use_razor2                          1
razor_timeout                       10

fold_headers                        1

Configure RAZOR for reporting

# su - vscan
# razor-admin -d -create
# razor-admin -register (Simply follow the steps to create your own username/password.)

Next add this to your "/etc/rc.conf" file

# vim /etc/rc.conf
spamd_enable="YES"
spamd_flags="--daemonize --sql-config --nouser-config --username=nobody"

Install ClamAV

# cd /usr/ports/security/clamav; make install clean
# cd /usr/ports/security/clamsmtp; make install clean

And then add this to your "/etc/rc.conf" file

# vim /etc/rc.conf
clamav_freshclam_enable="YES"
clamav_clamd_enable="YES"
clamsmtpd_enable="YES"

Now start SpamAssassin and ClamAV

# /usr/local/etc/rc.d/sa-spamd start
# /usr/local/etc/rc.d/clamav-clamd start
# /usr/local/etc/rc.d/clamav-freshclam start
# /usr/local/etc/rc.d/clamsmtpd start

We still have to create the database for Spamassassin

# mysql -u root -p
mysql> CREATE DATABASE spamassassin;
mysql> USE mysql;
mysql> GRANT ALL ON spamassassin.* TO 'vscan'@'localhost' IDENTIFIED BY 'ChangeMe';
mysql> FLUSH PRIVILEGES;
mysql> USE spamassassin;
mysql> CREATE TABLE userpref (
       username varchar(100) NOT NULL default '',
       preference varchar(30) NOT NULL default '',
       value varchar(100) NOT NULL default '',
       prefid int(11) NOT NULL auto_increment,
       PRIMARY KEY  (prefid),
       KEY username (username)
       ) TYPE=MyISAM;
mysql> CREATE TABLE awl (
       username varchar(100) NOT NULL default '',
       email varchar(200) NOT NULL default '',
       ip varchar(10) NOT NULL default '',
       count int(11) default '0',
       totscore float default '0',
       PRIMARY KEY  (username,email,ip)
       ) TYPE=MyISAM;
mysql> CREATE TABLE bayes_expire (
       id int(11) NOT NULL default '0',
       runtime int(11) NOT NULL default '0',
       KEY bayes_expire_idx1 (id)
       ) TYPE=MyISAM;

mysql> CREATE TABLE bayes_global_vars (
       variable varchar(30) NOT NULL default '',
       value varchar(200) NOT NULL default '',
       PRIMARY KEY  (variable)
       ) TYPE=MyISAM;

mysql> INSERT INTO bayes_global_vars VALUES ('VERSION','3');

mysql> CREATE TABLE bayes_seen (
       id int(11) NOT NULL default '0',
       msgid varchar(200) binary NOT NULL default '',
       flag char(1) NOT NULL default '',
       PRIMARY KEY  (id,msgid)
       ) TYPE=MyISAM;

mysql> CREATE TABLE bayes_token (
       id int(11) NOT NULL default '0',
       token char(5) NOT NULL default '',
       spam_count int(11) NOT NULL default '0',
       ham_count int(11) NOT NULL default '0',
       atime int(11) NOT NULL default '0',
       PRIMARY KEY  (id, token),
       INDEX bayes_token_idx1 (token),
       INDEX bayes_token_idx2 (id, atime)
       ) TYPE=MyISAM;

mysql> CREATE TABLE bayes_vars (
       id int(11) NOT NULL AUTO_INCREMENT,
       username varchar(200) NOT NULL default '',
       spam_count int(11) NOT NULL default '0',
       ham_count int(11) NOT NULL default '0',
       token_count int(11) NOT NULL default '0',
       last_expire int(11) NOT NULL default '0',
       last_atime_delta int(11) NOT NULL default '0',
       last_expire_reduce int(11) NOT NULL default '0',
       oldest_token_age int(11) NOT NULL default '2147483647',
       newest_token_age int(11) NOT NULL default '0',
       PRIMARY KEY  (id),
       UNIQUE bayes_vars_idx1 (username)
       ) TYPE=MyISAM;

mysql> INSERT INTO userpref (username,preference,value) VALUES ('$GLOBAL','required_hits','5.0');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('$GLOBAL','report_safe','1');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('$GLOBAL','use_bayes','1');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('$GLOBAL','use_dcc','1');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('%example.com','required_hits','4.5');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('%example.com','report_safe','1');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('%example.com','use_bayes','1');
mysql> INSERT INTO userpref (username,preference,value) VALUES ('%example.com','use_dcc','1');
mysql> exit;

Now test is by runing this from the command line

# echo -e "From: user\nTo:user\Subject: Test\n\n" | spamc -u '$GLOBAL'
Received: from localhost by mail.example.com
        with SpamAssassin (version 3.2.4);
        Thu, 24 Jan 2008 13:12:27 +0000
Subject: *****SPAM*****
X-Spam-Flag: YES
X-Spam-Checker-Version: SpamAssassin 3.2.4 (2008-01-01) on scurvy.violetlan.net
X-Spam-Level: ********
X-Spam-Status: Yes, score=8.9 required=5.0 tests=EMPTY_MESSAGE,MISSING_DATE,
        MISSING_HB_SEP,MISSING_HEADERS,MISSING_MID,MISSING_SUBJECT,NO_HEADERS_MESSAGE,
        NO_RECEIVED,NO_RELAYS,TVD_SPACE_RATIO autolearn=no version=3.2.4
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----------=_47988EBB.C05C05B1"

# echo -e "From: user\nTo:user\Subject: Test\n\n" | spamc -u '%example.com'
Received: from localhost by scurvy.violetlan.net
        with SpamAssassin (version 3.2.4);
        Thu, 24 Jan 2008 13:15:12 +0000
Subject: *****SPAM*****
X-Spam-Flag: YES
X-Spam-Checker-Version: SpamAssassin 3.2.4 (2008-01-01) on scurvy.violetlan.net
X-Spam-Level: ********
X-Spam-Status: Yes, score=8.9 required=4.5 tests=EMPTY_MESSAGE,MISSING_DATE,
        MISSING_HB_SEP,MISSING_HEADERS,MISSING_MID,MISSING_SUBJECT,NO_HEADERS_MESSAGE,
        NO_RECEIVED,NO_RELAYS,TVD_SPACE_RATIO autolearn=no version=3.2.4
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----------=_47988F60.614EB132"

You also need to add this little script into your crontab to clean out the old awl table in MySQL
Create the file

# touch /usr/local/etc/awl-trim.sh
# touch /usr/local/etc/awl-trim.sql
# vim /usr/local/etc/awl-trim.sh
#!/bin/sh
/usr/local/bin/mysql -uvscan -pChangeMe < /usr/local/etc/awl-trim.sql
exit 0

# vim /usr/local/etc/awl-trim.sql
USE spamassassin;
DELETE FROM awl WHERE count="1";

Then go and create a cronjob with the following in it

# vim /etc/crontab
## spamassassin
0   1   *   *   6   root    /usr/local/etc/awl-trim.sh

Now go to the Squirrelmail home page and download the SaSQL plugin and install it, this will give your users the possibility to change there SpamAssassin settings.
That should be it, if all went well then you should have a email server that uses TLS and filtering viruses and spam.

I finaly got maildrop working so here is how to get it set up. So here is how I got it working.

First thing we will do is create a user that will use maildrop from with in postfix

# pw groupadd mailgrp
# adduser

create a user called "mailusr" as you would normally do for a standard system user. Leave the shell at /bin/sh because we will use it to test maildrop later
At the group option for the mailusr tell it to use mailgrp

Next install maildrop from the ports

# cd /usr/ports/mail/maildrop/
# make NO_MAILWRAPPER=yes WITH_AUTHLIB=yes MAILDROP_TRUSTED_USERS="root mailusr postfix" install clean

Courier Authlib will come up with a options screen make sure that you have MySQL selected
Now go and edit "authdaemonrc" and "authmysqlrc"

# vim /usr/local/etc/authlib/authdaemonrc
authmodulelist="authmysql"

The rest can stay as it is

Now go and edit "authmysqlrc", it is very important that you keep the tabes in this file

# vim /usr/local/etc/authlib/authmysqlrc
MYSQL_SERVER        localhost
MYSQL_USERNAME      postfix
MYSQL_PASSWORD      ch@ngeMe
MYSQL_SOCKET        /tmp/mysql.sock
MYSQL_OPT       0
MYSQL_DATABASE      postfix
MYSQL_USER_TABLE    mailbox
MYSQL_CRYPT_PWFIELD password
MYSQL_CLEAR_PWFIELD password
MYSQL_UID_FIELD     '125'
MYSQL_GID_FIELD     '125'
MYSQL_LOGIN_FIELD   username
MYSQL_HOME_FIELD    '/usr/local/virtual'
MYSQL_NAME_FIELD    name
MYSQL_MAILDIR_FIELD maildir

Next go and fix some of the permissions that is wrongly set, we need to make these changes to get maildrop to work for the mailusr.

# cd /var/run/
# chmod 755 authdaemond
# chmod +s /usr/local/bin/maildrop
# cd /usr/local/virtual/
# touch /usr/local/virtual/.mailfilter
# chmod 600 /usr/local/virtual/.mailfilter
# mkdir /usr/local/virtual/mailfilters
# chmod 700 /usr/local/virtual/mailfilters
# chown -R postfix:postfix /usr/local/virtual/.mailfilter /usr/local/virtual/mailfilters

Add this to your /etc/rc.conf file

# vim /etc/rc.conf
courier_authdaemond_enable="YES"

and then start courier-authlib

# /usr/local/etc/rc.d/courier-authdaemond start

Look in your maillog for any errors

[hamba]

And here is the last bit you'll need


As the root user execute the following to test maildrop

# echo "test" | maildrop -V 9 -d you@example.com

If there are no errors go and have a look in your inbox, there should be the email you just send.

Next login as the mailusr and run maildrop again

# su mailusr
> echo "test" | maildrop -V 9 -d you@example.com

If by this stage you get an error then go back and check the permissions of the files.
If you didn't get any errors and you\'ve got the email in your inbox then it means that maildrop is working perfectly and its time to intergrade it into postfix

Go and edit the master.cf file from postfix
comment out all the spamassassin stuff and uncomment maildrop and make the required changes

# vim /usr/local/etc/postfix/master.cf
##    -o content_filter=spamassassin
maildrop  unix  -       n       n       -       -       pipe
  flags=DRhu user=mailusr argv=/usr/local/bin/maildrop -d ${recipient} ${recipient}
##SpamAssassin
##spamassassin unix -     n       n       -       -       pipe
##        user=nobody argv=/usr/local/bin/spamc -u ${recipient} -f -e
##        /usr/sbin/sendmail -oi -f ${sender} ${recipient}

Next go and edit the main.cf file of postfix

# vim /usr/local/etc/postfix/main.cf
##virtual_transport = virtual
virtual_transport = maildrop
##spamassassin_destination_recipient_limit = 1
maildrop_destination_recipient_limit = 1

Next go and make a change in MySQL

# mysql -p
mysql> use postfix;
mysql> UPDATE domain SET transport = 'maildrop' WHERE domain = 'example.com';
mysql> exit

Now restart postfix and have a look in your maillog for any errors.

# /usr/local/etc/rc.d/postfix restart

Here is the .mailfilter file I use globaly
It will be used for including user based .mailfilter files based on there email address, it also scans for spam and if it comes across any spam it will look for a Junk mail folder in the users maildir and if its not there it will create it for them and then move all the spam emails into it.

# vim /usr/local/virtual/.mailfilter
# My Global maildrop filter
xfilter "/usr/local/bin/spamc -u $1"

# Check for user defined filter file
exception {
    include "$HOME/mailfilters/$1"
}

# Deliver to Inbox or Spam box (create spam box if it does not exist)
if (/^X-Spam-Flag: YES/:h)
{
    `test -d $DEFAULT/.Junk`
    if ($RETURNCODE == 1)
    {
        `/usr/local/bin/maildirmake -f Junk $DEFAULT`
        `echo "Junk" >> $DEFAULT/subscriptions`
    }
    exception {
        to "$DEFAULT/.Junk"
    }
    # if all else fails, do regular delivery
    exception {
        to "$DEFAULT"
    }
}

All the user based .mailfilter files will be under the mailfilter folder in email address format.

# touch vim /usr/local/virtual/mailfilters/you@exmaple.com
# chown postfix:postfix /usr/local/virtual/mailfilters/you@exmaple.com
# vim /usr/local/virtual/mailfilters/you@exmaple.com
# FreeBSD Questions
if (/^X-BeenThere:.*freebsd-questions@freebsd.org/)
{
    to "$DEFAULT/.Questions"
}

In this users mailfilter it will move all the emails coming in from the freebsd questions maillist to a folder called .Questions

Thats it for now, happy mailling

If you find any problems, and I'm sure there will be, feel free to ask.

Have fun
hamba

[iron_michael86]

gr8 post.I am facing with some issue with global address book.Is it possible to configure global address book in postfix if yes how and can I use mysql for the same..