Possible bug with last_login plugin and PostgreSQL

[Zhang Huangbin]

Dear all,

I'm trying to get last_login plugin working with PostgreSQL, but seems Dovecot
doesn't handle duplicate key while updating last login time (note: same setting
works fine with MariaDB). I wonder whether it's a bug of Dovecot or i should handle
this with PostgreSQL trigger.

OS: OpenBSD 6.4 (amd64)
Dovecot: 2.2.36
PostgreSQL: 10.5

Error log:
###################
Mar 31 11:15:21 ob dovecot: imap-login: Login: user=<postmaster at a.io>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=5936, TLS, session=<63/jV2CFhzt/AAAB>
Mar 31 11:15:21 ob dovecot: imap(postmaster at a.io): Logged out in=300 out=1604
Mar 31 11:15:21 ob dovecot: imap-login: Login: user=<postmaster at a.io>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=29797, TLS, session=<lLboV2CFuZ9/AAAB>
Mar 31 11:15:21 ob dovecot: dict(9584): Error: sql dict: commit failed: ERROR:  duplicate key value violates unique constraint "last_login_pkey"
Mar 31 11:15:21 ob dovecot: dict: Error: DETAIL:  Key (username)=(postmaster at a.io) already exists.
Mar 31 11:15:21 ob dovecot: imap(postmaster at a.io): Error: dict-client: server returned failure: 1554023721 (reply took 0.012 secs)
Mar 31 11:15:21 ob dovecot: imap(postmaster at a.io): Error: last_login_dict: Failed to write value for user postmaster at a.io
Mar 31 11:15:21 ob dovecot: imap(postmaster at a.io): Logged out in=70 out=732
##################

My Dovecot config:

###################
protocol imap {
    mail_plugins = ... last_login
    ...
}

protocol pop3 {
    mail_plugins = ... last_login
    ...
}

plugin {
    last_login_dict = proxy::lastlogin
    #last_login_key = last-login/%u
    ...
}

dict {
    lastlogin = pgsql:/etc/dovecot/dovecot-last-login.conf
    ...
}
###################

SQL commands used to create PostgreSQL table:
###########
CREATE TABLE last_login (
    username VARCHAR(255) NOT NULL,
    last_login INT DEFAULT NULL,
    PRIMARY KEY (username)
);
###########

File /etc/dovecot/dovecot-last-login.conf:
###########
connect = host=127.0.0.1 port=5432 dbname=vmail user=vmailadmin password=<password>

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

    fields {
        username = $user
    }
}
##############