Multiple passwords for a single account with Dovecot

I recently looked for ways to allow the owner of an email account, that is managed on the server side by Dovecot, to authenticate to the server using a different password for each device she might want to access her emails from – something akin to the “application-specific passwords” that are proposed, for example, by Nextcloud.

Dovecot’s documentation is unfortunately a little bit light on this specific issue. It explains how it is possible to use multiple password databases, which is not what I want here: I’d want all the different passwords for a single user to be in the same database.

This question on StackOverflow suggests a possible solution using a SQL backend, but it omits a rather crucial point, which motivated this post.

Dovecot can use a SQL database as a passwords database in two different modes. In the first mode, it queries the database for the password of the to-be-authenticated user. The database should then return (assuming the user exists, of course) the password of that user (typically hashed), and upon receiving that password Dovecot will compare it with the password provided by the user (hashing it as necessary, unless it was already provided in hashed form, for example if the authentication mechanism is CRAM-MD5 or similar). In that mode, the database should return only one password! Dovecot cannot handle the case where its password query returns several rows, and it will not iterate over the rows to compare the user-provided password to the ones it got from the database until it finds a match.

In the second mode, Dovecot delegates the password verification to the database itself: it sends both the username and the password it got from the user to the database, and it lets the database find a row that matches both. Only that second mode is suitable to allow a user to have an arbitrary number of passwords.

To enable the second mode, follow the instructions given in the Password verification by SQL server section in Dovecot’s SQL documentation. For example, assuming a simple database schema like the one in the aforementioned StackOverflow response, set the password_query setting to something like this:

password_query = SELECT NULL AS password, "Y" AS nopassword, username AS user \
  FROM dovecot_passwords WHERE username = "%u" AND password = PASSWORD("%w")

The above is assuming two things that are worth noting:

First, this assumes that Dovecot is provided with the password to verify in plain text, which means the authentication mechanism must be plain or login; this should not be a concern as nobody should connect to their email server without TLS nowadays, so the password does not actually transit through the Internet in plain text. Plain text authentication is already the default behaviour of Dovecot.

Second, this assumes that the passwords are stored in the database using the same function as the one used in the password_query (in the example above, MariaDB’s PASSWORD function). That is, a new password for a given user should be inserted using:

INSERT INTO dovecot_passwords VALUES("alice@example.org", PASSWORD("password16"));

It is also worth noting that MariaDB’s PASSWORD function, AFAIK, is merely a single round of SHA-1.¹ This is barely better than storing the passwords in plain text.

1. ↑ It can be made to use a Ed25519-based scheme instead, but this is only applicable when the function is used to authenticate a MariaDB user, not when it is used for anything else. Incidentally, the use of that function for anything else than authenticating a MariaDB user is discouraged.

6 November 2024