Dovecot vpopmail-auth driver removal. Migrating to the SQL driver

January 18, 2021 Roberto Puzzanghera20 comments

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

  • 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

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" dbtable 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. 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.

Creating the table

Open your mysql prompt and run this query to create the aliasdomains table

USE vpopmail;
CREATE TABLE `aliasdomains` (
`alias` varchar(100) NOT NULL,
`domain` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `aliasdomains`
ADD PRIMARY KEY (`alias`);

Patching vpopmail

  • Patch used
  • Script installed by the patch (can be used autonomously to manually add/delete records)

Download the patch, recompile and reinstall vpopmail

cd /path/to/vpopmail/source
wget https://notes.sagredo.eu/files/hacks/qmail/patches/vpopmail/vpopmail-5.4.33_sql-aliasdomains.patch-2021.01.18
patch -p1 < vpopmail-5.4.33_sql-aliasdomains.patch-2021.01.18
./configure <your_options_here>
make
make install-strip

The patch installs the following shell scriptlet, which is called by vaddaliasdomain and vdeldomain to manage the aliasdomain dbrecord. If you installed vpopmail in a directory different from the default one you have to adjust the config in the very first lines.

#!/bin/bash 
# 
# v. 2021.01.17 
# by Roberto Puzzanghera 
# More info here https://notes.sagredo.eu/en/qmail-notes-185/dovecot-vpopmail-auth-driver-removal-241.html 
# 
# Records/delete the aliasdomains in mysql 
# This gets dovecot's sql auth driver working with vpopmail's aliasdomains as well 
# 

# Config and executables path 
VPOPMAIL_MYSQL_CONFIG="/home/vpopmail/etc/vpopmail.mysql" 

# Extract mysql params 
HOST=$(sed -n "/#/! s/^\(.*\)|.*|.*|.*|.*/\1/p" $VPOPMAIL_MYSQL_CONFIG) 
PORT=$(sed -n "/#/! s/^.*|\(.*\)|.*|.*|.*/\1/p" $VPOPMAIL_MYSQL_CONFIG) 
USER=$(sed -n "/#/! s/^.*|.*|\(.*\)|.*|.*/\1/p" $VPOPMAIL_MYSQL_CONFIG) 
 PWD=$(sed -n "/#/! s/^.*|.*|.*|\(.*\)|.*/\1/p" $VPOPMAIL_MYSQL_CONFIG) 
  DB=$(sed -n "/#/! s/^.*|.*|.*|.*|\(.*\)/\1/p" $VPOPMAIL_MYSQL_CONFIG) 


function exec_query() { 
       mysql -h $HOST -P $PORT -u $USER -p$PWD -N -A < /tmp/query.sql 
} 

function create() { 
       # Output the query to a file that we want to run 
       cat << EOF > /tmp/query.sql; 
       USE $DB; 
       INSERT INTO aliasdomains (domain,alias) VALUES ("$1","$2"); 
EOF 
       # Execute the query 
       exec_query 
} 

function delete() { 
       # Output the query to a file that we want to run 
       cat << EOF > /tmp/query.sql; 
       USE $DB; 
       DELETE FROM aliasdomains WHERE alias="$1"; 
EOF 
       # Execute the query 
       exec_query 
} 


if [ "$2" = 'delete' ]; then 

   # Delete mysql record 
   delete $1 

elif [ "$2" != '' ]; then 

   # Create mysql record 
   create $1 $2 

elif [ "$1" = '' ] || [ "$1" = 'help' ]; then 

   # Usage 
   echo 
   echo "Usage" 
   echo 
   echo "Creating an aliasdomain:" 
   echo "$0 real_domain alias_domain" 
   echo 
   echo "Deleting an aliasdomain:" 
   echo "$0 alias_domain delete"
   echo 
fi 

exit 0

From now on, when you create an aliasdomain, vpopmail will save the pair alias/domain in the aliasdomains dbtable. This will allow dovecot to do the auth both with real and alias domains:

MariaDB [vpopmail]> SELECT * FROM aliasdomains; 
+----------------------+----------------------+ 
| alias                | domain               | 
+----------------------+----------------------+ 
| alias.net            | domain.net           | 
+----------------------+----------------------+

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 using the script above  in this way:

valiasdomain domain.net alias.net

You can use the same script to delete records in the following way

valiasdomain alias.net delete

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

# If you wish to avoid two SQL lookups (passdb + userdb), you can use 
# userdb prefetch instead of userdb sql in dovecot.conf. In that case you'll 
# also have to return userdb fields in password_query prefixed with "userdb_" 
# string. For example: 
#password_query = \ 
#  SELECT userid AS user, password, \ 
#    home AS userdb_home, uid AS userdb_uid, gid AS userdb_gid \ 
#  FROM users WHERE userid = '%u' 

# [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.

Comments

Support for aliasdomains added!

Finally I found the time to write a wrapper for vaddaliasdomain and adjust the dovecot's sql query. Please test and let me know.

Have fun!

Reply | Permalink

Support for aliasdomains added!

Now the script has been replaced by a patch, so that vpopmail will do the new sql stuff transparently!

Reply | Permalink

Convert vpopmail cdb backend to use postgres for dovecot

I did a little research and you can easily convert vpopmail cdb backend to an sql one. Since I prefer postgres, this is what is needed. YMMW.

Start by installing postgres and add the table

createdb --username=postgres --owner=postgres vpopmail

If you have vpopmail re-compiled with postgres backend, this already should be enough to just create a new domain in there

vadddomain foo.bar postmasterpass

to create all the needed tables. Now it's time to get all the existing user accounts in there. We run the script (NOTE: For this export to work, vpopmail needs to be installed with cdb as backend, of course.)

#!/bin/sh

[ `id -u` = 0 ] || printf "Must run as root" || exit 1

alldomains=`/usr/local/vpopmail/bin/vdominfo | grep ^domain: | grep -v 'alias of' | cut -d ' ' -f 2-`

for domain in ${alldomains}; do
  domdir=`/usr/local/vpopmail/bin/vdominfo -d $a | head -n 1`

  if [ -r "${domdir}/.dir-control" ]; then
    dircontrol=`cat "${domdir}/.dir-control" | xargs | tr ' ' ','`
    printf "INSERT INTO dir_control (cur_users, level_cur, level_max, level_start0, level_start1, level_start2, level_end0, level_end1, level_end2, level_mod0, level_mod1, level_mod2, level_index0, level_index1, level_index2) VALUES (%s);\n" "${dircontrol}"
  fi

  /usr/local/vpopmail/bin/vuserinfo -D $domain -n -c -p -d | paste - - - - | while IFS=`printf '\t'` read user password comment dir; do

    printf "INSERT INTO vpopmail (pw_name, pw_domain, pw_passwd, pw_uid, pw_gid, pw_gecos, pw_dir, pw_shell) VALUES ( '%s', '%s', '%s', '%s', '%s', '%s', '%s', 'NOQUOTA');\n" "${user}" "${domain}" "${password}" 89 89 "${comment}" "${dir}"

  done
done

That produces an sql script that can be imported to postgres like

  psql -U postgres -d vpopmail -f 

Finally, as outlined in https://wiki2.dovecot.org/HowTo/DovecotPostgresql, we need to configure dovecot to use the new sql backend. To use the table format of vpopmail, the sql.conf must like something like this:

driver = pgsql
connect = host=localhost dbname=vpopmail user=postgres
default_pass_scheme = CRYPT
password_query = SELECT pw_name AS user, pw_passwd AS password, pw_dir
AS userdb_home, pw_uid AS userdb_uid, pw_gid AS userdb_gid FROM vpopmail
WHERE pw_name = '%n' AND pw_domain = '%d'
#user_query = pw_dir AS home, pw_uid AS uid, pw_gid AS gid FROM vpopmail WHERE pw_name = '%n' AND pw_domain = '%d'

The only problem I found is that alias domains don't work anymore for dovecot auth, you always need to auth against the main domain.

Reply | Permalink

Convert vpopmail cdb backend to use postgres for dovecot

Thank you, appreciated.

Concerning the aliasdomain issue, if one adds a table which links the domain to its aliases, and the sql query is modified in order to check both domain and aliases, the auth would be validated... I hope that someone can find the time to write that sql soon or later...

Reply | Permalink

Convert vpopmail cdb backend to use postgres for dovecot

Modifying the SQL request is not really hard, the hard part is convincing vpopmail to modify the extra table in the database everytime an aliasdomain is added or existing ones modified.

Currently vpopmail only changes /var/qmail/control/virtualdomains   :/

Reply | Permalink

Convert vpopmail cdb backend to use postgres for dovecot

It will be sufficent to write a vaddaliasdomain's wrapper to add a record on the aliasdomains' table and eventually create that table first. In this case we don't even have to patch vpopmail.

Personally it wouldn't be a simple task to do the auth in one single sql query as it's required...

Reply | Permalink

Convert vpopmail cdb backend to use postgres for dovecot

Supposing that we have a new table 'aliasdomains' with the couples domain/alias, this is an example of the new queries

/*
In this example I assume
%d = alias.com -> test.com (real domain)
%n = postmaster
*/

/* 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='alias.com'
WHERE
vpopmail.pw_name = 'postmaster'
AND
(vpopmail.pw_domain = 'alias.com' OR vpopmail.pw_domain = aliasdomains.domain);

/* 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='alias.com'
WHERE
vpopmail.pw_name = 'postmaster'
AND
(vpopmail.pw_domain = 'alias.com' OR vpopmail.pw_domain = aliasdomains.domain)
AND
('%a'!='995' or !(vpopmail.pw_gid & 2))
AND
('%r'!='[WEBMAIL-IP]' or !(pw_gid & 4))
AND
('%r'='[WEBMAIL-IP]' or '%a'!='993' or !(vpopmail.pw_gid & 8))

It will be a minor task to write up a wrapper of addaliasdomain which populates the 'aliasdomains' table.

Reply | Permalink

The passwd-file driver can replace the vpopmail one

 Hello,

I use vpopmail and was very, very disappointed by the dovecot team's decision to remove the vpopmail auth driver. The words I have for them would get me banned on the spot from any community.

However, with a bit of doc reading, I was able to migrate to another scheme quite easily, and almost painlessly. The backend you want is the passwd-file one.

- Deactivate the vpopmail backend in your /etc/dovecot/10-auth.conf

- Activate the passwd-file backend in your /etc/dovecot/10-auth.conf. The default extension file is probably named auth-passwdfile.conf.ext

- Edit your auth-passwdfile.conf.ext:

passdb {
driver = passwd-file
args = username_format=%n /home/vpopmail/domains/%d/vpasswd
}

userdb {
driver = passwd-file
args = username_format=%n /home/vpopmail/domains/%d/vpasswd
}

Replace /home/vpopmail/domains with the directory where you actually store your domains. :-)

This assumes you're using the default vpasswd settings, with a CRYPT passwd scheme in the vpasswd file. If you're using something else, you may have a bit more configuration work to do in that auth-passwdfile.conf.ext file.

- And now, the small extra pain: assuming all your vpopmail domains are handled by one vchkpw user, you need to change all the uids and gids in all your vpasswd files to the uid/gid of the vchkpwd user (typically 89:89). By default vpopmail puts 1:0 in the uid:gid fields; this would make dovecot attempt to change to uid 1 in order to read mail belonging to user vchkpwd, and would not work. Changing the uid/gid in the vpasswd files allows dovecot to access all the mail. You don't need to recompile vpasswd to vpasswd.cdb: dovecot will only read the text vpasswd files, never the cdb.

- Bear in mind you need to perform that uid/gid change every time you add/modify a user in a vpasswd file. Every time you run vadduser or equivalent, you need to go behind vadduser to make sure the uid and gid fields are correct, else dovecot won't be able to access mail for the new user. I haven't checked whether vmoduser modifies the uid/gid; if it does, you'll also need to clean up after it every time you invoke it.

- There you go. With a small amount of effort, you can keep using vpopmail together with dovecot, and in particular, you don't need to switch to a SQL backend and pull an unnecessary kitchen sink.

Reply | Permalink

The passwd-file driver can replace the vpopmail one

Thank's a lot for the contribution, it's very much appreciated. I'll check it out.

Reply | Permalink

The passwd-file driver can replace the vpopmail one

Note that the steps above worked for me because I only use very simple backends with vpopmail: the text vpasswd file (which is the one passwd-file piggybacks on) and the cdb one (unused by dovecot). If your vpopmail configuration is more complex, and you store vuser data into other databases (via SQL, for instance), then you'll have to perform more steps.

However, the idea should remain the same: look at the auth schemes vpopmail is using, and configure dovecot to use the same ones, but accessing the vpopmail data directly instead of relying on glue code that was implemented in the "vpopmail" auth driver. If you're lucky, there will be an auth driver for dovecot that corresponds to how you're using vpopmail, and you will be able to plug it directly into your vpopmail backends. If you're unlucky, however, you will have to program glue yourself, and use something like dovecot-auth-lua to make dovecot use your own programs that access the vpopmail data.

Reply | Permalink

The passwd-file driver can replace the vpopmail one

Thank you. I suppose that the passwd-file works well also with valiasdomains...

What about the doveadm iteration feature needed to expunge the mailboxes? Does it work with passwd-file as well?

Personally I'll stick with sql, because I already have a working setup. The problem is for people who have domain aliases which can't be easily transported in the sql database.

Reply | Permalink

The passwd-file driver can replace the vpopmail one

I don't know about the doveadm expunge feature, but if it's an admin tool it shouldn't need to authenticate like a remote user accessing their mail via IMAP would, so I don't see why it should be impacted by auth backends.

Reply | Permalink

The passwd-file driver can replace the vpopmail one

It is a feature where dovecot has to iterate among all the accounts in order to purge their Trash and Junk folders. The old vpopmail driver didn't have that feature and this is the reason why I switched to sql.

Reply | Permalink

Using version 2.3.11.3 of dovecot, and vpopmail auth still works !

Hi

I'm using version 2.3.11.3 of dovecot and vpopmail auth still working

Reply | Permalink

Dovecot is removing support for vpopmail

Hello again, Roberto. I wanted to share this link with folks to the dovecot mailing list. It appears that the plan is to remove support for vpopmail in an upcoming version of dovecot. 

https://dovecot.org/pipermail/dovecot/2020-March/118416.html

The thread is long, and has a many folks complaining about removing features on a point release. My guess is that it will not cause a problem for your suggested mysql install, but it will cause a problem for those who are using vpopmail directly. Your instructions here may need a slight edit about installing without mysql.

Reply | Permalink

Dovecot is removing support for vpopmail

I'm also wondering which reasons prevent the migration to the sql driver, apart from the one concerning the alias domains already pointed out by Alexandre below...

Reply | Permalink

Dovecot is removing support for vpopmail

Hi Jim,

I'll not fail to post something about the topic if I'll find the time, but I'm confident that some hint may also come from one of you who managed to find a solution with the LUA driver or whatelse. It can be enough to post some raw info about the new configuration, to use as a starting point for me to build the new how to.

Reply | Permalink

without vpopmail driver, what about domain aliases?

dovecot will ended vpopmail driver! oh no!

and what about domain aliases?

the entries of a domain alias are inside /var/qmail/control and /var/qmail/users and if you only use sql authentication in dovecot, it cannot authenticate any user using the alias domain, only by the main domain

currently, I keep the sql driver first to authenticate users and if it fail, dovecot tries the vpopmail driver, to auth the aliases too.

without the vpopmail driver, how would an email@aliasdomain be authenticated in pop3/imap?!

has anyone thought about it?

Reply | Permalink

without vpopmail driver, what about domain aliases?

Even if not much elegant one work around could be the following:

- build a db_table to save the couples aliasdomain / realdomain

- save a new record when creating an alias by means of a php script

- modify the sql dovecot/auth accordingly to allow both realdomain and aliasdomain

PS as you may have read, finally the dovecot team announced that they will not break vpopmail until the next major release (2.4)

PS2: sorry, the delay will be just for xz, not vpopmail. Look here

Reply | Permalink

without vpopmail driver, what about domain aliases?

indeed!

I also read about getting around via vchkpw that follows the checkpassword driver pattern, or even using the LUA driver

I will do some tests before dovecot abandon the vpopmail drivers and I will post here if I find a working solution.

Reply | Permalink