On 20/04/2023 13:18 EEST Bogusław Juza bogdan@agh.edu.pl wrote:

Hi Staff,

I'm creating the dovecot configuration for multiple user passwords e-mail site. It's working well, but I've one problem - I can't log, which password was used.

I'm using auth-sql, the query looks like:

password_query =
SELECT users.email AS user,
shadow.passwd AS password,
FROM shadow WHERE shadow.email = '%u' AND
( ('%r'='127.0.0.1' AND shadow.webmail<>0) OR
('%r'<>'127.0.0.1' AND shadow.imap<>0 AND
shadow.hash='%{sha512;rounds=5000:password}')
) LIMIT 1

The hash from random generated application password works as the selector, which password should be checked.

It works fine, but in the log I have got only the e-mail and both IP addresses. I need to log one more information - which password was used (shadow.id column). It would be a great feature to have one more extra variable, which I could set in this query and which goes directly to log and nowhere else.

I have tried something like:

password_query =
SELECT CONCAT(users.email,'#',shadow.id) AS user, ...

and then "repair it" in user_query: SELECT email AS user WHERE email = REGEXP_SUBSTR('%u','[^#]+')

and it even works well, except postfix-auth, which received email with #number as the username and it was problematic.

So I'm kindly asking for this extra variable in next versions of Dovecot ;)

                                    Bogusław Juza

---

dovecot mailing list -- dovecot@dovecot.org To unsubscribe send an email to dovecot-leave@dovecot.org

Try setting

login_log_format_elements = $login_log_format_elements %{passdb:some_variable_name}

and try set it with

password_query = SELECT ... ,'something' AS some_variable_name,

Hopefully it works.

Aki