Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fuMAG-0007Rd-Kc for pgsql-admin@arkaria.postgresql.org; Mon, 27 Aug 2018 18:23:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fuMAE-0000me-1O for pgsql-admin@arkaria.postgresql.org; Mon, 27 Aug 2018 18:22:58 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fuMAD-0000mX-6r for pgsql-admin@lists.postgresql.org; Mon, 27 Aug 2018 18:22:57 +0000 Received: from mail-wm0-x242.google.com ([2a00:1450:400c:c09::242]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fuMA4-0006SK-Nu for pgsql-admin@lists.postgresql.org; Mon, 27 Aug 2018 18:22:55 +0000 Received: by mail-wm0-x242.google.com with SMTP id q8-v6so9173052wmq.4 for ; Mon, 27 Aug 2018 11:22:48 -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=//ZBY/jgYq9qpzO5LQ2dJ5wHL3MbCb8NZaf5KWY7RJ4=; b=ri4rY+z9XBoCBBuxey8YWE4bOjqZ8INxJkSaENGPspC81o5JY9G+++Fq649wNcR+Kd XvHSRJejfKn5xgwnTBv6wIxJrkIa5ZPU21ScMpAbT811u2E1Ncm2VP5PXhhprU+7ztIa aNiq12C4FXBVGHRtJYJ4rlvzhr3uCetgoi9UwyW4sWGz3wwi6u9iuWmqp4Lwoj83H8Ey RY7DGlVWrSe9mIn45HpRYmfERqBZmRdM7l/NGGH7pc9nCEW+vvPhnax7fNEf34J1vMLS 1HlzKTFoDXem6NZQxPhgA6ibNc1Gqj/CHwI+OUxCztaHqm1aJhhezsdnwp4KXZZECblg qQig== 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=//ZBY/jgYq9qpzO5LQ2dJ5wHL3MbCb8NZaf5KWY7RJ4=; b=goveoBbwx29cwk3E1rtRrkOm+hEW3rPrG3sFIsCu0GImokKxBXCOPWslP/Qv1dlg8c LmVriU+MZlYzaiUDCcfimwt0WouyvYlCQvNKqYCqgdv5Zcqui3TrxmK7+/SswFHrXa0S RiwRfkLkI1JHDr2lDMC63MajBCqrBCpEEd6tucn0UiS8YDogpka/AcEpaNOzJOH1U6X5 TJnICXSr5sDvEqfAl3eRUaFe9xErwxqZgkAlF4cuHSQR9XmbDXy+z3oVZZLrJMU7Ztjk bnlmoBjlcWOp91C7vLK8VU/dcWsud4iLc/CJdtpwgWEkn3ix3EL7QnXoECY2CN9grUUR MF2w== X-Gm-Message-State: APzg51CzNTcaC9CihCFbpbyvOrm/M3WrCwL3WM2xqi3PNfhxDJam7rSQ o/cEnirM/YXyIAq6m05NQxK86K4m X-Google-Smtp-Source: ANB0VdYMI0LzK24tmhAWRKRT4bC3Xtd0Xi3TAqEpmQ225OK1seGSHkUiRsoMrmU8mpBrbtRVW6UleA== X-Received: by 2002:a1c:838a:: with SMTP id f132-v6mr6426589wmd.127.1535394166851; Mon, 27 Aug 2018 11:22:46 -0700 (PDT) Received: from LAPTOPE7EH7MDP (167.221-30-62.static.virginmediabusiness.co.uk. [62.30.221.167]) by smtp.gmail.com with ESMTPSA id 60-v6sm20908291wre.82.2018.08.27.11.22.45 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 27 Aug 2018 11:22:45 -0700 (PDT) From: "Mark Williams" To: , References: <032401d43326$6c275d80$44761880$@gmail.com> <034901d4333f$d2aa7650$77ff62f0$@gmail.com> <076401d43883$89ec6830$9dc53890$@gmail.com>,<4ea1e251-0aa2-be17-4405-d1ccec951ecc@sirap.fr> <1534771775784.73685@ucll.be>,<077b01d43895$51aad620$f5008260$@gmail.com> <1534925952974.49431@ucll.be>,<09bf01d43b01$e12213e0$a3663ba0$@gmail.com> <1535104761898.17646@ucll.be> In-Reply-To: Subject: FW: Setting up SSL for postgre Date: Mon, 27 Aug 2018 19:22:44 +0100 Message-ID: <000e01d43e32$f35aad40$da1007c0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000F_01D43E3B.55211110" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJ64nPKQfAewiPZ9t7gQ7SiT+1fnwIMhi/1AcD6KUcCiXXq5QDWmBXZAZlEObIB3ICezALgGU7vAsAuaGQBRyWFQgGcI70louoxBqCAAzjJwA== 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_000F_01D43E3B.55211110 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable =20 =20 __ =20 From: Mark Williams =20 Sent: 25 August 2018 18:14 To: 'Wim Bertels' Subject: RE: Setting up SSL for postgre =20 Hi Wim, =20 I don=92t understand. If I don=92t include the password option, the = connection will be refused because I have not included it. =20 I am connecting via PGAdmin with the same user ie postgres. =20 Re the log on the =93windows machine=94 both server and client are = windows machines. Which log should I check? =20 Finally, I=92m pretty sure FireDAC doesn=92t any restrictions on = self-certified connections. I connect to MySQL over SSL via FireDAC with a = self-certified certificate. =20 Thanks =20 Mark =20 __ =20 From: Wim Bertels >=20 Sent: 24 August 2018 10:59 To: Mark Williams >; pgsql-admin@lists.postgresql.org = ; s.dunand@sirap.fr =20 Subject: Re: Setting up SSL for postgre =20 Hallo Mark, =20 you should not include the password option, so possibly you are connecting with the password in pgadmin (with = another user) .. instead of the cert meth; =20 another option: check the posgresql log on the windows machine=20 =20 sslmode=3Drequire: firedac might require a valid (official or installed) certificate? =20 maybe check: https://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-CER= T https://www.postgresql.org/docs/10/static/runtime-config-connection.html#= RUN TIME-CONFIG-CONNECTION-SECURITY https://www.postgresql.org/docs/10/static/ssl-tcp.html https://www.postgresql.org/docs/10/static/libpq-ssl.html =20 hth, Wim =20 _____ =20 Van: Mark Williams > Verzonden: donderdag 23 augustus 2018 18:53 Aan: Wim Bertels; pgsql-admin@lists.postgresql.org ; s.dunand@sirap.fr =20 Onderwerp: RE: Setting up SSL for postgre=20 =20 Hi Wim, =20 I did intend Cert aut (at least I think I did!). =20 Still cannot connect to postgre database from my client app using = FireDAC. I can connect fine from PGAdmin3 on the same machine using the same certificates. =20 The call made by FireDAC to libPQ.Dll is the following: =20 PQconnectdb [ConnInfo=3Dhostaddr=3D192.168.0.12 port=3D5432 = dbname=3Drees user=3Dpostgres password=3D*** connect_timeout=3D10 sslmode=3Drequire sslrootcert=3DC:\ProgramData\MWC\Viewer\Certs\root.crt sslcert=3DC:\ProgramData\MWC\Viewer\Certs\postgresql.crt sslkey=3DC:\ProgramData\MWC\Viewer\Certs\postgresql.key password=3D1234, Result=3D$0000000003B262B0] 13222564840001 17:41:04.681 . ERROR: connection requires a valid = client certificate [Status=3D1] =20 The SSLmode is set to require when I connect with PGAdmin. So = presumably, there is no problem with the certificates. Is there anything that jumps = out from the FireDAC output as to why the SSL connection doesn=92t work? =20 Many thanks, =20 Mark __ =20 From: Wim Bertels >=20 Sent: 22 August 2018 09:19 To: Mark Williams >; pgsql-admin@lists.postgresql.org = ; s.dunand@sirap.fr =20 Subject: Re: Setting up SSL for postgre =20 Hallo Mark, =20 as i quickly read the error message in your question, these we're my first suggestions. =20 either * did you intent cert aut for the postgres user? * u use a selfsigned certificate, hence software that checks for the validity will fail or ask for this ** using for example the free, but official letsencrypt certificates = this should be solved =20 hth, Wim =20 _____ =20 Van: Mark Williams > Verzonden: maandag 20 augustus 2018 16:51 Aan: Wim Bertels; pgsql-admin@lists.postgresql.org ; s.dunand@sirap.fr =20 Onderwerp: RE: Setting up SSL for postgre=20 =20 Hi, =20 Sorry I don=92t understand what you are suggesting re the pg_hba file. =20 __ =20 From: Wim Bertels < wim.bertels@ucll.be>=20 Sent: 20 August 2018 14:30 To: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr Subject: Re: Setting up SSL for postgre =20 =20 =20 pg_hba.conf # TYPE DATABASE USER CIDR-ADDRESS METHOD =20 # IPv4 local & remote connections: host all all 127.0.0.1/32 trust hostssl all postgres 0.0.0.0/0 cert=20 =20 cert method for auth, hence this behaviour (client cert..) =20 extra tip:=20 https://duckduckgo.com/?q=3Dletsencrypt+postgresql for official server side certificates =20 mvg, Bertels Wim =20 Mark __ =20 This page helped me : =20 https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authen= tic ation/ Best regards, St=E9phane ------=_NextPart_000_000F_01D43E3B.55211110 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

 

 

__

 

From: Mark Williams = <markwillimas@gmail.com>
Sent: 25 August 2018 = 18:14
To: 'Wim Bertels' = <wim.bertels@ucll.be>
Subject: RE: Setting up SSL for = postgre

 

Hi Wim,

 

I = don’t understand. If I don’t include the password option, = the connection will be refused because I have not included = it.

 

I am = connecting via PGAdmin with the same user ie = postgres.

 

Re the log = on the “windows machine” both server and client are windows = machines. Which log should I check?

 

Finally, = I’m pretty sure FireDAC doesn’t any restrictions on = self-certified connections. I connect to MySQL over SSL via FireDAC with = a self-certified certificate.

 

Thanks

 

Mark

 

__

 

From: Wim Bertels <wim.bertels@ucll.be> =
Sent: 24 August 2018 10:59
To: Mark Williams <markwillimas@gmail.com>; = pgsql-admin@lists.postgr= esql.org; s.dunand@sirap.fr
Subject: Re: Setting up SSL for postgre

 

Hallo = Mark,

 

you should not include the = password option,

so possibly you are = connecting with the password in pgadmin (with another user) .. instead = of the cert meth;

 

another option: check the = posgresql log on the windows machine

 

sslmode=3Drequire: firedac = might require a valid (official or installed) = certificate?

 

maybe = check:

https://www.postgresql.org/docs/current/static/auth-methods.ht= ml#AUTH-CERT

https://www.postgresql.org/do= cs/10/static/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SEC= URITY

https://w= ww.postgresql.org/docs/10/static/ssl-tcp.html

https:/= /www.postgresql.org/docs/10/static/libpq-ssl.html

 

hth,

Wim

 

<= div class=3DMsoNormal align=3Dcenter style=3D'text-align:center'>

Van: Mark Williams <markwillimas@gmail.com>
= Verzonden: donderdag 23 augustus 2018 18:53
Aan: Wim = Bertels; pgsql-admin@lists.postgr= esql.org; s.dunand@sirap.fr
Onderwerp:<= /b> RE: Setting up SSL for postgre

 

Hi = Wim,

 

I did intend Cert aut = (at least I think I did!).

 

Still cannot connect to = postgre database from my client app using FireDAC. I can connect fine = from PGAdmin3 on the same machine using the same = certificates.

 

The call made by FireDAC = to libPQ.Dll is the following:

 

PQconnectdb = [ConnInfo=3Dhostaddr=3D192.168.0.12 port=3D5432 dbname=3Drees = user=3Dpostgres password=3D*** connect_timeout=3D10 sslmode=3Drequire = sslrootcert=3DC:\ProgramData\MWC\Viewer\Certs\root.crt = sslcert=3DC:\ProgramData\MWC\Viewer\Certs\postgresql.crt = sslkey=3DC:\ProgramData\MWC\Viewer\Certs\postgresql.key password=3D1234, = Result=3D$0000000003B262B0]

13222564840001 = 17:41:04.681      . ERROR: connection requires = a valid client certificate [Status=3D1]

 

The SSLmode is set to = require when I connect with PGAdmin. So presumably, there is no problem = with the certificates. Is there anything that jumps out from the FireDAC = output as to why the SSL connection doesn’t = work?

 

Many = thanks,

 

Mark

__

 

From: Wim Bertels <wim.bertels@ucll.be> =
Sent: 22 August 2018 09:19
To: Mark Williams <markwillimas@gmail.com>; = pgsql-admin@lists.postgr= esql.org; s.dunand@sirap.fr
Subject: Re: Setting up SSL for postgre

 

Hallo Mark,

 

as i quickly read the error = message in your question,

these we're my first = suggestions.

 

either

* did you intent cert aut for the = postgres user?

* u use a selfsigned certificate, = hence software that checks for the validity will fail or ask for = this

** using for example the free, = but official letsencrypt certificates this should be solved

 

hth,

Wim

 

Van: Mark Williams <markwillimas@gmail.com<= span style=3D'color:black'>>
Verzonden: maandag 20 augustus = 2018 16:51
Aan: Wim Bertels; pgsql-admin@lists.postgr= esql.org; s.dunand@sirap.fr
Onderwerp: RE: Setting up SSL for = postgre =

 

Hi,

 

Sorry I don’t = understand what you are suggesting re the pg_hba = file.

 

__

 

From: Wim Bertels <wim.bertels@ucll.be>
Sent: 20 August 2018 = 14:30
To: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Subject: Re: Setting up SSL for = postgre

 

 

 

pg_hba.conf

# TYPE  = DATABASE        = USER            = CIDR-ADDRESS       =      METHOD

 

# IPv4 local & remote = connections:

host    = all           &nbs= p; = all           &nbs= p; = 127.0.0.1/32          &= nbsp; trust

hostssl = all         = postgres    = 0.0.0.0/0          &nbs= p;  cert

 

cert = method for auth, hence this behaviour (client cert..)

 

extra tip:

https://duckduckgo.com/?q=3Dletsencrypt+postgr= esql

for official server side = certificates

 

mvg,

 Bertels Wim

 

Mark

__

 


This page helped me = :
https://www.depesz.com/2015/05/11/how-to-setup= -ssl-connections-and-authentication/

Best = regards,
St=E9phane

------=_NextPart_000_000F_01D43E3B.55211110--