I do it because I wanted to insert the mail dynamically (based on user
and date). This is just my approach of course and may not be inline with
your needs. Just throwing the idea out.
Adam
Rod G wrote:
Thanks for sharing your information Adam.
Is procmail necessary for inserting the mail into the MySQL database?
I'm pretty green on procmail and how dbmail inserts into the database.
Thanks,
Rod
On 11/23/05, Adam Kosmin <[EMAIL PROTECTED]> wrote:
I'm building this very system myself using dbmail/postfix/procmail/mysql
5.x. Here are some tips to get you started...
First, my assumptions:
I assume you will have an archive server (archive.example.com) running
another instance of postfix and dbmail.
Users will have an account on the archive server. I do this because I'm
making the archives accessible via IMAP. That way, user's can retrieve
their own mail when they delete it :)
On the main postfix server...
[/etc/postfix/main.cf]
sender_bcc_maps = mysql:/etc/postfix/virtual-alias.cf
recipient_bcc_maps = mysql:/etc/postfix/virtual-alias.cf
The purpose of these directives is to instruct postfix to automatically
bcc based on the sender and recipient. Both will lookup the alias and
return the userid via sql queries. The userid will be concatenated to
@archive.example.com in order to construct a valid email address for
postfix to bcc to.
[etc/postfix/virtual-alias.cf]
user = dbmail
password = xxxxxxx
hosts = localhost
dbname = dbmail
select_field = concat(userid, '@archive.example.com')
table = dbmail_users du, dbmail_aliases da
where_field = da.alias
additional_conditions = and da.deliver_to = du.user_idnr
[/etc/postfix/transport]
archive.example.com smtp:[archive.example.com]
So postfix knows how to send the bcc...
Now on the archive server, I've chosen to use MySQL 5.x in order to make
use of triggers. This trigger will set all newly created mailboxes to
read-only.
mysql> delimiter //
mysql> CREATE TRIGGER t01_bi
BEFORE INSERT ON dbmail_mailboxes
FOR EACH ROW
BEGIN
SET NEW.permission = 1;
END;//
Still on the archive server, configure postfix to use procmail to
deliver messages and add this (or some improved version to
/etc/procmailrc)...
SHELL = /bin/sh
PATH = /bin:/usr/bin:/usr/local/bin
LOGFILE = /var/log/procmail.log
LOGABSTRACT = "all"
VERBOSE = "on"
# for backups
SPOOL = /var/spool
BUP_SPOOL = $SPOOL/procmail_backup
#:0 c:
#$BUP_SPOOL
YEAR = `date +%Y`
MONTH = `date +%m`
# This will archive incoming mail
:0
* ^TO_ ()\/[a-z0-9_]*.example\.com
{
EMAIL = $MATCH
:0
* ^TO_ ()\/[a-z0-9_]*
{
USER = $MATCH
:0
| /usr/local/sbin/dbmail-smtp -u $USER -m
"INBOX/$YEAR/$MONTH/Incoming"
}
}
# This will archive outgoing mail
:0
* ^From ()\/[a-z0-9_]*.example\.com
{
EMAIL = $MATCH
:0
* ^From ()\/[a-z0-9_]*
{
USER = $MATCH
:0
| /usr/local/sbin/dbmail-smtp -u $USER -m
"INBOX/$YEAR/$MONTH/Outgoing"
}
}
I'm still working on the procmailrc file actually, but you should get
the idea. The goal is to store messages in user-accessible mailboxes
that are autocreated depending on the date. This way, users can connect
via IMAP and have read-only access to their archive.
Let me know if I've missed anything and I'll see what I can dig up.
Also, I'd be interested in hearing any ideas for improvement. So far,
everything is working nicely over here.
Best,
Adam Kosmin
Rod G wrote:
Hello,
I am interested in introducing dbmail into a system without modifying
how the current system works. I would like to use dbmail for message
archiving purposes. My MTA is postfix. I would like to setup all mail
-- sent and received -- to be archived into a database. Is this
possible to setup with dbmail and postfix? Any suggestions on how to
go about doing this?
Thanks,
Rod
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
--
Adam Kosmin
GNU/Linux SA
Visual Trading Systems, LLC
Empire State Building
350 Fifth Avenue, Suite 6420
New York, NY 10118, USA
Email [EMAIL PROTECTED]
Phone 1 (212) 871-1747 ext. 340
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
--
Adam Kosmin
GNU/Linux SA
Visual Trading Systems, LLC
Empire State Building
350 Fifth Avenue, Suite 6420
New York, NY 10118, USA
Email [EMAIL PROTECTED]
Phone 1 (212) 871-1747 ext. 340
