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 1ft97f-0006jS-Fz for pgsql-admin@arkaria.postgresql.org; Fri, 24 Aug 2018 10:15:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ft97a-0001h0-Vj for pgsql-admin@arkaria.postgresql.org; Fri, 24 Aug 2018 10:15:14 +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 1ft8sV-0000ea-OD for pgsql-admin@lists.postgresql.org; Fri, 24 Aug 2018 09:59:40 +0000 Received: from asp-level3-out1.belnet.be ([2001:6a8:3c80::154]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ft8sM-0002mC-C7 for pgsql-admin@lists.postgresql.org; Fri, 24 Aug 2018 09:59:38 +0000 Received: from ANTLER.int.ucll.be (antler.ucll.be [193.190.138.14]) by asp-level3-out1.belnet.be (8.14.4/8.14.4/Debian-4+deb7u1) with ESMTP id w7O9xNho011012 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT); Fri, 24 Aug 2018 11:59:23 +0200 Received: from Mistik.int.ucll.be (10.21.7.40) by webmail.ucll.be (193.190.138.14) with Microsoft SMTP Server (TLS) id 15.0.1293.2; Fri, 24 Aug 2018 11:59:22 +0200 Received: from ROLLING.int.ucll.be (10.21.7.30) by Mistik.int.ucll.be (10.21.7.40) with Microsoft SMTP Server (TLS) id 15.0.1293.2; Fri, 24 Aug 2018 11:59:22 +0200 Received: from ROLLING.int.ucll.be ([fe80::f93b:729:9ace:3688]) by ROLLING.int.ucll.be ([fe80::f93b:729:9ace:3688%14]) with mapi id 15.00.1293.006; Fri, 24 Aug 2018 11:59:22 +0200 From: Wim Bertels To: Mark Williams , "pgsql-admin@lists.postgresql.org" , "s.dunand@sirap.fr" Subject: Re: Setting up SSL for postgre Thread-Topic: Setting up SSL for postgre Thread-Index: AQJ64nPKQfAewiPZ9t7gQ7SiT+1fnwIMhi/1AcD6KUcCiXXq5aM+/62ggAmE0jD//+mFgIAAKFbm///2JwCAAtemcIACAXmAgAE6X5A= Date: Fri, 24 Aug 2018 09:59:21 +0000 Message-ID: <1535104761898.17646@ucll.be> 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> In-Reply-To: <09bf01d43b01$e12213e0$a3663ba0$@gmail.com> Accept-Language: nl-BE, en-GB, en-US Content-Language: nl-BE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [10.16.6.35] Content-Type: multipart/alternative; boundary="_000_153510476189817646ucllbe_" MIME-Version: 1.0 X-Spam-Score: undef - relay 193.190.138.14 marked with skip_spam_scan X-CanIt-Geo: ip=193.190.138.14; country=BE; region=Flanders; city=Leuven; latitude=50.8833; longitude=4.7000; http://maps.google.com/maps?q=50.8833,4.7000&z=6 X-CanItPRO-Stream: ucll-out:out (inherits from ucll-out:default,ucll-in:default,advanced:default,base:default) X-Canit-Stats-ID: Bayes signature not available X-Scanned-By: CanIt (www . roaringpenguin . com) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_153510476189817646ucllbe_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hallo Mark, you should not include the password option, so possibly you are connecting with the password in pgadmin (with another u= ser) .. 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) ce= rtificate? maybe check: https://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-CERT https://www.postgresql.org/docs/10/static/runtime-config-connection.html#RU= NTIME-CONFIG-CONNECTION-SECURITY https://www.postgresql.org/docs/10/static/ssl-tcp.html https://www.postgresql.org/docs/10/static/libpq-ssl.html hth, Wim ________________________________ Van: Mark Williams Verzonden: donderdag 23 augustus 2018 18:53 Aan: Wim Bertels; pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr Onderwerp: 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 certifi= cates. The call made by FireDAC to libPQ.Dll is the following: PQconnectdb [ConnInfo=3Dhostaddr=3D192.168.0.12 port=3D5432 dbname=3Drees u= ser=3Dpostgres password=3D*** connect_timeout=3D10 sslmode=3Drequire sslroo= tcert=3DC:\ProgramData\MWC\Viewer\Certs\root.crt sslcert=3DC:\ProgramData\M= WC\Viewer\Certs\postgresql.crt sslkey=3DC:\ProgramData\MWC\Viewer\Certs\pos= tgresql.key password=3D1234, Result=3D$0000000003B262B0] 13222564840001 17:41:04.681 . ERROR: connection requires a valid clien= t certificate [Status=3D1] The SSLmode is set to require when I connect with PGAdmin. So presumably, t= here 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 Sent: 22 August 2018 09:19 To: Mark Williams ; pgsql-admin@lists.postgresql.or= g; 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 validi= ty will fail or ask for this ** using for example the free, but official letsencrypt certificates this s= hould be solved hth, Wim ________________________________ Van: Mark Williams > Verzonden: maandag 20 augustus 2018 16:51 Aan: Wim Bertels; pgsql-admin@lists.postgresql.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 > 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 all 127.0.0.1/32 trust hostssl all postgres 0.0.0.0/0 cert cert method for auth, hence this behaviour (client cert..) extra tip: https://duckduckgo.com/?q=3Dletsencrypt+postgresql 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-authenti= cation/ Best regards, St=E9phane --_000_153510476189817646ucllbe_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hallo Mark,


