Not knowing much about mail protocols, the way I think about solving attachments (and decoupling it from db) is as following. The approach is mostly geared towards web-mail services and is most likely outside the scope of a typical email server. Today's email is mostly web based and mail servers should be providing a solution for it.
1. DB to never store attachments, this is just not efficient and there are many articles out there on this topic. *Components of (modern * *mostly web-based) **mail server* 2. Let there be three components for a (modern) email server: - A proxy with scripting abilities (e.g. nginx). This is where authentication would happen. - A web server (if using nginx then this is not needed), with a email storage docroot say /var/www/aox_storage - Db based email server (aox) - Ideally it should also have Elasticsearch/solr, so all searches can be handed over to search engine (at nginx level). This would be a must have for web based emails. *For storing emails* 3. For email requests, authentication to happen in nginx 3a. Successful auth requests are proxied to aox 3b. Aox to store email information in database as it would 3c. Aox to store email's attachments in /var/www/aox_storage and store the path in its database. It can also store md5/other calculations as needed for duplicate logic. The file it self is not associated to an email address, rather it gets stored in a file table, whose primary key is stored (as ref) to the email address. Hence the same file can be shared b/w many users (I think aox already does that). *Serving email clients (with attachments - as that is the only different case):* There are two options that I can think of: 4a. User connects to their email server imap/pop, aox reads the attachment /var/www/aox_storage and adds it to the email message. 4b. The attachments are (HTTP) links to aox_storage, hence if I open my attachment, it should open it a browser. Draw back to this that it will screw up attachment's extension and meta info and would be incorrectly displayed to the user. *Serving web mail clients (with attachments - as that is the only different case):* This can be done as above, or even better using the following one of the following. *Serving attachments via nginx* So email attachments ared stored and served via nginx but we also want it to be secure. Attachments must not be opened by any one else (unless it is a shared/duplicate attachment). This can also be done in many ways: 5a. One solution could be to attach a decodable hash of salt + user + ip + timestamp + something else to the links in attachments. So a link to my attachment would be file.txt?hash87298279879781jha. This hash should expire within some time so it cannot be used for ever. 5b. Since we are using nginx it might be possible to share a http session with email server. Not sure if it is possible, but I would like to think something can be done. 5c. Worst case scenario, user clicks on the attachment link and it asks the user to authenticate (against aox database and obtain a COOKIE for sroting session in browser). Where the length of this session can be set for 10 years (a long time) so that the user would never have to re-auth (probably not very secure). So now one can setup a cluster of aox + nginx servers, where sharding can be done at nginx proxy (user A,B & C go to node 1 and user B goes to node 2). Even sets of nginx can be load balanced b/w two master-master of aox (+ aox_storage glusterFS or similar replication) for true redundancy. *Possibilities of using external storage services:* aox_storage doesn't have to be a physical hard drive, it can be an external mount. Or there could be hooks for storing it on s3/other s3 like services. However if using s3, the link of s3 should never be shared/shown to the user. The request should always come to nginx server when then it can download the original file from s3 and serve it to the end user. This has drawbacks of increasing bandwidth (but perhaps with s3 HTTP caching it can be reduced a bit if same file is being asked for again and again). However this would definitely solve storage limitations of a single aox server. On Thu, Nov 21, 2013 at 3:46 PM, Stephen R. van den Berg <[email protected]>wrote: > Arnt Gulbrandsen wrote: > >Another option to combat the backup/restore/migrate thing... how about > >having one database per user? Or smaller groups? I discussed it with > >Abhijit, he didn't like it because we'd start too many postgres > >servers. > > I think I have to agree with Abhijit here, the downsides to that > solution are: > - Too much overhead in managing all the different postgresql databases > (and tablespaces and such). > - Too many database connections. > - Deduplication of content becomes difficult if not impossible. > - Doing a restore becomes somewhat of another challenge, because > now you have to figure out which parts of which database to restore. > - The nice thing about a database is that you configure it once, then > use it for everything to keep things stored in a structured way. > Fragmenting/sharding it like this increases complexity too fast. > The natural sharding size would be approximately one postgresql > database per physical server. This proposal would need a lot > more databases per physical server. > -- > Stephen. > >
