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

9 marzo 2021 Roberto Puzzanghera36 commenti

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 to replace the vpopmail driver:

  • Tyler Simkin 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

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

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.

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.

Commenti

Lua backend

As of Dovecot 2.3, the lua backend can also be used to emulate something close to the original vpopmail, including default domain,etc.  I am new to lua, but this seems to work quite well.

passdb { 
 driver = lua
 args = file=/path/to/auth.lua
 override_fields = uid=vpopmail gid=vchkpw

 auth_verbose = yes

}

userdb {

 driver = lua
 args = file=/path/to/auth.lua

}

 

-- auth.lua --

local databasebase = "/home/vpopmail/domains" 

local defaultdomain = "mydefaultdomain.com"

local returnuid = 520

local returngid = 520

---------------------------

function split(inputstr, sep)  
       sep=sep or '%s'  
       local t={}   
       for field,s in string.gmatch(inputstr, "([^"..sep.."]*)("..sep.."?)") do  
               table.insert(t,field)   
               if s=="" then  
                       return t  
               end  
       end  
end

-- Find user / password / home in database
function db_lookup(fullusername)

  --Convert to lower case
  fullusername = string.lower(fullusername)

  local username = ""
  local domain = ""
  local splituser

  -- Check for existance of an @
  if string.match (fullusername, "@") then
       splituser = split(fullusername, "@" )
       username = splituser[1]
       domain = splituser[2]
  else
       username = fullusername
       domain = defaultdomain
  end
   
  --Create real path where dB resides
  local database = databasebase .. "/" .. domain .. "/vpasswd"

  for line in io.lines(database) do

      local userparams = split(line, ":")

      local user = userparams[1]
       
      if (user == username) then
              return {result=0, password=userparams[8], home=userparams[6]}
      end
  end
  return {result=-1}
end

function auth_passdb_lookup(req)
  res = db_lookup(req.username)
  if res.result == 0 then
      return dovecot.auth.PASSDB_RESULT_OK, "password=" .. res.password
  end
  return dovecot.auth.PASSDB_RESULT_USER_UNKNOWN, ""
end

function auth_userdb_lookup(req)
  res = db_lookup(req.username)
  if res.result == 0 then
      -- you can add additional information here for userdb, like uid or home
      return dovecot.auth.USERDB_RESULT_OK, "uid="..returnuid.." gid="..returngid.." home="..res.home
  end
  return dovecot.auth.USERDB_RESULT_USER_UNKNOWN, ""
end



--Testing for working lookup running direct from cli
--x =db_lookup("postmaster@mydomain.com")
--print (x.password)
--print (x.home)

Rispondi | Permalink

Lua backend

This is an improved version of the auth.lua that Tyler sent me today (download)

--End user editable values

local databasebase = "/home/vpopmail/domains"
local defaultdomain = "yourdomain.tld"
local returnuid = 520
local returngid = 520

--Supporting functions----------------


--Check if a file exists
function file_exists(name)
   local f=io.open(name,"r")
   if f~=nil then io.close(f) return true else return false end
end

--Split username and domain portions of an email address
function split(inputstr, sep) 
        sep=sep or '%s' 
        local t={}  
        for field,s in string.gmatch(inputstr, "([^"..sep.."]*)("..sep.."?)") do 
                table.insert(t,field)  
                if s=="" then 
                        return t 
                end 
        end 
end

-- Get all subdirectories of a directory (1 level)
function scandir(directory)
    local i, t, popen = 0, {}, io.popen
    local pfile = popen('ls -d '..directory..'/*/')
    for filename in pfile:lines() do
        print("Filename: "..filename)
        i = i + 1
        t[i] = filename
    end
    pfile:close()
    return t
end

--DoveCot called fuctions ------------

-- Find user / password / home in database
function db_lookup(fullusername)

   --Convert to lower case
   fullusername = string.lower(fullusername)

   local username = ""
   local domain = ""

   -- Check for existance of an @
   if string.match (fullusername, "@") then
        local splituser = split(fullusername, "@" )
        username = splituser[1]
        domain = splituser[2]
   else
        username = fullusername
        domain = defaultdomain
   end
   
   --Create real path where dB resides
   local database = databasebase .. "/" .. domain .. "/vpasswd"

   if (file_exists(database)) then

           for line in io.lines(database) do

                local userparams = split(line, ":")
                local user = userparams[1]
       
                if (user == username) then
                        return {result=0, password=userparams[8], home=userparams[6]}
                end
        end
   end
   return {result=-1}
