Mailing List Archive

Solved: Encrypted SSL Postgres Connection
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/
Re: Solved: Encrypted SSL Postgres Connection [ In reply to ]
Pat via Exim-users <exim-users@exim.org> writes:

> 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 [...].

Thanks for the nice writeup. Does using an explicit path to the
certificate work instead of moving it into /var/spool/exim?
--
Regards,
Feri

--
## 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/
Re: Solved: Encrypted SSL Postgres Connection [ In reply to ]
On 2021-09-16, Pat via Exim-users <exim-users@exim.org> wrote:

> 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!

This is interesting. it will be hard (impossible) to use slashes in
the database parameters, so yes, you will need to put the key file (or
a symlink that points to it) in the spool directory.

This explains why the bug report is also asking for the option to use
URL style connection strings. that would allow slashes.

> 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.

Exim passes it to libpq. what libpq does with the parameters it gets
from exim is up to the postgresql developers.


--
Jasen.

--
## 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/
Re: Solved: Encrypted SSL Postgres Connection [ In reply to ]
??????? Original Message ???????
On Friday, September 17, 2021 12:46 PM, <wferi@niif.hu> wrote:

> Pat via Exim-users exim-users@exim.org writes:
>
> > 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 [...].
>
> Thanks for the nice writeup. Does using an explicit path to the
> certificate work instead of moving it into /var/spool/exim?

That is an excellent question. I was not able to use any path specifiers
at all. I did try. That was some of the quoting and parenthesis playing
that I did, but could not hit on a working solution. The only thing that
worked was moving the files to /var/spool/exim

>
> ------------------------------------------------------------------------------------------------------------------------------
>
> Regards,
> Feri



--
## 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/
Re: Solved: Encrypted SSL Postgres Connection [ In reply to ]
(Apologies to Jasen for the inadvertent direct reply)

??????? Original Message ???????
On Friday, September 17, 2021 1:06 PM, Jasen Betts via Exim-users <exim-users@exim.org> wrote:

> On 2021-09-16, Pat via Exim-users exim-users@exim.org wrote:
>
> > 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!
>
> This is interesting. it will be hard (impossible) to use slashes in
> the database parameters, so yes, you will need to put the key file (or
> a symlink that points to it) in the spool directory.

That does fit with what I observed. The only reason I didn't try links
was because I recently saw an authentication issue that "seemed" to be
related to a symbolic link to a certificate. That doesn't sound too
plausible but pointing to the actual file resolved the issue. We did
not dig too deep into the root cause after that.

>
> This explains why the bug report is also asking for the option to use
> URL style connection strings. that would allow slashes.
>
> > 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.
>
> Exim passes it to libpq. what libpq does with the parameters it gets
> from exim is up to the postgresql developers.

That is what I surmised, and it looks like from there libpq maybe quits
paying attention once it receives a full, syntatically correct connection
string? I could try looking through the code, or maybe even turning
up the PG logging to see if that shows it, but knowing where and how
that part gets consumed doesn't help much so I probably won't, or not
until I'm bored enough to re-visit that. :)

Appreciate the pointer that got this working, as well as Jeremy's help.

>
>
> ----------------------------------------------------------------------------------------------------------------------
>
> Jasen.
>
> -------
>
> 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/
>
> -----------------------------------------------------------



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