you should not include the password option,

so possibly you are connecting with the password in pgadmin (with a= nother 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 insta= lled) certificate?


maybe check:

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

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

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

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


hth,

Wim


Van: Mark Williams <ma= rkwillimas@gmail.com>
Verzonden: donderdag 23 augustus 2018 18:53
Aan: Wim Bertels; pgsql-admin@lists.postgresql.org; s.dunand@sirap.f= r
Onderwerp: RE: Setting up SSL for postgre
 

Hi Wim,

 

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

 

Still cannot connect to postgre dat= abase 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.D= ll is the following:

 

PQconnectdb [ConnInfo=3Dhostaddr=3D= 192.168.0.12 port=3D5432 dbname=3Drees user=3Dpostgres password=3D*** conne= ct_timeout=3D10 sslmode=3Drequire sslrootcert=3DC:\ProgramData\MWC\Viewer\C= erts\root.crt sslcert=3DC:\ProgramData\MWC\Viewer\Certs\postgresql.crt sslkey=3DC:\ProgramData\MWC\Viewer\Certs\postgresql.key password=3D1234, R= esult=3D$0000000003B262B0]

13222564840001 17:41:04.681 &n= bsp;    . ERROR: connection requires a valid client certific= ate [Status=3D1]

 

The SSLmode is set to require when = I connect with PGAdmin. So presumably, there is no problem with the certifi= cates. Is there anything that jumps out from the FireDAC output as to why t= he 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.= postgresql.org; s.dunand@sirap.fr
Subject: Re: Setting up SSL for postgre

 

Hallo Mark,

 

as i quickly read the erro= r message in your question,

these we're my first sugge= stions.

 

either

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

* u use a selfsigned certi= ficate, hence software that checks for the validity will fail or ask for th= is

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

 

hth,

Wim

 

Van: Mark Williams <markwillimas@gmail.com> Verzonden: maandag 20 augustus 2018 16:51
Aan: Wim Bertels; pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Onderwerp: RE: Setting up SSL for postgre

&nbs= p;

Hi,

 

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

 

__

 

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

 

 =

&nbs= p;

p= g_hba.conf

# TY= PE  DATABASE        USER  = ;          CIDR-ADDRESS &= nbsp;          METHOD

&nbs= p;

# IP= v4 local & remote connections:

host=     all         = ;    all        &nbs= p;    127.0.0.1/32       =      trust<= /p>

host= ssl all         postgres  = ;  0.0.0.0/0         &nbs= p;   cert

&nbs= p;

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

&nbs= p;

extr= a tip:

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

for = official server side certificates

&nbs= p;

mvg,=

&nbs= p;Bertels Wim

&nbs= p;

Mark=

__

&nbs= p;


This page helped me :
https://www.dep= esz.com/2015/05/11/how-to-setup-ssl-connections-and-authentication/<= /a>

Best regards,
St=E9phane

--_000_153510476189817646ucllbe_--