The cl-net email details

  • Debian (squeeze+backports)
  • Postfix 2 with SMTP AUTH
  • SASL2 with libpam-pgsql for Postfix
  • PostgreSQL
  • Dovecot
  • Mlmmj — Mailing List Management Made Joyful – http://mlmmj.org/

Add backports

deb http://mirrors.nl.kernel.org/debian-backports squeeze-backports main

Install servers

 sudo apt-get -t squeeze-backports install postgresql-9.1 postfix-doc \
dovecot-common dovecot-imapd dovecot-pop3d libsasl2-2 libsasl2-modules \
libsasl2-modules-sql sasl2-bin libpam-pgsql openssl telnet bsd-mailx postfix-pgsql sasl2-bin libsasl2-modules postgresql-contrib

Set up POSTGRESQL Server

This should already be there :

host    all         all          md5

Now, create the virtual administrator user.

sudo -u postgres createuser  -P -E -s -d virtual_admin

Create the 'virtual' database

sudo -u postgres createdb -O virtual_admin virtual -E UTF-8

I will use Postgres pgcrypto functions since passwords need to be encrypted using the UNIX original encryption scheme. (Host Email with Postfix, Dovecot and Postgres on Debian 6 (Squeeze) | Guillermo Cruz - Freelance Web Developer)

sudo -u postgres psql virtual --command "CREATE EXTENSION pgcrypto;"

The SQL database schema

