☰

Dovecot（扰频器插件）mysql 密码和用户查询失败

tag-icon • tag-icon mysql dovecot imap public-key-encryption
Dovecot（扰频器插件）mysql 密码和用户查询失败

我目前尝试更新我的邮件服务器以使用 posteo 加密所有邮箱扰频器插件。

我有以下 mysql 数据库：

mysql> describe accounts;
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| username   | varchar(64)      | NO   | MUL | NULL    |                |
| domain     | varchar(255)     | NO   | MUL | NULL    |                |
| password   | varchar(255)     | NO   |     | NULL    |                |
| quota      | int(10) unsigned | YES  |     | 100     |                |
| enabled    | tinyint(1)       | YES  |     | 1       |                |
| sendonly   | tinyint(1)       | YES  |     | 0       |                |
| TLSenforce | tinyint(1)       | YES  |     | 1       |                |


mysql> describe user_keys;
| Field                  | Type             | Null | Key | Default | Extra          |
+------------------------+------------------+------+-----+---------+----------------+
| id                     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| uid                    | int(10) unsigned | NO   | MUL | NULL    |                |
| enabled_encryp         | tinyint(1)       | NO   |     | 1       |                |
| public_key             | varchar(8191)    | NO   |     | NULL    |                |
| private_key            | varchar(8191)    | NO   |     | NULL    |                |
| private_key_salt       | varchar(255)     | NO   |     | NULL    |                |
| private_key_iterations | int(11)          | YES  |     | 5000    |                |

我的标准查询（未加密）有效，如下所示（/etc/dovecot/dovecot-sql.conf）

password_query = SELECT username AS user, domain, password FROM accounts WHERE username = '%n' AND domain = '%d' and enabled = true;

user_query = SELECT concat('*:storage=', quota, 'M') AS quota_rule FROM accounts WHERE username = '%n' AND domain = '%d' AND sendonly = false;

iterate_query = SELECT username, domain FROM accounts where sendonly = false;

不，我尝试修改查询，如下所示这模板：

password_query = \
SELECT username AS user, \
       password, \
       REPLACE('%w', '%%', '%%%%') AS userdb_scrambler_plain_password, \
       user_keys.enabled_encryp AS userdb_scrambler_enabled, \
       user_keys.public_key AS userdb_scrambler_public_key, \
       user_keys.private_key AS userdb_scrambler_private_key, \
       user_keys.private_key_salt AS userdb_scrambler_private_key_salt, \
       user_keys.private_key_iterations AS userdb_scrambler_private_key_iterations \
   FROM accounts \
   LEFT OUTER JOIN user_keys ON accounts.id = user_keys.id \
   WHERE accounts.username = '%n' AND accounts.domain = '%d' AND user_keys.enabled_encryp = true;


user_query = \
SELECT concat('*:storage=', quota, 'M') AS quota_rule,
       user_keys.enabled_encryp AS scrambler_enabled, \
       user_keys.public_key AS scrambler_public_key, \
       user_keys.private_key AS scrambler_private_key, \
       user_keys.private_key_salt AS scrambler_private_key_salt, \
       user_keys.private_key_iterations AS scrambler_private_key_iterations \
 FROM accounts \
 LEFT OUTER JOIN user_keys ON accounts.id = user_keys.id \
 WHERE accounts.username = '%n' AND accounts.domain = '%d' AND accounts.sendonly = false;

尝试通过

openssl s_client -connect localhost:993

我在系统日志中收到以下错误：

dovecot: auth: Fatal: sql /etc/dovecot/dovecot-sql.conf: Error in configuration file /etc/dovecot/dovecot-sql.conf line 32: Expecting '='

第 32 行是

WHERE accounts.username = ....

在 user_query 中。

我仔细检查了两遍，但还是没找到错误。任何帮助都非常感谢。

答案1

以下行后缺少反斜杠：

SELECT concat('*:storage=', quota, 'M') AS quota_rule,

应该是

SELECT concat('*:storage=', quota, 'M') AS quota_rule, \

因此，Dovecot 开始将剩余的 SQL 查询解析为 Dovecot 配置。由于所有剩余行都连接成一行（即删除其换行符），因此 Dovecot 直到查询结束时才意识到出现了问题。

相关内容