Track user last-login with Dovecot and MySQL in Postfix setup

This will help you to set up SQL based user last-login tracking.
Beware of potential SQL injection holes if you allow users to have special characters in usernames.

Create a MySQL table named “lastauth”

CREATE TABLE IF NOT EXISTS `lastauth` (
`user` char(32) NOT NULL,
`remote_ip` char(18) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY `user`
);

Create the bash script in /usr/bin/trackAuth.sh

#!/bin/bash

# $USER > login username 
# $IP > remote ip address 

MYSQL_USER='USERNAME'
PASSWD='PASSWORD'
DB_HOST='DBHOST'
DB_NAME='DNAME'


CHECK_USER=`mysql -h${DB_HOST} -u${MYSQL_USER} -p${PASSWD} ${DB_NAME} -AN -e "SELECT user from lastauth WHERE user='$USER' LIMIT 1"`

if [ $CHECK_USER == "$USER" ]; then

if [ X"${USER}" != X"dump-capability" ]; then
mysql -h${DB_HOST} -u${MYSQL_USER} -p${PASSWD} ${DB_NAME} >/dev/null 2>&1 <<EOF
UPDATE lastauth SET remote_ip="$IP", timestamp=NOW() WHERE user='$USER';
EOF
fi

else

if [ X"${USER}" != X"dump-capability" ]; then
mysql -h${DB_HOST} -u${MYSQL_USER} -p${PASSWD} ${DB_NAME} >/dev/null 2>&1 <<EOF
INSERT INTO lastauth (user, remote_ip, timestamp) VALUES("$USER", "$IP", NOW())
EOF
fi

fi

exec "$@"

Edit /etc/dovecot/conf.d/10-master.conf and add:
executable = lmtp imap-postlogin
right under the service lmtp { so that it looks like below:

service lmtp {
   ### CUSTOM
   executable = lmtp imap-postlogin
   ...
   ...
   ...

Under the same file, add:
executable = imap imap-postlogin
right under the service imap { so that it looks like below:

service imap {
   ### CUSTOM
   executable = imap imap-postlogin
   ...
   ...
   ...

Do the same with service pop3 by adding
executable = pop3 imap-postlogin
right under the service pop3 { so that it looks like below:

service pop3 {
   ### CUSTOM
   executable = pop3 imap-postlogin
   ...
   ...
   ...

Now let’s create a service name imap-postlogin at the end of 10-master.conf file

service imap-postlogin {
  # all post-login scripts are executed via script-login binary
  executable = script-login /usr/bin/trackAuth.sh

  # the script process runs as the user specified here (v2.0.14+):
  # Change the user if you have a different user for your dovecot/postif setup
  user = vmail
  # this UNIX socket listener must use the same name as given to imap executable
  unix_listener imap-postlogin {
  }
}

Chown script to vmail user and chmod it to a+x:

$ sudo chown vmail:vmail /usr/bin/trackAuth.sh ; sudo chmod a+x /usr/bin/trackAuth.sh ;

Restart dovecot service and test your setup by logging in and out with different mail users:

$ sudo service dovecot restart

 

For more details on Post Login Scripting on dovecot, please read this documentation page: http://wiki.dovecot.org/PostLoginScripting