Re: How to get original recipient from Postfix when using LMTP?

[Tuomo Soini via dovecot]

On Tue, 21 May 2019 18:24:46 +
MRob via dovecot  wrote:

> Many people prefer to use LMTP for delivery from postfix for better 
> efficiency but X-Original-to header support still missing after many 
> years. One affect of this is need to set 
> sieve_vacation_dont_check_recipient = yes which violate Sieve
> standard and cause auto-replyies sent to messages that should not
> happen. Or abandon LMTP. or abandon postfix??
> 
> So while feature request is stalled are there any realistic
> workarounds?

add to smtp_recipient_restrictions, before permitting email but after
all checks:

check_recipient_access pcre:/etc/postfix/recipient_access.pcre

# cat /etc/postfix/recipient_access.pcre 
/(.+)/ prepend X-Original-To: $1

-- 
Tuomo Soini 
Foobar Linux services
+358 40 5240030
Foobar Oy 

RE: Converting user mailboxes from maildir to sdbox

[Marc Roos via dovecot]

 
Let me know if you find a nice solution to migrate mailboxes per user, 
without downtime. I tried the use of the adviced userdb and using the 
override of the maillocation. But that doesn’t work with my mbox inbox. 


I wanted to migrate then per user to an new server environment but still 
did not find a definitive guide on how to do this. 



-Original Message-
From: David Mehler via dovecot [mailto:dovecot@dovecot.org] 
Sent: woensdag 22 mei 2019 2:31
To: dovecot
Subject: Converting user mailboxes from maildir to sdbox

Hello,

I've got a Postfix/Dovecot server setup. Currently Dovecot is version 
2.3.6, and it's using Maildir storage. The mailbox is:

mail_home = /home/vmail/mailboxes/%d/%n
mail_location = maildir:~/mail:LAYOUT=fs

I'm wanting to convert from Maildir to sdbox. I looked at:

https://wiki2.dovecot.org/MailboxFormat

I was initially thinking mdbox but that's multiple messages per file, 
which I do not believe is what I want. Am I correct that in the sdbox 
format if a single message file is corrupted only that message is 
corrupt and not the rest of the messages as would be the case in a mdbox 
setup?

On the page:

https://wiki2.dovecot.org/Migration/MailFormat
"
maildir -> sdbox migration. Set mail_location=sdbox:~/sdbox and run 
dsync -u username mirror maildir:~/Maildir "

If I set mail_location=sdbox:~/sdbox will that put the user mailboxes in 
mail_home/sdbox?

So it would be:

/home/vmail/mailboxes/example.com/username/sdbox

I am needing to do this migration, but am paranoid about loosing mail or 
corrupting mailboxes. All of my users are virtual users and they are 
stored in a mysql database.

Thanks.
Dave.

Integrity check mdbox?

[Marc Roos via dovecot]

Is it possible to do some sort of integrity check on mdbox's 

Re: Integrity check mdbox?

[Aki Tuomi via dovecot]

 
 
  
   
  
  
   
On 22 May 2019 12:11 Marc Roos via dovecot <
dovecot@dovecot.org> wrote:
   
   

   
   

   
   
Is it possible to do some sort of integrity check on mdbox's
   
  
  
   doveadm force-resync
  
  
   ---
Aki Tuomi
   
 

Re: JMAP support in Dovecot

[Aki Tuomi via dovecot]

Unfortunately we have not been able to work on this much, but also the
JMAP spec was until very recently still being worked. We have open
dialogue with the Thunderbird people, they haven't so far indicated any
pressing need for JMAP in Dovecot.

This said, JMAP is still very much in our roadmap. Perhaps just not as
close as I initially thought.

Aki

