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
Hi recently I make a daemon to track last authentication when the user send a message ( smtps and submisstion ), Please see for your selfes https://github.com/noskoski/postfix_smtpd_last_auth/
Hi
Recently i made a daemon in python to track last user authentication when he send an email ( ports 465 and 587 ), ( see for yourselves https://github.com/noskoski/postfix_smtpd_last_auth/ ).