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
>> >
>
> On April 29, 2017 at 12:29 AM David Mehler <dave.mehler at gmail.com> wrote: > > > 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 > > >You can configure all this in dovecot-dict-sql.conf, which you have not provided. The actual column names are defined there. Aki
Hello,
Here is the requested file:
driver = mysql
connect = host=/tmp/mysql.sock dbname=mail user=mail_admin password=MainBoard55
# Last Login
map {
pattern = shared/last-login/$user
table = virtual_users
username_field = user
value_field = lastlogin
fields {
user = $user
}
}
Thanks.
Dave.
On 4/28/17, Aki Tuomi <aki.tuomi at dovecot.fi>
wrote:>
>> On April 29, 2017 at 12:29 AM David Mehler <dave.mehler at
gmail.com> wrote:
>>
>>
>> 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
>>
>>
>>
>
> You can configure all this in dovecot-dict-sql.conf, which you have not
> provided. The actual column names are defined there.
>
> Aki
>
Hi, Thanks for the pointing out of the oops! I missed that. Don't worry that has been changed. Any suggestions on my issue? Thanks. Dave. On 4/28/17, David Mehler <dave.mehler at gmail.com> wrote:> Hi, > > OOPS! I missed that one and it was big. > > Changing now. > > Thanks. > Dave. > > > On 4/28/17, Noel Butler <noel.butler at ausics.net> wrote: >> ummmmmm >> >> It's usually a good idea to redact user and passwords for databases, >> even if only local socket ;) >> >> I suggest you change it ASAP. >> >> On 29/04/2017 10:53, David Mehler wrote: >> >>> Hello, >>> >>> Here is the requested file: >>> >>> driver = mysql >>> connect = host=/tmp/mysql.sock dbname=mail user=mail_admin >>> password=MainBoard55 >>> >>> # Last Login >>> map { >>> pattern = shared/last-login/$user >>> table = virtual_users >>> username_field = user >>> value_field = lastlogin >>> fields { >>> user = $user >>> } >>> } >>> >>> Thanks. >>> Dave. >>> >>> On 4/28/17, Aki Tuomi <aki.tuomi at dovecot.fi> wrote: >>> On April 29, 2017 at 12:29 AM David Mehler <dave.mehler at gmail.com> >>> wrote: >>> >>> 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 >>> >>> You can configure all this in dovecot-dict-sql.conf, which you have not >>> provided. The actual column names are defined there. >>> >>> Aki >> >> -- >> Kind Regards, >> >> Noel Butler >> >> This Email, including any attachments, may contain legally privileged >> information, therefore remains confidential and subject to copyright >> protected under international law. You may not disseminate, discuss, or >> reveal, any part, to anyone, without the authors express written >> authority to do so. If you are not the intended recipient, please notify >> the sender then delete all copies of this message including attachments, >> immediately. Confidentiality, copyright, and legal privilege are not >> waived or lost by reason of the mistaken delivery of this message. Only >> PDF [1] and ODF [2] documents accepted, please do not send proprietary >> formatted documents >> >> >> >> Links: >> ------ >> [1] http://www.adobe.com/ >> [2] http://en.wikipedia.org/wiki/OpenDocument >
I tried with following config, and it seems to work at least for me:
map {
pattern = shared/last-login/$user
table = users
value_field = last_login
value_type = uint
fields {
userid = $user
}
}
last_login and userid are SQL table's field name.
Aki
> On April 29, 2017 at 4:28 AM David Mehler <dave.mehler at gmail.com>
wrote:
>
>
> Hi,
>
> Thanks for the pointing out of the oops! I missed that. Don't worry
> that has been changed.
>
> Any suggestions on my issue?
>
> Thanks.
> Dave.
>
>
> On 4/28/17, David Mehler <dave.mehler at gmail.com> wrote:
> > Hi,
> >
> > OOPS! I missed that one and it was big.
> >
> > Changing now.
> >
> > Thanks.
> > Dave.
> >
> >
> > On 4/28/17, Noel Butler <noel.butler at ausics.net> wrote:
> >> ummmmmm
> >>
> >> It's usually a good idea to redact user and passwords for
databases,
> >> even if only local socket ;)
> >>
> >> I suggest you change it ASAP.
> >>
> >> On 29/04/2017 10:53, David Mehler wrote:
> >>
> >>> Hello,
> >>>
> >>> Here is the requested file:
> >>>
> >>> driver = mysql
> >>> connect = host=/tmp/mysql.sock dbname=mail user=mail_admin
> >>> password=MainBoard55
> >>>
> >>> # Last Login
> >>> map {
> >>> pattern = shared/last-login/$user
> >>> table = virtual_users
> >>> username_field = user
> >>> value_field = lastlogin
> >>> fields {
> >>> user = $user
> >>> }
> >>> }
> >>>
> >>> Thanks.
> >>> Dave.
> >>>
> >>> On 4/28/17, Aki Tuomi <aki.tuomi at dovecot.fi> wrote:
> >>> On April 29, 2017 at 12:29 AM David Mehler <dave.mehler at
gmail.com>
> >>> wrote:
> >>>
> >>> 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
> >>>
> >>> You can configure all this in dovecot-dict-sql.conf, which you
have not
> >>> provided. The actual column names are defined there.
> >>>
> >>> Aki
> >>
> >> --
> >> Kind Regards,
> >>
> >> Noel Butler
> >>
> >> This Email, including any attachments, may contain legally
privileged
> >> information, therefore remains confidential and subject to
copyright
> >> protected under international law. You may not disseminate,
discuss, or
> >> reveal, any part, to anyone, without the authors express written
> >> authority to do so. If you are not the intended recipient, please
notify
> >> the sender then delete all copies of this message including
attachments,
> >> immediately. Confidentiality, copyright, and legal privilege are
not
> >> waived or lost by reason of the mistaken delivery of this message.
Only
> >> PDF [1] and ODF [2] documents accepted, please do not send
proprietary
> >> formatted documents
> >>
> >>
> >>
> >> Links:
> >> ------
> >> [1] http://www.adobe.com/
> >> [2] http://en.wikipedia.org/wiki/OpenDocument
> >