Re: SSL Certificates in Windows 7 & Postgres 9.3

On 12/15/2014 11:41 AM, harpagornis wrote:
> I am trying to connect using SSL in Windows 7 and Postgres v9.3. The console
> output error message is "Failed to establish a connection to 127.0.0.1" The
> error message from the pg_log is:
> -----------------------------------------------------------
> 2014-12-15 19:20:24 GMT FATAL: connection requires a valid client
> certificate
> 2014-12-15 19:20:25 GMT FATAL: connection requires a valid client
> certificate
> 2014-12-15 19:20:26 GMT FATAL: no pg_hba.conf entry for host "127.0.0.1",
> user "SYSTEM", database "postgres", SSL off
> 2014-12-15 19:20:55 GMT LOG: could not accept SSL connection: No connection
> could be made because the target machine actively refused it.
> -----------------------------------------------------------
> This is the connection string from the console app.
>
> string conStr =
> "Server=127.0.0.01; " +
> "User Id=my_role; " +
> "Password=''; " +
> "Database=dbname; " +
> "SSL=True; " +
> "Sslmode=Require; ";
>
> -----------------------------------------------------------
> This is the pg_hba.conf
> hostssl all all 127.0.0.1/32 cert clientcert=1
> hostssl all all ::1/128 cert clientcert=1
> -----------------------------------------------------------
> This is the postgresql.conf
>
> listen_addresses = '*'
> port = 5432
> max_connections = 100
> ssl = on
> ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'
> ssl_renegotiation_limit = 512MB
> ssl_cert_file = 'server.crt'
> ssl_key_file = 'server.key'
> ssl_ca_file = 'root.crt'
> password_encryption = off
> shared_buffers = 128MB
> -----------------------------------------------------------
> I followed all documentation for creating the certificates, ie.
> -----------------------------------------------------------
> Server Side
> openssl genrsa -des3 -out server.key 2048
> openssl rsa -in server.key -out server.key
> openssl req -new –key server.key -days 3650 -out server.crt –config
> "D:\openssl\v9.8\openssl.cnf”
> -----------------------------------------------------------
> Client Side
> openssl genrsa -des3 -out postgresql.key 2048
> openssl rsa -in postgresql.key -out postgres.key
> openssl req -new -key postgresql.key -out postgresql.csr –config
> "D:\openssl\v9.8\openssl.cnf”
> copy server.crt root.crt
> openssl x509 -req -in postgresql.csr -CA root.crt -CAkey server.key -out
> postgresql.crt -CAcreateserial
> ----------------------------------------------------------
> Windows Pkcs12 file:
> openssl pkcs12 -export -out postgrcli.p12 -name "My Certificate" -in
> postgresql.crt
> -inkey postgresql.key
> -----------------------------------------------------------
> The Visual Studio solution includes as a project / reference, the source
> code of Npgsql v2.2.0.
> However, the program never reaches any of the breakpoints I put throughout
> the Npgsql code .
> -----------------------------------------------------------
> I really need some help, please. Any suggestions? I have scoured the
> documentation and the internet. Maybe I can try a psql command. What would
> that command be with the certificate included? Thank you in advance.

Did you set the CN of the client certificate to the user that you are
connecting as. For a good run through/explanation see:

http://www.howtoforge.com/postgresql-ssl-certificates
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/SSL-Certificates-in-Windows-7-Postgres-9-3-tp5830749.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com