Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
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:m...@fork.pl, gg:2532994
http://the.fork.pl
Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
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" To: "Dovecot Mailing List" 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..
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..
Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
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" To: "Dovecot Mailing List" 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" To: "Dovecot Mailing List" 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
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" To: "Dovecot Mailing List" 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
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
Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
-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-
Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
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@dovecot.org [mailto:dovecot-bounces+jkrejci=usinternet@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
Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
On Tue, Dec 23, 2008 at 1:54 PM, Seth Mattinen wrote: > Brandon Lamb wrote: >> >> On Tue, Dec 23, 2008 at 1:30 PM, Seth Mattinen 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.
Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
Brandon Lamb wrote: On Tue, Dec 23, 2008 at 1:30 PM, Seth Mattinen 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
Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
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" To: "Dovecot Mailing List" 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 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
Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
On Tue, Dec 23, 2008 at 1:30 PM, Seth Mattinen 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
Re: [Dovecot] Possible to log IMAP connections to MySQL Table?
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
