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 1frkM8-0007bP-9F for pgsql-admin@arkaria.postgresql.org; Mon, 20 Aug 2018 13:36:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1frkM6-0000Fi-DD for pgsql-admin@arkaria.postgresql.org; Mon, 20 Aug 2018 13:36:26 +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 1frkM5-0000FZ-TW for pgsql-admin@lists.postgresql.org; Mon, 20 Aug 2018 13:36:26 +0000 Received: from st14p37im-asmtp001.me.com ([17.41.209.30]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1frkM1-0001ds-PR for pgsql-admin@lists.postgresql.org; Mon, 20 Aug 2018 13:36:24 +0000 Received: from process-dkim-sign-daemon.st14p37im-asmtp001.me.com by st14p37im-asmtp001.me.com (Oracle Communications Messaging Server 8.0.2.2.20180531 64bit (built May 31 2018)) id <0PDR00G00HLMNM00@st14p37im-asmtp001.me.com> for pgsql-admin@lists.postgresql.org; Mon, 20 Aug 2018 13:36:20 +0000 (GMT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mac.com; s=04042017; t=1534772180; bh=gpr1LpIZx+8qGePYnG3pODtQsOSLcNv0wytDbQBYE2o=; h=From:Message-id:Content-type:MIME-version:Subject:Date:To; b=yDi8+jx1oBJ90BdssqaTICe1u8l6vl2BSjT41Y7HrpftaF+nHwAZmlkR7CV2GpnqB lZicsANw0wNE4Tt9UL92m3gNGp6KuTzwIR8O9QiqEIpQoN5yGftKbXiH7jWA0J2Wkv SEAR4vumoQESp9oGcdKX+mBof0UXbAr1+538NBfdSlkXV4+KIIK5jQ7Nl2IGfA0Bbq KpXKPsWXm5DIcRRHipylopJmt0HKMsKKIZzkxvuk1HQRwCE+7YFCJVpYydUbPcyrDP 4OL5tAZhbw8wuhTjRHu1puXZpQbQHW1pN1sFZymTeNze8dIvdczXdX+usSqJ/9H+fX J2O9IpkY0GdKw== Received: from icloud.com ([127.0.0.1]) by st14p37im-asmtp001.me.com (Oracle Communications Messaging Server 8.0.2.2.20180531 64bit (built May 31 2018)) with ESMTPSA id <0PDR00IQBHSF1W40@st14p37im-asmtp001.me.com>; Mon, 20 Aug 2018 13:36:17 +0000 (GMT) X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 clxscore=1011 suspectscore=1 malwarescore=0 phishscore=0 adultscore=0 bulkscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1707230000 definitions=main-1808200147 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2018-08-20_04:,, signatures=0 From: Evan Bauer Message-id: Content-type: multipart/alternative; boundary="Apple-Mail=_0B0F73B8-B7C3-4185-A8FE-2E3C5B06F119" MIME-version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\)) Subject: Re: Setting up SSL for postgre Date: Mon, 20 Aug 2018 09:36:14 -0400 In-reply-to: <4ea1e251-0aa2-be17-4405-d1ccec951ecc@sirap.fr> Cc: pgsql-admin@lists.postgresql.org To: s.dunand@sirap.fr References: <032401d43326$6c275d80$44761880$@gmail.com> <034901d4333f$d2aa7650$77ff62f0$@gmail.com> <076401d43883$89ec6830$9dc53890$@gmail.com> <4ea1e251-0aa2-be17-4405-d1ccec951ecc@sirap.fr> X-Mailer: Apple Mail (2.3445.9.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_0B0F73B8-B7C3-4185-A8FE-2E3C5B06F119 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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 = 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=3Di= nfo@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 >=20 > This page helped me : > = https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authent= ication/ = >=20 > Best regards, > St=C3=A9phane --Apple-Mail=_0B0F73B8-B7C3-4185-A8FE-2E3C5B06F119 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 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/emailAd= dress=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            = ;        # 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            = ; = all            = ; = 127.0.0.1/32          &n= bsp; trust
hostssl = all         = postgres    = 0.0.0.0/0           = ;  cert 
 
# IPv6 local connections:
host    = all            = ; = all            = ; = ::1/128           &= nbsp;     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
__


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

Best regards,
St=C3=A9phane

= --Apple-Mail=_0B0F73B8-B7C3-4185-A8FE-2E3C5B06F119--