thr3ads.net - dovecot - Set mail crypt private password with OAUTH? [Jan 2022]

If this information is useful, please help other people find it:
Share via:

Max Kostikov

2022-Jan-31 09:27 UTC

Set mail crypt private password with OAUTH?

Aki, thanks for your hint.
Unfortunatelly I can't get this work.
Here is my configuration.

auth_mechanisms = $auth_mechanisms oauthbearer xoauth2
passdb {
   driver = oauth2
   mechanisms = xoauth2 oauthbearer
   args = /etc/dovecot/dovecot-oauth2.conf.ext
}
passdb {
   driver = sql
   skip = unauthenticated
   args = /etc/dovecot/dovecot-sql-encryption.conf.ext
}
passdb {
   skip = authenticated
   driver = sql
   args = /etc/dovecot/dovecot-sql.conf.ext
}
userdb {
   driver = sql
   args = /etc/dovecot/dovecot-sql.conf.ext
}

* In the dovecot-oauth2.conf.ext I have typical Google configuration.

* dovecot-sql-encryption.conf.ext contains only SQL query for password:

password_query = \
   SELECT \
   150 AS userdb_uid, 8 AS userdb_gid, \
   SHA2(CONCAT(username, random_key), 256) AS 
userdb_mail_crypt_private_password, \
   'Y' AS noauthenticate \
   FROM mailbox WHERE username = '%u'

* dovecot-sql.conf.ext contains normal password authentication queries:

password_query = \
   SELECT username AS user, password, \
   150 AS userdb_uid, 8 AS userdb_gid, \
   SHA2(CONCAT(username, random_key), 256) AS 
userdb_mail_crypt_private_password \
   FROM mailbox WHERE username = '%u'
   AND active = '1'

user_query = \
   SELECT \
   CONCAT('/var/vmail/', LCASE(maildir)) AS home, \
   CONCAT('maildir:/var/vmail/', LCASE(maildir)) AS mail, \
   150 AS uid, 8 AS gid, concat('dirsize:storage=', quota) AS quota, \
   SHA2(CONCAT(username, random_key), 256) AS 
userdb_mail_crypt_private_password \
   FROM mailbox WHERE username = '%u'
   AND active = '1'

iterate_query = \
   SELECT username AS user FROM mailbox

It seems now dovecot-sql-encryption.conf.ext is not invoked