On 21.5.2019 16.29, Tanstaafl via dovecot wrote:
> Hello Aki,
>
> Well, it has been over 3 years since I last asked...
>
> You had said initial JMAP support would hopefully make it into 2.3.
> Since it didn't, I'm hoiping it isn't too far away.
>
> There is some movement with the new Thunderbird team on this, but they
> can't really start serious work on adding JMAP support until there is a
> server implementation to test against.
>
> Is JMAP support still hopefully on the near term roadmap?
>
> Thanks
>
> Charles
>
> On Sun Nov 27 2016 05:28:36 GMT-0500 (Eastern Daylight Time), Aki Tuomi
>  wrote:
>> Hi!
>>
>> We are working on including JMAP support to Dovecot. At this moment I cannot 
>> give any promise for exact
>> version, but hopefully it will be part of v2.3
>>
>> Aki Tuomi
>>
>> Dovecot Oy
>>
>>> On November 26, 2016 at 11:17 PM Andrew Jones  wrote:
>>>
>>> Hi Marcus
>>>
>>> Thanks for your helpful reply.
>>>
>>> Do you know what is going on with JMAP development into Dovecot 2.5?
>>>
>>> It's difficult to get any sort of information from the roadmap and there 
>>> are no Dovecot forums.
>>>
>>> One of the main reasons I'm interested in JMAP is because of Roundcube Next 
>>> and also the other clients it will >> power. Sadly, there has been little 
>>> going on and having emailed Thomas, he is no longer involved in Roundcube 
>>> >> Next - which is a shame. The Kolab guys are really taking liberties 
>>> here, and trying their product, the thing >> is littered with bugs 
>>> everywhere.
>>>
>>> Are you able to comment on what is going on with JMAP development into 
>>> Dovecot?
>>>
>>> Thanks 
>>>
>>> Andrew 
 On 26 Nov 2016, at 19:16, Marcus Rueckert  wrote:

> On 2016-11-26 11:07:00 -0800, WJCarpenter wrote:
> I don't know the answer to that question, but I am curious about 
> something.
> What client are you thinking about using with JMAP? I haven't found much.
> (And much of the demo stuff at jmap.io seems to be busted in various 
> ways.)
 roundcube-next builds on top of it.

darix

Re: weakforced: Possible to access the ip address of report/allow?

[Neil Cook via dovecot]

From dovecot, you can add any additional attributes you like using the 
auth_policy_request_attributes configuration setting, e.g.

By default in 2.3.1 this looks like:

login=%{requested_username} pwhash=%{hashed_password} remote=%{rip} 
device_id=%{client_id} protocol=%s

But you can add additional parameters:

login=%{requested_username} pwhash=%{hashed_password} remote=%{rip} 
device_id=%{client_id} protocol=%s attrs/local_ip=%{lip}

The above will add the local dovecot IP address to the attrs, which can then be 
accessed from wforce policy,

Neil

> On 22 May 2019, at 07:56, Tobi via dovecot  wrote:
> 
> Hi
> 
> I wonder if the information about the origin of report or allow can be
> accessed somehow. lt.remote gives the IP of the client trying to login
> but is there anything in lt which gives the ip of the system that
> connects to wforced?
> 
> Thanks and have a good one
> 
> --
> 
> tobi


Neil Cook
neil.c...@open-xchange.com

-
Open-Xchange AG, Rollnerstr. 14, 90408 Nuremberg, District Court Nuremberg HRB 
24738
Managing Board: Rafael Laguna de la Vera, Carsten Dirks, Michael Knapstein, 
Stephan Martin 
Chairman of the Board: Richard Seibt

European Office: 
Open-Xchange GmbH, Olper Huette 5f, D-57462 Olpe, Germany, District Court 
Siegen, HRB 8718 
Managing Director: Frank Hoberg

US Office: 
Open-Xchange. Inc., 530 Lytton Avenue, Palo Alto, CA 94301, USA 
-

lmtp report permission denied on delivery to multiple recipients

[Patrick Cernko via dovecot]

Hello,

when receiving mails for multiple recipients via LMTP, I often see the 
following messages on our servers:



