Track Dovecot User Last Login Date and IP Address

Idea how to implement tracking last user pop3 or imap login date using dovecot is described in Dovecot Wiki.

I have found complete solution, include script here.

My implementation to ispcp:

ALTER TABLE `mail_users` ADD `last_login_date` DATETIME NULL COMMENT 'last login date',
ADD `last_login_ip` VARCHAR( 39 ) NULL COMMENT 'last login ip',
ADD `last_login_proto` CHAR( 5 ) NULL ;

Create file /usr/bin/trackLastLogin.sh

#!/bin/sh

# $USER -> login username. It should be a valid email address.
# $IP -> remote ip address
# ${1} -> mail protocol: imap, pop3

# ------------------------------------------------------------------
# Update to MySQL database.
# Note: ${MYSQL_USER} must have SELECT and UPDATE privileges.
# ------------------------------------------------------------------
MYSQL_USER='postfix'
PASSWD='blabla'
VMAIL_DB_NAME='ispcp'

if [ X"${USER}" != X"dump-capability" ]; then
mysql -u${MYSQL_USER} -p${PASSWD} ${VMAIL_DB_NAME} >/dev/null 2>&1 <<EOF
UPDATE mail_users SET \
last_login_ip="$IP", \
last_login_date=NOW(), \
last_login_proto="${1}" \
WHERE mail_addr='$USER';
EOF
fi

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

Modify dovecot.conf, add ‘mail_executable’ and ‘mail_drop_priv_before_exec’ in protocol imap and/or pop3


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
...
}

One thought on “Track Dovecot User Last Login Date and IP Address

  1. Note that this script will make your mysql password visible by anyone running “ps”.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>