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@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@a.io): Logged out in=300 out=1604 Mar 31 11:15:21 ob dovecot: imap-login: Login: user=<postmaster@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@a.io) already exists. Mar 31 11:15:21 ob dovecot: imap(postmaster@a.io): Error: dict-client: server returned failure: 1554023721 (reply took 0.012 secs) Mar 31 11:15:21 ob dovecot: imap(postmaster@a.io): Error: last_login_dict: Failed to write value for user postmaster@a.io Mar 31 11:15:21 ob dovecot: imap(postmaster@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
}} ##############