Track user last login time


This feature is not available for PostgreSQL backend due to Dovecot does not yet support updating existing SQL record on conflict primary key.

Relevent posts in Dovecot mailing lists:

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)