Tag Archives: mysql

Per-user spam folder with Amavisd-new, Postfix+MySQL, virtual users, Dovecot


Long story short

This is a short summary for those who don’t need a pedantic step by step guide. If you want to know all the details, you should skip to the next section.

My objective is to deliver all spam messages to a dedicated IMAP folder called “spam”. This way my users can easily fetch messages which get erroneously identified as spam. Here is how I did it.

I configured Amavisd-new to deliver spam messages to a specific address extension (e.g. user+spam@domain.tld) using the $addr_extension_spam directive. Here I had my first problem: Amavisd-new ignores this directive for all domains which are not considered local. Therefore you have to tell Amavisd-new which domains are local or the whole thing WON’T WORK.

You have two options:

  • statically define the list of local domains using @local_domains_acl
  • if your account information is stored in MySQL database, tell Amavisd-new to fetch the list of local domains using a SQL query, like the following: $sql_select_policy = ‘SELECT “Y” as local FROM domain WHERE CONCAT(“@”,domain) IN (%k)’;

Next step is to configure the LDA (Local Delivery Agent) to deliver all messages with a specific address extension to a specific subfolder of the respective mailbox. This can be achieved by changing the LDA used by Postfix from Postfix itself to something which allows for rule filtering like procmail, maildrop or Dovecot.

If your mailboxes are also “system accounts”, you should stick to procmail or maildrop, which are fairly easy to setup. For example, with procmail you would use:

mailbox_command = procmail -a “$EXTENSION”

and then setup a .procmailrc which delivers mails for messages matching ARG ?? ^^spam^^ to a specific subfolder.

If you have a virtual users setup this gets tricky. The directive which defines the LDA for virtual accounts in main.cf is virtual_transport.

In a virtual users setup which relies on a MySQL backend, the easiest LDA to configure is Dovecot. We have to create a new “transport” inside master.cf like this:

dovecot      unix    –    n    n    –    –    pipe
flags=DRhu user=vmail:vmail argv=/usr/lib/dovecot/deliver -f ${sender} -d ${user}@${nexthop} -n -m ${extension}

And then reference it inside main.cf using the aforementioned virtual_transport directive (virtual_transport = dovecot).

Finally you must enable the sieve plugin in the lda section of dovecot.conf:

and create the default.sieve script with the following content:

That’s all folks!

Short story long

Here is an exhaustive description of this setup, with many useful hints to solve common problems.

The problem with filtering spam

Filtering spam is a endless battle. Spam filters are prone to false positives and SpamAssassin is no exception.

That’s why we usually configure our mail servers to quarantine spam messages, instead of rejecting/deleting them.

Here comes what I usually refer to as “the quarantine problem”.

Unfortunately most content filters tend to put all quarantined messages in a single directory which can only be accessed by the sysadmin. Then sysadmins get hammered by questions like this all the time: “I didn’t receive the e-mail from XYZ, can you check if it’s been quarantined by the spam filter?”

Users are angry because they have to wait to receive their “very-important” email, sysadmins are angry because they have to waste their time on a monkey activity. Nobody likes the “quarantine spam messages in a single directory” strategy.

