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.
--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/
