Those who are still using the Dovecot's vpopmail auth driver should consider a migration to the sql driver, as on January 4, 2021 dovecot-2.3.13 was released and the vpopmail auth driver removed (more info here).
Adding vpopmail's aliasdomains to MySQL
As some commentators have already pointed out, switching to dovecot's sql auth can be painful if one have domain aliases. I will show below how to make dovecot aware of the vpopmail's aliasdomains, so that a user who tries to login with a domain alias can pass the authentication.
The idea is to save the pairs alias/domain in a new "aliasdomains" MySQL table and modify the dovecot's sql query in order to select the user's domain from this table in case the domain is an alias or from the vpopmail table otherwise. For example:
MariaDB [vpopmail]> SELECT * FROM aliasdomains; +----------------------+----------------------+ | alias | domain | +----------------------+----------------------+ | alias.net | domain.net | +----------------------+----------------------+
A vpopmail sql-aliasdomains patch will transparently do the sql stuff when creating/deleting the alias in the usual way by means of the vaddaliasdomain/vdeldomain vpopmail's programs.
NB: if you are testing this solution, I would be glad if you give me a feedback by means of a comment below, so that I can speed up its introduction in the installation guide.
Changelog
- February 15, 2021
- fix in the configure file. An autoreconf is needed as I modified the configure.in and Makefile.am files - February 10, 2021
- a C programvsavealiasdomainscan now save all the existing domain aliases to MySQL. It can be useful in case of migrations to the dovecot's sql auth driver. - Feb 5, 2021
- The patch has been improved: the sql-aliasdomains stuff is now done by means of thevpopmail's C programs and functions. - Feb 3, 2021
- new patch and script released. Justconfigure --enable-sql-aliasdomains(default) and forget. The dbtable will be created the first time you will create analiasdomain. - Jan 18, 2021
- now everything is inside a vpopmail patch. The aliasdomain sql records will be created/deleted transparently when using vaddaliasdomain/vdeldomain in the usual way, provided that you have created the aliasdomains dbtable - Jan 17, 2021
- I modified the dovecot's sql query so that a pair real_domain/real_domain is not needed anymore in the dbtable - Jan 13, 2021
- added support for sql aliasdomains
Patching vpopmail
Download the patch, recompile and reinstall vpopmail
cd /path/to/vpopmail/source
wget https://notes.sagredo.eu/files/qmail/patches/vpopmail/roberto_vpopmail-5.4.33.patch
patch -p1 < roberto_vpopmail-5.4.33_sql-aliasdomains.patch
autoreconf -f -i
./configure
--other-options-here \
--enable-auth-module=mysql \
--enable-sql-aliasdomains
make
make install-strip
Be aware that if you already have aliasdomains and want to switch to the dovecot's sql driver, you must populate the database adding a record for each aliasdomain you have.
To do this you can simply delete/create the alias in the usual way or use the vsavealiasdomains program. To save all domain aliases to MySQL just do:
vsavealiasdomains -A
Type
vsavealiasdomains -h
for more options.
Modifing the sql auth
Finally you have to modify the dovecot-sql.conf.ext file as follows (download). Note the changes in the password_query and the userdb_query.
# Database driver: mysql, pgsql, sqlite
driver = mysql
# Database connection string. This is driver-specific setting.
#
# mysql:
# Basic options emulate PostgreSQL option names:
# host, port, user, password, dbname
#
# But also adds some new settings:
# client_flags - See MySQL manual
# connect_timeout - Connect timeout in seconds (default: 5)
# read_timeout - Read timeout in seconds (default: 30)
# write_timeout - Write timeout in seconds (default: 30)
# ssl_ca, ssl_ca_path - Set either one or both to enable SSL
# ssl_cert, ssl_key - For sending client-side certificates to server
# ssl_cipher - Set minimum allowed cipher security (default: HIGH)
# ssl_verify_server_cert - Verify that the name in the server SSL certificate
# matches the host (default: no)
# option_file - Read options from the given file instead of
# the default my.cnf location
# option_group - Read options from the given group (default: client)
#
connect = host=localhost dbname=vpopmail user=vpopmail password=
# Default password scheme.
#
# List of supported schemes is in
# http://wiki2.dovecot.org/Authentication/PasswordSchemes
#
default_pass_scheme = MD5-CRYPT
# passdb query to retrieve the password. It can return fields:
# password - The user's password. This field must be returned.
# user - user@domain from the database. Needed with case-insensitive lookups.
# username and domain - An alternative way to represent the "user" field.
#
# The "user" field is often necessary with case-insensitive lookups to avoid
# e.g. "name" and "nAme" logins creating two different mail directories. If
# your user and domain names are in separate fields, you can return "username"
# and "domain" fields instead of "user".
#
# The query can also return other fields which have a special meaning, see
# http://wiki2.dovecot.org/PasswordDatabase/ExtraFields
#
# Commonly used available substitutions (see http://wiki2.dovecot.org/Variables
# for full list):
# %u = entire user@domain
# %n = user part of user@domain
# %d = domain part of user@domain
#
# Note that these can be used only as input to SQL query. If the query outputs
# any of these substitutions, they're not touched. Otherwise it would be
# difficult to have eg. usernames containing '%' characters.
#
# Example:
# password_query = SELECT userid AS user, pw AS password \
# FROM users WHERE userid = '%u' AND active = 'Y'
#
password_query = SELECT \
CONCAT(vpopmail.pw_name, '@', vpopmail.pw_domain) AS user, \
vpopmail.pw_passwd AS password \
FROM `vpopmail` \
LEFT JOIN aliasdomains ON aliasdomains.alias='%d' \
WHERE \
vpopmail.pw_name='%n' \
AND \
(vpopmail.pw_domain='%d' OR vpopmail.pw_domain=aliasdomains.domain)
# userdb query to retrieve the user information. It can return fields:
# uid - System UID (overrides mail_uid setting)
# gid - System GID (overrides mail_gid setting)
# home - Home directory
# mail - Mail location (overrides mail_location setting)
#
# None of these are strictly required. If you use a single UID and GID, and
# home or mail directory fits to a template string, you could use userdb static
# instead. For a list of all fields that can be returned, see
# http://wiki2.dovecot.org/UserDatabase/ExtraFields
#
# Examples:
# user_query = SELECT home, uid, gid FROM users WHERE userid = '%u'
# user_query = SELECT dir AS home, user AS uid, group AS gid FROM users where userid = '%u'
# user_query = SELECT home, 501 AS uid, 501 AS gid FROM users WHERE userid = '%u'
#
user_query = \
SELECT \
vpopmail.pw_dir AS home, \
89 AS uid, \
89 AS gid, \
CONCAT('*:bytes=', REPLACE(SUBSTRING_INDEX(vpopmail.pw_shell, 'S', 1), 'NOQUOTA', '0')) AS quota_rule \
FROM vpopmail \
LEFT JOIN aliasdomains ON aliasdomains.alias='%d' \
WHERE \
vpopmail.pw_name='%n' \
AND \
(vpopmail.pw_domain='%d' OR vpopmail.pw_domain=aliasdomains.domain) \
AND \
('%a'!='995' OR !(vpopmail.pw_gid & 2)) \
AND \
('%r'!='localhost' OR !(vpopmail.pw_gid & 4)) \
AND \
('%r'='localhost' OR '%a'!='993' OR !(vpopmail.pw_gid & 8))
# [WEBMAIL-IP] is the IP of your webmail web server.
# I'm assuming that the imap connection is only on port 993 and the pop3 connection is on port 955.
# Adjust to your needs
#
# logically this means:
# SELECT user
# WHEN POP is not disabled for that user connecting on port 995 (995 is the pop3s port allowed from remote in my configuration)
# AND WHEN webmail access is not disabled for that user when connecting from [WEBMAIL-IP]
# AND WHEN IMAP is not disabled for that user connecting on port 993 (993 is the imap port allowed from remote
# in my configuration) unless his remote ip the one belonging to the webmail
# Query to get a list of all usernames.
#iterate_query = SELECT username AS user FROM users
iterate_query = SELECT CONCAT(pw_name,'@',pw_domain) AS username FROM `vpopmail`
Migrating your accounts to sql format
This solution requires that your accounts are already in sql format. To convert from cdb to sql format use the vpopmail's vconvert program:
vconvert: usage The first option sets which format to convert FROM, the second option sets which format to convert TO. -e = etc format -c = cdb format -m = sql format -S = set sqwebmail passwords -v = version -d = debug info
If you want to switch to postgres, take a look to the erdgeist's howto here.