May 22 11:06:10 sinon dovecot[44304]: lmtp(119718): Connect from $IP$
May 22 11:06:11 sinon dovecot[44304]: lmtp($USER1$)<119718><2HnmOgIR5Vym0wEA22L5Rg>: 
msgid=<$MSGID$>: saved mail to INBOX
May 22 11:06:11 sinon dovecot[44304]: 
lmtp($USER2$)<119718><2HnmOgIR5Vym0wEA22L5Rg:2>: Error: 
stat(/IMAP/mail/mailboxes/$USER1$/mdbox/mailboxes/INBOX/dbox-Mails/dovecot.index.cache) 
failed: Permission denied (euid=$UID2$($USER2$) egid=$GID2$(rbg) missing +x perm: 
/IMAP/mail/mailboxes/$USER1$, dir owned by $UID1$:$GID2$ mode=0700)
May 22 11:06:11 sinon dovecot[44304]: 
lmtp($USER2$)<119718><2HnmOgIR5Vym0wEA22L5Rg:2>: Error: 
open(/IMAP/mail/mailboxes/$USER1$/mdbox/mailboxes/INBOX/dbox-Mails/dovecot.index.cache) 
failed: Permission denied (euid=$UID2$($USER2$) egid=$GID2$(rbg) missing +x perm: 
/IMAP/mail/mailboxes/$USER1$, dir owned by $UID1$:$GID2$ mode=0700)
May 22 11:06:11 sinon dovecot[44304]: 
lmtp($USER2$)<119718><2HnmOgIR5Vym0wEA22L5Rg:2>: Error: 
lstat(/IMAP/mail/mailboxes/$USER1$/mdbox/mailboxes/INBOX/dbox-Mails/dovecot.index.cache.lock)
 failed: Permission denied
May 22 11:06:11 sinon dovecot[44304]: 
lmtp($USER2$)<119718><2HnmOgIR5Vym0wEA22L5Rg:2>: Error: 
file_dotlock_open(/IMAP/mail/mailboxes/$USER1$/mdbox/mailboxes/INBOX/dbox-Mails/dovecot.index.cache)
 failed: Permission denied (euid=$UID2$($USER2$) egid=$GID2$(rbg) missing +x perm: 
/IMAP/mail/mailboxes/$USER1$, dir owned by $UID1$:$GID2$ mode=0700)
May 22 11:06:11 sinon dovecot[44304]: 
lmtp($USER2$)<119718><2HnmOgIR5Vym0wEA22L5Rg:2>: Error: 
open(/IMAP/mail/mailboxes/$USER1$/mdbox/mailboxes/INBOX/dbox-Mails/dovecot.index.cache) 
failed: Permission denied (euid=$UID2$($USER2$) egid=$GID2$(rbg) missing +x perm: 
/IMAP/mail/mailboxes/$USER1$, dir owned by $UID1$:$GID2$ mode=0700)
May 22 11:06:11 sinon dovecot[44304]: 
lmtp($USER2$)<119718><2HnmOgIR5Vym0wEA22L5Rg:2>: 2HnmOgIR5Vym0wEA22L5Rg:2: sieve: 
msgid=<$MSGID$>: stored mail into mailbox 'INBOX'
May 22 11:06:11 sinon dovecot[44304]: lmtp(119718): Disconnect from $IP$: 
Successful quit

(usernames, uids, gids and IP addresses anonymized)

So far, I was not able to reproduce this issue on a simple test setup, 
but I have the strong impression, that is has something to do with the 
fact, if some of the affected users have a active sieve script and 
others do not use sieve. In the example above $USER1$ did not have a 
sieve script configured but $USER2$ had one, as you can see. As a 
workaround, I configured an empty sieve script for all users with out 
sieve configured. This seems to work but actually I do not want to have 
users with empty sieve scripts.


This bug seems to be related to 
https://dovecot.org/list/dovecot/2013-February/088540.html where Timo 
stated:


