Re: Setting up SSL for postgre

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(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/

Best regards,
Stéphane