Hi Aki and Remo,

switch from vpopmail driver to SQL driver (if you are using vpopmail with mysql as backend) is very simple.

First you need to setup the right query for vpopmail database:

# cat /etc/dovecot/dovecot-sql.conf.ext

### Vpopmail
driver = mysql
connect = host=192.168.1.2 dbname=vpopmail user=vpopmail password=Vp0pM4iL
default_pass_scheme = MD5-CRYPT

### Query to get a list of all usernames.
iterate_query = SELECT CONCAT(pw_name, '@', pw_domain) AS user FROM vpopmail

### user_query for vpopmail
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'

### password_query for vpopmail (not used)
#password_query = SELECT CONCAT(pw_name, '@', pw_domain) AS user, pw_passwd AS password FROM vpopmail WHERE pw_name = '%n' AND pw_domain = '%d'

### password_query for vpopmail with prefetch
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'

after to setup auth-sql like this:

# cat /etc/dovecot/conf.d/auth-sql.conf.ext

passdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf.ext
}

userdb {
  driver = prefetch
}

userdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf.ext
}

and after to swith from auth-vpopmail to auth-sql from /etc/dovecot/conf.d/10-auth.conf

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:

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));

where 192.168.100.1 and 192.168.100.2 are the IPs of your webmail servers.

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.

If you need more help or info I can help you.

Ciao

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

On 18/03/2020 17:52 Remo Mattei <remo@rm.ht> 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

-- 
Alessio Cecchi
Postmaster @ http://www.qboxmail.it
https://www.linkedin.com/in/alessice