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/


# $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.
# ------------------------------------------------------------------

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';

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_executable = /usr/bin/ imap

protocol pop3 {
mail_executable = /usr/bin/ pop3

1 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 *