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

February 10, 2023 by Roberto Puzzanghera 65 comments

Those who are still using the Dovecot's vpopmail auth driver should consider a migration to another backend, as on January 4, 2021 dovecot-2.3.13 was released and the vpopmail auth driver removed (more info here).

I'll show below how to support domain aliases with the sql driver both with all domains in the same vpopmail table and with one table for each domain (--disable-many-domains). You can find how to setup the driver in this page. A short reference to vpopmail's vconvert program is presented toward the bottom of this page, in case one is planning to switch to sql.

If you browse the comments below you'll find some other nice solutions:

Ali Erturk TURKER maintains a patch to dovecot which restores the old vpopmail auth driver to life (dedicated page here)
Tyler Simpkin posted his auth.lua file (enhanced by Rick Richards to work with encrypted passwords)
Laurent Bercot posted a solution based on passwd-file driver
Pablo Murillo improved the sql password_query to work with one table for each domain
erdgeist showed how to convert cdb accounts to postgres

Saving `vpopmail`'s `aliasdomains` to `MySQL`

As some commentators have pointed out, switching to the dovecot's sql auth driver can be painful if one has 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, for example:

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

...and then 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.

I patched vpopmail so that it will transparently do the sql stuff when creating/deleting the alias in the usual way by means of the vaddaliasdomain/vdeldomain vpopmail's programs.

Patching `vpopmail`

Patch used (already included in vpopmail-5.6.x)

Reconfigure, recompile and reinstall vpopmail

autoreconf -f -i
./configure
       --other-options-here \
       --enable-auth-module=mysql \
       --enable-mysql-limits \
       --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 aliases in the usual way or use the vsavealiasdomains program. For example, 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. Note the changes in the password_query.

password_query = \ 
       SELECT \ 
               CONCAT(vpopmail.pw_name, '@', vpopmail.pw_domain) AS user, \ 
               vpopmail.pw_passwd AS password, \ 
               vpopmail.pw_dir AS userdb_home, \ 
               89 AS userdb_uid, \ 
               89 AS userdb_gid, \ 
               CONCAT('*:bytes=', REPLACE(SUBSTRING_INDEX(vpopmail.pw_shell, 'S', 1), 'NOQUOTA', '0')) AS userdb_quota_rule \ 
       FROM `vpopmail` \ 
               LEFT JOIN aliasdomains ON aliasdomains.alias='%d' \ 
               LEFT JOIN limits ON limits.domain = '%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'!='<WEBMAIL-IP>' OR !(vpopmail.pw_gid & 4)) \ 
               AND \ 
               ('%r'='<WEBMAIL-IP>' OR '%a'!='993' OR !(vpopmail.pw_gid & 8)) \ 
               AND \ 
               ('%r'!='<WEBMAIL-IP>' OR COALESCE(disable_webmail,0)!=1) \ 
               AND \ 
               ('%r'='<WEBMAIL-IP>' OR COALESCE(disable_imap,0)!=1)

# <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 995. 
# Adjust to your needs 
# 
# logically this means: 
# 
# ************************** USER LIMITS via vpopmail.pw_gid field 
# 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 
# 
# ************************* DOMAIN LIMITS via limits table 
# AND WHEN WEBMAIL access for the domain is not disabled 
# AND WHEN IMAP access for the domain is not disabled when not connecting from <WEBMAIL-IP>

The user_query is served with the "prefetch" driver. Look at the dovecot page for all the details about it.

`aliasdomains` and `mysql-limits` support for "one table per domain" option

The above solution has to be modified when you save your accounts to one table for each domain (--disable-many-domains). The query turns out to be more advanced and "stored procedures" have to be considered as a valid solution (thanks to Pablo Murillo for sharing his sql example).

I have created a procedure named "dovecot_password_query_disable_many_domains" which does the sql stuff and can be called as follows in your password_query:

password_query = CALL dovecot_password_query_disable_many_domains('%n','%d','127.0.0.1','%r','%a')

Here is the content of the procedure plus some other sql functions (download):

USE vpopmail;

/****************************************************************
  Returns the domain table
 ****************************************************************/
DELIMITER $$
CREATE FUNCTION `get_domain_table`(`d` VARCHAR(100)) RETURNS varchar(100) CHARSET latin1
BEGIN

   DECLARE domain_table varchar(100);
   SET domain_table = dot2underscore(get_real_domain(d));

   RETURN domain_table;

END$$
DELIMITER ;


