Re: [Dovecot] mailbox not acceced
Osvaldo Alvarez Pozo wrote:
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
'-00-00 00:00:00';
That's not a strictly valid date. better use a real date ('1970-01-01
00:00:00' for instance). software that accesses databases in a portable
manner may break if you use non portable dates.
for the moment I run two cron jobs
1 egrep "dovecot: (imap|pop3)-login" /var/log/mail.log >/var/log/maillog
since you're using perl, no need for the egrep part. see below.
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 () {
($value0,$value1,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
#!/usr/bin/perl
use DBI;
use strict;
my $logfile = "/var/log/mail.log"; # or $ARGV[0]...
# syslog doesn't include a year... we could use current time or stat the
logfile or ...
my $current_year='2008';
my %monthnum =
qw( Jan 1 Feb 2 Mar 3 Apr 4 May 5 Jun 6 Jul 7 Aug 8 Sep 9 Oct 10 Nov
11 Dec 12 );
my %lastaccess = ();
open(LOGFILE, "$logfile") or die "Cannot open $logfile: $!\n";
while ()
chomp;
if (! /dovecot: (imap|pop3)-login/) { next; }
my ($monthname, $day, $time, $line) = split('\s+', $_, 4);
# XXX no space in usernames...
if ($line !~ /user=<(\S+)>/) { next;}
$lastaccess{$1} = $current_year . "-" . $monthnum{$monthname} .
"-$day $time";
}
close(LOGFILE);
#foreach (keys %lastaccess) { print "$_: $lastaccess{$_}\n"; }
now you have the timestamp of last access for each user. and you can
update your table.
to avoid, a lot of UPDATE queries, create a temporrary table in the
script and use INSERT with multiple values in a single query. then
UPDATE using the temporary table.
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?
Re: [Dovecot] mailbox not acceced
On Mon, 11 Feb 2008, Osvaldo Alvarez Pozo wrote: i changed permissions, but no results Are you sure the connection is opened correctly? If I were debugging this on my machine I'd: 1) Add something to check that the MySQL connection is fine: mysql postfix -e "select now()" > /root/dovecot-debug 2) and that USER contains what you think it contains: echo $USER >> /root/dovecot-debug [ 3) or more generally: ] env | sort >> /root/dovecot-debug [... cron jobs ...] 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? It shouldn't be, no. This still seems more like a MySQL problem than something specific to Dovecot. Best, Ben
Re: [Dovecot] mailbox not acceced
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
'-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 () {
($value0,$value1,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 <[EMAIL PROTECTED]> 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
> > = '[EMAIL PROTECTED]'"
> > 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
>
Re: [Dovecot] mailbox not acceced
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 = '[EMAIL PROTECTED]'" 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
Re: [Dovecot] mailbox not acceced
Hi did another test if I run from the console mysql postfix -e "update mailbox set last_log = now() WHERE username = '[EMAIL PROTECTED]'" the field last_log gets updated. but no way to make it work from /etc/dovecot.dovecot.conf On Feb 11, 2008 8:21 PM, Osvaldo Alvarez Pozo <[EMAIL PROTECTED]> wrote: > I did some test & the value of $UID is zero. how could I recover the > value of the logged user? > Thanks. > > > On Feb 11, 2008 6:57 PM, mouss <[EMAIL PROTECTED]> wrote: > > Hugo Monteiro wrote: > > > Osvaldo Alvarez Pozo wrote: > > >> i was looking your mail and as I have a data base i added a field to > > >> de data base I modified the script so it lokks like this. > > >> > > >> #!/bin/sh > > >> echo "update mailbox set last_log = now() WHERE username = '$USER'" | > > >> mysql postfix > > >> exec /usr/dovecot/libexec/dovecot/pop3 > > >> > > >> but nothing gets written to the field. > > >> I think there is a problem with the value of variable $USER, as this > > >> is a shell script. > > >> Any Ideas? > > >> > > >> > > > > > > > > > You should be using $UID and not $USER. > > > > and > > mysql postfix -e "update " > > instead of echo "..." | mysql ... > > > > > > > > >
Re: [Dovecot] mailbox not acceced
I did some test & the value of $UID is zero. how could I recover the value of the logged user? Thanks. On Feb 11, 2008 6:57 PM, mouss <[EMAIL PROTECTED]> wrote: > Hugo Monteiro wrote: > > Osvaldo Alvarez Pozo wrote: > >> i was looking your mail and as I have a data base i added a field to > >> de data base I modified the script so it lokks like this. > >> > >> #!/bin/sh > >> echo "update mailbox set last_log = now() WHERE username = '$USER'" | > >> mysql postfix > >> exec /usr/dovecot/libexec/dovecot/pop3 > >> > >> but nothing gets written to the field. > >> I think there is a problem with the value of variable $USER, as this > >> is a shell script. > >> Any Ideas? > >> > >> > > > > > > You should be using $UID and not $USER. > > and > mysql postfix -e "update " > instead of echo "..." | mysql ... > > > >
Re: [Dovecot] mailbox not acceced
Hugo Monteiro wrote: Osvaldo Alvarez Pozo wrote: i was looking your mail and as I have a data base i added a field to de data base I modified the script so it lokks like this. #!/bin/sh echo "update mailbox set last_log = now() WHERE username = '$USER'" | mysql postfix exec /usr/dovecot/libexec/dovecot/pop3 but nothing gets written to the field. I think there is a problem with the value of variable $USER, as this is a shell script. Any Ideas? You should be using $UID and not $USER. and mysql postfix -e "update " instead of echo "..." | mysql ...
Re: [Dovecot] mailbox not acceced
Osvaldo Alvarez Pozo wrote: i was looking your mail and as I have a data base i added a field to de data base I modified the script so it lokks like this. #!/bin/sh echo "update mailbox set last_log = now() WHERE username = '$USER'" | mysql postfix exec /usr/dovecot/libexec/dovecot/pop3 but nothing gets written to the field. I think there is a problem with the value of variable $USER, as this is a shell script. Any Ideas? You should be using $UID and not $USER. Regards, Hugo Monteiro. -- ci.fct.unl.pt:~# cat .signature Hugo Monteiro Email: [EMAIL PROTECTED] Telefone : +351 212948300 Ext.15307 Centro de Informática Faculdade de Ciências e Tecnologia da Universidade Nova de Lisboa Quinta da Torre 2829-516 Caparica Portugal Telefone: +351 212948596 Fax: +351 212948548 www.ci.fct.unl.pt [EMAIL PROTECTED] ci.fct.unl.pt:~# _
Re: [Dovecot] mailbox not acceced
On Mon, 11 Feb 2008, Hugo Monteiro wrote:
Osvaldo Alvarez Pozo wrote:
Hi
I wonder how can I know if a mailbox is not beeing consulted. I mean I
have severals mailboxs but I know some account are not used. I like to
know how to determine wich mail accounts are not beeing used. Is there
any way in dovecot to know the las time a user loged in to dovecot?
Thanks
I'm using dovecot's ability to use "pre execution" scripts.
Basically i have in my imap protocol definition in dovecot.conf an alternate
mail_executable
protocol imap {
...
mail_executable = /usr/local/bin/imap-wrapper.sh
...
}
And that executable has the following:
snip
#!/bin/sh
# Filesystem based timestamp in user's home directory and protocol used
echo imap > /var/spool/lastlogin/$UID
# Finally execute the imap/pop3 binary. If you use both, you'll need two
scripts.
exec /usr/lib/dovecot/imap
snip
I echo the protocol into the file because i have both pop and imap available
to my users and this way i can keep track when was the last time they
accessed their mailbox (ls -l /var/spool/lastlogin/username) and which
protocol they used in that access (cat /var/spool/lastlogin/username).
There are inumerous possibilities of course.
For a summary of recent-past emails about this, see:
http://dovecot.org/list/dovecot/2007-November/027148.html
To make the script in this email work for both POP3 and IMAP:
$ cat /root/dovecot-login.sh
#!/bin/sh
PROTO=`basename $0`
echo $PROTO > /var/spool/lastlogin/$UID
exec /usr/libexec/dovecot/$PROTO
$ ln -s /root/dovecot-login.sh /root/imap
$ ln -s /root/dovecot-login.sh /root/pop3
In dovecot.conf:
protocol imap {
...
mail_executable = /root/imap
...
}
protocol pop3 {
...
mail_executable = /root/pop3
...
}
Best,
Ben
Re: [Dovecot] mailbox not acceced
i was looking your mail and as I have a data base i added a field to
de data base I modified the script so it lokks like this.
#!/bin/sh
echo "update mailbox set last_log = now() WHERE username = '$USER'" |
mysql postfix
exec /usr/dovecot/libexec/dovecot/pop3
but nothing gets written to the field.
I think there is a problem with the value of variable $USER, as this
is a shell script.
Any Ideas?
On Feb 11, 2008 1:42 PM, Hugo Monteiro <[EMAIL PROTECTED]> wrote:
>
> Osvaldo Alvarez Pozo wrote:
> > Hi
> > I wonder how can I know if a mailbox is not beeing consulted. I mean I
> > have severals mailboxs but I know some account are not used. I like to
> > know how to determine wich mail accounts are not beeing used. Is there
> > any way in dovecot to know the las time a user loged in to dovecot?
> >
> > Thanks
> >
> >
>
> I'm using dovecot's ability to use "pre execution" scripts.
>
> Basically i have in my imap protocol definition in dovecot.conf an
> alternate mail_executable
>
> protocol imap {
> ...
> mail_executable = /usr/local/bin/imap-wrapper.sh
> ...
> }
>
> And that executable has the following:
>
> snip
> #!/bin/sh
>
> # Filesystem based timestamp in user's home directory and protocol used
> echo imap > /var/spool/lastlogin/$UID
>
> # Finally execute the imap/pop3 binary. If you use both, you'll need two
> scripts.
> exec /usr/lib/dovecot/imap
> snip
>
>
> I echo the protocol into the file because i have both pop and imap
> available to my users and this way i can keep track when was the last
> time they accessed their mailbox (ls -l /var/spool/lastlogin/username)
> and which protocol they used in that access (cat
> /var/spool/lastlogin/username).
>
>
> There are inumerous possibilities of course.
>
> Regards,
>
> Hugo Monteiro.
>
> --
> ci.fct.unl.pt:~# cat .signature
>
> Hugo Monteiro
> Email: [EMAIL PROTECTED]
> Telefone : +351 212948300 Ext.15307
>
> Centro de Informática
> Faculdade de Ciências e Tecnologia da
>Universidade Nova de Lisboa
> Quinta da Torre 2829-516 Caparica Portugal
> Telefone: +351 212948596 Fax: +351 212948548
> www.ci.fct.unl.pt [EMAIL PROTECTED]
>
> ci.fct.unl.pt:~# _
>
>
Re: [Dovecot] mailbox not acceced
Osvaldo Alvarez Pozo wrote: Hi I wonder how can I know if a mailbox is not beeing consulted. I mean I have severals mailboxs but I know some account are not used. I like to know how to determine wich mail accounts are not beeing used. Is there any way in dovecot to know the las time a user loged in to dovecot? you can parse the logs.
Re: [Dovecot] mailbox not acceced
Osvaldo Alvarez Pozo wrote:
Hi
I wonder how can I know if a mailbox is not beeing consulted. I mean I
have severals mailboxs but I know some account are not used. I like to
know how to determine wich mail accounts are not beeing used. Is there
any way in dovecot to know the las time a user loged in to dovecot?
Thanks
I'm using dovecot's ability to use "pre execution" scripts.
Basically i have in my imap protocol definition in dovecot.conf an
alternate mail_executable
protocol imap {
...
mail_executable = /usr/local/bin/imap-wrapper.sh
...
}
And that executable has the following:
snip
#!/bin/sh
# Filesystem based timestamp in user's home directory and protocol used
echo imap > /var/spool/lastlogin/$UID
# Finally execute the imap/pop3 binary. If you use both, you'll need two
scripts.
exec /usr/lib/dovecot/imap
snip
I echo the protocol into the file because i have both pop and imap
available to my users and this way i can keep track when was the last
time they accessed their mailbox (ls -l /var/spool/lastlogin/username)
and which protocol they used in that access (cat
/var/spool/lastlogin/username).
There are inumerous possibilities of course.
Regards,
Hugo Monteiro.
--
ci.fct.unl.pt:~# cat .signature
Hugo Monteiro
Email: [EMAIL PROTECTED]
Telefone : +351 212948300 Ext.15307
Centro de Informática
Faculdade de Ciências e Tecnologia da
Universidade Nova de Lisboa
Quinta da Torre 2829-516 Caparica Portugal
Telefone: +351 212948596 Fax: +351 212948548
www.ci.fct.unl.pt [EMAIL PROTECTED]
ci.fct.unl.pt:~# _
[Dovecot] mailbox not acceced
Hi I wonder how can I know if a mailbox is not beeing consulted. I mean I have severals mailboxs but I know some account are not used. I like to know how to determine wich mail accounts are not beeing used. Is there any way in dovecot to know the las time a user loged in to dovecot? Thanks
