i changed permissions, but no results So i added a field to the table mailbox like this: alter table mailbox add last_login datetime NOT NULL default '0000-00-00 00:00:00';
for the moment I run two cron jobs 1 egrep "dovecot: (imap|pop3)-login" /var/log/mail.log >/var/log/maillog
2 /root/accounts this scripts has de following content:
#!/usr/bin/perl use DBI; $dbpath = "dbi:mysql:database=postfix;host=localhost"; $dbh = DBI->connect($dbpath, "user","passwd") or die "Can't open database: $DBI::errstr"; open (FICHIER ,"/var/log/maillog"); while (<FICHIER>) { ($value0,$value1,undef)=split(/</); ($user,undef)=split(/>/,$value1);
$requete = "update mailbox set last_login =now() where username='$user'"; $sth = $dbh->prepare($requete);
$sth->execute(); $sth -> finish; } close FICHIER; $dbh -> disconnect
This solution is far from being efficient.The precition is 24 hours wich is ok for know. I would really love doing this from dovecot. I am disapointed for not being able to make it work from dovecot. I use debian Etch & dovecot was compiled by hand, is that important?
Thanks a lot
On Feb 11, 2008 8:34 PM, Benjamin R. Haskell <dovecot@benizi.com> wrote:
On Mon, 11 Feb 2008, Osvaldo Alvarez Pozo wrote:
Hi did another test if I run from the console mysql postfix -e "update mailbox set last_log = now() WHERE username = 'info@lab.fr'" the field last_log gets updated. but no way to make it work from /etc/dovecot.dovecot.conf
Sounds like a permissions problem. Put something like the following in a file readable only by root, which is what the PostLogin script runs as unless you've configured it to drop privileges before it runs:
$ cat foo.mysql # MySQL connection data [client] user=someone password=whatever
Then, change the line to:
mysql --defaults-file=foo.mysql postfix -e "update ..."
I also think that $USER is the variable you want, not $UID. UID = user ID, which is usually numeric. But, dovecot sets USER to something useful (IIRC, it depends on what variables you use elsewhere in your config as to whether it includes the domain.).
Best, Ben