Track user last login time

Dovecot ships a last_login plugin since Dovecot-2.2.14, it can be used to easily save and update user's last-login timestamp in SQL database.


Create required SQL table to store last login info

We need to create a sql table to store the user last login info.

SQL statement:

    `username` VARCHAR(255) NOT NULL DEFAULT '',
    `domain` VARCHAR(255) NOT NULL DEFAULT '',
    `last_login` INT(11) DEFAULT NULL,
    PRIMARY KEY (`username`),
    INDEX (domain),
    INDEX (`last_login`)

Configure Dovecot

Open Dovecot config file /etc/dovecot/dovecot.conf (Linux/OpenBSD) or /usr/local/etc/dovecot/dovecot.conf (FreeBSD), enable last_login plugin for POP3 and IMAP services by appending plugin name to its mail_plugins parameter like below, and add extra required settings. Note: we use ... below as a placeholder for your existing settings.

protocol imap {
    mail_plugins = ... last_login       # Append plugin name here

protocol pop3 {
    mail_plugins = ... last_login       # Append plugin name here

dict {

    # Add this line. For FreeBSD, please replace the path by
    # /usr/local/etc/dovecot/dovecot-last-login.conf
    lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf

plugin {

    # Add 2 lines
    last_login_dict = proxy::lastlogin
    last_login_key = last-login/%u/%d

Create file /etc/dovecot/dovecot-last-login.conf (Linux/OpenBSD) or /usr/local/etc/dovecot/dovecot-last-login.conf (FreeBSD) with content below:

connect = host= port=3306 dbname=vmail user=vmailadmin password=my_secret_password

map {
    pattern = shared/last-login/$user/$domain
    table = last_login
    value_field = last_login
    value_type = uint

    fields {
        username = $user
        domain = $domain

Since this file contain SQL username and password, we must protect it with proper owner/group and permission (we use Linux/BSD for example below):

chown dovecot:dovecot /etc/dovecot/dovecot-last-login.conf
chmod 0400 /etc/dovecot/dovecot-last-login.conf

Now it's ok to restart Dovecot service.


After restarted Dovecot service, please monitor its log file /var/log/dovecot/dovecot.log, if there's something wrong, Dovecot will log the error message to this file.

Try to access your mailbox with webmail or MUA like Outlook/Thunderbird, you should see the time has been updated in SQL table vmail.last_login (MySQL/MariaDB backends) or iredadmin.last_login (OpenLDAP backend). For example:

sql> USE vmail;
sql> SELECT * FROM last_login;
| username        | domain | last_login |
| |   | 1554172329 |
1 row in set (0.01 sec)