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 __ From: Mark Williams <markwillimas@xxxxxxxxx> Hi, The CN is “www-data” as per the howtoforge documentation and added to the pg_hba.conf file. Do I have to add an actual user called “www-data” to the database? If so, do I have to have a different root and client certificate for every ssl user? Re pg_ident. I have added “cert www-data” Postgres” to pg_ident. Doesn’t help. Still getting the “connection requires a valid client certificate” error. Thanks Mark __ From: Bear Giles <bgiles@xxxxxxxxxxxxxx> It never hurts to double check. What is the CN on the client cert? Is there a corresponding user in the database? You might need to add an entry in pg_ident.conf, e.g., cert bob@xxxxxxxxxxx bob if you want user with the cert with a CN of 'bob@xxxxxxxxxxx' to access the system as the postgresql user 'bob'. You won't need this if your CN is the same as the database user. I've only set up Kerberos authentication, not X.509 cert authentication, so I don't know the details about what this file needs specifically. Bear On Mon, Aug 13, 2018 at 1:57 PM, Mark Williams <markwillimas@xxxxxxxxx> wrote:
|
