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.