/****************************************************************
  Replaces dots and "-" with undescores in domain name
 ****************************************************************/
DELIMITER $$
CREATE FUNCTION `dot2underscore`(`d` VARCHAR(100)) RETURNS varchar(100) CHARSET latin1
BEGIN

   RETURN REPLACE(REPLACE(d, ".", "_"), "-", "_");

END$$
DELIMITER ;


/*******************************************************************
  Returns the real domain given an alias domain or the domain name
  if it's not an alias.
 *******************************************************************/
DELIMITER $$
CREATE FUNCTION `get_real_domain`(`d` VARCHAR(100)) RETURNS varchar(100) CHARSET latin1
BEGIN
   DECLARE real_domain varchar(100);

   IF NOT
      (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=dot2underscore(d))
   IS NULL THEN
      SET real_domain = d;

   ELSEIF NOT
      (SELECT 1 FROM aliasdomains WHERE alias=d)
   IS NULL THEN
      SELECT domain INTO real_domain FROM aliasdomains WHERE alias=d;

   ELSE
   SET real_domain = NULL;

   END IF;

   RETURN real_domain;

END$$
DELIMITER ;


/**************************************************************************
  Stored procedure for password_query in case of "disabled many domains"
 **************************************************************************/
DELIMITER $$
CREATE PROCEDURE `dovecot_password_query_disable_many_domains`(IN `name` VARCHAR(255), IN `domain` VARCHAR(255), IN `webmail_ip` VARCHAR(255), IN `remote_ip` VARCHAR(255), IN `port` INT)
BEGIN
DECLARE vpopmail varchar(256);
SET vpopmail = get_domain_table(domain);

IF (vpopmail) IS NULL THEN
   SET @SQL = "SELECT NULL";
ELSE
	SET @SQL = CONCAT("SELECT CONCAT(",vpopmail,".pw_name, '@', '",domain,"') AS user,",
	vpopmail,".pw_passwd AS password,",
	vpopmail,".pw_dir AS userdb_home,
	89 AS userdb_uid,
	89 AS userdb_gid,
	CONCAT('*:bytes=', REPLACE(SUBSTRING_INDEX(",vpopmail,".pw_shell, 'S', 1), 'NOQUOTA', '0')) AS userdb_quota_rule
	FROM ",vpopmail,"
	LEFT JOIN limits ON limits.domain='",get_real_domain(domain),"'
	WHERE ",vpopmail,".pw_name='",name,"'
	AND
	('",port,"'!='995' OR !(",vpopmail,".pw_gid & 2))
	AND
	('",remote_ip,"'!='",webmail_ip,"' OR !(",vpopmail,".pw_gid & 4))
	AND
	('",remote_ip,"'='",webmail_ip,"' OR '",port,"'!='993' OR !(",vpopmail,".pw_gid & 8))
	AND
	('",remote_ip,"'!='",webmail_ip,"' OR COALESCE(disable_webmail,0)!=1)
	AND
	('",remote_ip,"'='",webmail_ip,"' OR COALESCE(disable_imap,0)!=1)");
END IF;

PREPARE sql_code FROM @SQL;
EXECUTE sql_code;
DEALLOCATE PREPARE sql_code;

END$$
DELIMITER ;

To install the procedure you have to download and execute the code above as follows:

wget https://notes.sagredo.eu/files/qmail/patches/vpopmail/dovecot-pwd-query_disable-many-domains.txt
mysql < dovecot-pwd-query_disable-many-domains.txt -u root -p

The above sql stuff is automatically installed by vpopmail in the database when you create a new domain having configured the program with --disable-many-domains --sql-aliasdomains --enable-mysql-bin=PATH. You have to use my vpopmail patch dated March 9, 2021 or later.

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.

add a comment

Comments

Why not add the code back in?

WilliamSilverstein February 7, 2022 01:57 CET

Has anyone tried just taking the old vpopmail authorization code from the older version and add it back in to the current?

I have not thought of it until now, but I don't have the time to look into it until next month. I was thinking it would be the obvious path to look at. If it is not, why not?

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

Saving vpopmail's aliasdomains to MySQL

Patching vpopmail

Modifing the sql auth

aliasdomains and mysql-limits support for "one table per domain" option

Migrating your accounts to sql format

Comments

Add a comment

qmail notes

LXC scripts

Other contents

Recent comments

See also...

Recent posts

Saving `vpopmail`'s `aliasdomains` to `MySQL`

Patching `vpopmail`

`aliasdomains` and `mysql-limits` support for "one table per domain" option