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 1frlv5-0004qk-1X for pgsql-admin@arkaria.postgresql.org; Mon, 20 Aug 2018 15:16:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1frlv1-0006yQ-F6 for pgsql-admin@arkaria.postgresql.org; Mon, 20 Aug 2018 15:16:35 +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_SHA384:256) (Exim 4.89) (envelope-from ) id 1frlv1-0006yJ-46 for pgsql-admin@lists.postgresql.org; Mon, 20 Aug 2018 15:16:35 +0000 Received: from mail-wm0-x22f.google.com ([2a00:1450:400c:c09::22f]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1frlus-00063k-2T for pgsql-admin@lists.postgresql.org; Mon, 20 Aug 2018 15:16:34 +0000 Received: by mail-wm0-x22f.google.com with SMTP id w24-v6so14220620wmc.1 for ; Mon, 20 Aug 2018 08:16:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:cc:references:in-reply-to:subject:date:message-id :mime-version:thread-index:content-language; bh=xR48UN/S4sBjLKkOF1FcQUx/L7IQU5mYh8W8HXKvHDs=; b=SQansCZLeRJ6veln5kszxzvQ0e5Vw4rQaP8F8LM3Mgo375h0oaVvum0g9kaD5y8rzB G9X+7TpwaXsxZooW8VqwZjvgkq/BN1/SJiawg+rJt4mDA+PM7sB7+t1fcEkqteHbsKpO Pe52Bq4bNG6vDGrzwKM7yFmlmKqfCStlqqLaXAGNcj1v9RfM1Tecrl3UezdywIm+imDa efU+5vRSh0WzRQSvNdgHI1VyB8Zs+CBC9x0buc17qML0NVmFi8BKOniYAN8CvFEfM/i8 orNENruqHRR/Asxy6/EPeKOVEX2UiIjkzdZIWdXDk9Gf3LbOXuGVXovvEaC/b31fr7RL 2ASQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:cc:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=xR48UN/S4sBjLKkOF1FcQUx/L7IQU5mYh8W8HXKvHDs=; b=B728Dj2Bl4VlLFKKR+XKsxV5EYcPDwz0NYpqctg5tbtNaVV5RjLWq1B9G0X45xRHyu LDPgrfSwN1tdA8XuXASnV+KnNWNqRkRhJQT8ZD33JN61R4moVMcU0VtrM880ZPHFy0Rr 4gJVNpmTV07qXsxkI14G4h13j1upcYqtlXawWZMM7WJLwxjRgCF7vKSmxgigdYBtjnVo b9wkT/K8YjFv8GpehNIPZ69VZvrfrAu/Do2dDe86D6bYnKoHkX81ktGGVl3ku2mq9lnk SshoDi4bnGnA7QjQE6r3CtkB7nqU/mtvXdpRXl0nd6tgd9lyTIY4G1KjWswtqK5zZV3G 4qeQ== X-Gm-Message-State: AOUpUlFJePlcH3PiVkfw41M07ZAfHkGn5nvL4GmgQohFfDB+8SrRb6qa j4qSKTNJ1mzB1tHqjy1KSkM= X-Google-Smtp-Source: AA+uWPza6mDI6OzvE0y6GSZND/GqILR1U7aZkSSCwD8vSHnlkDS+9vPQm685jPFT7ZiF5TlfzC+kbw== X-Received: by 2002:a1c:d0ce:: with SMTP id h197-v6mr24543170wmg.43.1534778185402; Mon, 20 Aug 2018 08:16:25 -0700 (PDT) Received: from LAPTOPE7EH7MDP (167.221-30-62.static.virginmediabusiness.co.uk. [62.30.221.167]) by smtp.gmail.com with ESMTPSA id t70-v6sm10775483wmt.30.2018.08.20.08.16.24 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 20 Aug 2018 08:16:24 -0700 (PDT) From: "Mark Williams" To: "'Evan Bauer'" , Cc: References: <032401d43326$6c275d80$44761880$@gmail.com> <034901d4333f$d2aa7650$77ff62f0$@gmail.com> <076401d43883$89ec6830$9dc53890$@gmail.com> <4ea1e251-0aa2-be17-4405-d1ccec951ecc@sirap.fr> In-Reply-To: Subject: RE: Setting up SSL for postgre Date: Mon, 20 Aug 2018 16:16:24 +0100 Message-ID: <078a01d43898$c2ad78c0$48086a40$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_078B_01D438A1.24758A40" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQJ64nPKQfAewiPZ9t7gQ7SiT+1fnwIMhi/1AcD6KUcCiXXq5QDWmBXZAZlEObICVxkxk6MifQeg 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_078B_01D438A1.24758A40 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks for the suggestion. Should have thought of that before. =20 I can connect remotely with PGAdmin and ssl. So must be a FireDAC = problem. =20 Thanks, =20 Mark =20 __ =20 From: Evan Bauer =20 Sent: 20 August 2018 14:36 To: s.dunand@sirap.fr Cc: pgsql-admin@lists.postgresql.org Subject: Re: Setting up SSL for postgre =20 Mark, =20 Have you tried a remote connection from the client with something other = than Delphi =E2=80=94 psql or pgAdmin =E2=80=94 to whether the the issue = is on the server or client side of the connection? =20 Cheers, =20 - Evan =20 Evan Bauer eb@evanbauer.com =20 +1 646 641 2973 Skype: evanbauer On Aug 20, 2018, at 09:02, St=C3=A9phane Dunand > wrote: =20 Le 20/08/2018 =C3=A0 14:44, Mark Williams a =C3=A9crit : I have started all over again to see if I can resolve this issue. = Unfortunately not. I am still pulling my hair out. =20 I am still following the instructions howtoforge. =20 I am working with pg10. I am trying to use SSL on a small network server = (running on Windows 7. I am trying to connect from a client machine = running on Windows 10. =20 Commands for certificate creation openssl genrsa -des3 -out c:\certs\server.key 1024 =20 openssl rsa -in c:\certs\server.key -out c:\certs\server.key =20 openssl req -new -key c:\certs\server.key -days 3650 -out = c:\certs\server.crt -x509 -subj '/ = = C=3DUK/ST=3DWales/L=3DCardiff/O=3DMWC/CN=3D192.168.0.12/emailAddress=3Din= fo@mwconsult.co.uk' =20 {192.168.0.12 is the ipaddress of the server machine on the local = network. =20 =20 cp server.crt root.crt {manually copied as on Windows} openssl genrsa -des3 -out c:\certs\postgresql.key 1024 =20 openssl rsa -in c:\certs\postgresql.key -out c:\certs\postgresql.key =20 openssl req -new -key c:\certs\postgresql.key -out = c:\certs\postgresql.csr -subj = '/C=3DUK/ST=3DWales/L=3DCardiff/O=3DMWC/CN=3Dpostgres' =20 openssl x509 -days 3650 -req -in c:\certs\postgresql.csr -CA = c:\certs\root.crt -CAkey c:\certs\server.key -out = c:\certs\postgresql.crt -CAcreateserial =20 I then copy the server.key, server.crt and root.crt file to the postgres = data folder on the server machine.=20 =20 Postgresql.conf listen_addresses =3D '*' ssl =3D on #ssl_ciphers =3D 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers #ssl_prefer_server_ciphers =3D on #ssl_ecdh_curve =3D 'prime256v1' #ssl_dh_params_file =3D '' ssl_cert_file =3D 'server.crt' ssl_key_file =3D 'server.key' ssl_ca_file =3D 'root.crt' #ssl_crl_file =3D '' #password_encryption =3D md5 # md5 or scram-sha-256 #db_user_namespace =3D off #row_security =3D on =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 # IPv6 local connections: host all all ::1/128 trust =20 I restart the service. =20 Client Machine I am trying to connect from an application written in Delphi and using = FireDAC. The FireDAC params are set as follows Params.Values['UseSSL'] :=3D 'True'; Params.values['SSL_ca'] :=3D sslCertsPath + 'root.crt'; Params.values['SSL_cert'] :=3D sslCertsPath + 'postgresql.crt.'; Params.values['SSL_key'] :=3D sslCertsPath + 'postgresql.key'; =20 The client certs are copied to =E2=80=9CsslCertsPath=E2=80=9D =20 When I connect I get the =E2=80=9Cconnection requires a valid client = certificate=E2=80=9D error. =20 Is there something else I need to do? Do I have to added any of the = self-certified certificates to the Windows Trusted certificate store = and, if so, which ones on which machines? =20 Hopefully, somebody can work out why this connection fails, if not, I = can see no alternative to booking myself in t Dignitas! =20 Many thanks. =20 Mark __ =20 This page helped me : = = https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authen= tication/ Best regards, St=C3=A9phane =20 ------=_NextPart_000_078B_01D438A1.24758A40 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Thanks for the suggestion. Should = have thought of that before.

 

I can = connect remotely with PGAdmin and ssl. So must be a FireDAC = problem.

 

Thanks,

 

Mark

 

__

 

From: Evan Bauer = <evanbauer@mac.com>
Sent: 20 August 2018 = 14:36
To: s.dunand@sirap.fr
Cc: = pgsql-admin@lists.postgresql.org
Subject: Re: Setting up SSL = for postgre

 

Mark,

 

Have you tried a remote connection from the client = with something other than Delphi =E2=80=94 psql or pgAdmin =E2=80=94 to = whether the the issue is on the server or client side of the = connection?

 

Cheers,

 

- = Evan

 

Evan Bauer
eb@evanbauer.com
+1 646 641 = 2973
Skype: evanbauer



On Aug 20, 2018, at 09:02, St=C3=A9phane Dunand <s.dunand@sirap.fr> = wrote:

 

Le 20/08/2018 =C3=A0 14:44, Mark Williams a = =C3=A9crit :

I have started all over again to see if I can = resolve this issue. Unfortunately not. I am still pulling my hair = out.

 

I am still following the = instructions howtoforge.

 

I am working with pg10. I = am trying to use SSL on a small network server (running on Windows 7. I = am trying to connect from a client machine running on Windows  = 10.

 

Commands for certificate = creation

openssl genrsa -des3 -out c:\certs\server.key = 1024

 

openssl rsa -in = c:\certs\server.key -out c:\certs\server.key

 

openssl req -new -key = c:\certs\server.key -days 3650 -out c:\certs\server.crt -x509 -subj '/C=3DUK/ST=3DWales/L=3DCardiff/O=3DMWC/CN=3D192.168= .0.12/emailAddress=3Dinfo@mwconsult.co.uk'

 

{192.168.0.12 is the = ipaddress of the server machine on the local = network.

 

 

cp server.crt root.crt = {manually copied as on Windows}

openssl genrsa -des3 -out = c:\certs\postgresql.key 1024

 

openssl rsa -in = c:\certs\postgresql.key -out = c:\certs\postgresql.key

 

openssl req -new -key = c:\certs\postgresql.key -out c:\certs\postgresql.csr -subj = '/C=3DUK/ST=3DWales/L=3DCardiff/O=3DMWC/CN=3Dpostgres'

 

openssl x509 -days 3650 = -req -in c:\certs\postgresql.csr -CA c:\certs\root.crt -CAkey = c:\certs\server.key -out c:\certs\postgresql.crt = -CAcreateserial

 

I then copy the server.key, = server.crt and root.crt file to the postgres data folder on the server = machine. 

 

Postgresql.conf

listen_addresses =3D = '*'

ssl =3D on

#ssl_ciphers =3D = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL = ciphers

#ssl_prefer_server_ciphers =3D = on

#ssl_ecdh_curve =3D = 'prime256v1'

#ssl_dh_params_file =3D = ''

ssl_cert_file =3D = 'server.crt'

ssl_key_file =3D = 'server.key'

ssl_ca_file =3D = 'root.crt'

#ssl_crl_file =3D = ''

#password_encryption =3D = md5           &nbs= p;        # md5 or = scram-sha-256

#db_user_namespace =3D = off

#row_security =3D = on

 

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 

 

# IPv6 local = connections:

host    = all           &nbs= p; = all           &nbs= p; = ::1/128           =       trust

 

I restart the = service.

 

Client = Machine

I am trying to connect from an application = written in Delphi and using FireDAC.

The FireDAC params are set = as follows

        Params.Values['UseSSL'] = :=3D 'True';

      =   Params.values['SSL_ca'] :=3D sslCertsPath + = 'root.crt';

      =   Params.values['SSL_cert'] :=3D sslCertsPath + = 'postgresql.crt.';

      =   Params.values['SSL_key'] :=3D sslCertsPath + = 'postgresql.key';

 

The client certs are copied = to =E2=80=9CsslCertsPath=E2=80=9D

 

When I connect I get the = =E2=80=9Cconnection requires a valid client certificate=E2=80=9D = error.

 

Is there something else I = need to do? Do I have to added any of the self-certified certificates to = the Windows Trusted certificate store and, if so, which ones on which = machines?

 

Hopefully, somebody can = work out why this connection fails, if not, I can see no alternative to = booking myself in t Dignitas!

 

Many = thanks.

 

Mark

__

 <= /o:p>


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

Best regards,
St=C3=A9phane

 

------=_NextPart_000_078B_01D438A1.24758A40--