From: Mark Williams <markwillimas@gmail.com>
To: 'Evan Bauer' <evanbauer@mac.com>
To: s.dunand@sirap.fr
Cc: pgsql-admin@lists.postgresql.org
Subject: RE: Setting up SSL for postgre
Date: Mon, 20 Aug 2018 16:16:24 +0100
Message-ID: <078a01d43898$c2ad78c0$48086a40$@gmail.com> (raw)
In-Reply-To: <DB982C3D-8124-4DA9-B59E-382AE31F8EFC@mac.com>
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>
<DB982C3D-8124-4DA9-B59E-382AE31F8EFC@mac.com>
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 — psql or pgAdmin — to whether the the issue is on the server or client side of the connection?
Cheers,
- Evan
Evan Bauer
eb@evanbauer.com <mailto:eb@evanbauer.com>
+1 646 641 2973
Skype: evanbauer
On Aug 20, 2018, at 09:02, Stéphane Dunand <s.dunand@sirap.fr <mailto:s.dunand@sirap.fr> > wrote:
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 '/ <mailto:C=UK/ST=Wales/L=Cardiff/O=MWC/CN=192.168.0.12/emailAddress=info@mwconsult.co.uk> 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/; https://www.depesz.com/2015/05/11/how-to-setup-ssl-connections-and-authentication/
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, evanbauer@mac.com, s.dunand@sirap.fr, pgsql-admin@lists.postgresql.org
Subject: RE: Setting up SSL for postgre
In-Reply-To: <078a01d43898$c2ad78c0$48086a40$@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