IPv4/v6 based access checking and logging

[julio covolato]

And a litle shell script to query data from db:


#!/bin/sh
#########################################################################################
# ex.: uso: dovecotLastLogin.sh -d 10
#       dovecotLastLogin.sh -u julio
#########################################################################################
# For Mysql postfixadmin database
#########################################################################################
MYSQL="mysql --login-path=vmail -Dvmail -t -e"
         case $1 in
         -d)
         DAYSAGO=`date --date="$2 days ago" +%s`
         $MYSQL "select 
username,last_login.domain,FROM_UNIXTIME(last_login) AS 
last_login,rip,proto,active FROM last_login INNER JOIN mailbox USING 
(username) WHERE last_login < '$DAYSAGO' ORDER BY 
mailbox.active,last_login;"
         ;;

         -u)
         $MYSQL "select 
username,last_login.domain,FROM_UNIXTIME(last_login) AS 
last_login,rip,proto,active FROM last_login INNER JOIN mailbox USING 
(username) WHERE username LIKE '%$2%' ORDER BY mailbox.active,last_login;"
         ;;

         *)
         echo
         echo " USO: dovecotLastLogin.sh [-d -u] [days user]"
         echo
         echo " -d --> All users whith no login in N days ago, ex.: 
dovecotLastLogin.sh -d 90"
         echo " -u --> Last login from user, ex.: dovecotLastLogin.sh -u 
user at domain.com"
         echo " List last login from ALL users from one domain: 
dovecotLastLogin.sh -u domain.com"
         echo " List lat login from ALL users: dovecotLastLogin.sh -u %"
         exit 1
         ;;
         esac
# End

----------------------------------
     _    Engº Julio Cesar Covolato
    0v0   <julio at psi.com.br>
   /(_)\  F: 55-11-99175-9260
    ^ ^   PSI INTERNET
----------------------------------

Em 12/11/2021 15:33, julio covolato escreveu:
>
> Em 12/11/2021 14:47, Lefteris Tsintjelis escreveu:
>> Hi,
>>
>> I am currently using postfix/dovecot with postfix admin and I track 
>> the last login date already by using this:
>>
>> https://doc.dovecot.org/configuration_manual/lastlogin_plugin/
>>
>> Besides last login date, I would like to also implement IPv4 and IPv6 
>> last login tracking also and if possible, IP based login checking. Is 
>> post-login scripting the best most efficient way to go?
>>
>> Regards,
>>
>> Lefteris
>
> Hi,
>
> This is my lastlogin config to track remote ip:
>
> dovecot.conf:
>
> dict {
>
>   lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf
>   ....
> }
>
> dovecot-last-login.conf:
>
> connect = host=127.0.0.1 port=3306 dbname=vmail user=vmailadmin 
> password=XXXXXXXXXXXXXXX
>
> map {
>     pattern = shared/last-login/$user/$domain/$rip/$service
>     table = last_login
>     value_field = last_login
>     value_type = uint
>
>     fields {
>         username = $user
>         domain = $domain
>         rip = $rip
>         proto = $service
>     }
> }
>
> Mysql table last_login:
>
> +------------+--------------+------+-----+---------+-------+
> | Field      | Type         | Null | Key | Default | Extra |
> +------------+--------------+------+-----+---------+-------+
> | username   | varchar(255) | NO   | PRI |         |       |
> | domain     | varchar(255) | NO   | MUL |         |       |
> | last_login | int(11)      | YES  | MUL | NULL    |       |
> | rip        | varchar(16)  | NO   | MUL |         |       |
> | proto      | varchar(10)  | NO   |     | NULL    |       |
> +------------+--------------+------+-----+---------+-------+
>
> Hope this help!
>
> ----------------------------------
>     _    Engº Julio Cesar Covolato
>    0v0   <julio at psi.com.br>
>   /(_)\  F: 55-11-99175-9260
>    ^ ^   PSI INTERNET
> ----------------------------------
>