1

Topic: lastlogindate, lastloginipv4, lastloginprotocol ?

Hi.

What are `lastlogindate`, `lastloginipv4` and `lastloginprotocol` in `mailbox` table for?

They never get updated. `lastlogindate` is always '0000-00-00 00:00:00', `lastloginipv4` is '0' and `lastloginprotocol` is empty...

How can I make them to update? `lastlogindate` would be especially useful, I can track unused accounts...

Thanks.

2

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

Reference: http://www.iredmail.org/forum/post742.html#p742

3 (edited by maxie_ro 2009-10-27 18:36:54)

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

I don't know why, but it doesn't work.

I created the shell script file, "imaplastlogin":

#!/bin/sh
mysql --password=...... --user=root --default-character-set=utf8 --database=vmail --execute="UPDATE vmail.mailbox SET lastlogindate = NOW(), lastloginipv4=INET_ATON('$IP'), lastloginprotocol='IMAP' WHERE username = '$USER';"

exec /usr/local/libexec/dovecot/imap $*

Modified dovecot.conf:

# POP3 configuration
protocol imap {
    #mail_plugins = quota imap_quota zlib expire
    mail_plugins = quota imap_quota zlib

    # number of connections per-user per-IP
    #mail_max_userip_connections = 10
    mail_executable = /usr/libexec/dovecot/imaplastlogin
}

When restarting dovecot I get the following error:

Stopping Dovecot Imap:                                     [  OK  ]
Starting Dovecot Imap: Fatal: execv(/usr/libexec/dovecot/imaplastlogin) failed: No such file or directory
Error: imap dump-capability process returned 84
Fatal: Invalid configuration in /etc/dovecot.conf
                                                           [FAILED]

When I run the mysql command directly in the shell it works ok.
But why do I get such an error?! The permissions appear to be ok... (0755).

Also, if I modify to the default:

mail_executable = /usr/libexec/dovecot/imap

everything works ok.

4

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

It's clear in the output message:

Starting Dovecot Imap: Fatal: execv(/usr/libexec/dovecot/imaplastlogin) failed: No such file or directory

Did you use 'chmod' to set correct file permission? such as 0755:

# chmod 0755 /usr/libexec/dovecot/imaplastlogin

5

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

Of course I did, I said it in the previous post.

The imaplastlogin shell script is in the same directory as the imap executable, and they have the same permissions and owner. But it can't find the shell script.

I triple-checked the path & name, everything is ok.

6

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

You must miss this in dovecot wiki page:

WARNING: The process still runs as root at this point! The privileges are dropped only after the imap process starts. You can change this by setting mail_drop_priv_before_exec=yes.

7

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

Not missed anything tongue

Tested with both "mail_drop_priv_before_exec" set to 'no' and 'yes' already. Not working... Still not finding the file...

And it shouldn't have to do anything with it anyway, because default is 'no', and changing it to 'yes' should only make things worse...

Besides, it can find the "imap" executable in the same directory with identical permissions and owner.... ?!

BTW, I tried replied previously and I think I accidentaly hit the report button, sorry.

8

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

It works for me:

    protocol imap {
        mail_drop_priv_before_exec=yes
        mail_executable = /usr/bin/TrackLastLogin.sh imap
        ...
    }

    protocol pop3 {
        mail_drop_priv_before_exec=yes
        mail_executable = /usr/bin/TrackLastLogin.sh pop3
        ...
    }

9

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

OK..... and what is the contents of TrackLastLogin.sh?

10

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

#!/bin/sh

# =========================================================
# Author:   Zhang Huangbin (michaelbibby@gmail.com)
# Date:     2009.07.28
# Purpose:  Track user last login date & ip address.
# =========================================================

# $USER -> login username
# $IP   -> remote ip address
# ${1}  -> mail protocol: imap, pop3

# Debug & Update to plain file.
# Note: user 'dovecot' should have write permission on this file.
#echo "$(date +%Y.%m.%d-%H:%M:%S), $USER, $IP, ${1})" >> /tmp/tracking.log

