Possible bug with last_login plugin and PostgreSQL
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=
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
}} ##############
On Apr 1, 2019, at 6:39 PM, Zhang Huangbin zhb@iredmail.org 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#... [2] PostgreSQL: "ON CONFLICT" Clause: https://www.postgresql.org/docs/9.5/sql-insert.html#SQL-ON-CONFLICT
participants (1)
-
Zhang Huangbin