PostgreSQL SSL specifying certificate paths

From: "Mark Williams" <markwillimas(at)gmail(dot)com>
To: <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: PostgreSQL SSL specifying certificate paths
Date: 2019-03-18 17:51:19
Message-ID: 008001d4ddb3$31b5f5c0$9521e140$@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I have had a problem previously connecting to PostgreSQL using Firedac. I
managed to get this working, but then a recent update of Rad Studio has
somehow changed the way that it passes through the cert file paths. I think
this was to address an issue I raised about path delimiters being stripped
away.

To overcome the problem I was specifying the paths as follows:

sslmode=verify-ca sslrootcert=C:\\ProgramData\\MWC\\Viewer\\Certs\\root.crt
sslcert=C:\\ProgramData\\MWC\\Viewer\\Certs\\postgresql.crt
sslkey=C:\\ProgramData\\MWC\\Viewer\\Certs\\postgresql.key.

The new version of FireDac seems to be inserting escape characters itself so
that it is passing through the "ConnInfo" as:

sslmode='verify-ca
sslrootcert=C:\\\\ProgramData\\\\MWC\\\\Viewer\\\\Certs\\\\root.crt
sslcert=C:\\\\ProgramData\\\\MWC\\\\Viewer\\\\Certs\\\\postgresql.crt
sslkey=C:\\\\ProgramData\\\\MWC\\\\Viewer\\\\Certs\\\\postgresql.key'

Which is producing a Postgre error of:

ERROR: invalid sslmode value: "verify-ca
sslrootcert=C:\\ProgramData\\MWC\\Viewer\\Certs\\root.crt
sslcert=C:\\ProgramData\\MWC\\Viewer\\Certs\\postgresql.crt
sslkey=C:\\ProgramData\\MWC\\Viewer\\Certs\\postgresql.key

Naturally, I recoded so that I specified non-escaped windows paths and
received the following error:

ERROR: invalid sslmode value: "verify-ca
sslrootcert=C:\ProgramData\MWC\Viewer\Certs\root.crt
sslcert=C:\ProgramData\MWC\Viewer\Certs\postgresql.crt
sslkey=C:\ProgramData\MWC\Viewer\Certs\postgresql.key

I then tried forward slashes:

sslmode='verify-ca sslrootcert=C:/ProgramData/MWC/Viewer/Certs/root.crt
sslcert=C:/ProgramData/MWC/Viewer/Certs/postgresql.crt
sslkey=C:/ProgramData/MWC/Viewer/Certs/postgresql.key'

And got the error:

ERROR: invalid sslmode value: "verify-ca
sslrootcert=C:/ProgramData/MWC/Viewer/Certs/root.crt
sslcert=C:/ProgramData/MWC/Viewer/Certs/postgresql.crt
sslkey=C:/ProgramData/MWC/Viewer/Certs/postgresql.key

I tried double forward slashes to no avail and then I tried Linux paths or
at least what in my almost non-existent Linux experience I think is a Linus
path:

sslmode='verify-ca sslrootcert=/C/ProgramData//MWC/Viewer/Certs/root.crt
sslcert=/C/ProgramData/MWC/Viewer/Certs/postgresql.crt
sslkey=/C/ProgramData/MWC/Viewer/Certs/postgresql.key'

And got the error:

ERROR: invalid sslmode value: "verify-ca
sslrootcert=/C/ProgramData//MWC/Viewer/Certs/root.crt
sslcert=/C/ProgramData/MWC/Viewer/Certs/postgresql.crt
sslkey=/C/ProgramData/MWC/Viewer/Certs/postgresql.key

Is anybody able to show me in what format the conninfo parameter of
PostgreSQL expects to receive a Windows path please?

Thanks

Mark

__

Browse pgsql-admin by date

  From Date Subject
Next Message Mark Williams 2019-03-18 18:14:45 FW: PostgreSQL SSL specifying certificate paths
Previous Message wambacher 2019-03-18 08:50:11 Re: Can't stop pgAdmin4 server process