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 "$@"
Continue reading “Track user last-login with Dovecot and MySQL in Postfix setup”