Re: [exim] DKIM and debian buster...

On 07/07/2020 00:23, Jeremy Harris via Exim-users wrote:
> On 07/07/2020 00:01, Mike Tubby via Exim-users wrote:
>> remote_smtp:
>>         driver = smtp
>>         dkim_domain = ${lc:${domain:$h_from:}}
>>         dkim_selector = ${lookup mysql{SELECT selector FROM dkim WHERE
>> domain='${quote_mysql:${dkim_domain}}' AND active=1}{$value}{false}}
>>         dkim_canon = ${lookup mysql{SELECT canon FROM dkim WHERE
>> domain='${quote_mysql:${dkim_domain}}' AND active=1}{$value}{false}}
>>         dkim_hash = ${lookup mysql{SELECT hash FROM dkim WHERE
>> domain='${quote_mysql:${dkim_domain}}' AND active=1}{$value}{false}}
>>         dkim_private_key = ${lookup mysql{SELECT private_key FROM dkim
>> WHERE domain='${quote_mysql:${dkim_domain}}' AND active=1}{$value}{false}}
>>         dkim_strict = 0
> I'd be tempted to roll all those queries together, and then extract
> the items from the one result string - just to cut down on DB load.

Fortunately my system is lightly loaded, I'm dealing with less than 20
domains and domain is a unique/primary key on the table.

I would nice to have a method to extract multiple results from a single
select statement, for example:

    set (dkim_selector, dkim_dkim_canon, dkim_private_key, dkim_hash) =
${lookup mysql {SELECT selector,canon,private_key, hash FROM dkim WHERE
domain='${quote_mysql:${dkim_domain}}' AND active=1}{$value}{false}}

in a composite statement to transfer all the results over in one go. 
This would also have applications elsewhere where the interface to MySQL
(databases in general?) is a little clunky, for example my vacation driver:

vacation_reply:
        driver = autoreply
        to = "${sender_address}"
        from = "${local_part}@${domain}"
        log = /var/spool/exim/log/vacation.log
        once =/var/spool/exim/db/vacation.db
        once_repeat = 1d
        subject = "${lookup mysql{SELECT subject FROM vacations LEFT JOIN \
                users ON vacations.user_id=users.id LEFT JOIN \
                domains ON users.domain_id=domains.id WHERE \
                users.username='${quote_mysql:$local_part}' AND \
                domains.domain='${quote_mysql:$domain}' \
                AND vacations.active=1 \
                AND users.active=1 AND \
                domains.active=1 \
                ORDER BY vacations.id DESC LIMIT 1}}"
        text = "${lookup mysql{SELECT message FROM vacations LEFT JOIN \
                users ON vacations.user_id=users.id LEFT JOIN \
                domains ON users.domain_id=domains.id WHERE \
                users.username='${quote_mysql:$local_part}' AND \
                domains.domain='${quote_mysql:$domain}' \
                AND vacations.active=1 \
                AND users.active=1 AND \
                domains.active=1 \
                ORDER BY vacations.id DESC LIMIT 1}}"
        file_optional = true
        user = mail
        group = mail


In this example users can have multiple vacation messages in the
database and the last, active, one will be used - but the query has to
be run twice - once for the subject and once for the body text ;-)


... and my authenticator, which grew sufficiently complicated I switched
to using a PERL shim with Exim and a PHP backend via Nginx and made the
whole thing webservices based, hence my authenticators became:

auth_plain:
        driver = plaintext
        public_name = PLAIN
        server_condition =
${perl{exim_auth}{$auth2}{$auth3}{$sender_host_address}}
        server_prompts = :
        server_set_id = $auth2

auth_login:
        driver = plaintext
        public_name = LOGIN
        server_condition =
${perl{exim_auth}{$auth1}{$auth2}{$sender_host_address}}
        server_prompts = Username:: : Password::
        server_set_id = $auth1



Mike


>
> Unfortunately set= isn't available in a transport; only in a router
> or acl. You could hack it via an acl expansion, or you could
> just rely on the lookup caching.
>
> I probably should add set= in transports, now I've seen this.