Re: Setting up SSL for postgre

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx
+1 646 641 2973
Skype: evanbauer


On Aug 20, 2018, at 09:02, Stéphane Dunand <s.dunand@xxxxxxxx> 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 '/C=UK/ST=Wales/L=Cardiff/O=MWC/CN=192.168.0.12/emailAddress=info@xxxxxxxxxxxxxxx'
 
{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


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux