From: "Mark Williams" <markwillimas@gmail.com>
To: "'Wim Bertels'" <wim.bertels@ucll.be>,
	"'Tim Cross'" <theophilusx@gmail.com>
Cc: <pgsql-admin@lists.postgresql.org>,
	<s.dunand@sirap.fr>
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>
 <000e01d43e32$f35aad40$da1007c0$@gmail.com>
 <87efejihy9.fsf@gmail.com>,<013b01d43f00$527caf80$f7760e80$@gmail.com>
 <1535615748971.37236@ucll.be>
In-Reply-To: <1535615748971.37236@ucll.be>
Subject: RE: FW: Setting up SSL for postgre
Date: Thu, 30 Aug 2018 12:57:54 +0100
Message-ID: <027801d44058$afa40a10$0eec1e30$@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: 
 AQJ64nPKQfAewiPZ9t7gQ7SiT+1fnwIMhi/1AcD6KUcCiXXq5QDWmBXZAcT4CjwCSq7bNgG5QIOHAf1Z0v6jE0wGcA==
Content-Language: en-gb
Precedence: bulk

Hi,

I have finally discovered the problem and thanks to everyone for their help.

I have changed the 
Pg_hha.conf file to md5 clientcert=1 instead of just cert.

It still didn't work and I read a suggestion on a link provided by Wim which
suggested change sslmode to verify-ca. 

This threw up a new error, namely that it couldn't find the root certificate
at the location I had specified. The reason for this was that although my
file path was being ready by FireDAC correctly, when it was passed through
to Postgre, it was removing the path delimiters. The answer was to escape
the delimiters with a backslash eg "c:\\pathtomycerts\\postgre.sql.cert"

I'm assuming you guys are all on Linux and don't have this problem.

For the benefit of future Windows users, who may be tempted to give up on
Postgre due to the agony of trying to connect with SSL it would be well
worth a little addition to the manual at
https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONN
STRING to let Windows users know they need to escape their path delimiters.

I will let Embarcadero know of this issue for FireDAC users.

Aside from that little niggle, it's great to know that Postgre users are so
willing to help. Many thanks again.


__

-----Original Message-----
From: Wim Bertels <wim.bertels@ucll.be> 
Sent: 30 August 2018 08:56
To: Mark Williams <markwillimas@gmail.com>; 'Tim Cross'
<theophilusx@gmail.com>
Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Subject: Re: FW: Setting up SSL for postgre

Hallo Mark,

in your pg_hha.conf you have used

      cert

as authentication,
which is authorization using a certificate (not a password) (as mailed
before with documentation links)

did you test pgadmin and firedac from the same client machine?

hth,
Wim        
________________________________________
Van: Mark Williams <markwillimas@gmail.com>
Verzonden: dinsdag 28 augustus 2018 20:52
Aan: 'Tim Cross'
CC: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr; Wim Bertels
Onderwerp: RE: FW: Setting up SSL for postgre

Hi Tim,

Thanks for the reply.

Unfortunately, I don't know what private certificate authorisation is. I
assume this is different to SSL and is not the same as a self signed cert. I
have created my certificate with OpenSSL so I assume I am not in the arena
of private certificate authorisation.

Thanks for the tip re Debian, but sadly client and server are all Windows
machines.

I think I will put a plea out there to anyone who uses FireDAC and has
managed to get SSL working with Postgre. Absent anything useful there, I
will give up on Postgre.

All the best.

Mark

__

-----Original Message-----
From: Tim Cross <theophilusx@gmail.com>
Sent: 27 August 2018 23:05
To: Mark Williams <markwillimas@gmail.com>
Cc: pgsql-admin@lists.postgresql.org; s.dunand@sirap.fr
Subject: Re: FW: Setting up SSL for postgre


Mark Williams <markwillimas@gmail.com> writes:

>
>
>
>
> __
>
>
>
> 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.
>

I suspect Wim was referring to private certificate authentication rather
than connections over SSL - use the same basic technologies, but for
different goals.

While it may or may not be useful, I believe that recent versions of Debian
actually come with SSL connections enabled by default (using self signed
cert). Might provide the example you need?

Tim

--
Tim Cross

=