"LMTP always delivers the mail to the first user. Then it tries to copy 
the first mail to the second user, because in some setups this can be 
done using hard links. With mbox that of course doesn't work, but looks 
like instead of failing silently it logs an error. So everything is 
working as it should, except there are these unnecessary errors logged. 
I'll see about getting rid of them."


Is it possible to get rid of these error messages, either by a config 
setting that prevents if they do not lead to a real problem or by 
changing the code to avoid the behaviour described by Timo?


Attached you can find doveconf -n output and the used ldap.conf (both 
anonymized), the referenced /etc/dovecot/passdb.deny and 
/etc/dovecot/userdb.overrides are usually empty files and only used 
during mailbox migrations.


Best regards,
--
Patrick Cernko  +49 681 9325 5815
Joint Administration: Information Services and Technology
Max-Planck-Institute fuer Informatik & Softwaresysteme
# 2.2.36.3 (a7d78f5a2): /etc/dovecot/dovecot.conf
# Object storage plugin version 2.2.36.3 (64b24e4d)
# Pigeonhole version 0.4.24 (5a7e9e62)
# OS: Linux 4.14.99.1.amd64-smp x86_64 Debian 9.8 
# Hostname: XXX
auth_verbose = yes
default_vsz_limit = 2 G
doveadm_password =  # hidden, use -P to show it
doveadm_port = 12345
license_checksum =  method=%m rip=%r lip=%l mpid=%e %c
mail_attachment_dir = /IMAP/mail/attachments
mail_attachment_fs = sis-queue /IMAP/mail/attachments/queue:posix
mail_home = /IMAP/mail/mailboxes/%u
mail_location = mdbox:~/mdbox
mail_log_prefix = "%s(%u)<%{pid}><%{session}>: "
mail_max_userip_connections = 0
mail_plugins = " notify replication zlib fts fts_dovecot"
maildir_stat_dirs = 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
namespace inbox {
  inbox = yes
  location = 
  mailbox Drafts {
special_use = \Drafts
  }
  mailbox Junk {
special_use = \Junk
  }
  mailb

Re: weakforced: Possible to access the ip address of report/allow?

[Tobi via dovecot]

Hi Neil

thanks for the hint with the dovecot config, adding this and I can see that

> ... attrs={local_ip="XX.XX.XX.XX"} ...

is now logged by wforce daemon. Then I tried to access that value from
wforce with the following testcode

> if (#lt.attrs > 0)
> then
>   return 7, "ip_local", "ip_local", { test=test }
> end

but even if attrs are set (according to wforce logs), the code above
does not go into if condition. What is the proper way to access the attrs?

Thanks for your help and have a good one

--

tobi
Am 22.05.19 um 11:53 schrieb Neil Cook:
> From dovecot, you can add any additional attributes you like using the 
> auth_policy_request_attributes configuration setting, e.g.
> 
> By default in 2.3.1 this looks like:
> 
> login=%{requested_username} pwhash=%{hashed_password} remote=%{rip} 
> device_id=%{client_id} protocol=%s
> 
> But you can add additional parameters:
> 
> login=%{requested_username} pwhash=%{hashed_password} remote=%{rip} 
> device_id=%{client_id} protocol=%s attrs/local_ip=%{lip}
> 
> The above will add the local dovecot IP address to the attrs, which can then 
> be accessed from wforce policy,
> 
> Neil
> 
>> On 22 May 2019, at 07:56, Tobi via dovecot  wrote:
>>
>> Hi
>>
>> I wonder if the information about the origin of report or allow can be
>> accessed somehow. lt.remote gives the IP of the client trying to login
>> but is there anything in lt which gives the ip of the system that
>> connects to wforced?
>>
>> Thanks and have a good one
>>
>> --
>>
>> tobi
> 
> 
> Neil Cook
> neil.c...@open-xchange.com
> 
> -
> Open-Xchange AG, Rollnerstr. 14, 90408 Nuremberg, District Court Nuremberg 
> HRB 24738
> Managing Board: Rafael Laguna de la Vera, Carsten Dirks, Michael Knapstein, 
> Stephan Martin 
> Chairman of the Board: Richard Seibt
> 
> European Office: 
> Open-Xchange GmbH, Olper Huette 5f, D-57462 Olpe, Germany, District Court 
> Siegen, HRB 8718 
> Managing Director: Frank Hoberg
> 
> US Office: 
> Open-Xchange. Inc., 530 Lytton Avenue, Palo Alto, CA 94301, USA 
> -
> 
> 

Re: weakforced: Possible to access the ip address of report/allow?

[Neil Cook via dovecot]

 
 
  
   Hi Tobi,
  
  
   
  
  
   you need to process lt.attrs in a loop. It's not a real Lua table, it's a user data pretending to be a table, so # doesn't work.
  
  
   
  
  
   For example:
  
  
   
  
  
   
for k, v in pairs(lt.attrs) do
   
   

   
   
    if ((k == "accountStatus") and (v == "blocked"))
   
   

   
   
    then
   
   

   
   
        return -1, "accountStatus blocked", "accountStatus blocked", {}
   
   

   
   
    end
   
   

   
   
end
   
   

   
   
For examples of almost anything you can do in wforce.conf, including the above, please read the wforce.conf man page. It's really very thorough.
   
   

   
   
Neil
   
  
  
   
On 22 May 2019 13:53 Tobi <
tobiswo...@gmail.com> wrote:
   
   

   
   

   
   
Hi Neil
   
   

   
   
thanks for the hint with the dovecot config, adding this and I can see that
   
   

   
   

 ... attrs={local_ip="XX.XX.XX.XX"} ...

   
   
is now logged by wforce daemon. Then I tried to access that value from
   
   
wforce with the following testcode
   
   

   
   

 if (#lt.attrs > 0)


 then


 return 7, "ip_local", "ip_local", { test=test }


 end

   
   
but even if attrs are set (according to wforce logs), the code above
   
   
does not go into if condition. What is the proper way to access the attrs?
   
   

   
   
Thanks for your help and have a good one
   
   

   
   
--
   
   

   
   
tobi
   
   
Am 22.05.19 um 11:53 schrieb Neil Cook:
   
   

 From dovecot, you can add any additional attributes you like using the auth_policy_request_attributes configuration setting, e.g.


 


 By default in 2.3.1 this looks like:


 


 login=%{requested_username} pwhash=%{hashed_password} remote=%{rip} device_id=%{client_id} protocol=%s


 


 But you can add additional parameters:


 


 login=%{requested_username} pwhash=%{hashed_password} remote=%{rip} device_id=%{client_id} protocol=%s attrs/local_ip=%{lip}


 


 The above will add the local dovecot IP address to the attrs, which can then be accessed from wforce policy,


 


 Neil


 


 
  On 22 May 2019, at 07:56, Tobi via dovecot <
  dovecot@dovecot.org> wrote:
 

   
   
>> Hi
   
   
>>
   
   
>> I wonder if the information about the origin of report or allow can be
   
   
>> accessed somehow. lt.remote gives the IP of the client trying to login
   
   
>> but is there anything in lt which gives the ip of the system that
   
   
>> connects to wforced?
   
   
>>
   
   
>> Thanks and have a good one
   
   
>>
   
   
>> --
   
   
>>
   
   
>> tobi
   
   

 


 Neil Cook


 neil.c...@open-xchange.com


 


 -


 Open-Xchange AG, Rollnerstr. 14, 90408 Nuremberg, District Court Nuremberg HRB 24738


 Managing Board: Rafael Laguna de la Vera, Carsten Dirks, Michael Knapstein, Stephan Martin


 Chairman of the Board: Richard Seibt


 


 European Office:


 Open-Xchange GmbH, Olper Huette 5f, D-57462 Olpe, Germany, District Court Siegen, HRB 8718


 Managing Director: Frank Hoberg


 


 US Office:


 Open-Xchange. Inc., 530 Lytton Avenue, Palo Alto, CA 94301, USA


 -


 

   
  
  
   
  
  
    
   -- kind regards,Neil CookChief Security ArchitectPhone: +44 774 7012545Email: neil.c...@open-xchange.com-Open-Xchange AG, Rollnerstr. 14, 90408 Nuremberg, District Court Nuremberg HRB 24738Managing Board: Rafael Laguna de la Vera, Carsten Dirks, Michael Knapstein, Stephan Martin Chairman of the Board: Richard SeibtEuropean Office: Open-Xchange GmbH, Olper Huette 5f, D-57462 Olpe, Germany, District Court Siegen, HRB 8718 Managing Director: Frank HobergUS Office: Open-Xchange. Inc., 530 Lytton Avenue, Palo Alto, CA 94301, USA -
    
   
 

Re: JMAP support in Dovecot

[Tanstaafl via dovecot]

On Wed May 22 2019 05:44:59 GMT-0400 (Eastern Standard Time), Aki Tuomi
via dovecot  wrote:
> Unfortunately we have not been able to work on this much, but also the
> JMAP spec was until very recently still being worked. We have open
> dialogue with the Thunderbird people, they haven't so far indicated any
> pressing need for JMAP in Dovecot.
> 
> This said, JMAP is still very much in our roadmap. Perhaps just not as
> close as I initially thought.

Thanks Aki - no pressing need because of the old chicken/egg problem I
guess...

That said, a few tidbits...

The Thunderbird Devs are using Topicbox for discussing Thunderbird UI
development, and Topicbox is built directly on top of JMAP for the email
integration (I'm not sure they even knew this until I told them yesterday):

From: https://fastmail.blog/2018/12/27/jmap-is-on-the-home-straight/

"But enough about the software, how about the experience! When we
created our brand new Topicbox product, we built directly on top of JMAP
for the email. We also used JMAP-inspired APIs for the rest of the
product experience, so Topicbox’s early users have been on JMAP for over
a year now."

Lastly, Fastmail is now rolling it out - 30% of their userbase is on
JMAP, and all new users are automatically on it. Cyrus also provides
experimental JMAP support in their development snapshots.

So, maybe - hopefully - this might be some reasons to push that
development up some.

if not, its good to know that it is at least still on the roadmap.

Re: How to get original recipient from Postfix when using LMTP?

[MRob via dovecot]

On 2019-05-22 08:18, Tuomo Soini via dovecot wrote:

On Tue, 21 May 2019 18:24:46 +
MRob via dovecot  wrote:


Many people prefer to use LMTP for delivery from postfix for better
efficiency but X-Original-to header support still missing after many
years. One affect of this is need to set
sieve_vacation_dont_check_recipient = yes which violate Sieve
standard and cause auto-replyies sent to messages that should not
happen. Or abandon LMTP. or abandon postfix??

So while feature request is stalled are there any realistic
workarounds?


add to smtp_recipient_restrictions, before permitting email but after
all checks:

check_recipient_access pcre:/etc/postfix/recipient_access.pcre

# cat /etc/postfix/recipient_access.pcre
/(.+)/ prepend X-Original-To: $1


Warning, do not do this unless you don't mind recipients of 
multi-recipient emails to see a list of everyone who got a copy of the 
email message, including BCC recipients. This isn't a good solution if 
you want to respect user privacy.

Re: How to get original recipient from Postfix when using LMTP?

[Tanstaafl via dovecot]

On Wed May 22 2019 13:34:42 GMT-0400 (Eastern Standard Time), MRob via
dovecot  wrote:
> On 2019-05-22 08:18, Tuomo Soini via dovecot wrote:
>> On Tue, 21 May 2019 18:24:46 +
>> MRob via dovecot  wrote:
>>
>>> Many people prefer to use LMTP for delivery from postfix for better
>>> efficiency but X-Original-to header support still missing after many
>>> years. One affect of this is need to set
>>> sieve_vacation_dont_check_recipient = yes which violate Sieve
>>> standard and cause auto-replyies sent to messages that should not
>>> happen. Or abandon LMTP. or abandon postfix??
>>>
>>> So while feature request is stalled are there any realistic
>>> workarounds?
>>
>> add to smtp_recipient_restrictions, before permitting email but after
>> all checks:
>>
>> check_recipient_access pcre:/etc/postfix/recipient_access.pcre
>>
>> # cat /etc/postfix/recipient_access.pcre
>> /(.+)/ prepend X-Original-To: $1
> 
> Warning, do not do this unless you don't mind recipients of 
> multi-recipient emails to see a list of everyone who got a copy of the 
> email message, including BCC recipients. This isn't a good solution if 
> you want to respect user privacy.

Bummer, I was actually glad to see a way to get the x-original-to when
using LMTP.

Guess I'm stuck with the LDA until this gets implemented.

Re: JMAP support in Dovecot

[@lbutlr via dovecot]

On 22 May 2019, at 07:42, Tanstaafl via dovecot  wrote:
> Thanks Aki - no pressing need because of the old chicken/egg problem I
> guess...

I have to say, looking at JAMP it looks very interesting. I'd love to throw a 
JAMP webmail implementation up and play with it.

-- 
"I can't marry her; she's my friend!"

Re: Dict issue with PostgreSQL for last_login plugin (duplicate key)

[John Fawcett via dovecot]

On 21/05/2019 15:45, mabi via dovecot wrote:
> ‐‐‐ Original Message ‐‐‐
> On Monday, May 20, 2019 12:37 AM, John Fawcett via dovecot
>  wrote:
>
>> So looking into this with a postgresql databse to work with: the
>> above query does not work. You have to specify either the column name
>> or the constraint name that you expect to be violated in order for
>> the update to take place.
>>
>> With a map like this one you're using
>>
>> |map { pattern = shared/last-login/$user/$domain table = last_login
>> value_field = last_login value_type = uint fields { username = $user
>> domain = $domain } }|
>>
>> there's no field name that is obviously the primary key. I've
>> reworked the patch to use the postgres default primary key constraint
>> name (tablename_pkey).
>>
> So as you mention the new query you adapted which includes the primary
> key works, I tested it manually against PostgreSQL 10.5.
>>
>> The attached fix should work in that case, although I feel it's not
>> general enough.
>>
> Unfortunately my compiling skills are quite poor and I did not manage
> to patch and recompile Dovecot on OpenBSD.
>
> Do you think your patch will make it into the Dovecot code?
>
I feel confident that the patch works as the query has been manually
verified and the code change is not complex to validate.

The last_login plugin does not work at the moment with PostgreSql and
probably does not work with Sqlite, given that the only logic that tries
an update when insert fails seems to be a MySQL specific extension to
standard Sql. So I think that it's clear that support for PostgreSql and
Sqlite  needs to be implemented. The same issue likely exist in other
plugins too, for example expire.

My doubts are around the right solution to adopt. Initially I thought
that there was a PostgreSql syntax similar to MySQL which could be
easily added to the code, but closer inspection shows that the
PostgreSql syntax requires specification of either a constraint name or
the index column(s) for the primary/unique keys.

Constraint names are nowhere specified in the dictionary map syntax and
it's not possible either to identify with 100% certainty the primary key
column(s).

The solution I adopted in the latest version of the patch is to use the
default primary key constraint name derived from the table name, but
that won't help if people define custom constraint names. That may be an
unlikely scenario so the fix is certainly better than AS-IS. However it
is not perfect and added to that is the fact that the PostgreSql
extension is available only from 9.5.

I have no issues to submit the patch officially, as long as Dovecot
developers agree. However it may be worthwhile reflecting on a more
structural change

1) logic which always tries to update and falls back to insert if the
update fails (or viceversa) for all sql dictionaries.

2) updates to the map syntax so that either the constraint name or
primary key columns can be specified.

Ideas are welcome.

John

more generic approach as for userdb? (was: Dict issue with PostgreSQL for last_login plugin (duplicate key))

[Steffen Kaiser via dovecot]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Wed, 22 May 2019, John Fawcett via dovecot wrote:


an update when insert fails seems to be a MySQL specific extension to
standard Sql. So I think that it's clear that support for PostgreSql and
Sqlite  needs to be implemented. The same issue likely exist in other
plugins too, for example expire.

My doubts are around the right solution to adopt. Initially I thought
that there was a PostgreSql syntax similar to MySQL which could be
easily added to the code, but closer inspection shows that the
PostgreSql syntax requires specification of either a constraint name or
the index column(s) for the primary/unique keys.


You mean the "target" in ON CONFLICT target action, right?
http://www.postgresqltutorial.com/postgresql-upsert/


Constraint names are nowhere specified in the dictionary map syntax and
it's not possible either to identify with 100% certainty the primary key
column(s).


One could dive into Postgres-specifics to get it, but there are other 
SQLs, too; the quota plugin advertises to use TRIGGERs to turn an INSERT 
into an UPDATE silently, which is no general approach either.

https://wiki2.dovecot.org/Quota/Dict


1) logic which always tries to update and falls back to insert if the
update fails (or viceversa) for all sql dictionaries.

2) updates to the map syntax so that either the constraint name or
primary key columns can be specified.

