<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Aki and Remo,<br>
</p>
<p>switch from vpopmail driver to SQL driver (if you are using
vpopmail with mysql as backend) is very simple.</p>
<p>First you need to setup the right query for vpopmail database:</p>
<p># cat /etc/dovecot/dovecot-sql.conf.ext<br>
<br>
### Vpopmail<br>
driver = mysql<br>
connect = host=192.168.1.2 dbname=vpopmail user=vpopmail
password=Vp0pM4iL<br>
default_pass_scheme = MD5-CRYPT<br>
<br>
### Query to get a list of all usernames.<br>
iterate_query = SELECT CONCAT(pw_name, '@', pw_domain) AS user
FROM vpopmail<br>
<br>
### user_query for vpopmail<br>
user_query = SELECT pw_dir AS home, 89 AS uid, 89 AS gid,
concat('*:backend=', pw_shell) AS quota_rule FROM vpopmail WHERE
pw_name = '%n' AND pw_domain = '%d'<br>
<br>
### password_query for vpopmail (not used)<br>
#password_query = SELECT CONCAT(pw_name, '@', pw_domain) AS user,
pw_passwd AS password FROM vpopmail WHERE pw_name = '%n' AND
pw_domain = '%d'<br>
<br>
### password_query for vpopmail with prefetch<br>
password_query = SELECT CONCAT(pw_name, '@', pw_domain) AS user,
pw_passwd AS password, concat('*:backend=', pw_shell) as
userdb_quota_rule, 89 AS userdb_uid, 89 AS userdb_gid, pw_dir AS
userdb_home FROM vpopmail WHERE pw_name = '%n' AND pw_domain =
'%d'<br>
</p>
<p>after to setup auth-sql like this:</p>
<p># cat /etc/dovecot/conf.d/auth-sql.conf.ext<br>
<br>
passdb {<br>
driver = sql<br>
args = /etc/dovecot/dovecot-sql.conf.ext<br>
}<br>
<br>
userdb {<br>
driver = prefetch<br>
}<br>
<br>
userdb {<br>
driver = sql<br>
args = /etc/dovecot/dovecot-sql.conf.ext<br>
}</p>
<p>and after to swith from auth-vpopmail to auth-sql from
/etc/dovecot/conf.d/10-auth.conf</p>
<p>You can also setup Dovecot in order to apply vpopmail
POP/IMAP/SMTP/Webmail gids/domains limits for example with a
password query more complicated like this:</p>
<p>password_query = SELECT CONCAT(pw_name, '@', pw_domain) AS user,
pw_passwd AS password, concat('*:backend=', pw_shell) as
userdb_quota_rule, 89 AS userdb_uid, 89 AS userdb_gid, pw_dir AS
userdb_home FROM vpopmail LEFT JOIN limits ON vpopmail.pw_domain =
limits.domain WHERE pw_name = '%n' AND pw_domain='%d' AND (( '%s'
= 'smtp' AND (pw_gid & 2048)<>2048 AND
COALESCE(disable_smtp,0)!=1) OR ('%s' = 'pop3' AND (pw_gid &
2)<>2 AND COALESCE(disable_pop,0) != 1 ) OR ('%s' = 'imap'
AND ('%r'='192.168.100.1' OR '%r'='192.168.100.2') AND (pw_gid
& 4)<>4 AND COALESCE(disable_webmail,0)!=1) OR ('%s' =
'imap' AND ('%r'!='192.168.100.1' AND '%r'!='192.168.100.2') AND
(pw_gid & 8)<>8 AND COALESCE(disable_imap,0)!=1)); <br>
</p>
<p>where 192.168.100.1 and 192.168.100.2 are the IPs of your webmail
servers.</p>
<p>For a more beautifull setup and to show in dovecot logs "user
disabled" instead of "password error" you can put this
password_query under the dovecot auth-deny.conf.ext
configurations.<br>
</p>
<p>If you need more help or info I can help you.</p>
<p>Ciao<br>
</p>
<div class="moz-cite-prefix">Il 18/03/20 18:26, Aki Tuomi ha
scritto:<br>
</div>
<blockquote type="cite"
cite="mid:1640692265.1445.1584552415903@appsuite-dev-gw1.open-xchange.com">
<pre class="moz-quote-pre" wrap="">Hi!
I understand that it is not trivial to move away from vpopmail and does require changing a working system. But then again, one should be able to configure MySQL passdb/userdb with vpopmail schema.
I am not familiar with vpopmail but if someone comes with instructions we can polish them a bit (if necessary) and publish them as howto on doc.dovecot.org.
Aki
</pre>
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">On 18/03/2020 17:52 Remo Mattei <a class="moz-txt-link-rfc2396E" href="mailto:remo@rm.ht"><remo@rm.ht></a> wrote:
So I am on of the many users with qmail, and using vpopmail auth, I guess chatting with some other guys in the other mailing list we will convert to mysql driver but this is a lot of work for many people.
I do understand dropping things out but a valid solutions needs to be proposed.
Remo</pre>
</blockquote>
</blockquote>
<pre class="moz-signature" cols="72">--
Alessio Cecchi
Postmaster @ <a class="moz-txt-link-freetext" href="http://www.qboxmail.it">http://www.qboxmail.it</a>
<a class="moz-txt-link-freetext" href="https://www.linkedin.com/in/alessice">https://www.linkedin.com/in/alessice</a></pre>
</body>
</html>