dbmail

• All inbound and outbound mail should be archived to a separate database.

• Mail should be archived according to userid and made available via IMAP so users can retrieve messages they “accidentally” delete

• Messages should be stored in mailboxes that are dynamically created according to direction and time of transmission. The end result would look like this:

(user1)INBOX/Incoming/YYYY/MM
(user1)INBOX/Outgoing/YYYY/MM
(user2)INBOX/Incoming/YYYY/MM
(user2)INBOX/Outgoing/YYYY/MM

…

• Configuration should be kept as simple as possible, but no simpler.

• 2 mail servers exist; mail.example.com and archive.example.com
• Both servers will run postfix and DBMail and are listed in DNS.
• Users will not have local accounts on either host.
• Users must have these aliases on archive.example.com:
  • userid@archive-inbound.example.com
  • userid@archive-outbound.example.com

Tested with the following:

• DBMail 2.0.7, 2.1.7
• Postfix 2.2.2.2
• MySQL 5.0.16
• GNU/Linux (pick your favorite)

This project is still in the design phase and is not considered to be optimal at this time. Everything here has been tested and does work but will be optimized over time. I will do my best to keep this information current as progress is made.

Also, I'd like to thank the folks in #dbmail, #postfix, and #dspam for all their help.

Let's dig in…

• ./configure –with-mysql && make && sudo make install
• Copy dbmail.conf to /etc and modify
• Start dbmail-imapd

Install as per DBMail documentation.

/etc/postfix/main.cf

transport_maps      hash:/etc/postfix/transport
sender_bcc_maps     mysql:/etc/postfix/sender_bcc_maps.cf
recipient_bcc_maps  mysql:/etc/postfix/recipient_bcc_maps.cf

Only accounts that are associated with the specified client ID number will get translated into a valid email address to be used for archiving.

By modifying the client_idnr section of the query, admins can control what groups of users will be archived and prevent postfix from generating bcc addresses for undesired accounts (e.g. system and RFC).

In my case, I assign business generics with client_idnr 4 and real accounts with client_idnr 5.

2006-09-13 Update

I have decided to remove the information for postfix 2.1 since I'm not running it any more and can't test enhancements. The queries below are in use on 2.2.

Also, I've improved these queries in order to overcome an oversight where email address used by the archive may have been generated inappropriately in the case where an inbound message was sent to an alias rather than a valid userid. For example, user bob exists and has 2 entries listed in the dbmail_aliases table:

bob@example.com b@example.com

Using the previous logic, recipient_bcc_maps.cf would have returned an address of b@archive-inbound.example.com for messages sent to b@example.com. The MTA for archive-inbound.example.com would have been dumbfounded since we aren't maintaining aliases in that database.

The following queries address that issue…

/etc/postfix/sender_bcc_maps.cf

user = dbmail                                                                                                               
password = xxxxxxx                                                                                                         
hosts = mysql.example.com mysql2.example.com
dbname = dbmail

query = SELECT concat(du.userid, '@archive-outbound.example.com') FROM dbmail_users du, dbmail_aliases da WHERE da.deliver_to = du.user_idnr AND da.alias = '%s' AND (da.client_idnr = 4 OR da.client_idnr = 5)

/etc/postfix/recipient_bcc_maps.cf

user = dbmail                                                                                                               
password = xxxxxxx                                                                                                         
hosts = mysql.example.com mysql2.example.com
dbname = dbmail

query = SELECT concat(du.userid, '@archive-inbound.example.com') FROM dbmail_users du, dbmail_aliases da WHERE da.deliver_to = du.user_idnr AND da.alias = '%s' AND (da.client_idnr = 4 OR da.client_idnr = 5)

/etc/postfix/transport

localhost                     dbmail-lmtpd.127.0.0.1:24
example.com                   dbmail-lmtpd.127.0.0.1:24
archive-inbound.example.com   smtp:[archive.example.com]
archive-outbound.example.com  smtp:[archive.example.com]

