Dovecot SQL updating mysql lastauth

Since dovecot dropped vpopmail auth support a couple years back, MT6 adapted by switching to SQL authentication. A consequence of that is that the lastauth table in MySQL stopped getting updated.

Today I whipped up a solution using Dovecot's post-login scripting. Start by creating a script that updates lastauth after a user successfully authenticates. I stored my update script at /data/dovecot/bin/imap-postlogin.sh.

#!/bin/sh

# SQL based lastauth tracking. Beware of potential SQL injection holes if you allow
# users to have ' characters in usernames.
USER_PART=$(echo $USER | cut -f1 -d@)
DOMAIN_PART=$(echo $USER | cut -f2- -d@)

echo "UPDATE vpopmail.lastauth SET timestamp=UNIX_TIMESTAMP(),remote_ip='$IP' WHERE user='$USER_PART' AND domain='$DOMAIN_PART'" \
	| mysql --defaults-extra-file=/usr/local/vpopmail/.my.cnf vpopmail
exec "$@"

Populate the mysql defaults-extra-file with the mysql client settings needed to connect to your DB. Mine looks like this:

[client]
host=mysql
user=vpopmail
password=********

Finally, edit dovecot's local.conf file, per the instructions on the post-login scripting page.

# diff -c local.conf.b4 local.conf
*** local.conf.b4	Wed May 15 10:45:14 2024
--- local.conf	Wed May 15 10:26:29 2024
***************
*** 219,224 ****
--- 219,236 ----
    # limit if you have huge mailboxes.
    vsz_limit = 384M
    process_limit = 512
+   executable = imap imap-postlogin
+ }
+ 
+ service imap-postlogin {
+   # all post-login scripts are executed via script-login binary
+   executable = script-login /data/bin/imap-postlogin.sh
+ 
+   # the script process runs as the user specified here (v2.0.14+):
+   user = vpopmail
+   # this UNIX socket listener must use the same name as given to imap executable
+   unix_listener imap-postlogin {
+   }
  }
  
  login_access_sockets = tcpwrap

I also noticed that the lastauth.remote_ip field is far too short for IPv6 addresses, so I bumped that up so they wouldn't get truncated:

ALTER TABLE lastauth MODIFY remote_ip char(39);