sudo -u postgres psql virtual -f ./database/schema.sql

 CREATE TABLE domain_name (
   domain_name VARCHAR(128) NOT NULL,
   postfix_transport VARCHAR(128) DEFAULT 'virtual:' NOT NULL,
   PRIMARY KEY (domain_name)

 CREATE TABLE virtual_user (
   username VARCHAR(128) NOT NULL,
   password VARCHAR(128),
   full_name VARCHAR(128),
   PRIMARY KEY (username)

 CREATE TABLE email_domain (
  domain_name VARCHAR(128) UNIQUE NOT NULL,
  username VARCHAR(128) NOT NULL,
  PRIMARY KEY (domain_name, username) 

CREATE TABLE email_alias (
  alias TEXT NOT NULL,
  forward_address TEXT NOT NULL, 
  PRIMARY KEY (alias)

 CREATE TABLE email_address (
   domain_name VARCHAR(128) NOT NULL,
   name VARCHAR(255) NOT NULL,
   PRIMARY KEY (domain_name, name)

  SELECT email_address.name || '@' || email_address.domain_name AS email_address, 
         email_domain.username || '/' || email_address.domain_name 
          || '/email/' || email_address.name || '/' AS maildir,
  FROM email_address 
  JOIN email_domain 
  ON email_address.domain_name = email_domain.domain_name 
  JOIN virtual_user as u
  ON u.username = email_domain.username;    

Postfix via pgsql

The scripts do everything and just need to be run in the proper order.

needs to be more literate

cp /etc/postfix/main.cf ./etc/postfix/
./etc/postfix/pgsql/make-cf.sh #<password for virtual_admin>

The make-cf.sh

DIR="$( cd "$( dirname "$0" )" && pwd )"
VETC_DIR="$(cd ${DIR}/../ && pwd)"

VIRTUAL_DB_CF="user = virtual_admin
password = $VIRTUAL_DB_PW
dbname = virtual
hosts = localhost"

vdb_cf () {
 cmd="echo $2 >> $2"
 sudo sh -c "echo \"$VIRTUAL_DB_CF\" > $2 "
 sudo sh -c "echo 'query =' $1 >> $2"
 sudo chmod o= $2
 sudo chown postfix:postfix $2

CF_EMAIL_DOMAIN="virtual_mailbox_domains = \
Q_EMAIL_DOMAIN="SELECT domain_name FROM email_domain WHERE domain_name=\'%s\'"


CF_MAILDIR="virtual_mailbox_maps = proxy:pgsql:/etc/postfix/pgsql/$N_MAILDIR"
Q_MAILDIR="SELECT maildir FROM maildir WHERE email_address = \'%s\'"

vdb_cf "$Q_MAILDIR" "${DIR}/${N_MAILDIR}"

CF_UID="virtual_uid_maps = proxy:pgsql:/etc/postfix/pgsql/$N_UID"
Q_UID="SELECT uid FROM maildir WHERE email_address = \'%s\'"

vdb_cf "$Q_UID" "${DIR}/${N_UID}"

CF_GID="virtual_gid_maps = proxy:pgsql:/etc/postfix/pgsql/$N_GID"
Q_GID="SELECT gid FROM maildir WHERE email_address = \'%s\'"

vdb_cf "$Q_GID" "${DIR}/${N_GID}"

CF_ALIAS="virtual_alias_maps = proxy:pgsql:/etc/postfix/pgsql/$N_ALIAS"
Q_ALIAS="SELECT forward_address FROM email_alias WHERE alias = \'%s\'"

vdb_cf "$Q_ALIAS" "${DIR}/${N_ALIAS}"

# postconf -e -c $VETC_DIR 

do sudo postconf -e -c $VETC_DIR "$cf" ; echo $cf ; done                      

make the conf via make-conf.sh

This is a shell script which configures postfix to use what we have.

DIR="$( cd "$( dirname "$0" )" && pwd )"

for cf in  "virtual_mailbox_base = /home" \
           "smtpd_sasl_auth_enable = yes" \
           "broken_sasl_auth_clients = yes" \
           "smtpd_sasl_authenticated_header = yes" \
           "smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination" \
           "myhostname = mail.common-lisp.net" \
           "mydestination = mail.common-lisp.net, localhost"  
 sudo postconf -e -c $DIR "$cf"; echo $cf ; 

echo "Copy this configuration to $ETC_DIR ? (y or n)"
read foo
if test $foo = 'y' 
 bname=${MAIN_CF}.`date +"%Y-%m-%d--%H-%m-%S"`.bak
 echo "backing up to $bname"
 sudo cp ${ETC_DIR}/main.cf $bname

 echo "copy $MAIN_CF to $ETC_DIR"
 sudo cp $MAIN_CF $ETC_DIR

 pbname=${DIR}/pgsql.`date +"%Y-%m-%d--%H-%m-%S"`.bak
 echo "backing up to $pbname"
 sudo cp -r ${ETC_DIR}/pgsql $pbname

 echo copy directory ${DIR}/pgsql to $etc_pgsql;
 sudo mkdir -p $etc_pgsql
 sudo cp -r ${DIR}/pgsql/*.cf $etc_pgsql;
 sudo chown postfix:postfix $etc_pgsql/*

 echo "reload postfix?"
 read foo ; if test $foo = 'y' ; then sudo /etc/init.d/postfix reload ; fi
 sudo /etc/init.d/postfix status 



Postfix integration with amavis-new will be presented. Amavis-new is a wrapper that can call any number of content filtering programs for spam detection, antivirus, etc. – https://help.ubuntu.com/community/PostfixAmavisNew

sudo apt-get -t squeeze-backports install amavisd-new spamassassin clamav-daemon\
     libnet-dns-perl pyzor razor\
     arj bzip2 cabextract cpio file gzip nomarch pax  unzip  zip zoo


. A daemon (clamd), signatures are retrieved every day. /etc/clamav

Add clamav user to the amavis group and vice versa in order for Clamav to have access to scan files: – https://help.ubuntu.com/community/PostfixAmavisNew

sudo adduser clamav amavis
sudo adduser amavis clamav


Spamasssassin autodetects optional components.

Edit /etc/default/spamassassin

sudo /etc/init.d/spamassassin start


First, activate spam and antivirus detection in Amavis by editing /etc/amavis/conf.d/15-content_filter_mode:

use strict;

# You can modify this file to re-enable SPAM checking through spamassassin
# and to re-enable antivirus checking.

# Default antivirus checking mode
# Uncomment the two lines below to enable it

@bypass_virus_checks_maps = (
   \%bypass_virus_checks, \@bypass_virus_checks_acl, \$bypass_virus_checks_re);

# Default SPAM checking mode
# Uncomment the two lines below to enable it

@bypass_spam_checks_maps = (
   \%bypass_spam_checks, \@bypass_spam_checks_acl, \$bypass_spam_checks_re);

1;  # insure a defined return

sudo /etc/init.d/amavis restart

Postfix integration

For postfix integration, you need to add the contentfilter configuration variable to the Postfix configuration file /etc/postfix/main.cf. This instructs postfix to pass messages to amavis at a given IP address and port.

sudo postconf -e "content_filter = smtp-amavis:[]:10024"

Next edit /etc/postfix/master.cf and add the following to the end of the file:

smtp-amavis unix - - - - 2 smtp -o smtpdatadonetimeout=1200 -o smtpsendxforwardcommand=yes -o disablednslookups=yes -o maxuse=20 inet n - - - - smtpd -o contentfilter= -o localrecipientmaps= -o relayrecipientmaps= -o smtpdrestrictionclasses= -o smtpddelayreject=no -o smtpdclientrestrictions=permitmynetworks,reject -o smtpdhelorestrictions= -o smtpdsenderrestrictions= -o smtpdrecipientrestrictions=permitmynetworks,reject -o smtpddatarestrictions=rejectunauthpipelining -o smtpdendofdatarestrictions= -o mynetworks= -o smtpderrorsleeptime=0 -o smtpdsofterrorlimit=1001 -o smtpdharderrorlimit=1000 -o smtpdclientconnectioncountlimit=0 -o smtpdclientconnectionratelimit=0 -o receiveoverrideoptions=noheaderbodychecks,nounknownrecipientchecks

Also add the following two lines immediately below the "pickup" transport service:

-o contentfilter= -o receiveoverrideoptions=noheaderbodychecks This will prevent messages that are generated to report on spam from being classified as spam.

More information can be found from "README.postfix from amavisd-new" and "D.J.Fan"

Reload postfix:

sudo /etc/init.d/postfix reload Now content filtering with spam and virus detection is enabled.

PAM : Pluggable authentication module

chmod o-rwx etc/pam_pgsql.conf
sed -i -e "s/#put-real-pw-here/!!password!!/" ./etc/pam_pgsql.conf
cp etc/pam_pgsql.conf /etc
chown root:root /etc/pam_pgsql.conf

chmod o-wx+r ./etc/pam.d/smtp
cp ./etc/pam.d/smtp /etc/pam.d/smtp

"pluggable authentication modules (PAM) are a mechanism to integrate multiple low-level authentication schemes into a high-level application programming interface (API)."


I am not sure if our scheme is low level, but regardless. Dovecot, in our configuration, looks into PAM to see where the Maildir is (maildir is $HOME for email address users). And we also use it to authenticate via postfix.


auth        required    pam_pgsql.so
account     required    pam_pgsql.so
password    required    pam_pgsql.so


database = virtual
host = localhost
user = virtual_admin
password = #put-real-pw-here
table = maildir
user_column =  email_address
pwd_column = password
pw_type = crypt


For dovecot to work 'properly', it need to know where the maildir is. Besides that, encryption is a good thing. So.

Dovecot config

sed -i -e "s/#put-real-pw-here/!!!REAL_PW_HERE!!!/" ./etc/dovecot/dovecot-sql.conf
bname=./etc/dovecot/dovecot-sql-conf.`date +"%Y-%m-%d--%H-%m-%S"`.bak
cp /etc/dovecot/dovecot-sql.conf $bname
cp ./etc/dovecot/dovecot-sql.conf /etc/dovecot/dovecot-sql.conf
chmod 0600 /etc/dovecot/dovecot-sql.conf
chown root:root /etc/dovecot/dovecot-sql.conf
# ls -l /etc/dovecot/dovecot.conf 
# => -rw-r--r-- 1 root root 4180 Feb 23 07:34 /etc/dovecot/dovecot.conf
cp ./etc/dovecot/dovecot.conf /etc/dovecot/dovecot.conf
/etc/init.d/dovecot restart

needs to be more literate


# This file is opened as root, so it should be owned by root and mode
# 0600.  http://wiki2.dovecot.org/AuthDatabase/SQL
driver = pgsql
connect = host=localhost dbname=virtual user=virtual_admin password=#put-real-pw-here 
default_pass_scheme = CRYPT
password_query = SELECT email_address as user, password FROM maildir WHERE email_address = '%u'
user_query = SELECT '/home/'||maildir AS home, uid, gid FROM maildir WHERE email_address = '%u'


## Dovecot configuration file

mail_location = maildir:~/
passdb {
        args = /etc/dovecot/dovecot-sql.conf
userdb sql {
        args = /etc/dovecot/dovecot-sql.conf

# If you're in a hurry, see http://wiki2.dovecot.org/QuickConfiguration

# "doveconf -n" command gives a clean output of the changed settings.
# Enable installed protocols
!include_try /usr/share/dovecot/protocols.d/*.protocol

# Greeting message for clients.
#login_greeting = Common Lisp (Dovecot ready).

dict {
  #quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
  #expire = sqlite:/etc/dovecot/dovecot-dict-sql.conf.ext

# Most of the actual configuration gets included below. The filenames are
# first sorted by their ASCII value and parsed in that order. The 00-prefixes
# in filenames are intended to make it easier to understand the ordering.
!include conf.d/*.conf

# A config file can also tried to be included without giving an error if
# it's not found:
!include_try local.conf

mlmmj : the mailing list manager

Is setup by default, should have things in the database.

Send mail to the list at listname@example.com
Send off-list mail to the list owner at listname+owner@example.com
Send off-list mail to the list moderators at listname+moderators@example.com
Subscribe with an empty email to listname+subscribe@example.com
Unsubscribe with an empty email to listname+unsubscribe@example.com
Get help on other features of mlmmj by sending an empty email to listname+help@example.com.

The cl-net virtual DB setup

the user with the name cl-net must already exist via adduser

uid=`id -u cl-net` 
gid=`id -g cl-net`
sudo -u postgres psql virtual \
  --command "INSERT INTO virtual_user \
              VALUES ('cl-net', crypt('#password-here', gen_salt('des')), \
                       'Common-Lisp.net Administrator', $uid, $gid)" 

sudo -u postgres psql virtual \
  --command "INSERT INTO domain_name (domain_name) \
              VALUES ('common-lisp.org');"

sudo -u postgres psql virtual \
  --command "INSERT INTO email_domain (domain_name,username) \
              VALUES ('common-lisp.org', 'cl-net');"

sudo -u postgres psql virtual \
  --command "INSERT INTO email_address (domain_name,name) \
              VALUES ('common-lisp.org', 'cl-net');"

sudo -u postgres psql virtual \
  --command "INSERT INTO domain_name (domain_name) \
              VALUES ('common-lisp.net');"

sudo -u postgres psql virtual \
  --command "INSERT INTO email_domain (domain_name,username) \
              VALUES ('common-lisp.net', 'cl-net');"

sudo -u postgres psql virtual \
  --command "INSERT INTO email_address (domain_name,name) \
              VALUES ('common-lisp.net', 'cl-net');"

Add the .forward things to the email alias table

sudo -u postgres psql virtual \
   --command "INSERT INTO email_alias (alias, forward_address) \
              VALUES ('co', 'cl-net');"

cd /tiger/home
for f in $(ls */.forward); 
   fwd="`cat $f`"
   name=`dirname $f`

   sudo -u postgres psql virtual \
   --command "INSERT INTO email_alias (alias, forward_address) \
              VALUES ('$alias', '$fwd');"

blog comments powered by Disqus