Hello,
Trying to implement lastlogin via mysql. I'm getting an unknown column
username which I don't get. Here's the log:
Apr 28 17:18:15 ohio dovecot: imap-login: Login:
user=<user at example.com>, method=PLAIN, rip=127.0.0.1, lip=127.0.0.1,
mpid=16257, secured, session=<KxiSnEBOwUx/AAAB>
Apr 28 17:18:15 ohio dovecot: dict(17099): Error: sql dict: commit
failed: Unknown column 'username' in 'field list'
Apr 28 17:18:15 ohio dovecot: imap(user at example.com): Error:
dict-client: server returned failure: 1493414295 (reply took 0.026
secs)
Apr 28 17:18:15 ohio dovecot: imap(user at example.com): Error:
last_login_dict: Failed to write value for user user at example.com
and my configuration:
# 2.2.29.1 (e0b76e3): /usr/local/etc/dovecot/dovecot.conf
# Pigeonhole version 0.4.18 (29cc74d)
# OS: FreeBSD 10.3-RELEASE-p18 amd64
auth_default_realm = example.com
auth_mechanisms = plain login cram-md5
auth_realms = example.com example.net
dict {
acl = mysql:/usr/local/etc/dovecot/dovecot-share-folder.conf
lastlogin = mysql:/usr/local/etc/dovecot/dovecot-last-login.conf
sqlquota = mysql:/usr/local/etc/dovecot/dovecot-used-quota.conf
}
disable_plaintext_auth = no
first_valid_gid = 999
first_valid_uid = 999
hostname = mail.example.com
imap_client_workarounds = delay-newmail tb-extra-mailbox-sep tb-lsub-flags
last_valid_gid = 999
last_valid_uid = 999
lda_mailbox_autocreate = yes
lda_mailbox_autosubscribe = yes
listen = 127.0.0.1 xxx.xxx.xxx.xxx
mail_fsync = never
mail_gid = vmail
mail_home = /home/vmail/%d/%n
mail_location = maildir:~/mail/:LAYOUT=fs:INDEX=~/mail/
mail_plugins = acl mail_log notify quota quota_clone trash virtual welcome zlib
mail_server_admin = mailto:postmaster at example.com
mail_uid = vmail
mailbox_list_index = yes
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 imapflags notify imapsieve vnd.dovecot.imapsieve
namespace {
hidden = no
list = yes
location =
maildir:/home/vmail/public:LAYOUT=fs:CONTROL=~/mail/public:INDEXPVT=~/mail/public:INDEX=~/mail/public
mailbox TestFolder {
auto = subscribe
comment = Public Folder for message sharing
}
prefix = public/
separator = /
subscriptions = yes
type = public
}
namespace {
list = yes
location = maildir:~/mail/:INDEX=~/mail/shared/%%Ld/%%Ln
prefix = shared/%%u/
separator = /
subscriptions = yes
type = shared
}
namespace {
location = virtual:/usr/local/etc/dovecot/virtual
mailbox All {
auto = subscribe
comment = All my messages
special_use = \All
}
prefix = virtual/
separator = /
}
namespace inbox {
inbox = yes
location mailbox Archive {
auto = no
special_use = \Archive
}
mailbox Archives {
auto = subscribe
special_use = \Archive
}
mailbox "Deleted Messages" {
auto = no
autoexpunge = 30 days
special_use = \Trash
}
mailbox Drafts {
auto = subscribe
special_use = \Drafts
}
mailbox Junk {
auto = no
autoexpunge = 30 days
special_use = \Junk
}
mailbox "Junk E-mail" {
auto = no
autoexpunge = 30 days
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 = subscribe
autoexpunge = 30 days
special_use = \Junk
}
mailbox Trash {
auto = subscribe
autoexpunge = 30 days
special_use = \Trash
}
prefix separator = /
type = private
}
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_anyone = allow
acl_shared_dict = file:/usr/local/etc/dovecot/shared-mailboxes
imapsieve_mailbox1_before file:/usr/local/lib/dovecot/sieve/report-spam.sieve
imapsieve_mailbox1_causes = COPY
imapsieve_mailbox1_name = Spam
imapsieve_mailbox2_before = file:/usr/local/lib/dovecot/sieve/report-ham.sieve
imapsieve_mailbox2_causes = COPY
imapsieve_mailbox2_from = Spam
imapsieve_mailbox2_name = *
last_login_dict = proxy::lastlogin
last_login_key = last-login/%u
mail_log_events = delete undelete expunge copy mailbox_delete mailbox_rename
mail_log_fields = uid box msgid size
quota = count:User quota
quota_clone_dict = proxy::sqlquota
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 = /home/vmail/%d/sieve/dovecot.sieve
sieve_before = /home/vmail/sieve/dovecot.sieve
sieve_default = /usr/local/etc/dovecot/sieve/dovecot.sieve
sieve_dir = /usr/local/etc/dovecot/sieve
sieve_extensions = +notify +imapflags
sieve_global_dir = /home/vmail/sieve
sieve_global_extensions = +vnd.dovecot.pipe +vnd.dovecot.execute
sieve_max_redirects = 30
sieve_max_script_size = 1M
sieve_pipe_bin_dir = /usr/local/lib/dovecot/sieve
sieve_plugins = sieve_imapsieve sieve_extprograms
sieve_user_log = /home/vmail/sieve/sieve_error.log
trash = /usr/local/etc/dovecot/dovecot-trash.conf.ext
welcome_script = welcome %u
welcome_wait = yes
}
protocols = imap sieve
sendmail_path = /usr/local/sbin/sendmail
service auth {
unix_listener /var/spool/postfix/private/auth {
mode = 0666
}
unix_listener auth-userdb {
group = vmail
mode = 0666
user = vmail
}
}
service dict {
unix_listener dict {
mode = 0660
user = vmail
}
}
service imap-login {
inet_listener imap {
port = 143
}
inet_listener imaps {
port = 993
ssl = yes
}
}
service managesieve-login {
inet_listener sieve {
address = 127.0.0.1
port = 4190
}
}
service quota-status {
client_limit = 1
executable = quota-status -p postfix
inet_listener {
address = 127.0.0.1
port = 12345
}
}
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/bin/welcome.sh
unix_listener welcome {
user = vmail
}
user = vmail
}
ssl_cert = </usr/local/etc/letsencrypt/live/mail.example.com/fullchain.pem
ssl_cipher_list
ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256
at STRENGTH
ssl_dh_parameters_length = 2048
ssl_key = # hidden, use -P to show it
ssl_prefer_server_ciphers = yes
ssl_protocols = !SSLv2 !SSLv3 !TLSv1 !TLSv1.1
userdb {
driver = prefetch
}
userdb {
args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
userdb {
args = uid=vmail gid=vmail home=/home/vmail/%d/%n
driver = static
}
protocol lda {
mail_fsync = optimized
mail_plugins = acl mail_log notify quota quota_clone trash virtual
welcome zlib sieve
}
protocol imap {
mail_plugins = acl mail_log notify quota quota_clone trash virtual
welcome zlib imap_acl imap_quota imap_sieve imap_zlib last_login
}
dovecot-last-login.conf
connect = host=/tmp/mysql.sock dbname=dbname user=user password=password
# Last Login
map {
pattern = shared/last-login/$user
table = virtual_users
username_field = user
value_field = lastlogin
fields {
username = $user
}
}
can anyone spot my error?
Thanks.
Dave.
On 4/28/17, Aki Tuomi <aki.tuomi at dovecot.fi>
wrote:>
> https://wiki2.dovecot.org/Plugins/LastLogin
>
> Aki
>
>> On April 28, 2017 at 9:05 PM David Mehler <dave.mehler at
gmail.com> wrote:
>>
>>
>> Hi Aki,
>>
>> Thanks. Can you tell me how you implemented it?
>>
>> Thanks.
>> Dave.
>>
>>
>> On 4/28/17, Aki Tuomi <aki.tuomi at dovecot.fi> wrote:
>> >
>> >> On April 28, 2017 at 8:08 PM David Mehler <dave.mehler at
gmail.com>
>> >> wrote:
>> >>
>> >>
>> >> Hello,
>> >>
>> >> Is anyone using the last_login plugin with a Mysql database?
I'd like
>> >> to track when users were last on the system.
>> >>
>> >> Thanks.
>> >> Dave.
>> >
>> > It's used by our customers.
>> >
>> > Aki
>> >
>