Those who are still using the
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).
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
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
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 | +----------------------+----------------------+
vpopmail sql-aliasdomains patch will transparently do the sql stuff when creating/deleting the alias in the usual way by means of the
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.
- 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 program
vsavealiasdomainscan 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 the
vpopmail's C programs and functions.
- Feb 3, 2021
- new patch and script released. Just
configure --enable-sql-aliasdomains(default) and forget. The dbtable will be created the first time you will create an
- 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
Download the patch, recompile and reinstall
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:
for more options.
Modifing the sql auth
# 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
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.