best way to store mail in DB (postfix) [closed]

Question

Please give me a best solution to store all mails (send, receive, forward) in mysql with postfix? and Is this the correct approach?

Confiscate

yes, I'm looking for best schema. – Confiscate

Is dbmail the kind of thing you're looking for? – Disembark

Answer 1

Postfix server by itself does not store email (not for a long time at least) - it can only temporarily queue until it is forwarded to intended recipient(s). Typically only IMAP (or outdated POP3) servers actually store your email.

Most of the open source IMAP servers (Courier, Cyrus, Dovecot...) do support using MySQL as database to keep meta information like list of users and passwords, list of accepted domains, user aliases, etc. But none even attempt to make use of database like MySQL as actual storage backend.

Of course, if you really feel adventurous, you can try to implement SQL IMAP backend of your own. One of the most important considerations for you would be good support of unlimited length VARCHAR or BLOBs (for email attachments) by database engine. Unfortunately, MySQL is not very good server as far as BLOB support goes (you will have to tweak both client and server config for that). Also, you would probably want to have good transaction support - if you care for safety of your email data. For MySQL that means you must use InnoDB engine.

Repine

I think your answer is wrong in some parts.if we talk about outgoing mails the queue is correct. but incoming mails postfix will stores the email into the file system. from there the imap/pop server will pick them up. the storage is done in mbox format usually located at /var/spool/mail/[username] – Selfdrive

@BerndOtt: I never said that postfix stores mails in database, quite the opposite: "none even attempt to use database as actual storage backend". I only said that list of users or aliases can be stored in db. – Repine

This is misleading... accepted answer and plain wrong... You should research how it actually works and edit your answer. – Erythro

@ChazyChaz: this is just your opinion, not backed up by anything. Feel free to add your better answer. – Repine

If you install postfix on a server (no IMAP or POP3) what happens to the mail that you send and receive? Boo! It fades into the nether... – Erythro

@ChazyChaz: basically correct - whatever happens to that mail, there are no traces of it left on server itself. It can be relayed to another servers, but without IMAP/POP3 it simply cannot be stored on our SMTP server. SMTP - simple mail transfer protocol, it has no means to store email other than for very short period of time for queuing purposes. – Repine

Sorry, that last part was sarcasm but in any way I mean offense. I just though the point I wanted to make was obvious. If you take a look at the configs you can see two of them that specifies where you want to store the mail, yes postfix configs: home_mailbox = Maildir/ and virtual_mailbox_base = /mail. So, in my case all mail is stored in /mail, each on it's respective folder inbox/sent and inside the account name parent folder. Also, SMTP is just the protocol postfix uses, postfix is a MTA (Mail Transport System). Postfix devs made it so that it can store mail (until today it's true). – Erythro

@ChazyChaz: you're entitled to your opinion, and I'm to mine. Add your own answer if you think mine is wrong. – Repine

Is what I'm saying an opinion or is it a fact? What proof do you need to see that your answer is wrong? Answers are disabled, the question is closed. This is not about who's wrong or right, you should at least take a look at the configuration and see it for yourself and edit for the sake of new people finding your answer through google or any other search engine... – Erythro

You claim postfix does not store mail and yet you can configure it to do so, to deliver mail to a mailbox in the local filesystem... I'm just saying, edit it to say: "Postfix can deliver mail to a mailbox if configured"... And this is not my opinion, you wrote a lie and the postfix documentation proves it. Also, edit the part where says IMAP (and POP3) servers store mail, because what they do is authenticate virtual users and retrieve their mail, just as Bernd Ott told you... Again, not my opinion, the documentation proves you wrong. If you answer, the least you should do is research... – Erythro

Answer 2

You can try on https://wiki.ubuntu.com/PostfixCompleteVirtualMailSystemHowto Virtual mail, domain and user using mysql and postfix.

Luca

The link you provide doesn't actually store emails send or received into mysql, as requested. Only virtual users and domains are saved into mysql. Emails content is still saved in the filesystem. – Anabel