David,
My server pg_hba.conf file already has the following to connect to the client: hostssl all all myipaddress/32 cert Trust me. Make your life simple. Change that line to generate some 'empirical' testing: hostssl all all 0.0.0.0/0 md5 Forget the locked-down CIDR addressing for the moment - given the routing and multi-net environment of any large institution, can you be sure of the IP address you're coming in on? (Presumably you're tailing your logs?) Forget the cert. Establish a password-only connection, using SSL, as I've suggested earlier. Have you created a simple test user, with an md5-hashed password? (There will be plenty of time for 'Fun with Certs', believe me). And my postgre configuration file has ssl = on set already Well, if you've successfully restarted the server with that option, this may be diagnostic: That you DO have SSL built in to the server. When you say the server must be built with SSL support, it has a dropdown option when setting up a database for SSL Mode, and I selected (for now) Required Not relevant. Sounds like you are setting the _client-side_ connection options - perhaps in pgAdminIII? This only set pqlib's behavior. BTW, Friends don't let friends use pgAdminIII...(!) It's a basis for an 'intervention'; you'll want to get onto the PostgreSQL CLI ASAP! Last: You realize this is the OpenSSL list; not really the right place to get the advice you seek? (Am happy to help nonetheless... !) Let me know how you get on! (I will want to know the resolution of all this...) Lou Picciano --- Subject: RE: First time attempting PostgreSQL SSL My server pg_hba.conf file already has the following to connect to the client: hostssl all all myipaddress/32 cert And my postgre configuration file has ssl = on set already. I installed pgAdmin on my client computer with the Binary file. And I have no idea what the difference is between a local and roaming profile, as my appdata folder is under c:\Documents and Settings\--account--\Application Data\postgresql When you say the server must be built with SSL support, it has a dropdown option when setting up a database for SSL Mode, and I selected (for now) Required. If there’s some other feature I missed in this regard then I am unaware of it. From: owner-openssl-us...@openssl.org [mailto:owner-openssl-us...@openssl.org] On Behalf Of Lou Picciano Sent: Monday, January 31, 2011 5:16 PM To: openssl-users@openssl.org Cc: david.patric...@jefferson.edu Subject: Re: First time attempting PostgreSQL SSL 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
