[Dovecot] Possible to log IMAP connections to MySQL Table?
Is it currently possible to log all IMAP connection attempts to a MySQL table? Thanks.
Corey
Corey Shaw wrote:
Is it currently possible to log all IMAP connection attempts to a MySQL table? Thanks.
Sure. You could use syslog-ng to log directly to a database or syslog plus SEC (http://kodu.neti.ee/~risto/sec/) to trigger insert rules.
~Seth
On Tue, Dec 23, 2008 at 1:30 PM, Seth Mattinen <sethm@rollernet.us> wrote:
Corey Shaw wrote:
Is it currently possible to log all IMAP connection attempts to a MySQL table? Thanks.
Sure. You could use syslog-ng to log directly to a database or syslog plus SEC (http://kodu.neti.ee/~risto/sec/) to trigger insert rules.
~Seth
If someone ever decides to make a log-login-tomysql plugin i would love to use it. I currently have a php script that runs during logrotate that parses the info.log for all pop3/imap logins and logs them to mysql, i only keep per minute and then i have a history table of peruser-perday for 90 days.
It would be way cool to have dovecot throw that to mysql automagically. If i were a c programmer i would do it myself =S
On our current mail system we've gone down the route of a PHP script as well for logging the connections. Since I use metalog for logging (instead of syslog-ng) and I don't really want to make an exception for one of our servers, I'll probably still have to use that script. I definitely want to put in a vote for the ability to log to an SQL backend though.
Corey Shaw Q90 Corporation Technology Specialist O. 801.491.0705 (x. 157) F. 801.491.8774 www.q90.com
----- Original Message ----- From: "Brandon Lamb" <brandonlamb@gmail.com> To: "Dovecot Mailing List" <dovecot@dovecot.org> Sent: Tuesday, December 23, 2008 2:41:56 PM GMT -07:00 US/Canada Mountain Subject: Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
On Tue, Dec 23, 2008 at 1:30 PM, Seth Mattinen <sethm@rollernet.us> wrote:
Corey Shaw wrote:
Is it currently possible to log all IMAP connection attempts to a MySQL table? Thanks.
Sure. You could use syslog-ng to log directly to a database or syslog plus SEC (http://kodu.neti.ee/~risto/sec/) to trigger insert rules.
~Seth
If someone ever decides to make a log-login-tomysql plugin i would love to use it. I currently have a php script that runs during logrotate that parses the info.log for all pop3/imap logins and logs them to mysql, i only keep per minute and then i have a history table of peruser-perday for 90 days.
It would be way cool to have dovecot throw that to mysql automagically. If i were a c programmer i would do it myself =S
Brandon Lamb wrote:
On Tue, Dec 23, 2008 at 1:30 PM, Seth Mattinen <sethm@rollernet.us> wrote:
Corey Shaw wrote:
Is it currently possible to log all IMAP connection attempts to a MySQL table? Thanks. Sure. You could use syslog-ng to log directly to a database or syslog plus SEC (http://kodu.neti.ee/~risto/sec/) to trigger insert rules.
~Seth
If someone ever decides to make a log-login-tomysql plugin i would love to use it. I currently have a php script that runs during logrotate that parses the info.log for all pop3/imap logins and logs them to mysql, i only keep per minute and then i have a history table of peruser-perday for 90 days.
It would be way cool to have dovecot throw that to mysql automagically. If i were a c programmer i would do it myself =S
Somewhere on my hit list is customers wanting to see last access time for mailboxes and some informational IMAP/POP3 logging. If I ever get around to it, I could try making it a plugin rather than another rule in the fifo watcher.
Although I've never done any Dovecot development before, so if someone else does it first I wouldn't be hurt. ;)
~Seth
On Tue, Dec 23, 2008 at 1:54 PM, Seth Mattinen <sethm@rollernet.us> wrote:
Brandon Lamb wrote:
On Tue, Dec 23, 2008 at 1:30 PM, Seth Mattinen <sethm@rollernet.us> wrote:
Corey Shaw wrote:
Is it currently possible to log all IMAP connection attempts to a MySQL table? Thanks.
Sure. You could use syslog-ng to log directly to a database or syslog plus SEC (http://kodu.neti.ee/~risto/sec/) to trigger insert rules.
~Seth
If someone ever decides to make a log-login-tomysql plugin i would love to use it. I currently have a php script that runs during logrotate that parses the info.log for all pop3/imap logins and logs them to mysql, i only keep per minute and then i have a history table of peruser-perday for 90 days.
It would be way cool to have dovecot throw that to mysql automagically. If i were a c programmer i would do it myself =S
Somewhere on my hit list is customers wanting to see last access time for mailboxes and some informational IMAP/POP3 logging. If I ever get around to it, I could try making it a plugin rather than another rule in the fifo watcher.
Although I've never done any Dovecot development before, so if someone else does it first I wouldn't be hurt. ;)
~Seth
Its definately valuable information, I like being able to look up a user to see their past logins and by type (pop3, imap) and be able to use this information to shut off or reduce quotas on seemingly dormant accounts, and helps to identify spam boxes. If i didnt have to run a php script it would be all the better, plus if i want information RIGHT NOW I have to remember to run the script before querying the current logins.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Seth Mattinen said the following on 23/12/08 22:30:
Sure. You could use syslog-ng to log directly to a database or syslog plus SEC (http://kodu.neti.ee/~risto/sec/) to trigger insert rules.
Or you can enable MySQL query log and see what's happening: http://dev.mysql.com/doc/refman/5.0/en/query-log.html
Ciao, luigi
/ +--[Luigi Rosa]-- \
Walt Disney is in suspended animation. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAklRxLYACgkQ3kWu7Tfl6ZQpGgCgimRRKTxyNN501ju3+NfIlY35 C5sAn1kC8W8I1K0bZOp8hBFVDC9dkiZM =SYqR -----END PGP SIGNATURE-----
If you use MySQL for auth you could include some logging bits as part of your SQL query.
-----Original Message----- From: dovecot-bounces+jkrejci=usinternet.com@dovecot.org [mailto:dovecot-bounces+jkrejci=usinternet.com@dovecot.org] On Behalf Of Corey Shaw Sent: Tuesday, December 23, 2008 2:50 PM To: dovecot@dovecot.org Subject: [Dovecot] Possible to log IMAP connections to MySQL Table?
Is it currently possible to log all IMAP connection attempts to a MySQL table? Thanks.
Corey
Am 23.12.2008 21:50 schrieb Corey Shaw:
Is it currently possible to log all IMAP connection attempts to a MySQL table? Thanks.
If you really want to log _all_ IMAP connections you could create a MySQL procedure that will log all interesting data you provide and finally returns the data for Dovecots password query. If you want to log only successful IMAP logins you should have a look at http://wiki.dovecot.org/PostLoginScripting
Regards, Pascal
Ubuntu is an ancient African word meaning “I can’t install debian.” -- unknown
I'll take a look at the post login scripting. It sounds like it would be much better than a script that simply tails the log file for any IMAP connections. Thanks for the help!
Corey
----- Original Message ----- From: "Pascal Volk" <user+dovecot@localhost.localdomain.org> To: "Dovecot Mailing List" <dovecot@dovecot.org> Sent: Wednesday, December 24, 2008 1:43:51 AM GMT -07:00 US/Canada Mountain Subject: Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
Am 23.12.2008 21:50 schrieb Corey Shaw:
Is it currently possible to log all IMAP connection attempts to a MySQL table? Thanks.
If you really want to log _all_ IMAP connections you could create a MySQL procedure that will log all interesting data you provide and finally returns the data for Dovecots password query. If you want to log only successful IMAP logins you should have a look at http://wiki.dovecot.org/PostLoginScripting
Regards, Pascal
Ubuntu is an ancient African word meaning “I can’t install debian.” -- unknown
I've started going down the path of the mail_executable script, but I've run into a snag. I have a script that works fine and inserts rows into the database as wanted, but whenever I restart the Dovecot service it inserts a row into the database with the USER variable set to "dump-capability" using the IMAP mail_executable. This doesn't happen with the pop3 mail_executable.
Below is my configuration for Dovecot 1.1.7. I'm assuming that the mail_executable(default) value is being run whenever Dovecot restarts. How do I prevent this from happening? Can I set the default to an empty string?
# 1.1.7: /etc/dovecot/dovecot.conf # OS: Linux 2.6.26-gentoo-r4 i686 Gentoo Base System release 1.12.11.1 reiserfs base_dir: /var/run/dovecot/ log_path: /var/log/dovecot/dovecot_error.log info_log_path: /var/log/dovecot/dovecot_info.log protocols: imap imaps pop3 pop3s ssl_cert_file: /etc/ssl/dovecot/server.pem ssl_key_file: /etc/ssl/dovecot/server.key disable_plaintext_auth: no verbose_ssl: yes login_dir: /var/run/dovecot/login/ login_executable(default): /usr/libexec/dovecot/imap-login login_executable(imap): /usr/libexec/dovecot/imap-login login_executable(pop3): /usr/libexec/dovecot/pop3-login login_greeting_capability(default): yes login_greeting_capability(imap): yes login_greeting_capability(pop3): no login_processes_count: 5 login_max_processes_count: 25 max_mail_processes: 256 mail_max_userip_connections: 5 verbose_proctitle: yes first_valid_uid: 8 first_valid_gid: 12 mail_privileged_group: mail mail_uid: 8 mail_gid: 12 mail_location: maildir:/mail/%d/%n/Maildir mail_debug: yes mail_executable(default): /etc/dovecot/dovecot-imap.sh mail_executable(imap): /etc/dovecot/dovecot-imap.sh mail_executable(pop3): /etc/dovecot/dovecot-pop3.sh mail_plugins(default): quota imap_quota mail_plugins(imap): quota imap_quota mail_plugins(pop3): quota mail_plugin_dir(default): /usr/lib/dovecot/imap mail_plugin_dir(imap): /usr/lib/dovecot/imap mail_plugin_dir(pop3): /usr/lib/dovecot/pop3 pop3_client_workarounds(default): pop3_client_workarounds(imap): pop3_client_workarounds(pop3): outlook-no-nuls oe-ns-eoh auth default: mechanisms: plain login verbose: yes debug: yes debug_passwords: yes passdb: driver: sql args: /etc/dovecot/dovecot-sql.conf userdb: driver: sql args: /etc/dovecot/dovecot-sql.conf userdb: driver: static args: uid=8 gid=12 home=/mail/%d/%n/config socket: type: listen client: path: /var/spool/postfix/private/auth mode: 438 user: postfix group: postfix master: path: /var/run/dovecot/auth-master mode: 438 user: dovecot group: dovecot plugin: quota: maildir:User quota_rule: *:storage=20M quota_rule2: Trash:storage=50M
Corey
----- Original Message ----- From: "Corey Shaw" <cshaw@q90.com> To: "Dovecot Mailing List" <dovecot@dovecot.org> Sent: Wednesday, December 24, 2008 8:07:17 AM GMT -07:00 US/Canada Mountain Subject: Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
I'll take a look at the post login scripting. It sounds like it would be much better than a script that simply tails the log file for any IMAP connections. Thanks for the help!
Corey
----- Original Message ----- From: "Pascal Volk" <user+dovecot@localhost.localdomain.org> To: "Dovecot Mailing List" <dovecot@dovecot.org> Sent: Wednesday, December 24, 2008 1:43:51 AM GMT -07:00 US/Canada Mountain Subject: Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
Am 23.12.2008 21:50 schrieb Corey Shaw:
Is it currently possible to log all IMAP connection attempts to a MySQL table? Thanks.
If you really want to log _all_ IMAP connections you could create a MySQL procedure that will log all interesting data you provide and finally returns the data for Dovecots password query. If you want to log only successful IMAP logins you should have a look at http://wiki.dovecot.org/PostLoginScripting
Regards, Pascal
Ubuntu is an ancient African word meaning “I can’t install debian.” -- unknown
On Dec 24, 2008, at 7:47 PM, Corey Shaw wrote:
I've started going down the path of the mail_executable script, but
I've run into a snag. I have a script that works fine and inserts
rows into the database as wanted, but whenever I restart the Dovecot
service it inserts a row into the database with the USER variable
set to "dump-capability" using the IMAP mail_executable. This
doesn't happen with the pop3 mail_executable.Below is my configuration for Dovecot 1.1.7. I'm assuming that the
mail_executable(default) value is being run whenever Dovecot
restarts. How do I prevent this from happening? Can I set the
default to an empty string?
How about you just add a if ! [ $USER = dump-capability ]; then do
your stuff; fi
You could prevent Dovecot from calling it by manually setting
imap_capability setting, but I don't really recommend it since you'll
probably forget to update it when upgrading to a new Dovecot version..
I just barely finished adding that if statement before you replied. You're right though. Best not to play with the default for that. Thanks.
Corey Shaw Q90 Corporation Technology Specialist O. 801.491.0705 (x. 157) F. 801.491.8774 www.q90.com
----- Original Message ----- From: "Timo Sirainen" <tss@iki.fi> To: "Dovecot Mailing List" <dovecot@dovecot.org> Sent: Wednesday, December 24, 2008 10:52:23 AM GMT -07:00 US/Canada Mountain Subject: Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
On Dec 24, 2008, at 7:47 PM, Corey Shaw wrote:
I've started going down the path of the mail_executable script, but I've run into a snag. I have a script that works fine and inserts rows into the database as wanted, but whenever I restart the Dovecot service it inserts a row into the database with the USER variable set to "dump-capability" using the IMAP mail_executable. This doesn't happen with the pop3 mail_executable.
Below is my configuration for Dovecot 1.1.7. I'm assuming that the mail_executable(default) value is being run whenever Dovecot restarts. How do I prevent this from happening? Can I set the default to an empty string?
How about you just add a if ! [ $USER = dump-capability ]; then do your stuff; fi
You could prevent Dovecot from calling it by manually setting imap_capability setting, but I don't really recommend it since you'll probably forget to update it when upgrading to a new Dovecot version..
On Wednesday of December 24 2008, Corey Shaw wrote:
I'll take a look at the post login scripting. It sounds like it would be much better than a script that simply tails the log file for any IMAP connections. Thanks for the help!
I used such script and it works fine (on not really busy site though):
I think you could use better way to find out if it's imap or pop3 (better than looking if $ENV{POP3_CLIENT_WORKAROUNDS} exists), but it works for me...
#!/usr/bin/perl use DBI; $db_user = 'dovecot'; $db_pass = 'DOVECOTPASSWORD'; $db_db = 'system';
$mode = exists $ENV{POP3_CLIENT_WORKAROUNDS} ? "pop3" : "imap"; $ip = $ENV{IP};
$u = $ENV{USER}; $virtual = $u =~ m/(.*)\@(.*)/ ? 1 : 0; if ($virtual) { $local = $1; $domain = $2;
$dbh = DBI->connect("DBI:mysql:$db_db", $db_user, $db_pass) || die "Could not connect to database: $DBI::errstr"; $dbh->do('UPDATE user set lastlogin=now(), lastlogin_ip=? where username=? and domain=?', undef, $ip, $local, $domain); $dbh->disconnect(); }
exec "/usr/local/libexec/dovecot/$mode";
-- Marcin Gryszkalis, PGP 0x9F183FA3 jabber jid:mg@fork.pl, gg:2532994 http://the.fork.pl
participants (8)
-
Brandon Lamb
-
Corey Shaw
-
Justin Krejci
-
Luigi Rosa
-
Marcin Gryszkalis
-
Pascal Volk
-
Seth Mattinen
-
Timo Sirainen