[Dovecot] auth socket goes away on reload

[David Jonas]

On 6/3/10 , Jun 3, 11:17 AM, William Blunn wrote:
> On 03/06/2010 19:08, David Jonas wrote:
>> On 6/3/10 , Jun 3, 10:16 AM, William Blunn wrote:
>>   
>>> On 03/06/2010 17:35, David Jonas wrote:
>>>     
>>>> We're using the SQLite backend for authentication of Postfix SASL.
>>>> When
>>>> the db is replaced we HUP dovecot to close and reopen its connection.
>>>> During this time it appears the socket file is removed and Postfix
>>>> rejects the authentication attempt. From the logs:
>>>>
>>>> Jun  3 00:23:02 xxx dovecot: dovecot: SIGHUP received - reloading
>>>> configuration
>>>> Jun  3 00:23:02 xxx postfix/smtpd[14746]: warning: SASL: Connect to
>>>> private/auth failed: Connection refused
>>>> Jun  3 00:23:02 xxx postfix/smtpd[14746]: warning:
>>>> unknown[dd.dd.dd.dd]:
>>>> SASL LOGIN authentication failed:
>>>> Jun  3 00:23:02 xxx postfix/smtpd[14746]: NOQUEUE: reject: RCPT from
>>>> unknown[dd.dd.dd.dd]: 554 5.7.1<user at example.com>: Relay access
>>>> denied;
>>>> from=<user2 at example.com>   to=<user at example.com>   proto=ESMTP
>>>> helo=<localhost.localdomain>
>>>>
>>>> Jun  3 00:23:02 xxx postfix/smtpd[14930]: warning:
>>>> unknown[dd.dd.dd.dd]:
>>>> SASL LOGIN authentication failed: Connection lost to authentication
>>>> server
>>>> Jun  3 00:23:02 xxx postfix/smtpd[14930]: lost connection after AUTH
>>>> from unknown[dd.dd.dd.dd]
>>>>
>>>> Is there an obvious way around this? I know I could somehow merge the
>>>> changes into the running sqlite db but that undermines the
>>>> simplicity of
>>>> the design I have. Maybe a patch to reopen the db if it's replaced? Or
>>>> perhaps I should just switch to a different db format -- that's
>>>> probably
>>>> the quickest/easiest solution. Any other ideas? There are about 20k
>>>> entries to deal with.
>>>>        
>>> It sounds like your updates arrive in the shape of entire-table
>>> updates.
>>>
>>> That is no problem. You can easily apply entire-table updates to the
>>> database without having to re-create the SQLite database file, and
>>> without having to tell Dovecot.
>>>
>>> Just create a new table (with a different name) inside the SQLite
>>> database file, with the new content, then snap it into place using a
>>> pair of table renames inside a transaction; then delete the old table.
>>>
>>> That way you don't need to re-create the database file or HUP Dovecot,
>>> and Dovecot will only ever see the old data or the new data.
>>>      
>> That sounds reasonable, not sure why I didn't think of it! Thanks.
>>    
>
> If you are going to keep the SQLite database around, you might want to
> look at vacuuming it periodically using either VACUUM or the
> auto_vacuum PRAGMA depending on what fits your context best.
>
> http://sqlite.org/lang_vacuum.html
> http://sqlite.org/pragma.html#pragma_auto_vacuum
>
> Bill
Here is what I came up with. Seems to work pretty well.

#!/bin/sh

TMP=/home/config/tmp
DATA=/home/config/data

rsync -t -e ssh xxx at xxx:mail/accounts.sqlite $TMP

echo "
ATTACH '$TMP/accounts.sqlite' AS db2;
BEGIN TRANSACTION;
DROP TABLE IF EXISTS mytemp;
CREATE TABLE mytemp (
 'user' varchar(128) COLLATE NOCASE,
 'domain' varchar(128) COLLATE NOCASE,
 'password' varchar(64) default NULL
);
INSERT INTO mytemp (user,domain,password) SELECT user,domain,password
FROM db2.accounts;
DROP TABLE accounts;
ALTER TABLE mytemp RENAME TO accounts;
COMMIT TRANSACTION;
" | sqlite3 $DATA/accounts.sqlite


No vacuuming needed really. It doubles the size of the db but only once.
I didn't see a speed improvement on the consecutive runs but I also
didn't see any additional latency. Looks good so far.

Thanks for your help!