The use of %w in password_query leads to "Failed to expand plugin setting" error

[Filidor Wiese]

Thanks Aki, that sounds like a good solution. So you're suggesting?

password_query = \
 SELECT domain, password, TO_BASE64('%w') AS userdb_plain_pass, \
 domain as userdb_domain, concat('*:storage=', quota) AS userdb_quota_rule, 5000 as userdb_uid, 5000 as userdb_gid, '/home/%d/%n' as userdb_home \
 FROM users WHERE (catch='%n' AND domain='%d')

I'll give it a go when our users are asleep!

Regards,
Filidor

On 14-01-2021 16:34, Aki Tuomi wrote:
>> On 14/01/2021 17:30 Filidor Wiese <fili at fili.nl> wrote:
>>
>>
>> Hi,
>> I'm trying to convert my existing users to a more modern password scheme by following the how-to at https://wiki.dovecot.org/HowTo/ConvertPasswordSchemes.
>> One of the steps is to include the use of '%w' in the password_query, like so:
>>> password_query = \
>>>  SELECT domain, password, '%w' AS userdb_plain_pass, \
>>>  domain as userdb_domain, concat('*:storage=', quota) AS userdb_quota_rule, 5000 as userdb_uid, 5000 as userdb_gid, '/home/%d/%n' as userdb_home \
>>>  FROM users WHERE (catch='%n' AND domain='%d')
>> and also to configure:
>>> userdb {
>>>  driver = prefetch
>>>  }
>> in order to combine the user/password_query into one. Now that all seemed to work well, except for one thing.
>>  When a user has a % sign in their password, the following error occurs:
>>> Error: Failed to expand plugin setting plain_pass = 'Tfew3322gYEp$%5Qjk0': Unknown variable '%Q'
>>>
>> Which indicates that dovecot is trying to expand variables in the password.
>> As a consequence, the login process fails.
>> Is there some way around this behavior?
>> Thanks in advance,
>> Filidor Wiese
>>
> You might get better results with, say, base64 encoding the password. TO_BASE64() should work from 5.6 mysql. You can also try HEX encoding it.
>
> Aki