Hi,
I'm configuring Last_Login plugin whith Mysql back end and all works fine.
I just wonder to track besides time_stamp, user and domain, the remote IP and proto (imap or pop3)
I tried in dovecot.conf:
plugin {
...
...
# Track last login time on imap and pop3 last_login_dict = proxy::lastlogin last_login_key = last-login/%u/%d/%r
}
dict { ...
lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf ... }
protocol imap {
mail_plugins = $mail_plugins ... last_login ...
}
protocol pop3 { mail_plugins = $mail_plugins last_login
...
}
And dovecot-last-login.conf:
connect = host=127.0.0.1 port=3306 dbname=vmail user=vmailadmin password=xxxxxxxxxxxxxxxxxx
map { pattern = shared/last-login/$user/$domain table = last_login value_field = last_login value_type = uint
fields { username = $user domain = $domain rip = $rip } }
And the table criated in mysql:
CREATE TABLE IF NOT EXISTS last_login (
username VARCHAR(255) NOT NULL DEFAULT '',
domain VARCHAR(255) NOT NULL DEFAULT '',
last_login INT(11) DEFAULT NULL,
rip VARCHAR(16) NOT NULL DEFAULT '',
PRIMARY KEY (username),
INDEX (domain),
INDEX (last_login),
INDEX (rip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
And got de error in dovecot log:
Jun 06 13:56:59 dict(21157): Error: Failed to initialize dictionary 'lastlogin': dict mysql: Error in configuration file /etc/dovecot/dovecot-last-login.conf line 13: Unused variable: rip
Thanks in any help.
--
_ Engº Julio Cesar Covolato
0v0 <julio@psi.com.br>/(_)\ F: +55 11 99175-9260 ^ ^ PSI INTERNET
Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus
Sory, forgot dovecot -n:
# 2.2.33.2 (d6601f4ec): /etc/dovecot/dovecot.conf # Pigeonhole version 0.4.21 (92477967) # OS: Linux 4.15.0-47-generic x86_64 Ubuntu 18.04.2 LTS auth_default_realm = psi.com.br auth_master_user_separator = * auth_mechanisms = PLAIN LOGIN deliver_log_format = from=%{from}, envelope_sender=%{from_envelope}, subject=%{subject}, msgid=%m, size=%{size}, %$ dict { lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf quotadict = mysql:/etc/dovecot/dovecot-used-quota.conf } disable_plaintext_auth = no first_valid_uid = 2000 last_valid_uid = 2000 listen = * [::] log_path = /var/log/dovecot/dovecot.log mail_gid = 2000 mail_location = maildir:%Lh/Maildir/:INDEX=%Lh/Maildir/ mail_plugins = quota mailbox_alias acl mail_log notify stats mail_uid = 2000 managesieve_notify_capability = mailto managesieve_sieve_capability = fileinto reject envelope encoded-character vacation subaddress comparator-i;ascii-numeric relational regex imap4flags copy include variables body enotify environment mailbox date index ihave duplicate mime foreverypart extracttext namespace { inbox = yes location = mailbox Archive { auto = no special_use = \Archive } mailbox Archives { auto = no special_use = \Archive } mailbox "Deleted Messages" { auto = no special_use = \Trash } mailbox Drafts { auto = subscribe special_use = \Drafts } mailbox Junk { auto = subscribe special_use = \Junk } mailbox "Junk E-mail" { auto = no special_use = \Junk } mailbox Sent { auto = subscribe special_use = \Sent } mailbox "Sent Items" { auto = no special_use = \Sent } mailbox "Sent Messages" { auto = no special_use = \Sent } mailbox Spam { auto = no special_use = \Junk } mailbox Trash { auto = subscribe special_use = \Trash } prefix = separator = / type = private } namespace { list = children location = maildir:%%Lh/Maildir/:INDEX=%%Lh/Maildir/Shared/%%Ld/%%Ln prefix = Shared/%%u/ separator = / subscriptions = yes type = shared } passdb { args = /etc/dovecot/dovecot-mysql.conf driver = sql } passdb { args = /etc/dovecot/dovecot-master-users driver = passwd-file master = yes } plugin { acl = vfile last_login_dict = proxy::lastlogin last_login_key = last-login/%u/%d mail_log_fields = uid box msgid size from subject mailbox_alias_new = Sent Messages mailbox_alias_new2 = Sent Items mailbox_alias_old = Sent mailbox_alias_old2 = Sent quota = dict:user::proxy::quotadict quota_grace = 10%% quota_warning = storage=100%% quota-warning 100 %u quota_warning2 = storage=95%% quota-warning 95 %u quota_warning3 = storage=90%% quota-warning 90 %u quota_warning4 = storage=85%% quota-warning 85 %u sieve = ~/sieve/dovecot.sieve sieve_before = /var/vmail/sieve/dovecot.sieve sieve_dir = ~/sieve sieve_global_dir = /var/vmail/sieve sieve_max_redirects = 30 sieve_vacation_send_from_recipient = yes stats_refresh = 30 secs stats_track_cmds = yes } protocols = pop3 imap sieve lmtp service auth { unix_listener /var/spool/postfix/private/dovecot-auth { group = postfix mode = 0666 user = postfix } unix_listener auth-master { group = vmail mode = 0666 user = vmail } unix_listener auth-userdb { group = vmail mode = 0660 user = vmail } } service dict { unix_listener dict { group = vmail mode = 0660 user = vmail } } service imap-login { process_limit = 500 service_count = 1 } service lmtp { executable = lmtp -L inet_listener lmtp { address = 127.0.0.1 port = 24 } process_min_avail = 5 unix_listener /var/spool/postfix/private/dovecot-lmtp { group = postfix mode = 0600 user = postfix } user = vmail } service managesieve-login { inet_listener sieve { address = 127.0.0.1 port = 4190 } } service pop3-login { service_count = 1 } service quota-warning { executable = script /usr/local/bin/dovecot-quota-warning.sh unix_listener quota-warning { group = vmail mode = 0660 user = vmail } } service stats { fifo_listener stats-mail { mode = 0644 user = vmail } inet_listener { address = 127.0.0.1 port = 24242 } } ssl_cert = </etc/letsencrypt/live/psi.com.br/fullchain.pem ssl_cipher_list = ECDH+AESGCM:DH+AESGCM:ECDH+AES256:DH+AES256:ECDH+AES128:DH+AES:ECDH+3DES:DH+3DES:RSA+AESGCM:RSA+AES:RSA+3DES:!aNULL:!MD5 ssl_key = # hidden, use -P to show it ssl_prefer_server_ciphers = yes userdb { args = /etc/dovecot/dovecot-mysql.conf driver = sql } protocol lda { lda_mailbox_autocreate = yes lda_mailbox_autosubscribe = yes log_path = /var/log/dovecot/sieve.log mail_plugins = quota mailbox_alias acl mail_log notify stats sieve } protocol lmtp { lmtp_save_to_detail_mailbox = yes log_path = /var/log/dovecot/lmtp.log mail_plugins = quota sieve recipient_delimiter = + } protocol imap { imap_client_workarounds = tb-extra-mailbox-sep log_path = /var/log/dovecot/imap.log mail_max_userip_connections = 30 mail_plugins = quota mailbox_alias acl mail_log notify stats imap_quota imap_acl imap_stats last_login } protocol pop3 { log_path = /var/log/dovecot/pop3.log mail_max_userip_connections = 30 mail_plugins = quota mailbox_alias acl mail_log notify stats last_login pop3_client_workarounds = outlook-no-nuls oe-ns-eoh pop3_uidl_format = %08Xu%08Xv }
_ Engº Julio Cesar Covolato
0v0 <julio@psi.com.br>/(_)\ F: +55 11 99175-9260 ^ ^ PSI INTERNET
Em 06/06/2019 16:40, Júlio Covolato via dovecot escreveu:
Hi,
I'm configuring Last_Login plugin whith Mysql back end and all works fine.
I just wonder to track besides time_stamp, user and domain, the remote IP and proto (imap or pop3)
I tried in dovecot.conf:
plugin {
...
...
# Track last login time on imap and pop3 last_login_dict = proxy::lastlogin last_login_key = last-login/%u/%d/%r
}
dict { ...
lastlogin = mysql:/etc/dovecot/dovecot-last-login.conf ... }
protocol imap {
mail_plugins = $mail_plugins ... last_login ...
}
protocol pop3 { mail_plugins = $mail_plugins last_login
...
}
And dovecot-last-login.conf:
connect = host=127.0.0.1 port=3306 dbname=vmail user=vmailadmin password=xxxxxxxxxxxxxxxxxx
map { pattern = shared/last-login/$user/$domain table = last_login value_field = last_login value_type = uint
fields { username = $user domain = $domain rip = $rip } }
And the table criated in mysql:
CREATE TABLE IF NOT EXISTS
last_login(usernameVARCHAR(255) NOT NULL DEFAULT '',domainVARCHAR(255) NOT NULL DEFAULT '',last_loginINT(11) DEFAULT NULL,ripVARCHAR(16) NOT NULL DEFAULT '', PRIMARY KEY (username), INDEX (domain), INDEX (last_login), INDEX (rip) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;And got de error in dovecot log:
Jun 06 13:56:59 dict(21157): Error: Failed to initialize dictionary 'lastlogin': dict mysql: Error in configuration file /etc/dovecot/dovecot-last-login.conf line 13: Unused variable: rip
Thanks in any help.
Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus
Em 06/06/2019 22:45, Aki Tuomi via dovecot escreveu:
map { pattern = shared/last-login/$user/$domain table = last_login value_field = last_login value_type = uint
fields { username = $user domain = $domain rip = $rip } }
You are missing /$rip in pattern.
Aki Tuomi
Thanks Aki.
Works perfectly.
What I did:
#$ cat 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 } }
dovecot.conf:
plugin {
...
...
# Track last login time on imap and pop3 last_login_dict = proxy::lastlogin last_login_key = last-login/%u/%d/%r/%s }
Result on mysql:
mysql> select * from last_login where username = 'julio@xxx.com.br'; +------------------+------------+------------+---------------+-------+ | username | domain | last_login | rip | proto | +------------------+------------+------------+---------------+-------+ | julio@xxx.com.br | xxx.com.br | 1559921589 | 177.xxx.xxx.230 | imap | +------------------+------------+------------+---------------+-------+ 1 row in set (0.00 sec)
Thank you very much!!
_ Engº Julio Cesar Covolato
0v0 <julio@psi.com.br>/(_)\ F: +55 11 99175-9260 ^ ^ PSI INTERNET
Este email foi escaneado pelo Avast antivírus. https://www.avast.com/antivirus
participants (2)
-
Aki Tuomi
-
Júlio Covolato