end

function auth_passdb_lookup(req)
   res = db_lookup(req.user)
   if res.result == 0 then
       return dovecot.auth.PASSDB_RESULT_OK, "password=" .. res.password
   end
   return dovecot.auth.PASSDB_RESULT_USER_UNKNOWN, ""
end

function auth_userdb_lookup(req)
   res = db_lookup(req.user)
   if res.result == 0 then
       -- you can add additional information here for userdb, like uid or home
       return dovecot.auth.USERDB_RESULT_OK, "uid="..returnuid.." gid="..returngid.." home="..res.home
   end
   return dovecot.auth.USERDB_RESULT_USER_UNKNOWN, ""
end

function auth_userdb_iterate()
  users = {}
  
  for _,domain in ipairs(scandir(databasebase)) do


        --Create real path where dB resides
        local database = domain .. "vpasswd"

        print("Current vpasswd file: "..database)

        if (file_exists(database)) then

           --split path into array of folders after removing the trailing /
           local splitpath=split(domain:sub(1, #domain -1),"/")
           local rawdomain

           --last piece of path is domain
           for _,path in ipairs(splitpath) do
                rawdomain=path
           end

           for line in io.lines(database) do

                local userparams = split(line, ":")
                local user = userparams[1]

                table.insert(users, user.."@"..rawdomain)     
           end
        end
   end

   return users
end

--Testing for API function operation
--Dovecot relies on these

--Testing for working db_lookup()
--x =db_lookup("user@email.address")
--
--print (x.password)
--print (x.home)

--Testing for working auth_userdb_iterate
--for index,data in ipairs(auth_userdb_iterate()) do
--      print (data)
--end

Rispondi | Permalink

Lua backend

Should note that this lua script only works if you store passwords in plaintext in vpopmail.  If using MD5-CRYPT as many of us are then add this function:

function auth_password_verify(req, pass)
res = db_lookup(req.user)
if res.result == 0 then
if req:password_verify("{MD5-CRYPT}" .. res.password, pass) == 1 then
return dovecot.auth.PASSDB_RESULT_OK, {}
end
end
end

and then modify db_lookup to return the crypted password like so:

(password = userparams[2] instead of password = userparams[8])

-- Find user / password / home in database
function db_lookup(fullusername)

--Convert to lower case
fullusername = string.lower(fullusername)

local username = ""
local domain = ""

-- Check for existance of an @
if string.match (fullusername, "@") then
local splituser = split(fullusername, "@" )
username = splituser[1]
domain = splituser[2]
else
username = fullusername
domain = defaultdomain
end

--Create real path where dB resides
local database = databasebase .. "/" .. domain .. "/vpasswd"

if (file_exists(database)) then

for line in io.lines(database) do

local userparams = split(line, ":")
local user = userparams[1]

if (user == username) then
return {result=0, password=userparams[2], home=userparams[6]}
end
end
end
return {result=-1}
end

Rispondi | Permalink

Lua backend

Thanks a lot, very much appreciated. I'll check it out

Rispondi | Permalink

What about using --disable-many-domains on vpopmail configure ?

Hi

Now, the dovecot 2.3.13 arrives to FreeBSD ports, and now I found that there is not a patch (or I don't find it) for vpopmail with the option -disable-many-domains

The bigger problem is that domain tables reaplace dots whit underscore

Is there a solution for this or I need to start programming :D ?

Rispondi | Permalink

What about using --disable-many-domains on vpopmail configure ?

I think you meant --enable-many-domains, right? Are you using the sql auth driver? If yes I think it can be sufficient to modify a bit the password_query, in order to look in a different table depending on the user's domain.

Rispondi | Permalink

What about using --disable-many-domains on vpopmail configure ?

VpopMail config instruction are confused, but to enable 1 domain per table the config option is -disable-many-domains

md5 passwords = ON --enable-md5-passwords (default)
auth module = mysql --enable-auth-module=mysql
one domain per SQL table = --disable-many-domains

The problem is the domain table, is changed to underscore, but I just solve this sending the USER whit the domain with underscore :D

User : xxxx@xxxx_com and I use domain as table name

 SELECT \
CONCAT(vpopmail.pw_name,'@',%L{domain_first}) 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 %d AS vpopmail \
WHERE \
vpopmail.pw_name='%n';

But after a lot of test I found that only works with PLAIN password, if I use CRAM-MD5 or MD5-CRYPT, I get errors from dovecot

roundcube with : NULL 
dovecot with: MD5-CRYPT 

Requested CRAM-MD5 scheme, but we have only MD5-CRYPT

Any idea?

Rispondi | Permalink

What about using --disable-many-domains on vpopmail configure ?

The query works here but the %L{domain_first} had to be embedded into quotes.

Can you clarify how did you pass the domain with the underscore?

btw, modifing the query like this breaks the possibility to authenticate with alias domains. I find that todays Steve's solution in dovecot m/l (using sql view) would do both things.

Rispondi | Permalink

What about using --disable-many-domains on vpopmail configure ?

weird... Let me know if the hint of Aki Tuomi in the dovecot m/l solves.

Rispondi | Permalink

What about using --disable-many-domains on vpopmail configure ?

Finally

The hint of Aki Tuomi in the dovecot m/l, don't work, but ... he give me another idea

I set encrypted password on Thunder and RoundCube and set default_pass_scheme to PLAIN on dovecot-sql.conf.ext, and now, is working !

Now I need to change vpopmail, beacuse UID / GID is not saved on domain tables !, more work !

Rispondi | Permalink

What about using --disable-many-domains on vpopmail configure ?

I think adding this to your select will be sufficient

89 AS uid, \ 
89 AS gid, \

Please post your auth config when finished

Rispondi | Permalink

What about using --disable-many-domains on vpopmail configure ?

After a lot of test, I was looking for other thing and I found this :
https://wiki.dovecot.org/AuthDatabase/VPopMail

I can't belive that there was a working example :D

Anyway, I did something different for the way we use vpopmail, and I don't changed vpopmail, I created other table that was updated from the system we use, so the uid was taken from the other table

The BIG difference is the way the user MUST be sent to dovecot

USER@DOMAIN@DOAMIN_with_UNDERSCORES

Example: me@pablomurillo.com.ar@pablomurillo_com_ar

Roundcube must be modified too, in rcube_imap.php on connect fucntion I added:

$user .= "@" . preg_replace(array("/\./","/\-/"), "_", $host);

But I have rouncube modified too, so, I don't know if host has the real host for everybody

password_query = \
SELECT \
CONCAT(vpopmail.pw_name,'@','%L{domain_first}') AS user, \
vpopmail.pw_clear_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 quota_rule \
FROM \
%L{domain_last} AS vpopmail \
LEFT JOIN \
aliasdomains ON aliasdomains.alias='%L{domain_first}' \
LEFT JOIN \
limits ON limits.domain = '%L{domain_first}' \
WHERE \
vpopmail.pw_name='%n' \
AND \
(('[WEBMAIL-IP]'!='%l' AND limits.disable_imap = 0) \
OR \
('[WEBMAIL-IP]'='%l' AND limits.disable_webmail = 0))

Rispondi | Permalink

What about using --disable-many-domains on vpopmail configure ?

what if a user connects with a client dfferent from roundcube? I think that we should consider a solution which doesn't touch the client but only the dovecot query...

Rispondi | Permalink

What about using --disable-many-domains on vpopmail configure ?

I find a better solution, made an storage procedure returning the information :

password_query = CALL STORAGE_PROCEDURE_NAME('%n', '%d','%r','%l')

Parameters:
User name
Domanin name
Remote IP
Local IP

The select is the same, I only convert domain to table name (replace "-" and "." to "_")
No need to declare OUT variables
I have a lot of code for our internal system in the procedure, this is why I'm not posting it, but is just a :

BEGIN
DECLARE table varchar(256);
SET table = REPLACE(REPLACE(domain, ".", "_"), "-", "_");

SET @sql = CONCAT("SELECT HERE using the parameters received and the table name as variable");

PREPARE stmt FROM @SQL;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
END

Rispondi | Permalink

What about using --disable-many-domains on vpopmail configure ?

Pablo, did you find a way to iterate among users when domains have their own table?

Rispondi | Permalink

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!

Rispondi | Permalink

Support for aliasdomains added!

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

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

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

Rispondi | 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   :/

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

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

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

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

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

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

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

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

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

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

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

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

Rispondi | 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?

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

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

Rispondi | Permalink