Build maps and reload postfix

sudo /usr/sbin/postmap /etc/postfix/transport && /usr/sbin/postfix reload

• ./configure –with-mysql && make && sudo make install
• Copy dbmail.conf to /etc and modify
• Start dbmail-imapd

In general, follow DBMail documentation to install MySQL. Just make these changes and additions where appropriate:

• Create dbmailarc database

mysql> create database dbmailarc

• Create dbmailarc user

mysql> grant all on dbmailarc.* to dbmailarc@localhost identified by 'DBPASSWD';

• Create postfix user (used for virtual user lookups)

mysql> grant select (userid, user_idnr, client_idnr) on dbmailarc.dbmail_users
       to postfix@localhost identified by 'DBPASSWD';
mysql> grant select (alias_idnr, alias, deliver_to) on dbmailarc.dbmail_aliases
       to postfix@localhost identified by 'DBPASSWD'; 

• Create a trigger to set all mailboxes read-only

mysql> delimiter //
mysql> CREATE TRIGGER t01_bi
       BEFORE INSERT ON dbmail_mailboxes
       FOR EACH ROW
       BEGIN
         SET NEW.permission = 1;
       END;//

- Although the Trash folder is read-only as a result of our trigger, user's can still populate the mailbox when attempting to delete mail. In order to minimize confusion, one of the following solutions will need to be investigated:

1. Remove the Trash mailbox via trigger or cron job - Probably no good. This might generate client side errors.
2. Remove messages from the Trash mailbox via cron job - Probably the way to go.

/etc/postfix/main.cf

mydestination = archive-inbound.example.com, archive-outbound.example.com
local_recipient_maps = mysql:/etc/postfix/virtual_alias_maps.cf
transport_maps = /etc/postfix/transport

# Prevent MAILER-DAEMON notifying message sender with 'unknown user'
# in the event of a non-existant or misconfigured account
unknown_local_recipient_reject_code = 450

/etc/postfix/virtual_alias_maps.cf

user = postfix
password = xxxxxx
hosts = unix:/var/run/mysqld/mysqld.sock
dbname = dbmailarc

# If using postfix 2.1, uncomment the next 4 lines
# table = dbmail_users du, dbmail_aliases da
# select_field = userid
# where_field = da.alias
# additional_conditions = and da.deliver_to = du.user_idnr

# This is for postfix 2.2 or greater
query = SELECT alias FROM dbmail_aliases WHERE alias = '%s'

/etc/postfix/transport

archive-inbound.example.com       dbmail-archive-inbound:
archive-outbound.example.com      dbmail-archive-outbound:

/etc/postfix/master.cf

dbmail-archive-inbound  unix    -       n       n       -       -       pipe
      flags=RDO user=dbmail argv=/usr/local/sbin/dbmail-archive-inbound ${recipient}

dbmail-archive-outbound  unix    -       n       n       -       -       pipe
      flags=RDO user=dbmail argv=/usr/local/sbin/dbmail-archive-outbound ${recipient}

/usr/local/sbin/dbmail-archive-inbound

#!/bin/sh
# email account should be passed as $1

cat - | /usr/local/sbin/dbmail-smtp -u "$1" -m "INBOX/Incoming/$(/bin/date +%Y)/$(/bin/date +%m)"

Update! Versions of dbmail-smtpd equal to or greater than 2224 should use -M rather than -m. See this for more info.

/usr/local/sbin/dbmail-archive-outbound

#!/bin/sh
# email account should be passed as $1

cat - | /usr/local/sbin/dbmail-smtp -u "$1" -m "INBOX/Outgoing/$(/bin/date +%Y)/$(/bin/date +%m)"

Update! Versions of dbmail-smtpd equal to or greater than 2224 should use -M rather than -m. See this for more info.

1. Write a script to notify us when a user exists in the main database but has no account in the archive database.

Fixed in SVN 2252. Thanks Aaron Stone! :)