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 -- ## 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/
