Author: Adam Kosmin
Date: 1/06/06
(user1)INBOX/Incoming/YYYY/MM
(user1)INBOX/Outgoing/YYYY/MM
(user2)INBOX/Incoming/YYYY/MM
(user2)INBOX/Outgoing/YYYY/MM
…
Tested with the following:
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…
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
In general, follow DBMail documentation to install MySQL. Just make these changes and additions where appropriate:
mysql> create database dbmailarc
mysql> grant all on dbmailarc.* to dbmailarc@localhost identified by 'DBPASSWD';
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';
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:
/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.
Fixed in SVN 2252. Thanks Aaron Stone! :)