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 1frjpX-0005jU-RI for pgsql-admin@arkaria.postgresql.org; Mon, 20 Aug 2018 13:02:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1frjpV-0007cg-Ip for pgsql-admin@arkaria.postgresql.org; Mon, 20 Aug 2018 13:02:45 +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 1frjpV-0007cY-AB for pgsql-admin@lists.postgresql.org; Mon, 20 Aug 2018 13:02:45 +0000 Received: from inv-18.93.247.146.static.fprt.com ([146.247.93.18] helo=mail.groupesirap.fr) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1frjpR-0002lS-BE for pgsql-admin@lists.postgresql.org; Mon, 20 Aug 2018 13:02:44 +0000 Received: (qmail 27783 invoked by uid 105); 20 Aug 2018 13:02:39 -0000 Reply-To: s.dunand@sirap.fr Subject: Re: Setting up SSL for postgre To: pgsql-admin@lists.postgresql.org References: <032401d43326$6c275d80$44761880$@gmail.com> <034901d4333f$d2aa7650$77ff62f0$@gmail.com> <076401d43883$89ec6830$9dc53890$@gmail.com> From: =?UTF-8?Q?St=c3=a9phane_Dunand?= Message-ID: <4ea1e251-0aa2-be17-4405-d1ccec951ecc@sirap.fr> Date: Mon, 20 Aug 2018 15:02:39 +0200 MIME-Version: 1.0 In-Reply-To: <076401d43883$89ec6830$9dc53890$@gmail.com> Content-Type: multipart/alternative; boundary="------------1A19D0E7ACD62EACD47EBD30" Content-Language: fr Received: from mail.groupesirap.fr (HELO mail.groupesirap.fr) (127.0.0.1) by mail.groupesirap.fr (qpsmtpd/0.96) with ESMTPSA (ECDHE-RSA-AES256-GCM-SHA384 encrypted); Mon, 20 Aug 2018 15:02:39 +0200 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------1A19D0E7ACD62EACD47EBD30 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Le 20/08/2018 à 14:44, Mark Williams a écrit : > > 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=UK/ST=Wales/L=Cardiff/O=MWC/CN=192.168.0.12/emailAddress=info@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=UK/ST=Wales/L=Cardiff/O=MWC/CN=postgres' > > 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 = '*' > > ssl = on > > #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers > > #ssl_prefer_server_ciphers = on > > #ssl_ecdh_curve = 'prime256v1' > > #ssl_dh_params_file = '' > > ssl_cert_file = 'server.crt' > > ssl_key_file = 'server.key' > > ssl_ca_file = 'root.crt' > > #ssl_crl_file = '' > > #password_encryption = md5                    # md5 or scram-sha-256 > > #db_user_namespace = off > > #row_security = on > > *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 > > # IPv6 local connections: > > host all             all             ::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'] := 'True'; > >   Params.values['SSL_ca'] := sslCertsPath + 'root.crt'; > >   Params.values['SSL_cert'] := sslCertsPath + 'postgresql.crt.'; > >   Params.values['SSL_key'] := sslCertsPath + 'postgresql.key'; > > The client certs are copied to “sslCertsPath” > > When I connect I get the “connection requires a valid client > certificate” 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éphane --------------1A19D0E7ACD62EACD47EBD30 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Le 20/08/2018 à 14:44, Mark Williams a écrit :

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=UK/ST=Wales/L=Cardiff/O=MWC/CN=192.168.0.12/emailAddress=info@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=UK/ST=Wales/L=Cardiff/O=MWC/CN=postgres'

 

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 = '*'

ssl = on

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

#ssl_prefer_server_ciphers = on

#ssl_ecdh_curve = 'prime256v1'

#ssl_dh_params_file = ''

ssl_cert_file = 'server.crt'

ssl_key_file = 'server.key'

ssl_ca_file = 'root.crt'

#ssl_crl_file = ''

#password_encryption = md5                    # md5 or scram-sha-256

#db_user_namespace = off

#row_security = on

 

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

 

# IPv6 local connections:

host    all             all             ::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'] := 'True';

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

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

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

 

The client certs are copied to “sslCertsPath”

 

When I connect I get the “connection requires a valid client certificate” 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éphane
--------------1A19D0E7ACD62EACD47EBD30--