| 1 |
Hi folks ! |
| 2 |
|
| 3 |
What does do MySQMail ? |
| 4 |
----------------------- |
| 5 |
MySQmail helps keeping all the qmail MTA login and trafic informations in a |
| 6 |
database, and IN REAL TIME. |
| 7 |
|
| 8 |
This tool was written mostly to work with Domain Technologie Control (DTC). |
| 9 |
MySQMail is not mandatory for DTC, but it's a way better to have it. |
| 10 |
|
| 11 |
I needed to have qmail accounting, so I had to write couples of binaries. |
| 12 |
Fortunatly qmail is modular and it's realy easy to make some add-ons. Here is |
| 13 |
what MySQmail package will add to qmail: pop will use MySQL backend for |
| 14 |
password, and both SMTP and POP will have trafic logged into MySQL by mailbox. |
| 15 |
|
| 16 |
MySQmail requipement |
| 17 |
-------------------- |
| 18 |
- mysql client library and dev files |
| 19 |
- dotconf lib for reading conf file /etc/mysqmail.conf |
| 20 |
|
| 21 |
MySQmail binaries |
| 22 |
----------------- |
| 23 |
MySQMail is made of few binaries. It currently consists of 4 loggers that |
| 24 |
are all working IN REAL TIME, so you can monitor your email traffic in real |
| 25 |
time as well. |
| 26 |
|
| 27 |
- mysqmail-postfix-logger: A mysql logger for postfix. Reads from syslog |
| 28 |
using tail -F and send corresponding results in a MySQL table, so you |
| 29 |
have monthly statistics. |
| 30 |
|
| 31 |
- mysqmail-courier-logger: Seaches for POP3 / IMAP connections printed in |
| 32 |
the syslog, and generate monthly traffic report in SQL. |
| 33 |
|
| 34 |
- mysqmail-dovecot-logger: Same as mysqmail-courier-logger, but for the |
| 35 |
much faster dovecot daemon. |
| 36 |
|
| 37 |
- mysqmail-pure-ftpd-logger: Logs traffic from |
| 38 |
/var/log/pure-ftpd/transfer.log or /var/log/pureftpd.log (which ever is |
| 39 |
present in your system, the first one above having priority), using tail |
| 40 |
-F, and writes a score board in MySQL. |
| 41 |
|
| 42 |
- mysqmail-qmail-logger: A mysql logger for qmail-send. It will read from |
| 43 |
the syslog using tail -F, and all successfull delivery (either remote or |
| 44 |
local) will generate a tiny mysql record containing remote and local user |
| 45 |
and domain name. |
| 46 |
|
| 47 |
- mysqmail-checkpwd: A mysql alternative checkpassword for qmail-pop3d. |
| 48 |
Reads the passwords and mailbox account infos for pop3 from a mysql db, |
| 49 |
and set env variable DOMAINNAME and USER to be used for putting trafic |
| 50 |
info in the DB. |
| 51 |
|
| 52 |
- mysqmail-pop3d-patch: A qmail-pop3d.c patched version of the orriginal one |
| 53 |
from DJB that logs the trafic of all pop3 sessions users into the password |
| 54 |
table. |
| 55 |
|
| 56 |
MySQmail config file |
| 57 |
-------------------- |
| 58 |
In order to read it's /etc/mysqmail.conf config file, all of the MySQmail |
| 59 |
binaries uses libdotconf. |
| 60 |
This small library is perfect for reading one program config file, and I |
| 61 |
encourage all developpers to use it. It's available for all Unix and is |
| 62 |
compatible with config files like apache's httpd.conf. |
| 63 |
|
| 64 |
MySQmail has been written using libdotconf 1.0.9, with it's config files |
| 65 |
directive set as case insensitive. |
| 66 |
|
| 67 |
No need to explain the content of this config file, I suppose you know |
| 68 |
what is a mysql login/password... |
| 69 |
|
| 70 |
MySQail instalation |
| 71 |
------------------- |
| 72 |
Get qmail sources, apply my qmail-pop3d patch, and recopile qmail-pop3d |
| 73 |
binary. Don't forget that you need both mysqlclient and dotconf library |
| 74 |
installed in your system (and -dev or -devel flavor maybe). The provided |
| 75 |
Makefile only adds mysql and dotconf to linking. Install the new |
| 76 |
qmail-pop3d it according to your system (normaly in /var/qmail/bin). |
| 77 |
|
| 78 |
Compile and install mysqmail-checkpwd and mysqmail-logger using: |
| 79 |
|
| 80 |
> make install |
| 81 |
|
| 82 |
Edit your /etc/mysqmail.conf so all 3 binaries can log in the mysql |
| 83 |
server. |
| 84 |
|
| 85 |
Then edit your qmail start file so it uses the new programs for loggin. |
| 86 |
Note that mysqmail-logger will flush all unknown or non-trafic logs to |
| 87 |
standard output, so you can still keep a logfile for status/alert/etc... |
| 88 |
Keep the normal system logger for qmail-smptd, only qmail-send gives |
| 89 |
accounting informations. Change the qmail checkpasswd to the new one for |
| 90 |
pop3. |
| 91 |
|
| 92 |
Here is what I have on my debian machine. Note that this may differ on |
| 93 |
some other system, aspecialy if you are using the svscan (daemontools) |
| 94 |
stuff, but it's by the way a good example. |
| 95 |
|
| 96 |
sh -c "start-stop-daemon --start --quiet --user qmails \ |
| 97 |
--exec /usr/sbin/qmail-send \ |
| 98 |
--startas /usr/sbin/qmail-start -- \"Maildir\" \ |
| 99 |
/usr/sbin/mysqmail-logger >>/var/log/qmail.log &" |
| 100 |
|
| 101 |
sh -c "start-stop-daemon --start --quiet --user root \ |
| 102 |
--exec /usr/bin/tcpserver -v -- \ |
| 103 |
0 pop-3 /usr/sbin/qmail-popup `hostname` \ |
| 104 |
/usr/sbin/mysqmail-checkpwd /usr/sbin/qmail-pop3d Maildir |
| 105 |
|
| 106 |
The POP MySQL Database |
| 107 |
---------------------- |
| 108 |
Be sure that you have your tables corresponding to the ones described in the |
| 109 |
*.sql scripts. Remember that because of virtual domains support, pop3 logins |
| 110 |
will be of the style "[email protected]" (and NOT simply "user"). |
| 111 |
|
| 112 |
Credit |
| 113 |
------ |
| 114 |
All code and patch done by Thomas GOIRAND <[email protected]> |
| 115 |
|
| 116 |
Mysql password check code was written reading the code from Jedi's alternative |
| 117 |
checklocalpwd programm. Thanks him for that good example and usefull auth. |
| 118 |
Mysql logger was written from scratch. |
| 119 |
|
| 120 |
Message to DJB |
| 121 |
-------------- |
| 122 |
On next version, please add login & traffic log to your pop3d. Please include |
| 123 |
all the good ideas other developpers have add to your software and release a |
| 124 |
toaster with more options. Why don't you release something with all options |
| 125 |
(relay control, SSL, password auths, spam prevention, etc...) BUT configurable |
| 126 |
with some simple config files ? |