Hello Aki,
Thanks for your suggestion. I followed it and the wiki at:
https://wiki.dovecot.org/Plugins/LastLogin
Good news, I get a record in the users table, with the username that just logged in. This is good. Bad news is I got unexpected output from the last_login time stamp. Here's the table and the user:
mysql> describe users; +------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+-------+ | userid | varchar(255) | NO | PRI | NULL | | | last_login | int(11) | YES | | NULL | | +------------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
mysql> select * from users; +------------------------+------------+ | userid | last_login | +------------------------+------------+ | user@domain.com | 1523459718 | +------------------------+------------+ 1 row in set (0.01 sec)
The issue is the 1523459718 I was expecting something like a time stamp. Is this fixable? Also, can I use last_login to see on which IP the user last logged in from?
Any ideas on my other issue, the one on the running out of memory when attempting to access the imported gmail messages?
Thanks for your help so far.
Thanks. Dave.
On 4/11/18, Aki Tuomi aki.tuomi@dovecot.fi wrote:
Hi!
On 10.04.2018 19:31, David Mehler wrote:
Hello,
I've got a few misc issues that I'm trying to get a handle on.
I'm having what is probably a simple error with last logins. It's not working, unable to write values to the database. Here's the log lines:
2018-04-10 10:43:34 imap-login: Info: Login: user=user@domain.com, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1, mpid=75934, TLS, session=
2018-04-10 10:43:34 imap(user@domain.com)<75934> : Error: last_login_dict: Failed to write value for user user@domain.com: dict-server returned failure: sql dict: commit failed: Field 'name' doesn't have a default value (reply took 0.013 secs (0.000 in dict wait, 0.012 in other ioloops, 0.000 in locks, async-id reply 0.002 secs ago, started on dict-server 0.005 secs ago, took 0.005 secs)) It's attempting to insert a new row there for some reason. I'd recommend using separate table for last-login values. Here's an excerpt from my dovecot-dict-sql.conf.ext file:
map { pattern = shared/last-login/$user table = accounts value_field = last_login value_type = uint
fields { username = $user } }
and the relevant database table: describe accounts; +------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| username | varchar(64) | NO | MUL | NULL | |
| domain | varchar(255) | NO | MUL | NULL | |
| password | varchar(255) | NO | | NULL | |
| quota | int(10) unsigned | YES | | 0 | |
| enabled | tinyint(1) | YES | | 0 | |
| sendonly | tinyint(1) | YES | | 0 | |
| last_login | int(11) | YES | | NULL | |
Second issue, I've used imapsync to download my gmail messages to dovecot. I am atempting to access these messages and getting: 2018-04-08 11:11:58 indexer-worker(user@domain.com)<44193><4GzoqFdpisJ/AAAB:StoZDD4xylqhrAAA0J78UA>: Info: Indexed 0 messages in INBOX 2018-04-08 11:11:58 indexer-worker(user@domain.com)<44193><4GzoqFdpisJ/AAAB:0AaXFD4xylqhrAAA0J78UA>: Info: Indexed 0 messages in INBOX 2018-04-08 11:11:58 indexer-worker(user@domain.com)<44193><4GzoqFdpisJ/AAAB:CP3TIT4xylqhrAAA0J78UA>: Info: Indexed 0 messages in INBOX 2018-04-08 11:11:58 indexer-worker(user@domain.com)<44193><4GzoqFdpisJ/AAAB:eNEWMT4xylqhrAAA0J78UA>: Info: Indexed 0 messages in INBOX 2018-04-08 11:15:28 imap(user@domain.com)<21374><4GzoqFdpisJ/AAAB>: Fatal: block_alloc(8388608): Out of memory 2018-04-08 11:15:28 imap(user@domain.com)<21374><4GzoqFdpisJ/AAAB>: Fatal: master: service(imap): child 21374 returned error 83 (Out of memory (service imap { vsz_limit=256 MB }, you may need to increase it) - set CORE_OUTOFMEM=1 environment to get core dump)
when I atempt to do so. For completeness the imapsync command I used was:
imapsync --host1 imap.gmail.com --user1 Username --password1 Password --host2 localhost --user2 username --password2 Password --ssl1 --useheader 'Message-Id' --regextrans2 's/\[Gmail\]/user\@domain/'
Increase memory, your cache takes more memory than you have anticipated.
Any help appreciated on either issue. Thanks. Dave.
doveconf -n # 2.3.1 (8e2f634): /usr/local/etc/dovecot/dovecot.conf # Pigeonhole version 0.5.1 (d9bc6dfe) # OS: FreeBSD 11.1-RELEASE-p4 amd64 # Hostname: localhost auth_cache_size = 24 M auth_cache_ttl = 18 hours auth_default_realm = domain.com auth_mechanisms = plain login dict { acl = mysql:/usr/local/etc/dovecot/dovecot-dict-sql.conf.ext lastlogin = mysql:/usr/local/etc/dovecot/dovecot-dict-sql.conf.ext quota = mysql:/usr/local/etc/dovecot/dovecot-dict-sql.conf.ext } first_valid_gid = 999 first_valid_uid = 999 hostname = xxxx.xxxx.xxxx imap_idle_notify_interval = 10 mins last_valid_gid = 999 last_valid_uid = 999 lda_mailbox_autocreate = yes lda_mailbox_autosubscribe = yes lda_original_recipient_header = X-Original-To listen = 127.0.0.1 xxx.xxx.xxx.xxx log_path = /var/log/dovecot/dovecot.log log_timestamp = "%Y-%m-%d %H:%M:%S " mail_access_groups = vmail mail_gid = vmail mail_home = /home/vmail/mailboxes/%d/%n mail_location = maildir:~/mail:LAYOUT=fs mail_plugins = acl mail_log notify quota trash virtual welcome zlib mail_privileged_group = vmail mail_server_admin = mailto:postmaster@davemehler.com mail_uid = vmail mailbox_idle_check_interval = 59 secs 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 spamtest spamtestplus virustest editheader imapflags notify imapsieve vnd.dovecot.imapsieve namespace { location = maildir:/home/vmail/public/:LAYOUT=fs:CONTROL=~/mail/public:INDEX=~/mail/public mailbox office { auto = subscribe comment = Public Folder for message sharing } prefix = public/ separator = / subscriptions = yes type = public } namespace inbox { inbox = yes location = mailbox Archives { auto = subscribe special_use = \Archive } mailbox Drafts { auto = subscribe special_use = \Drafts } mailbox Sent { auto = subscribe special_use = \Sent } mailbox Spam { auto = subscribe autoexpunge = 30 days special_use = \Junk } mailbox Trash { auto = subscribe autoexpunge = 30 days special_use = \Trash } mailbox virtual/All { comment = All my messages special_use = \All } prefix = separator = / type = private } namespace virtual { location = virtual:/usr/local/etc/dovecot/virtual:INDEX=~/virtual:CONTROL=~/virtual prefix = virtual/ separator = / } passdb { args = /usr/local/etc/dovecot/dovecot-sql.conf.ext driver = sql } plugin { acl = vfile:/usr/local/etc/dovecot/global-acls:cache_secs=300 acl_globals_only = yes acl_shared_dict = proxy::acl fts = lucene fts_autoindex = yes fts_autoindex_max_recent_msgs = 80 fts_index_timeout = 90 fts_lucene = whitespace_chars=@. normalize no_snowball imapsieve_mailbox1_before = file:/home/vmail/sieve/global/report-spam.sieve imapsieve_mailbox1_causes = COPY imapsieve_mailbox1_name = Spam imapsieve_mailbox2_before = file:/home/vmail/sieve/global/report-ham.sieve imapsieve_mailbox2_causes = COPY imapsieve_mailbox2_from = Spam imapsieve_mailbox2_name = * last_login_dict = proxy::lastlogin last_login_key = last-login/%n mail_log_events = delete undelete expunge copy mailbox_delete mailbox_rename mail_log_fields = uid box msgid size quota = dict:User quota::proxy::quota quota_exceeded_message = Storage quota for this account has been exceeded, please try again later. quota_grace = 10%% quota_status_nouser = DUNNO quota_status_overquota = 552 5.2.2 Mailbox is full quota_status_success = DUNNO quota_vsizes = true quota_warning = storage=100%% quota-exceeded 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 quota_warning5 = storage=75%% quota-warning 75 %u sieve = ~/.dovecot.sieve sieve_before = /home/vmail/sieve/before.d sieve_default = /home/vmail/sieve/default.sieve sieve_dir = ~/sieve sieve_extensions = +notify +imapflags +spamtest +spamtestplus +virustest +editheader sieve_global_dir = /home/vmail/sieve sieve_global_extensions = +vnd.dovecot.pipe +vnd.dovecot.execute +vnd.dovecot.environment sieve_max_redirects = 30 sieve_max_script_size = 1M sieve_pipe_bin_dir = /home/vmail/sieve sieve_plugins = sieve_imapsieve sieve_extprograms sieve_spamtest_max_header = X-Spamd-Result: default: [[:alnum:]]+ \[-?[[:digit:]]+\.[[:digit:]]+ / (-?[[:digit:]]+\.[[:digit:]]+)\] sieve_spamtest_status_header = X-Spamd-Result: default: [[:alnum:]]+ \[(-?[[:digit:]]+\.[[:digit:]]+) / -?[[:digit:]]+\.[[:digit:]]+\] sieve_spamtest_status_type = score sieve_user_log = /home/vmail/sieve/sieve_error.log sieve_virustest_status_header = X-Virus-Scan: Found to be (.+)\. sieve_virustest_status_type = text sieve_virustest_text_value1 = clean sieve_virustest_text_value5 = infected trash = /usr/local/etc/dovecot/trash.conf welcome_script = welcome %u postmaster@%d welcome_wait = yes } postmaster_address = postmaster@xxx protocols = imap lmtp sieve sendmail_path = /usr/local/sbin/sendmail service auth-worker { user = vmail } service auth { unix_listener /var/spool/postfix/private/auth { group = postfix mode = 0666 user = postfix } unix_listener auth-userdb { group = vmail mode = 0666 user = vmail } } service dict { unix_listener dict { group = vmail mode = 0660 user = vmail } user = root } service imap-login { inet_listener imap { address = 127.0.0.1 } inet_listener imaps { address = xxx.xxx.xxx.xxx port = 993 ssl = yes } } service imap-postlogin { executable = script-login /usr/local/etc/dovecot/trackauth.sh user = vmail } service imap { executable = imap imap-postlogin } service lmtp { executable = lmtp unix_listener /var/spool/postfix/private/dovecot-lmtp { group = postfix mode = 0666 user = postfix } } service managesieve-login { inet_listener sieve { address = 127.0.0.1 port = 4190 } } service quota-status { client_limit = 1 executable = quota-status -p postfix unix_listener /var/spool/postfix/private/dovecot-quota { group = postfix mode = 0660 user = postfix } } service quota-warning { executable = script /usr/local/etc/dovecot/quota-warning.sh unix_listener quota-warning { group = vmail mode = 0660 user = vmail } user = vmail } service welcome { executable = script /usr/local/etc/dovecot/welcome.sh unix_listener welcome { user = vmail } user = vmail } ssl = required ssl_cert =