David, a couple of additional thoughts, as I re-read this...
First, in your client certs location - %appdata%/postgresql - you do _not_ need the CSR. This is the Certificate Signing Request. It won't hurt you, but save this elsewhere. Next: A common gotcha on Windows is that the %APPDATA% is not always exactly what you think it is. It's worth experimenting with your local vs. roaming profiles, for example... Having said all that, here's some Good Advice(!): Skip certificates for the moment, and be sure you have SSL working _at all_ on your server. You must be sure of a couple of things: 1) The server _must_ be built with SSL support 2) The client lib you are using - typically pq_lib, if you haven't compiled your own code, must ALSO be built with SSL support. 3) The pg_hba.conf access line must 'activate' SSL for a given connection. HINT: A 'simple' pg_hba.conf setting will be adequate to testing SSL; remove the variables one by one in your logic. IE, use a 'hostssl' line for user 'test' from 0.0.0.0/0 with an md5 password. The whole SSL-build-plus-certificates chain above, all at the same time, is not for the faint of heart, if you have never done it before. It's a bit bigger than just setting the options in pg_hba.conf. Once you've verified SSL as a baseline, then you're ready to move on to certificates! Let me know! Lou Picciano ----- Original Message ----- From: "David Patricola" <david.patric...@jefferson.edu> To: openssl-users@openssl.org Sent: Monday, January 31, 2011 4:31:08 PM Subject: FW: First time attempting PostgreSQL SSL Whoops, I forgot to include root.crt in the Postgre /data directory. Now that I fixed that, I am still getting the same connection error on the client side. -----Original Message----- From: David Patricola [mailto:david.patric...@jefferson.edu] Sent: Monday, January 31, 2011 4:10 PM To: 'openssl-users@openssl.org' Subject: RE: First time attempting PostgreSQL SSL I found an excellent tutorial on this: http://www.howtoforge.com/postgresql-ssl-certificates I followed the instructions perfectly, and used my client computer's IP address as the CA. Here's my setup so far: On the server: - In Postgre data/ I have the server.crt and server.key files - pg_hba.conf has hostssl all all myipaddress/32 cert - postgresql.conf has ssl = on - I restarted the service and it connected. On the client: - In my %appdata%/postgresql folder I have postgresql.crt, postgresql.csr, postgresql.key and root.crt After this, I try to connect to the server again from my client box and it says "server does not support SSL, but SSL is required". Would this be a Postgre issue or did I not put a certificate file correctly somewhere? -----Original Message----- From: owner-openssl-us...@openssl.org [mailto:owner-openssl-us...@openssl.org] On Behalf Of Alan Buxey Sent: Saturday, January 29, 2011 3:21 PM To: openssl-users@openssl.org Subject: Re: First time attempting PostgreSQL SSL Hi, > I’m new as can be with creating SSL certificates on my own. I downloaded > the openssl binary and installed it. The instructions and tutorials on > the website don’t help me much in terms of steps A,B,C; this could also be > due to a lack of familiarity with technical terms used for each part of > this. The only thing I did accomplish is the following > > > > openssl req -out CSR.csr -new -newkey rsa:2048 -nodes -keyout > privateKey.key > > > > I now have a privateKey and CSR file in the openssl/bin folder. Beyond > this I do not know what goes where. I’m on Windows 2003 (server) and > Windows XP (client). I know what to change in the PostgreSQL config but > do not know if PostgreSQL implicitly knows that a certificate exists, and > what the client box should have. you probably want to check the postgreSQL documentation for where and how to use the certs - as this is not specific to OpenSSL. anyway, in general terms...you now have a private key - good, the PSQL server would have that.... you need to get your CSR signed by a CA that the client knows..... you then would configure PSQL to use the public version of the signed CSR - usually a DER or PEM file by that point. at this point, its just like a client talking to an SSL'd web server (or any other service). client connects, gets given the cert...which it trusts (because of CA) and then SLS tunnel gets made. data is transferred over that tunnel. alan ______________________________________________________________________ OpenSSL Project http://www.openssl.org User Support Mailing List openssl-users@openssl.org Automated List Manager majord...@openssl.org ______________________________________________________________________ OpenSSL Project http://www.openssl.org User Support Mailing List openssl-users@openssl.org Automated List Manager majord...@openssl.org
