Hi all,
I recently asked about configuring encrypted passwordless SSL based
database connections to a remote PostgreSQL cluster. With help I was able
to do so and wanted to report my findings (for testing purposes I am
working with local VMs but the process should remain the same).
First a little housekeeping. Jeremy Harris asked[0] to create a
wishlist bug report for implementing a proper key/value connection
string or a proper libpq connection URI, which I have done:
https://bugs.exim.org/show_bug.cgi?id=2808 I would like to try to
contribute to this at some point but no promises.
He also offered a potential workaround[1] that I was not able to get
working, but it was close.
Then Jasen Betts offered a hint[2], and I was eventually able to work
with that.
I first thought that things would be easier using macros to hold the
various pieces, so that I could redefine the macro as I went, easily
capturing my changes while keeping a clean pgsql_server string. So I
started by creating a PG role for password based authentication since I
already knew how to make that work, and added a pg_hba.conf entry above
the existing hostnossl reject rule on the PG cluster:
host all eximtest 192.168.45.0/24 scram-sha-256
hostnossl all all 0.0.0.0/0 reject
and then SIGHUPped the postmaster.
I verified that I could connect to the remote PG from the exim box
through the psql client, so I knew any Exim attempts that failed would
be due to the configuration and not something else.
Then I defined macros in the configuration file and tested the password
based access, which did work:
DB_HOST = 192.168.45.16
DB_NAME = exim
DB_PW = thisisatest
DB_USER = eximtest
pgsql_servers = DB_HOST/DB_NAME/DB_USER/DB_PW
So then I added a line to redefine the DB_NAME macro (still using the
test role name) :
DB_NAME == dbname=exim sslcert=client.crt sslkey=client.key sslmode=verify-ca sslrootcert=root.crt
That failed with:
Failed: lookup of "select generate_series(1,10) " gave DEFER: PGSQL connection failed: root certificate file "root.crt" does not exist
Either provide the file or change sslmode to disable server certificate verification.
I was a little stumped at that point. I was testing from
/usr/local/etc/exim, and the certificate was indeed present. I tried a
few different things to the DB_NAME value, such as quoting the redefined
contents, wrapping some and then all in parenthesis, doing both, etc. but
nothing changed the output. Then I ran /usr/local/sbin/exim -d +all -be
'${lookup pgsql{ select generate_series(1,10) }}' which didn't really
give me anything. However in looking over the output I noticed several
references to /var/spool/exim, such as:
lock name: /var/spool/exim/eximuser.lock.
So I moved the two certificates and the key file to /var/spool/exim. Bingo!
The final steps were to undo the pg_hba.conf changes for the test user,
and to redefine the DB_USER macro to be the correct DB role name, which
worked just fine. So the final configuration snippet is:
DB_HOST = 192.168.45.6
DB_NAME = dbname=exim sslcert=client.crt sslkey=client.key sslmode=verify-ca sslrootcert=root.crt
DB_PW = notused
DB_USER = thepguser
pgsql_servers = DB_HOST/DB_NAME/DB_USER/DB_PW
I am assuming at this point that the DB_PW portion is noise that the
PG cluster ignores (or at least doesn't parse) because it is set to
an invalid value but I see no sign of it in the PG log. In fact the
thepguser role has no password in the cluster.
And for completeness, the hide directive can be applied in the Exim
config if desired:
hide pgsql_servers = DB_HOST/DB_NAME/DB_USER/DB_PW
I have also found that other connection key/value pairs can be included,
such as connect_timeout
One last note, in case anyone is wondering why I'm using sslmode=verify-ca
instead of sslmode=verify-full. I gave the server's certificate the
wrong CN when I generated it. Oops! Will fix but haven't yet. :)
Regards,
Pat
[0]
https://lists.exim.org/lurker/message/20210910.081332.b5e3289d.en.html
[1]
https://lists.exim.org/lurker/message/20210910.083326.50f67601.en.html
[2]
https://lists.exim.org/lurker/message/20210910.120706.97a048c6.en.html
