Users cannot authenticate using an aliased domain against Dovecot SQL.

[greenshrike]

After converting Dovecot from using the Vpopmail backend to using the SQL backend, users can no longer authenticate POP3/IMAP if they submit a username with an aliased domain. If they switch the username to using their primary domain, authentication works.

The basic issue is that Vpopmail doesn't store aliased domains in the database, so the Dovecot SQL query can't check them.

Adding them is fairly straightforward, however:

In MySQL:

connect vpopmail;
create table domainalias (alias char(96) primary key not null, domain char(96) not null, index domain (domain));

To dump all aliased domains and generate SQL suitable for importing them into the table:

cat /data/vpopmail/qmail-users/assign | \
awk -F: '{print $1,$2}' | grep '[a-z]' | \
sed -E 's/^\+(.*)- (.*)$/\1 \2/' |  \
awk '{if ($1 != $2) print "insert into domainalias set alias = @"$1"@, domain = @"$2"@;"}' | \
sed 's/@/"/g'

For Dovecot's SQL queries:

  password_query = SELECT DISTINCT \
    CASE \
      WHEN (pw_domain = '%d') THEN CONCAT(pw_name, '@', pw_domain) \
      WHEN (alias = '%d') THEN CONCAT(pw_name, '@', alias) \
     END AS user \
    ,pw_clear_passwd AS password \
    ,pw_dir AS userdb_home, 89 AS userdb_uid, 89 AS userdb_gid \
    ,CASE \
      WHEN (pw_shell = 'NOQUOTA') THEN '*:bytes=0' \
      WHEN (pw_shell RLIKE '^[0-9]+S') THEN concat('*:bytes=', SUBSTRING_INDEX(pw_shell, 'S', 1)) \
      ELSE '?:bytes=0' \
     END AS userdb_quota_rule \
    FROM vpopmail LEFT JOIN domainalias \
      ON pw_domain = domain \
    WHERE pw_name = '%n' \
    AND (pw_domain = '%d' OR alias = '%d')

  user_query = SELECT DISTINCT pw_dir as home \
    ,89 AS uid ,89 AS gid \
    ,CASE \
      WHEN (pw_shell = 'NOQUOTA') THEN '*:bytes=0' \
      WHEN (pw_shell RLIKE '^[0-9]+S') THEN concat('*:bytes=', SUBSTRING_INDEX(pw_shell, 'S', 1)) \
      ELSE '?:bytes=0' \
     END AS quota_rule \
    FROM vpopmail LEFT JOIN domainalias \
      ON pw_domain = domain \
    WHERE pw_name = '%n' \
    AND (pw_domain = '%d' OR alias = '%d')

The left join will result in multiple rows in the resulting joined table for any primary domain with more than one alias -- one per aliased domain.

If the user auths with an aliased domain (i.e. one of the domainalias.alias entries), only the line matching the alias will be returned.

However, if the user auths with the primary domain (i.e. uses pw_domain), then there is nothing to distinguish between the multiple rows, as their only difference will be in the domainalias.alias column. As such, multiple lines will be returned, all identical.

While appending LIMIT 1 would be the usual choice to force the return of only a single row, Dovecot seems to object to the SQL command, even though MySQL obviously has no issue with it. Instead, SELECT DISTINCT is used to eliminate the duplicate rows.

[msimerson]

Thanks for the great description of the issue and the solution. I've linked to this from the Updating page on the wiki.

[msimerson]

Note: this only works with existing alias domains. If alias domains are added after the "convert and insert into SQL" step, those alias domains won't work until a human comes along and adds an appropriate entry to the domainalias table.

[msimerson]

I am updating the vpopmail build scripts to use a newer patched version of vpopmail from brunonymous/vpopmail. That version has support for alias domains and creates them in a similar aliasdomains table. I'm accepting that as the solution, as it also keeps the table updated. The updated dovecot build script has a newer query which always returns the right info.

Anyone who (like me) deployed this version can convert their domainalias table to an aliasdomains table like so:

CREATE TABLE IF NOT EXISTS `aliasdomains` (
  `alias` varchar(100) NOT NULL,
  `domain` varchar(100) NOT NULL,
  PRIMARY KEY (`alias`)
);

INSERT INTO aliasdomains SELECT * FROM domainalias;