Skip to content

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

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

Leave a Reply

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