Dovecot (scrambler-plugin) mysql password & user query fails

Question

I currently try to update my mailserver to encrypt all mailboxes, using the posteo scrambler plugin.

I have the following mysql DB:

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

My standard-query (without encryption) works, and is the following (/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;

No I tried to modify the querys, following this template:

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;

While trying to connect via

openssl s_client -connect localhost:993

I get the following error in syslog:

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

Where line 32 is the

WHERE accounts.username = ....

in the user_query.

I checked double, but can't find the error. Any help would be awesome.

Please do **not** [crosspost](https://superuser.com/questions/1210111/dovecot-scrambler-plugin-mysql-password-user-query-fails). See [Is cross-posting a question on multiple Stack Exchange sites permitted if the question is on-topic for each site?](//meta.stackexchange.com/a/64069) — DavidPostill, May 17 '17 at 19:37

score 0 · Accepted Answer · answered May 17 '17 at 19:37

0

You're missing a backslash after following line:

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

which should be

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

Because of this, Dovecot starts parsing the remaining SQL query as Dovecot configuration. As all remaining lines get concatenated to a single line (ie. get their newlines removed), Dovecot does not realize something's broken until the end of the query.

answered May 17 '17 at 19:37

Jens Erat

1,400
2
11
26

Don't post "thank you" comments, select the answer using the checkmark instead if it solved your problem. Have a look at the [FAQ] for details. – Jens Erat May 20 '17 at 18:46

Dovecot (scrambler-plugin) mysql password & user query fails

1 Answers1

Linked