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 "$@"