Re: Possible bug with last_login plugin and PostgreSQL

[Zhang Huangbin via dovecot]

> On Apr 1, 2019, at 6:39 PM, Zhang Huangbin  wrote:
> 
> 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.

According to Dovecot source code[1], only MySQL has the "ON DUPLICATE KEY"
support, this is out of date because PostgreSQL has similar feature ("ON 
CONFLICT")[2]
since version 9.5. Also:

- Debian 9 ships PostgreSQL-9.6.
- Ubuntu 18.04 ships PostgreSQL-10.6.
- openSUSE tumbleweed ships PostgreSQL 10 and 11.
- Although CentOS 7 ships PostgreSQL 9.2 (NOT CAPABLE), but RHEL 8 public beta
  already ships PostgreSQL 9.6 and 10.
- OpenBSD 6.4 ships PostgreSQL 10.5, upcoming OpenBSD 6.5 ships PGSQL 11.2.
- FreeBSD ports tree offers PGSQL 9.5, 9.6.

Seems latest mainstream Linux/BSD distribution releases are all ok to use this
"ON CONFLICT" support, the question is, can we have this feature in Dovecot for 
PostgreSQL?

Thank you very much for the help. :)

BTW, SQLite has similar feature since version 3.24.0. 
https://www.sqlite.org/lang_UPSERT.html

[1] Dovecot source code: 
https://github.com/dovecot/core/blob/master/src/lib-dict-backend/dict-sql.c#L108
[2] PostgreSQL: "ON CONFLICT" Clause: 
https://www.postgresql.org/docs/9.5/sql-insert.html#SQL-ON-CONFLICT

Possible bug with last_login plugin and PostgreSQL

[Zhang Huangbin via dovecot]

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=, 
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(postmas...@a.io): Logged out in=300 out=1604
Mar 31 11:15:21 ob dovecot: imap-login: Login: user=, 
method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=29797, TLS, 
session=
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)=(postmas...@a.io) already exists.
Mar 31 11:15:21 ob dovecot: imap(postmas...@a.io): Error: dict-client: server 
returned failure: 1554023721 (reply took 0.012 secs)
Mar 31 11:15:21 ob dovecot: imap(postmas...@a.io): Error: last_login_dict: 
Failed to write value for user postmas...@a.io
Mar 31 11:15:21 ob dovecot: imap(postmas...@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=

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

fields {
username = $user
}
}
##