RE: Setting up SSL for postgre

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(at)mac(dot)com>
Sent: 20 August 2018 14:36
To: s(dot)dunand(at)sirap(dot)fr
Cc: pgsql-admin(at)lists(dot)postgresql(dot)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(at)evanbauer(dot)com <mailto:eb(at)evanbauer(dot)com>
+1 646 641 2973
Skype: evanbauer

On Aug 20, 2018, at 09:02, Stéphane Dunand <s(dot)dunand(at)sirap(dot)fr <mailto:s(dot)dunand(at)sirap(dot)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(at)mwconsult(dot)co(dot)uk> C=UK/ST=Wales/L=Cardiff/O=MWC/CN=192.168.0.12/emailAddress=info(at)mwconsult(dot)co(dot)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