postfix + mysql

@vitre0us:

a guy i worked with a while back - who was a smarter man than i - set a box up with postfix that used mysql to not only store virtual account information, but also the mailboxes…

i believe he somehow had to patch postfix to do this…

I'm not sure, but I think there's a README.MYSQL file in the source distribution of Postfix that describes how to set up a MySQL-based Postfix system. But remember, one thing is getting the SMTP server to store the messages in a database - another is finding a POP3/IMAP server which is able to read the messages in the database.

@vitre0us:

a guy i worked with a while back - who was a smarter man than i - set a box up with postfix that used mysql to not only store virtual account information, but also the mailboxes…

Interesting. What are the advantages of doing this?

@milo:

Interesting. What are the advantages of doing this?

Not having to deal with Maildir's come to mind …

Keeps a file system cleaner ….

Makes it easier to deal with external authentication (via SASL) ….

Bill Clinton

Replication. :twisted:

since LDA's can pipe emails to programs, it wouldnt be too hard to create a program/script that takes an email as input and inserts it into a table in a database..

@vitre0us:

a guy i worked with a while back - who was a smarter man than i - set a box up with postfix that used mysql to not only store virtual account information, but also the mailboxes…

i believe he somehow had to patch postfix to do this…

i was just wondering if anyone was familiar with this technique, knew how to do it and how well it works?

course this was for an isp with far more customers than a tiny linode would want to see, but i thought it would be worth exploring…

I am working on getting this setup on my new linode. I have proftpd pulling from mysql, postfix and courier will be in there tonight. Also libpam-mysql lets you have pam pull from the DB, and there is a libnss-mysql.

I don't know about maildir in mysql, I haven't come across anything about that, but when I get home I can give you some of the bookmarks I have. You can get your users, aliases, and forwards all in mysql. Try googling for postfix+courier+mysql or something along those lines.

Just curious as why someone would want this? That is, what are the advantages of storing email in an SQL server?

It's certainly faster than mbox format, which crams every folder's email into an enormous file.

It's more efficient than maildir because maildir creates a file for every email, cluttering your filesystem with a billion tiny files.

I'd imagine it must be orders of magnitude faster for something like searching, since you don't have to parse either the huge files or read a billion tiny ones. It's ideal for a database; let the program that's designed to handle things like this do its work.

MS Exchange deals with email in this fashion, with a database, and that enables it to handle them very efficiently. That and calendaring are its main advantages over OSS, although with postfix-mysql it may just be the calendaring.

@Xan:

It's certainly faster than mbox format, which crams every folder's email into an enormous file.

It's more efficient than maildir because maildir creates a file for every email, cluttering your filesystem with a billion tiny files.

I'd imagine it must be orders of magnitude faster for something like searching, since you don't have to parse either the huge files or read a billion tiny ones. It's ideal for a database; let the program that's designed to handle things like this do its work.

MS Exchange deals with email in this fashion, with a database, and that enables it to handle them very efficiently. That and calendaring are its main advantages over OSS, although with postfix-mysql it may just be the calendaring.

Heh. "MS Exchange" and "efficient" are two words that really don't belong in the same sentence. Also MS Exchange's calendaring is the worst, most braindead, most broken thing I have ever seen to come out of Redmond, and that's saying quite a lot.

Your filesystem is a type of database. Maildir uses it quite efficiently. A billion files are easier to deal with using standard Unix tools than a database.

I think it's fine to store your mail in a relational database if that's what you want to do, but there's no reason to knock Maildir. It works very well.

Hmm… interesting points. I would think that with a filesystem like Reiserfs3, which is supposedly very efficent with small files, the disadvantages of Maildir become negligable.

I can see why there might be some benefit in searching, OTOH (though if you wanted to do body searches, you'd have to somehow index the body, no?)

There just seems to be a general trend to move everything into a MySQL database these days; it seems to me that the filesystem is more than sufficient for many things (such as mail) and does not rely on some potentially buggy server software running (and using more resources).