Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h5wmp-0005DS-76 for pgsql-admin@arkaria.postgresql.org; Mon, 18 Mar 2019 18:14:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h5wmn-0004Q3-KQ for pgsql-admin@arkaria.postgresql.org; Mon, 18 Mar 2019 18:14:57 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h5wmn-0004Pw-9v for pgsql-admin@lists.postgresql.org; Mon, 18 Mar 2019 18:14:57 +0000 Received: from mail-wr1-x431.google.com ([2a00:1450:4864:20::431]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h5wmg-0003oE-H0 for pgsql-admin@lists.postgresql.org; Mon, 18 Mar 2019 18:14:56 +0000 Received: by mail-wr1-x431.google.com with SMTP id j9so4003845wrn.6 for ; Mon, 18 Mar 2019 11:14:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :thread-index:content-language; bh=7eBDIptaJRCsK7e7yJJ85XrrcRvyhXpJOcyXPKqSGYE=; b=MBcpZWhm8ju+xKlqdRzCedqAV2rFDyjyoQFnDHPSNAsm2wJiMXmlOjqWmnzD2WpaVX cWzKfp9u3hD0sMkK+wZBkTx8QFKcTkts3zafykymL8IkIG3lUuLmtidibTgWjyyOd0oV VdllVDcIGW+tgwP2vcaB0wmpW0ZKfeSGem3kR29fgAhvdsg1ZcxyAzuu1mDowA18q58+ b94fpah7wRaEcT+saZMj2w8JQxOOid5O7hJkGEz7RgueiYnfZf3E22B/NCsv4FQ5afU+ s5tYcBKQpC8EphEJdvdGkS4h23YUui0byuUmS8QlT2cbpLvSQnqFpnJ/tUR1X1ulXxcw kctg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=7eBDIptaJRCsK7e7yJJ85XrrcRvyhXpJOcyXPKqSGYE=; b=E/HnlbZv6FTQMcLlXT82gvSgBxiTsXRCXnrLhrbXGiDsrHL8e5AZDT4PjgT71ooYn/ gy7Zr2nRXk3HS/9jXfv2PymsvjLbqeY7r9cf5yj5tSPC7/7sGfPAgzX60FY7pqDcaOBJ NlSWYrzYYuntds3VtALGLs9c1Af5LSBtg7POvzDh9xILPzBk/DxUsRmdY4zCHDAU/O2X wra5FpxEC/lPa5ylOb7A+B/TPeF2CgTgSGHPJ9HHiM961YXngfvADGoZ1Rml0XN89Nc1 UREhqqYir9434GUQqqrR2hgxySUxHtRPqrXI49KkuQciY5GGri3LA7YhRyru2KGadTgn n2Qw== X-Gm-Message-State: APjAAAXzaXt90A79tvPJsCHSr6IgHfR5VsEv10Fn7NkyNgNOkYRb78q5 teB0Q/5Oblh+m30SGnFFCK3D43mF X-Google-Smtp-Source: APXvYqxbBQt6RfpWRDQd7sMZOKwDQucr3ESuiuxnB+HWawA3ib+O7P1wW1LXWbR+c/hCUTXCMNR9YQ== X-Received: by 2002:a5d:4e87:: with SMTP id e7mr6922088wru.161.1552932888992; Mon, 18 Mar 2019 11:14:48 -0700 (PDT) Received: from LAPTOPE7EH7MDP (167.221-30-62.static.virginmediabusiness.co.uk. [62.30.221.167]) by smtp.gmail.com with ESMTPSA id n189sm60702wmb.28.2019.03.18.11.14.47 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 18 Mar 2019 11:14:48 -0700 (PDT) From: "Mark Williams" To: References: In-Reply-To: Subject: FW: PostgreSQL SSL specifying certificate paths Date: Mon, 18 Mar 2019 18:14:45 -0000 Message-ID: <008d01d4ddb6$77c03aa0$6740afe0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_008E_01D4DDB6.77C0FDF0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AdTdsU4AZOnhfQyaRfq9gNkCaP8+2wABP6og Content-Language: en-gb List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_008E_01D4DDB6.77C0FDF0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I have discovered what the problem is. Firedac is now wrapping the conninfo in single quotes before submitting it! __ From: Mark Williams Sent: 18 March 2019 17:51 To: 'pgsql-admin@lists.postgresql.org' Subject: PostgreSQL SSL specifying certificate paths 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 __ ------=_NextPart_000_008E_01D4DDB6.77C0FDF0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I have discovered what the problem is. Firedac is now = wrapping the conninfo in single quotes before submitting = it!

 

__

 

From: Mark Williams = <markwillimas@gmail.com>
Sent: 18 March 2019 = 17:51
To: 'pgsql-admin@lists.postgresql.org' = <pgsql-admin@lists.postgresql.org>
Subject: PostgreSQL = SSL specifying certificate paths

 

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=3Dverify-ca = sslrootcert=3DC:\\ProgramData\\MWC\\Viewer\\Certs\\root.crt = sslcert=3DC:\\ProgramData\\MWC\\Viewer\\Certs\\postgresql.crt = sslkey=3DC:\\ProgramData\\MWC\\Viewer\\Certs\\postgresql.key.<= /p>

 

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

 

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

 

Which is producing a Postgre error = of:

 

ERROR: invalid sslmode = value: "verify-ca = sslrootcert=3DC:\\ProgramData\\MWC\\Viewer\\Certs\\root.crt = sslcert=3DC:\\ProgramData\\MWC\\Viewer\\Certs\\postgresql.crt = sslkey=3DC:\\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=3DC:\ProgramData\MWC\Viewer\Certs\root.crt = sslcert=3DC:\ProgramData\MWC\Viewer\Certs\postgresql.crt = sslkey=3DC:\ProgramData\MWC\Viewer\Certs\postgresql.key

 

I then tried = forward slashes:

 

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

 

And got the = error:

 

ERROR: invalid sslmode = value: "verify-ca = sslrootcert=3DC:/ProgramData/MWC/Viewer/Certs/root.crt = sslcert=3DC:/ProgramData/MWC/Viewer/Certs/postgresql.crt = sslkey=3DC:/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=3D'verify-ca = sslrootcert=3D/C/ProgramData//MWC/Viewer/Certs/root.crt = sslcert=3D/C/ProgramData/MWC/Viewer/Certs/postgresql.crt = sslkey=3D/C/ProgramData/MWC/Viewer/Certs/postgresql.key'

 

And got the = error:

 

ERROR: invalid sslmode = value: "verify-ca = sslrootcert=3D/C/ProgramData//MWC/Viewer/Certs/root.crt = sslcert=3D/C/ProgramData/MWC/Viewer/Certs/postgresql.crt = sslkey=3D/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

 

 

__

 

------=_NextPart_000_008E_01D4DDB6.77C0FDF0--