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