dbmail_cplogs patch

The dbmail_cplogs patch makes it possible to log messages moved/copied to and from certain mailboxes. The patch currently exists for the 2.2 series, available on the dbmail list. It might be included in the dbmail 2.4 series (not released, still in development).

It's primary purpose is to create a convenient way of letting IMAP users control the antispam filter by teaching it what is spam and what's not. It may be used for any other purpose too.

As of now it has been tested with mysql. The patch also supports sqlite, but I'm not aware of anyone using it. Postgresql schema changes are not included in the patch, feel free to add them.

general antispam setup

A spam filter is in front of dbmail, SA or dspam for example. Ham messages (messages identified by the antispam program as NOT spam) are delivered as usually to the users' Inbox or some other folder if sieve filtering is involved. Identified spam messages on the other hand are delivered to the users' spam folder, where he/she can manually inspect them.

Spam training is controlled using IMAP copy. Every user has his/her own spam folder as mentioned. By moving mail from any folder to the spam folder, the moved message will be retrained as spam. When moving a message from the spam folder, the message is retrained as innocent, exceptions such as the Trash folder are handled.


To install the patch, apply it to the dbmail source and compile as usual.

Some modifications are done to the dbmail schema, look at the file sql/mysql/2_2_x-2_3_0.mysql generated by the patch or the sqlite equivalent. Apply this after you have created the tables. It's OK to apply these changes to a production db, but as always NO guarantees. Make backups and inspect the SQL yourself first.

usage and config

When the cplog_flag column value is set to 1 in dbmail_mailboxes for some mailbox, movements from and to this mailbox will be logged in the dbmail_cplogs table. These are the users' spam folders.

It's a good idea to have a nightly script which checks that users have a spam folder and if not, creates it with cplog_flag=1. So when adding a new user you simply have to wait for the script to run or run it yourself. And if some user deletes the entire folder by accident, it will be recreated automatically (somehow this happens more often in case of spam folders).

When trash_flag is set to 1, it is considered a Trash mailbox. Movements from a mailbox with cplog_flag=1 to this mailbox will NOT be logged. Otherwise if a user deletes spam messages from the spam folder with a client that first moves the messages to a Trash mailbox (Thunderbird), all the spam would be relearned as ham which is not expected behaviour.

Marking Trash folders with trash_flag=1 should be done by the nightly script too.

It is now the job of an external cron script, to log in to the dbmail db, look at the dbmail_cplogs table and based on the message_idnr's relearn the messages appropriately. If the direction is 1, this message should be relearned as spam. If 0 then the message should be relearned as ham.


When using dspam, have the X-DSPAM-Signature in the headers. dbmail will have the value of the signature in the dbmail_headervalue table. You can query that and give dspamc only the signature for relearning messages. You don't even have to parse the messages for the signatures now!

When using dspam and the signature in the headers, it's a good idea to have the MTA remove X-DSPAM-* headers before handing the message off to dspam. It's even good to remove it on outgoing mail too. For postfix use /^(X-DSPAM-.*)/ IGNORE with the header_checks feature. essay writing service

dbmail_cplogs.txt · Last modified: 2011/07/19 11:06 by robertsoncallista
DBMail is developed by Paul J Stevens together with developers world-wide