Jan 31 09:11:45 example.com dovecot: imap-login: Login: 
user=<max.kostikov at gmail.com>, method=XOAUTH2, rip=127.0.0.1, 
lip=127.0.0.1, mpid=22615, TLS, session=<XtdzLN3WnMl/AAAB>
Jan 31 09:11:45 example.com dovecot: auth: Debug: client in: 
AUTH#0111#011XOAUTH2#011service=imap#011secured=tls#011session=56F7LN3Wnsl/AAAB#011lip=127.0.0.1#011rip=127.0.0.1#011lport=143#011rport=51614#011local_name=example.com#011resp=<hidden>
Jan 31 09:11:45 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Performing passdb 
lookup
Jan 31 09:11:45 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Making token 
validation lookup to 
https://www.googleapis.com/oauth2/v3/tokeninfo?access_tokenJan 31 09:11:45
example.com dovecot: auth: Debug: http-client[1]: queue
https://www.googleapis.com:443: Using existing connection to 
172.253.63.95:443 (SSL=www.googleapis.com) (1 requests pending)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
request [Req9: GET 
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]:
Submitted (requests left=1)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: peer 
172.253.63.95:443: Using 1 idle connections to handle 1 requests (1 
total connections ready)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: queue 
https://www.googleapis.com:443: Connection to peer 172.253.63.95:443 
claimed request [Req9: GET 
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
172.253.63.95:443 [1]: Claimed request [Req9: GET 
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
request [Req9: GET 
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]:
Sent header
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: peer 
172.253.63.95:443: No more requests to service for this peer (1 
connections exist, 0 pending)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
172.253.63.95:443 [1]: Got 200 response for request [Req9: GET 
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]:
OK (took 20 ms + 0 ms in queue)
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Token validation 
succeeded
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field
azp
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field
aud
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field
sub
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field 
scope
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field
exp
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field 
expires_in
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field 
email
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field 
email_verified
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field 
access_type
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Making
introspection
request to https://www.googleapis.com/oauth2/v2/userinfo
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: queue 
https://www.googleapis.com:443: Using existing connection to 
172.253.63.95:443 (SSL=www.googleapis.com) (1 requests pending)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
request [Req10: GET https://www.googleapis.com/oauth2/v2/userinfo]: 
Submitted (requests left=2)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
172.253.63.95:443 [1]: Response payload stream destroyed (0 ms after 
initial response)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
request [Req9: GET 
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]:
Finished
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: queue 
https://www.googleapis.com:443: Dropping request [Req9: GET 
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
request [Req9: GET 
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]:
Free (requests left=2)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: peer 
172.253.63.95:443: Using 1 idle connections to handle 1 requests (1 
total connections ready)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: queue 
https://www.googleapis.com:443: Connection to peer 172.253.63.95:443 
claimed request [Req10: GET 
https://www.googleapis.com/oauth2/v2/userinfo]
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
172.253.63.95:443 [1]: Claimed request [Req10: GET 
https://www.googleapis.com/oauth2/v2/userinfo]
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
request [Req10: GET https://www.googleapis.com/oauth2/v2/userinfo]: Sent 
header
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: peer 
172.253.63.95:443: No more requests to service for this peer (1 
connections exist, 0 pending)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
172.253.63.95:443 [1]: Got 200 response for request [Req10: GET 
https://www.googleapis.com/oauth2/v2/userinfo]: OK (took 57 ms + 0 ms in 
queue)
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Introspection 
succeeded
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field id
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field 
email
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field 
verified_email
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field 
name
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field 
given_name
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field 
family_name
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field 
picture
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing field 
locale
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Finished passdb 
lookup
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
auth(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Auth request finished
Jan 31 09:11:46 example.com dovecot: auth: Debug: client passdb out: 
OK#0111#011user=foo at
gmail.com#011token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
172.253.63.95:443 [1]: Response payload stream destroyed (0 ms after 
initial response)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
request [Req10: GET https://www.googleapis.com/oauth2/v2/userinfo]: 
Finished
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: queue 
https://www.googleapis.com:443: Dropping request [Req10: GET 
https://www.googleapis.com/oauth2/v2/userinfo]
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: host 
www.googleapis.com: Host is idle (timeout = 1786062 msecs)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
request [Req10: GET https://www.googleapis.com/oauth2/v2/userinfo]: Free 
(requests left=1)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: peer 
172.253.63.95:443: No requests to service for this peer (1 connections 
exist, 0 pending)
Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
172.253.63.95:443 [1]: No more requests queued; going idle (timeout = 
60000 msecs)
Jan 31 09:11:46 example.com dovecot: auth: Debug: master in: 
REQUEST#0113880255489#01122604#0111#0117acd1216a7041ddbdf7b563a2bc10dd1#011session_pid=22629#011request_auth_token
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
sql(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Performing userdb 
lookup
Jan 31 09:11:46 example.com dovecot: auth-worker(22005): Debug: conn 
unix:auth-worker (pid=22001,uid=113): auth-worker<19>: Handling USER 
request
Jan 31 09:11:46 example.com dovecot: auth-worker(22005): Debug: conn 
unix:auth-worker (pid=22001,uid=113): auth-worker<19>: 
sql(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Performing userdb 
lookup
Jan 31 09:11:46 example.com dovecot: auth-worker(22005): Debug: conn 
unix:auth-worker (pid=22001,uid=113): auth-worker<19>: 
sql(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): SELECT 
CONCAT('/var/vmail/', LCASE(maildir)) AS home, 
CONCAT('maildir:/var/vmail/', LCASE(maildir)) AS mail, 150 AS uid, 8 AS 
gid, concat('dirsize:storage=', quota) AS quota, SHA2(CONCAT(username, 
random_key), 256) AS userdb_mail_crypt_private_password FROM mailbox 
WHERE username = 'foo at gmail.com'
Jan 31 09:11:46 example.com dovecot: auth-worker(22005): Debug: 
mysql(localhost): Finished query 'SELECT CONCAT('/var/vmail/', 
LCASE(maildir)) AS home, CONCAT('maildir:/var/vmail/', LCASE(maildir)) 
AS mail, 150 AS uid, 8 AS gid, concat('dirsize:storage=', quota) AS 
quota, SHA2(CONCAT(username, random_key), 256) AS 
userdb_mail_crypt_private_password FROM mailbox WHERE username = 
'foo at gmail.com'' in 8 msecs
Jan 31 09:11:46 example.com dovecot: auth-worker(22005): Debug: conn 
unix:auth-worker (pid=22001,uid=113): auth-worker<19>: 
sql(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Finished userdb lookup
Jan 31 09:11:46 example.com dovecot: auth-worker(22005): Debug: conn 
unix:auth-worker (pid=22001,uid=113): auth-worker<19>: Finished
Jan 31 09:11:46 example.com dovecot: auth: Debug: 
sql(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Finished userdb lookup
Jan 31 09:11:46 example.com dovecot: auth: Debug: master userdb out: 
USER#0113880255489#011foo at
gmail.com#011home=/var/vmail/gmail.com/max.kostikov/#011mail=maildir:/var/vmail/gmail.com/max.kostikov/#011uid=150#011gid=8#011quota=dirsize:storage=0#011userdb_mail_crypt_private_password=<hidden>#011auth_mech=XOAUTH2#011auth_token=cd1adb77645bc681e152d945b5617ef602b29fb7
Jan 31 09:11:46 example.com dovecot: imap-login: Login: 
user=<foo at gmail.com>, method=XOAUTH2, rip=127.0.0.1, lip=127.0.0.1, 
mpid=22629, TLS, session=<56F7LN3Wnsl/AAAB>
Jan 31 09:11:46 example.com dovecot: 
imap(foo at gmail.com)<22615><XtdzLN3WnMl/AAAB>: Error: Mailbox
INBOX:
UID=1: read() failed: 
read(/var/vmail/gmail.com/max.kostikov/cur/1643481212.M353350P24555.example.com,S=2140,W=2193:2,S)
failed: Private key not available: Cannot decrypt key 
34255c3a029dc86ba4f07fa9bae2e87e4254de1d582f220a99b46f20bd382870: Cannot 
decrypt key 
98ae0f998f9139ebe20a97de77f162dcdeed496e38c9b5910186f999f3ef66c8: 
Password not available (FETCH BODY[HEADER])
Jan 31 09:11:46 example.com dovecot: 
imap(foo at gmail.com)<22615><XtdzLN3WnMl/AAAB>: Disconnected: FETCH
read()
failed in=57 out=800 deleted=0 expunged=0 trashed=0 hdr_count=1 
hdr_bytes=0 body_count=0 body_bytes=0

I've tried to add

pass_attrs = 
userdb_mail_crypt_private_password=%{userdb:userdb_mail_crypt_private_password}

into the pass_attrs = 
userdb_mail_crypt_private_password=%{userdb:mail_crypt_private_password}
but it looks like no correct key decrypt password passed

...
Jan 31 09:10:49 example.com dovecot: 
imap(foo at gmail.com)<20613><HVcHKd3W9sh/AAAB>: Error: Mailbox
INBOX:
UID=1: read() failed: 
read(/var/vmail/gmail.com/max.kostikov/cur/1643481212.M353350P24555.example.com,S=2140,W=2193:2,S)
failed: Private key not available: Cannot decrypt key 
34255c3a029dc86ba4f07fa9bae2e87e4254de1d582f220a99b46f20bd382870: 
error:03070068:bignum routines:BN_mpi2bn:encoding error (FETCH 
BODY[HEADER])
Jan 31 09:10:50 example.com dovecot: 
imap(foo at gmail.com)<20631><cuIWKd3W+Mh/AAAB>: Error: Mailbox
INBOX:
UID=1: read() failed: 
read(/var/vmail/gmail.com/max.kostikov/cur/1643481212.M353350P24555.example.com,S=2140,W=2193:2,S)
failed: Private key not available: Cannot decrypt key 
34255c3a029dc86ba4f07fa9bae2e87e4254de1d582f220a99b46f20bd382870: 
error:03070068:bignum routines:BN_mpi2bn:encoding error


Aki Tuomi ?????(?) 2022-01-31 08:34:>> On 28/01/2022 21:27 Max Kostikov <max at kostikov.co> wrote:
>> 
>> 
>> We currently use Dovecot and mailbox encryption via the
>> mail-crypt-plugin.
>> With standard password authentication, we set the value of the
>> individual
>> password to encrypt the contents of the
>> userdb_mail_crypt_private_password
>> mailbox in the SQL query.
>> Is it possible to set the userdb_mail_crypt_private_password value 
>> when
>> authenticating via OAUTH2 ?
>> 
>> --
>> Best regards,
>> Max Kostikov
> 
> You probably want to split your passdb drivers into two, one which
> does the authentication and second which populates the private
> password value. This way it should work correctly on both cases.
> 
> passdb {
>  driver = sql
>  skip = unauthenticated
>  ...
> }
> 
> and ensure your return in this sql query `'Y' as
noauthenticate'`
> 
> Aki
-- 
Best regards,
Max Kostikov

W: https://kostikov.co | E: max at kostikov.co | T: +7(952)7927000

Aki Tuomi

2022-Jan-31 09:33 UTC

head link

Set mail crypt private password with OAUTH?

try adding

result_success = continue-ok

to the oauth2 database.

Aki
> On 31/01/2022 11:27 Max Kostikov <max at kostikov.co> wrote:
> 
>  
> Aki, thanks for your hint.
> Unfortunatelly I can't get this work.
> Here is my configuration.
> 
> auth_mechanisms = $auth_mechanisms oauthbearer xoauth2
> passdb {
>    driver = oauth2
>    mechanisms = xoauth2 oauthbearer
>    args = /etc/dovecot/dovecot-oauth2.conf.ext
> }
> passdb {
>    driver = sql
>    skip = unauthenticated
>    args = /etc/dovecot/dovecot-sql-encryption.conf.ext
> }
> passdb {
>    skip = authenticated
>    driver = sql
>    args = /etc/dovecot/dovecot-sql.conf.ext
> }
> userdb {
>    driver = sql
>    args = /etc/dovecot/dovecot-sql.conf.ext
> }
> 
> * In the dovecot-oauth2.conf.ext I have typical Google configuration.
> 
> * dovecot-sql-encryption.conf.ext contains only SQL query for password:
> 
> password_query = \
>    SELECT \
>    150 AS userdb_uid, 8 AS userdb_gid, \
>    SHA2(CONCAT(username, random_key), 256) AS 
> userdb_mail_crypt_private_password, \
>    'Y' AS noauthenticate \
>    FROM mailbox WHERE username = '%u'
> 
> * dovecot-sql.conf.ext contains normal password authentication queries:
> 
> password_query = \
>    SELECT username AS user, password, \
>    150 AS userdb_uid, 8 AS userdb_gid, \
>    SHA2(CONCAT(username, random_key), 256) AS 
> userdb_mail_crypt_private_password \
>    FROM mailbox WHERE username = '%u'
>    AND active = '1'
> 
> user_query = \
>    SELECT \
>    CONCAT('/var/vmail/', LCASE(maildir)) AS home, \
>    CONCAT('maildir:/var/vmail/', LCASE(maildir)) AS mail, \
>    150 AS uid, 8 AS gid, concat('dirsize:storage=', quota) AS
quota, \
>    SHA2(CONCAT(username, random_key), 256) AS 
> userdb_mail_crypt_private_password \
>    FROM mailbox WHERE username = '%u'
>    AND active = '1'
> 
> iterate_query = \
>    SELECT username AS user FROM mailbox
> 
> It seems now dovecot-sql-encryption.conf.ext is not invoked
> 
> Jan 31 09:11:45 example.com dovecot: imap-login: Login: 
> user=<max.kostikov at gmail.com>, method=XOAUTH2, rip=127.0.0.1, 
> lip=127.0.0.1, mpid=22615, TLS, session=<XtdzLN3WnMl/AAAB>
> Jan 31 09:11:45 example.com dovecot: auth: Debug: client in: 
>
AUTH#0111#011XOAUTH2#011service=imap#011secured=tls#011session=56F7LN3Wnsl/AAAB#011lip=127.0.0.1#011rip=127.0.0.1#011lport=143#011rport=51614#011local_name=example.com#011resp=<hidden>
> Jan 31 09:11:45 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Performing
passdb
> lookup
> Jan 31 09:11:45 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Making token 
> validation lookup to 
> https://www.googleapis.com/oauth2/v3/tokeninfo?access_token> Jan 31
09:11:45 example.com dovecot: auth: Debug: http-client[1]: queue
> https://www.googleapis.com:443: Using existing connection to 
> 172.253.63.95:443 (SSL=www.googleapis.com) (1 requests pending)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
> request [Req9: GET 
>
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]:
> Submitted (requests left=1)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: peer 
> 172.253.63.95:443: Using 1 idle connections to handle 1 requests (1 
> total connections ready)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: queue 
> https://www.googleapis.com:443: Connection to peer 172.253.63.95:443 
> claimed request [Req9: GET 
>
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
> 172.253.63.95:443 [1]: Claimed request [Req9: GET 
>
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
> request [Req9: GET 
>
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]:
> Sent header
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: peer 
> 172.253.63.95:443: No more requests to service for this peer (1 
> connections exist, 0 pending)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
> 172.253.63.95:443 [1]: Got 200 response for request [Req9: GET 
>
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]:
> OK (took 20 ms + 0 ms in queue)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Token
validation
> succeeded
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field azp
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field aud
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field sub
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field
> scope
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field exp
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field
> expires_in
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field
> email
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field
> email_verified
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field
> access_type
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Making
introspection
> request to https://www.googleapis.com/oauth2/v2/userinfo
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: queue 
> https://www.googleapis.com:443: Using existing connection to 
> 172.253.63.95:443 (SSL=www.googleapis.com) (1 requests pending)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
> request [Req10: GET https://www.googleapis.com/oauth2/v2/userinfo]: 
> Submitted (requests left=2)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
> 172.253.63.95:443 [1]: Response payload stream destroyed (0 ms after 
> initial response)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
> request [Req9: GET 
>
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]:
> Finished
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: queue 
> https://www.googleapis.com:443: Dropping request [Req9: GET 
>
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
> request [Req9: GET 
>
https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI]:
> Free (requests left=2)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: peer 
> 172.253.63.95:443: Using 1 idle connections to handle 1 requests (1 
> total connections ready)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: queue 
> https://www.googleapis.com:443: Connection to peer 172.253.63.95:443 
> claimed request [Req10: GET 
> https://www.googleapis.com/oauth2/v2/userinfo]
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
> 172.253.63.95:443 [1]: Claimed request [Req10: GET 
> https://www.googleapis.com/oauth2/v2/userinfo]
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
> request [Req10: GET https://www.googleapis.com/oauth2/v2/userinfo]: Sent 
> header
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: peer 
> 172.253.63.95:443: No more requests to service for this peer (1 
> connections exist, 0 pending)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
> 172.253.63.95:443 [1]: Got 200 response for request [Req10: GET 
> https://www.googleapis.com/oauth2/v2/userinfo]: OK (took 57 ms + 0 ms in 
> queue)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Introspection 
> succeeded
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field id
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field
> email
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field
> verified_email
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field
> name
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field
> given_name
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field
> family_name
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field
> picture
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Processing
field
> locale
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> oauth2(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Finished
passdb
> lookup
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> auth(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Auth request
finished
> Jan 31 09:11:46 example.com dovecot: auth: Debug: client passdb out: 
> OK#0111#011user=foo at
gmail.com#011token=ya29.A0ARrdaM_UsPVyxGVJ2aSHPS0D7gnONiuBcEF5CyUwFhnd6nq94c4T1PAWQeUE5IItCIwLb90QpkNLduHD8_vkeka7j6m574SP3gbg114lopTrmPZmHBIpDzaj2w-JqlV0bkFCT7jKqd5UHWXrRIAdqS0ksDCI
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
> 172.253.63.95:443 [1]: Response payload stream destroyed (0 ms after 
> initial response)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
> request [Req10: GET https://www.googleapis.com/oauth2/v2/userinfo]: 
> Finished
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: queue 
> https://www.googleapis.com:443: Dropping request [Req10: GET 
> https://www.googleapis.com/oauth2/v2/userinfo]
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: host 
> www.googleapis.com: Host is idle (timeout = 1786062 msecs)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: 
> request [Req10: GET https://www.googleapis.com/oauth2/v2/userinfo]: Free 
> (requests left=1)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client[1]: peer 
> 172.253.63.95:443: No requests to service for this peer (1 connections 
> exist, 0 pending)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: http-client: conn 
> 172.253.63.95:443 [1]: No more requests queued; going idle (timeout = 
> 60000 msecs)
> Jan 31 09:11:46 example.com dovecot: auth: Debug: master in: 
>
REQUEST#0113880255489#01122604#0111#0117acd1216a7041ddbdf7b563a2bc10dd1#011session_pid=22629#011request_auth_token
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> sql(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Performing userdb
> lookup
> Jan 31 09:11:46 example.com dovecot: auth-worker(22005): Debug: conn 
> unix:auth-worker (pid=22001,uid=113): auth-worker<19>: Handling USER 
> request
> Jan 31 09:11:46 example.com dovecot: auth-worker(22005): Debug: conn 
> unix:auth-worker (pid=22001,uid=113): auth-worker<19>: 
> sql(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Performing userdb
> lookup
> Jan 31 09:11:46 example.com dovecot: auth-worker(22005): Debug: conn 
> unix:auth-worker (pid=22001,uid=113): auth-worker<19>: 
> sql(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): SELECT 
> CONCAT('/var/vmail/', LCASE(maildir)) AS home, 
> CONCAT('maildir:/var/vmail/', LCASE(maildir)) AS mail, 150 AS uid,
8 AS
> gid, concat('dirsize:storage=', quota) AS quota,
SHA2(CONCAT(username,
> random_key), 256) AS userdb_mail_crypt_private_password FROM mailbox 
> WHERE username = 'foo at gmail.com'
> Jan 31 09:11:46 example.com dovecot: auth-worker(22005): Debug: 
> mysql(localhost): Finished query 'SELECT CONCAT('/var/vmail/', 
> LCASE(maildir)) AS home, CONCAT('maildir:/var/vmail/',
LCASE(maildir))
> AS mail, 150 AS uid, 8 AS gid, concat('dirsize:storage=', quota) AS
> quota, SHA2(CONCAT(username, random_key), 256) AS 
> userdb_mail_crypt_private_password FROM mailbox WHERE username = 
> 'foo at gmail.com'' in 8 msecs
> Jan 31 09:11:46 example.com dovecot: auth-worker(22005): Debug: conn 
> unix:auth-worker (pid=22001,uid=113): auth-worker<19>: 
> sql(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Finished userdb
lookup
> Jan 31 09:11:46 example.com dovecot: auth-worker(22005): Debug: conn 
> unix:auth-worker (pid=22001,uid=113): auth-worker<19>: Finished
> Jan 31 09:11:46 example.com dovecot: auth: Debug: 
> sql(foo at gmail.com,127.0.0.1,<56F7LN3Wnsl/AAAB>): Finished userdb
lookup
> Jan 31 09:11:46 example.com dovecot: auth: Debug: master userdb out: 
> USER#0113880255489#011foo at
gmail.com#011home=/var/vmail/gmail.com/max.kostikov/#011mail=maildir:/var/vmail/gmail.com/max.kostikov/#011uid=150#011gid=8#011quota=dirsize:storage=0#011userdb_mail_crypt_private_password=<hidden>#011auth_mech=XOAUTH2#011auth_token=cd1adb77645bc681e152d945b5617ef602b29fb7
> Jan 31 09:11:46 example.com dovecot: imap-login: Login: 
> user=<foo at gmail.com>, method=XOAUTH2, rip=127.0.0.1,
lip=127.0.0.1,
> mpid=22629, TLS, session=<56F7LN3Wnsl/AAAB>
> Jan 31 09:11:46 example.com dovecot: 
> imap(foo at gmail.com)<22615><XtdzLN3WnMl/AAAB>: Error: Mailbox
INBOX:
> UID=1: read() failed: 
>
read(/var/vmail/gmail.com/max.kostikov/cur/1643481212.M353350P24555.example.com,S=2140,W=2193:2,S)
> failed: Private key not available: Cannot decrypt key 
> 34255c3a029dc86ba4f07fa9bae2e87e4254de1d582f220a99b46f20bd382870: Cannot 
> decrypt key 
> 98ae0f998f9139ebe20a97de77f162dcdeed496e38c9b5910186f999f3ef66c8: 
> Password not available (FETCH BODY[HEADER])
> Jan 31 09:11:46 example.com dovecot: 
> imap(foo at gmail.com)<22615><XtdzLN3WnMl/AAAB>: Disconnected:
FETCH read()
> failed in=57 out=800 deleted=0 expunged=0 trashed=0 hdr_count=1 
> hdr_bytes=0 body_count=0 body_bytes=0
> 
> I've tried to add
> 
> pass_attrs = 
>
userdb_mail_crypt_private_password=%{userdb:userdb_mail_crypt_private_password}
> 
> into the pass_attrs = 
> userdb_mail_crypt_private_password=%{userdb:mail_crypt_private_password}
> but it looks like no correct key decrypt password passed
> 
> ...
> Jan 31 09:10:49 example.com dovecot: 
> imap(foo at gmail.com)<20613><HVcHKd3W9sh/AAAB>: Error: Mailbox
INBOX:
> UID=1: read() failed: 
>
read(/var/vmail/gmail.com/max.kostikov/cur/1643481212.M353350P24555.example.com,S=2140,W=2193:2,S)
> failed: Private key not available: Cannot decrypt key 
> 34255c3a029dc86ba4f07fa9bae2e87e4254de1d582f220a99b46f20bd382870: 
> error:03070068:bignum routines:BN_mpi2bn:encoding error (FETCH 
> BODY[HEADER])
> Jan 31 09:10:50 example.com dovecot: 
> imap(foo at gmail.com)<20631><cuIWKd3W+Mh/AAAB>: Error: Mailbox
INBOX:
> UID=1: read() failed: 
>
read(/var/vmail/gmail.com/max.kostikov/cur/1643481212.M353350P24555.example.com,S=2140,W=2193:2,S)
> failed: Private key not available: Cannot decrypt key 
> 34255c3a029dc86ba4f07fa9bae2e87e4254de1d582f220a99b46f20bd382870: 
> error:03070068:bignum routines:BN_mpi2bn:encoding error
> 
> 
> Aki Tuomi ?????(?) 2022-01-31 08:34:
> >> On 28/01/2022 21:27 Max Kostikov <max at kostikov.co> wrote:
> >> 
> >> 
> >> We currently use Dovecot and mailbox encryption via the
> >> mail-crypt-plugin.
> >> With standard password authentication, we set the value of the
> >> individual
> >> password to encrypt the contents of the
> >> userdb_mail_crypt_private_password
> >> mailbox in the SQL query.
> >> Is it possible to set the userdb_mail_crypt_private_password value
> >> when
> >> authenticating via OAUTH2 ?
> >> 
> >> --
> >> Best regards,
> >> Max Kostikov
> > 
> > You probably want to split your passdb drivers into two, one which
> > does the authentication and second which populates the private
> > password value. This way it should work correctly on both cases.
> > 
> > passdb {
> >  driver = sql
> >  skip = unauthenticated
> >  ...
> > }
> > 
> > and ensure your return in this sql query `'Y' as
noauthenticate'`
> > 
> > Aki
> 
> -- 
> Best regards,
> Max Kostikov
> 
> W: https://kostikov.co | E: max at kostikov.co | T: +7(952)7927000

dovecot - Jan 2022 - Set mail crypt private password with OAUTH?

Set mail crypt private password with OAUTH?

Set mail crypt private password with OAUTH?