SQL-AUTH driver replace for VPOPMAIL-AUTH

[Pablo Murillo]

The query, using SLQ for limits too

password_query = \

   SELECT \
       CONCAT(vpopmail.pw_name,'@','%L{domain_first}') AS user, \
       vpopmail.pw_clear_passwd AS password, \
       vpopmail.pw_dir AS userdb_home, \
       89 AS userdb_uid, \
       89 AS userdb_gid, \
       CONCAT('*:bytes=', REPLACE(SUBSTRING_INDEX(vpopmail.pw_shell, 
'S', 1), 'NOQUOTA', '0')) AS quota_rule \
     FROM \
       %L{domain_last} AS vpopmail \
     LEFT JOIN \
       aliasdomains ON aliasdomains.alias='%L{domain_first}' \
     LEFT JOIN \
       limits ON limits.domain = '%L{domain_first}' \
     WHERE \
       vpopmail.pw_name='%n' \
     AND \
      (('[WEBMAIL-IP]'!='%l' AND limits.disable_imap = 0) \
     OR  \
       ('[WEBMAIL-IP]'='%l' AND limits.disable_webmail = 0))


IMPORTANT !!!

The user mas be sent as :

USER @ DOMAIN @ DOMAIN_WITH_UNDERSCORES

Example:

info at pablomurillo.com.ar@pablomurillo_com_ar

If you use roundcube, must be modified too,  rcube_imap.php on connect 
function
$user .= "@" . preg_replace(array("/\./","/\-/"), "_", $host);

The table aliasdomains and the changes on vpopmail could be found on
https://notes.sagredo.eu/en/qmail-notes-185/dovecot-vpopmail-auth-driver-removal-migrating-to-the-sql-driver-241.html



On 2/10/2021 3:02 PM, Eric Broch wrote:
> Pablo,
>
> Can you share your dovecot-sql.conf.ext file for 'one table for each 
> domain'.
>
> Eric
>
> On 2/10/2021 9:24 AM, Pablo Murillo wrote:
>> Hi Aki
>>
>>
>> Don't work, but you give me a new idea
>> I set default_pass_scheme = PLAIN on dovecot-sql.conf.ext anda I send 
>> the pass encrypted and now is working !
>>
>> On 2/10/2021 3:34 AM, Aki Tuomi wrote:
>>> Return the password with {CRAM-MD5} or {MD5-CRYPT} prefix, then 
>>> dovecot will know the password scheme.
>>>
>>> Aki
>>>
>>>> On 10/02/2021 05:04 Pablo Murillo <info at pablomurillo.com.ar> wrote:
>>>>
>>>>   Hi
>>>>
>>>> I'm made a lot of test trying to get SLQ auth driver work with
>>>> vpopmail/mysql configured with many domains (one table for each 
>>>> domain)
>>>> Finally I made it work, but ... only with PLAIN password
>>>> Is there a way to make SQL auth work with vpopmail/mysql tables with
>>>> encrypted password (CRAM-MD5 / MD5-CRYPT) ?
>>>>
>>>> Thanks
>>>> Pablo Murillo
>>>>
>>>>
>>>>
>>>> -- 
>>>> This email has been checked for viruses by AVG.
>>>> https://www.avg.com
>