Many solutions have been proposed to allow users to retrieve spam messages from the quarantine directory by themselves: the most successful was Maia Mailguard (http://www.maiamailguard.com/maia/wiki), which is now replaced by MailZu (http://sourceforge.net/projects/mailzu/).

Unfortunately all these solutions are quite clumsy to setup, they do not scale very well and they can be a pain to maintain.

Content filters also allow to only tag messages as spam, and deliver them to the user’s mailbox together with regular messages, but users don’t like this solution for two reasons:

  • they have to download spam messages anyway (which can be expensive when roaming)
  • they have to setup filters on their MUAs to store spam messages in a separate folder (which can be difficult to do on mobiles)

There is a simple solution to “the quarantine problem” which exploits the power of IMAP to have different folders in a single mailbox.

Here is the strategy we are going to adopt in this article:

  • the first time a user accesses his mailbox via IMAP, a new folder called “INBOX.spam” is created.
  • whenever a message is tagged as spam, it’s delivered to the address: user+spam@domain.tld instead of user@domain.tld
  • if the subfolder INBOX.spam exists, the messages is stored under INBOX.spam instead of INBOX. If the subfolder doesn’t exist, the message is delivered to INBOX.
  • messages in the INBOX.spam folder older than 60 days are automatically deleted.

If the user wants to check whether his very-important email was marked as spam, he can just open the Spam folder and try to find it. Normally he won’t need to download spam messages.

If he is using only POP3 to access his email, this “quarantine” system won’t be activated and he’ll have to rely on his MUA’s filters to store the spam in a separate folder.

Alternatively, the sysadmin can provide him with a webmail which uses the IMAP protocol.


In this howto we assume to be running the following software:

  • MTA: Postfix with virtual users served off a MySQL database
  • LDA: Dovecot delivery agent with sieve plugin
  • POP3/IMAP: Dovecot
  • Content filter: Amavisd-new
  • Spam filter: SpamAssassin

Configuring Postfix for virtual users stored in a MySQL database is out of the scope of this howto. There is plenty literature available on Postfix Website (http://www.postfix.org/docs.html) and official documentation (http://www.postfix.org/MYSQL_README.html).

The integration of Amavisd-new into Postfix is trivial as well and there are already plenty of howtos available.

Finally we assume Dovecot is already configured to authenticate users using the MySQL database. Many howtos already describe how to implement this and it’s also described in the official documentation of Dovecot (http://wiki.dovecot.org/AuthDatabase/SQL).

We shall assume that you have a working setup with virtual domains stored on MySQL database.

The big picture

Here is how the whole thing is going to work:

  1. an email comes in, via SMTP
  2. Postfix check if the mailbox exists and passes it to Amavisd-new
  3. Amavisd-new checks if the email is spam. Then it checks if the recipient of the email is a local domain or not. If both conditions are true, then the message recipient address (user@domain.tld) is replaced with user+spam@domain.tld. In all other cases (e.g. if domain.tld is not a local domain) the recipient address is left untouched.
  4. Amavisd-new passes the email back to Postfix
  5. Postfix passes the email to the LDA. As reported in the “Premises” paragraph, in our case Dovecot acts also as a LDA, not only as POP3/IMAP server!
  6. Dovecot uses the default sieve rules to decide where to store the message. If the recipient of the message contains a “spam” extention (like in the case of user+spam@domain.tld) and the INBOX.spam subfolder exists, then the message is delivered to user@domain.tld under then INBOX.spam subfolder. In all other cases (that is if the recipient is user@domain.tld or INBOX.spam doesn’t exist) the message is delivered to INBOX.



You will then need to change the following lines in /etc/postfix/main.cf:

and add the following line to /etc/postfix/master.cf:

This is needed to make Postfix use Dovecot as the LDA. We need to switch to Dovecot as LDA because it has support for sieve rules. Using these rules we can decide in which folder to store a message based on a certain condition.


We have to tell Dovecot to automatically create the subfolder “Spam” on the first time the user accesses the mailbox via IMAP. For this we need to enable the autocreate plugin:

Next step is to enable the cmusieve plugin and configure Dovecot to apply a special rule to all messages processed by the delivery agent (LDA):

In our case the filtering rule is stored inside /var/lib/sieve/default.sieve:

Be aware that the file /var/lib/sieve/default.sieve is compiled by Dovecot the first time it’s used. Dovecot must store the binary version of this file (/var/lib/sieve/default.sievec) in the same dir as the script so it requires write access to the directory /var/lib/sieve/.

Dovecot delivery agent is called by Postfix and Postfix uses the uid/gid specified in /etc/postfix/main.cf to run it. In my case this is uid 2000 / gid 2000. Check your main.cf for the correct values, look for the lines:

Then make sure that /var/lib/sieve/ is owned by the same uid/gid specified above. If everything is ok, when the first email comes in, Dovecot will create a new file called /var/lib/sieve/default.sievec.

You can check the syntax of the file default.sieve on this nice online sieve compiler: http://libsieve-php.exit0.net/.


Depending on the distro you are using, your Amavisd-new config file can come as a single config. file called amavisd.conf or multiple small config files which are loaded sequentially.

In my case I’m using Debian Lenny and I store all my customized Amavisd-new settings inside /etc/amavis/conf.d/50-user .

Here is what must be modified in the standard Amavisd-new configuration:

Note for SysCP users: if you are running SysCP the correct query is SELECT “Y” as local FROM panel_domains WHERE CONCAT(“@”,domain) IN (%k).

The $sa_* config items are pretty much self explanatory: you can tune their values to your likening. If SpamAssassin assigns a score higher than sa_tag2_level_deflt to the message, it will be delivered to the user’s INBOX.spam subfolder.

The @addr_extension_spam_maps directive (together with $replace_existing_extension and recipient_delimiter) tells Amavisd-new to rewrite the recipient address of spam messages from user@domain.tld to user+spam@domain.tld .

The @lookup_sql_dsn directive tells Amavisd-new how to connect to the MySQL database used by Postfix to lookup the local domains, mailboxes, and aliases.

The $sql_select_policy directive contains a MySQL query which fetches the list of local domains from the MySQL database used by Postfix. Depending on your Postfix setup you may need to change the query so that it returns “Y” if the domain (%k) is listed inside the “domain” table.

CAVEAT: THIS IS CRITICAL TO MAKE THE SETUP WORK!!! If Amavisd-new doesn’t know that the domain is a local one, IT WON’T CHANGE THE RECIPIENT ADDRESS TO user+spam@domain.tld . The email will always be delivered to user@domain.tld and stored inside INBOX instead of INBOX.Spam.

Amavisd-new only modifies the original message if both the following conditions are met:

  • the domain is known to be a local domain
  • the spam score is higher sa_tag2_level_deflt

The $localpart_is_case_sensitive directive, when set to false, tells Amavisd-new to treat domain names as case insensitive.

Finally, $final_spam_destiny  tells Amavisd-new to deliver spam messages instead of simply quarantine/reject them.

Purging old spam messages

The INBOX.spam subfolders will tend to grow with time. We want to setup a cronjob which purges messages older than 60 days. Assuming that our virtual mailboxes are stored under /var/mail/virtual, we may use add to crontab something like this:


Thumbs up! We are done :)
Please send me your comments if something is not clear or not working as expected!