# ----------------------------------------------
# Update to MySQL database.
# ----------------------------------------------
#mysql -uvmailadmin -psecret_passwd vmail <<EOF
#UPDATE mailbox SET \
#   lastloginipv4="INET_ATON('$IP')", \
#   lastlogindate="NOW()", \
#   lastloginprotocol="${1}" \
#   WHERE username='$USER';
#EOF

# ----------------------------------------------
# Update to LDAP (OpenLDAP) directory server.
# ----------------------------------------------
# Convert username to LDAP dn.
# -c         continuous operation mode (do not stop on errors)
# -x            Simple authentication
# -H URI        Uniform Resource Identifier(s)
# -D binddn     Bind dn. Default is 'cn=vmailadmin,dc=iredmail,dc=org'
# -w bindpw     Bind password (for simple authentication)
if [ X"${USER}" != X"dump-capability" ]; then
    ldapmodify -c -x \
        -H ldap://127.0.0.1:389 \
        -D 'cn=vmailadmin,dc=iredmail,dc=org' \
        -w passwd <<EOF
dn: mail=${USER},ou=Users,domainName=$(echo ${USER} | awk -F'@' '{print $2}'),o=domains,dc=iredmail,dc=org
changetype: modify
replace: lastLoginDate
lastLoginDate: $(date +%Y%m%d%H%M%SZ)
-
replace: lastLoginIP
lastLoginIP: ${IP}
-
replace: lastLoginProtocol
lastLoginProtocol: ${1}
EOF

fi

# Execute IMAP process.
# RHEL/CentOS:
exec /usr/libexec/dovecot/imap $*
# Debian & Ubuntu:
#exec /usr/lib/dovecot/imap $*

11

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

Thank you.

Your script is working, if I modify something: remove the double quotes from the INET_ATON('$IP') and NOW(). Otherwise mysql will literally try to write in the field `lastlogindate` the text "NOW()" instead of the actual result of the function NOW() (which is current datetime).

P.S.
If I put my script in /usr/bin it's working too.... Problem with libexec/dovecot?! Pff.... didn't cross my mind.

12

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

LE: nope, not working. I'm stumped.

The mysql part is working properly (e.g. the table `mailbox` gets updated) but imap/pop3 isn't working anymore... Nobody can read mails.

Maybe because the last line is

exec /usr/libexec/dovecot/imap $*

which will also send "imap" or "pop3" keywords (argument of the script) to imap and pop3 binaries, causing them to return error?

13

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

Change the last line to:

exec /usr/libexec/dovecot/${1} $*

14

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

That won't work either, because the first argument of the script is sent to the "imap" and "pop3" binaries. There is no such command line switches for them, so they crash. You need to make 2 separate scripts, one for IMAP and one for POP3, and don't feed them any arguments.

And, don't put them in libexec/dovecot, it won't work! (maybe because of SELinux?)

15 (edited by maxie_ro 2009-10-27 21:51:45)

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

Maybe I wasn't clear enough. Let me explain.

In dovecot.conf there is, as you said:

mail_executable = /usr/bin/TrackLastLogin.sh imap

where imap here is the first command-line argument of the script.

In <TrackLastLogin.sh> the last line is:

exec /usr/libexec/dovecot/imap $*

,where $* means that you are passing to /usr/libexec/dovecot/imap the same arguments as the current script received. One of the arguments it received was "imap", so you are running:

/usr/libexec/dovecot/imap imap

, but "imap" is an incorrect command line argument for the binary.

Thus, you need 2 separate scripts, one for POP3, one for IMAP to work.

16

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

Hi, maxie_ro.

I already updated tracking script, redirect stderr and stdout message to /dev/null, and it works with POP3 & IMAP now. Please refer to this FAQ:
http://www.iredmail.org/forum/topic379- … tocol.html

Enjoy smile

17

Re: lastlogindate, lastloginipv4, lastloginprotocol ?

Thank you, it's working.