From: Mark Williams <markwillimas@gmail.com>
To: 'Wim Bertels' <wim.bertels@ucll.be>
To: pgsql-admin@lists.postgresql.org
To: s.dunand@sirap.fr
Subject: RE: Setting up SSL for postgre
Date: Thu, 23 Aug 2018 17:53:55 +0100
Message-ID: <09bf01d43b01$e12213e0$a3663ba0$@gmail.com> (raw)
In-Reply-To: <1534925952974.49431@ucll.be>
References: <032401d43326$6c275d80$44761880$@gmail.com>
<CALBNtw6-BnnA9AGpaTfSecSnjppLApaKTsYJ664HtfzBt14nRg@mail.gmail.com>
<034901d4333f$d2aa7650$77ff62f0$@gmail.com>
<CALBNtw7+xDCocj6M-K6mna4Ujresami2YfvdfVS0Q-q-B5PFTg@mail.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>
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=hostaddr=192.168.0.12 port=5432 dbname=rees
user=postgres password=*** connect_timeout=10 sslmode=require
sslrootcert=C:\ProgramData\MWC\Viewer\Certs\root.crt
sslcert=C:\ProgramData\MWC\Viewer\Certs\postgresql.crt
sslkey=C:\ProgramData\MWC\Viewer\Certs\postgresql.key password=1234,
Result=$0000000003B262B0]
13222564840001 17:41:04.681 . ERROR: connection requires a valid client
certificate [Status=1]
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.postgresql.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 <mailto:markwillimas@gmail.com> >
Verzonden: maandag 20 augustus 2018 16:51
Aan: Wim Bertels; pgsql-admin@lists.postgresql.org
<mailto:pgsql-admin@lists.postgresql.org> ; s.dunand@sirap.fr
<mailto: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 < <mailto:wim.bertels@ucll.be> wim.bertels@ucll.be>
Sent: 20 August 2018 14:30
To: <mailto:pgsql-admin@lists.postgresql.org>
pgsql-admin@lists.postgresql.org; <mailto:s.dunand@sirap.fr>
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=letsencrypt+postgresql;
https://duckduckgo.com/?q=letsencrypt+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/>
https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentic
ation/
Best regards,
Stéphane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: markwillimas@gmail.com, wim.bertels@ucll.be, pgsql-admin@lists.postgresql.org, s.dunand@sirap.fr
Subject: RE: Setting up SSL for postgre
In-Reply-To: <09bf01d43b01$e12213e0$a3663ba0$@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox