smtp + sql = more than it seems so (part 1)

for quite some time i wanted to write about my experiences with combining smtp server with sql server.

this subject for some people might be too boring, for others – it might be seen as a simple howto. but in my opinion it clearly shows that when you combine 2 such technologies outcome is much better then it would seem (it's called synergy in merketoid-speak).

this subject will be divided into several parts, that will be posted separately (yeah, let's get some traffic to website 🙂

also, i hope to extend it in future if/when i'll find something else that can be added/changed in proposed solution.

so, without further ado, let's start:

first part, is about initial setup – i'll tell you what i'm going to use, why, and how to set it up for tests.

so, for system, i have chosen ubuntu 7.1 (kubuntu actually). why? because i have it on my disk 🙂

if you want to setup test system for this (just like i did) you have to either install it somewhere, or do:

# debootstrap gutsy /var/tmp/exim-by-depesz

(/var/tmp/exim-by-depesz after installation is ~ 210mb)

of course location of your sandbox might be different, and you can use any other system.

smtp server is exim. i'm not saying it's the best or compare it with others – it's just that i know exim for the longest time, and i know (at least a bit) it's configuration.

since i will be needing sql connection, i need the more “beefier" version of exim, so, inside of my sandbox i do:

# apt-get install exim4-daemon-heavy

you might also consider installation of exim4-doc-html – to have a handy reference in case something will go wrong. or, simply bookmark online docs

now, to setup “real" exim you will need also some kind of anti virus (clamav?), antispam tool (spam assassin?), ssl “things" and perhaps some other tools (spf? eximon?).

but since this is test only – i will not bother (or, if you'd like to use exim configured “my way", and want me to include information about spam assassin and clamav – just let me know).

exim serves as smtp, but we'll also need some pop3/imap capabilities. for the same reason i choose exim, i choose courier now:

apt-get install courier-pop courier-imap courier-authlib-postgresql

please note that i also installed courier-authlib-postgresql, which shows clearly which database i will use 🙂

apt-get install postgresql-client-8.2 postgresql-contrib-8.2 postgresql-8.2

one note – before you will install postgresql, remember to fill /etc/hosts with something useful. like:

127.0.0.1 localhost xxx

now, we have our base set of tools installed (i would also suggest to add some side tools like vim, strace, netcat, tcpdump for debugging purposes, but it's up to you).

since we have everything installed, let's start configuration.

now, if you're not familiar with debian, you might be shocked to know that what's in /etc/exim4 is not actually configuration, but some kind of configuration template.

actual configuration is written in /var/lib/exim4/config.autogenerated, and is regenerated each time you do /etc/init.d/exim4 restart.

so, just after installation “config" file in /etc/exim4/exim4.conf.template looks like this, and generated real config like this. debians specific config is in /etc/exim4/update-exim4.conf.conf file, and it looks like this.

what we will do with it – you'll see in next chapter.

Chapters list:

  1. part 1
  2. part 2
  3. part 3
  4. part 4
  5. part 5
  6. part 6
  7. part 7
  8. part 8
  9. part 9

3 thoughts on “smtp + sql = more than it seems so (part 1)”

  1. I’d like to see the full integration with ClamAV and spam assassin… especially if they log to the database their stats…

    Seems like this is a nice alternative to Zimbra…

  2. @Ned:
    i will think about it. right now i have ideas for 5-6 posts about exim/courier integration with pg, and various extensions possible with it.
    when i’ll finish – i think i can write something more about clamav and spamassassin.

Comments are closed.