Ideas are welcome.


Maybe, one should drop the automatic at all and let the user specify the 
commands manually like with the userdb/passwd. Hence, the generic SQL 
preparation code is already present. There could/should/would be 
documented lots of "best practice" settings for various backends.


In fact, this approach would better fit into the open and more "general" 
base idea Dovecot uses in other places, IMHO.


Kind regards,

- -- 
Steffen Kaiser

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iQEVAwUBXOY0bsQnQQNheMxiAQKNOQgAmRzNVJTNn3XpHBBGnZOtZ5Ku9Cp9UZIY
70HukeDKdR6rg7XNFGhwTDGa30QRGABByoospMHLAIabZ7j9WFaajAKI01roXotc
skD+T8orvpk7BH/2+f2v5f67xa3GU6LJE330yZJubFb87NFq4otdtXGjhPjCf16j
/wREiuSi0CqDTMtSOXjHXtViI9EL/e+CoJtEgK+gaXINCdCP7Cb2OEjtXHpItuqm
tUAQoh418wWfVt6k6NgpDVX/hD+RyRfxKI4dste0VJZ9OEhH1mpPGaRB/BIkhEh4
OJ18upVhIXbJPDyAPofSB1YGDkPl/HlChmh+QuOpVm9rolmt9SyZQg==
=unPo
-END PGP SIGNATURE-

Re: more generic approach as for userdb? (was: Dict issue with PostgreSQL for last_login plugin (duplicate key))

[Aki Tuomi via dovecot]

> Maybe, one should drop the automatic at all and let the user specify
> the commands manually like with the userdb/passwd. Hence, the generic
> SQL preparation code is already present. There could/should/would be
> documented lots of "best practice" settings for various backends.
>
> In fact, this approach would better fit into the open and more
> "general" base idea Dovecot uses in other places, IMHO.
>
> Kind regards,
>
> -- Steffen Kaiser


Hi!

You can write completely custom last_login plugin by using mail-lua
plugin, by having functions

mail_user_created(user)

and

mail_user_deinit(user)

in your Lua script.

This of course requires v2.3.4 or later.

Aki




signature.asc
Description: OpenPGP digital signature