Thread: SSL Connection help, pls...
Hi All, I'm not a novice with Postgres, but I sure feel like one after struggling to get an SSL connection going! Problem Summary: Although I think I've got everything configured correctly, I'm not getting ssl encrypted connections to be accepted. Also, havent' figured out how to tell psql to try _only_ an ssl-type connection. I plan on using JDBC as the primary connection strategy, but am testing with both JDBC, psql, and pgAdmin III, 1.8.2 (from 2008). Problem Details: The server is on a linux box runnnig PG 8.2.3, the JDBC driver version I'm not certain of but is likely identical vintage. (I'm not sure how to get the JDBC drivers' version easily.) I STRONGLY suspect there's not a damned thing wrong except my understanding / perceptions about how to do this. _Maybe_ a bit of additional commentary in the documentation will be helpful, too. But I'm presuming the problem is me. I've been using the online docs for reference - pages like: http://jdbc.postgresql.org/documentation/80/connect.html http://jdbc.postgresql.org/documentation/80/ssl.html http://www.postgresql.org/docs/current/static/runtime-config-connection.html#GUC-SSL http://www.postgresql.org/docs/8.4/static/auth-pg-hba-conf.html I started with a working configuration that didn't use SSL but with both OpenSSL installed and the server having been compiled with the ssl option. I followed the directions on this page to the letter: http://www.postgresql.org/docs/current/static/ssl-tcp.html then turned on ssl in the server's configuration file. Restarting the server didn't work because it needed another file, root.crt, IIRC, which I think is supposed to contain the permitted certificating authorities - which is self-signed at the moment, so I copied the just created server.crt file. That seemed to make the server happy and thereafter it comes up with the ssl option with the setting either off or on - I've left it on. For the time being I'm not interested in using user-certificates for authentication. I'm only looking for encryption point-to-point, but will at some point want to use certificates as the reason I'm doing this is in preparation to put an application on the internet. For now, authenticating users with certificates isn't necessary. In pg_hba.conf I've been using these two, alternatively, with only one enabled at a time - this is just for testing purposes. There are _no_ other entries: host all all 192.168.1.1 255.255.255.0 trust hostssl all all 192.168.1.1 255.255.255.0 trust Of course, I recycle the server every time this file changes. I can consistently connect without any difficulty with just the host entry and NOT directing JDBC to use ssl. I haven't ever gotten the SSL Connection confirmation blerb as described at the top of this page: http://jdbc.postgresql.org/documentation/80/ssl.html I have looked and have not found any flag to psql to tell it to use ssl, nor is it used in the example. There isn't one, is there? I have been using urls like these with JDBC, the top one for testing without ssl (to make sure there are no other problems), the rest for testing with ssl: jdbc:postgresql://myhost:5432/mydatabase jdbc:postgresql://myhost:5432/mydatabase?ssl=true jdbc:postgresql://myhost:5432/mydatabase?user=me&password=mypassword&ssl=true In each of the above, the approrpiate calls to the driver for username and password were made, as needed, though theoretically, they're not used anyway due to the entry in pg_hba.conf using "trust." (right?) The exception generated is always the same: org.postgresql.util.PSQLException: The connection attempt failed. ...So... When I get to pgAdmin III, it always connects without ssl just fine and never connects with it. The options are no etry, "require," "prefer," "allow," and "disable". The difference between prefer and allow isn't clear but in any case "require" seems to want there to be user certificate as it complains there's not a file "postgresql.crt" in the appropriate windows "Documents and Settings/me/Application Data/postgresql" directory. ... I satisfied this with the only .crt I had laying around - the one from the server installation discussed above. That didn't work with a complaint - which got the filename wrong! (.key instead of .crt) - that the contents weren't a private key. Not finding in the docs how to solve that (and not yet looking into users providing their own keys), I moved on and reconfigured the connection to "prefer". This time it comes up when ssl is not forced at the pg_hba.conf entry, but fails when it is with: FATAL: no pg_hba.conf entry for host "192.168.1.128", user "me", database "postgres", SSL off ...OK, I give up; help! Thanks for any input / guidance - including the suggestion this belongs on a different list! Regards, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-717-6942 rtroy@ScienceTools.com, http://ScienceTools.com/
Richard Troy <rtroy@ScienceTools.com> writes:
> Although I think I've got everything configured correctly, I'm not getting
> ssl encrypted connections to be accepted. Also, havent' figured out how to
> tell psql to try _only_ an ssl-type connection.
I don't know the answer to your problems offhand, but a few suggestions:
* Read the version of the docs corresponding to your server version,
not earlier or later ones. This stuff changes.
* Look in the postmaster log to see what gets logged during a failed
connection attempt.
* I do know about try-only-SSL, it's driven by an environment variable:
export PGSSLMODE=require
* The docs only cover SSL in the context of psql and other libpq-based
clients. For JDBC you should probably ask on pgsql-jdbc. But try to
get psql working first.
regards, tom lane
On Wed, 27 Jan 2010, Tom Lane wrote: > > Richard Troy <rtroy@ScienceTools.com> writes: > > Although I think I've got everything configured correctly, I'm not getting > > ssl encrypted connections to be accepted. Also, havent' figured out how to > > tell psql to try _only_ an ssl-type connection. > > I don't know the answer to your problems offhand, but a few suggestions: > > * Read the version of the docs corresponding to your server version, > not earlier or later ones. This stuff changes. Thanks, Tom, I hadn't thought any of this had changed since before version 7, or at the least had been pretty consistent through v 8, but that's a silly assumption on my part! > * Look in the postmaster log to see what gets logged during a failed > connection attempt. Of course! -duh!- Depending on which test, I get either: LOG: could not accept SSL connection: sslv3 alert certificate unknown LOG: could not accept SSL connection: peer did not return a certificate ...which seems to (strongly) suggest that it's requiring not only an encrypted connection but that the user present a certificate. > * I do know about try-only-SSL, it's driven by an environment variable: > export PGSSLMODE=require Good to know. > * The docs only cover SSL in the context of psql and other libpq-based > clients. For JDBC you should probably ask on pgsql-jdbc. But try to > get psql working first. Yes, I agree. I have been thinking about updating all my systems to the same (latest) version - perhaps it's time to do that and then see where things are. Thanks for your suggestions, Tom, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-717-6942 rtroy@ScienceTools.com, http://ScienceTools.com/
Richard Troy <rtroy@ScienceTools.com> writes:
>> * Look in the postmaster log to see what gets logged during a failed
>> connection attempt.
> Of course! -duh!-
> Depending on which test, I get either:
> LOG: could not accept SSL connection: sslv3 alert certificate unknown
> LOG: could not accept SSL connection: peer did not return a certificate
> ...which seems to (strongly) suggest that it's requiring not only an
> encrypted connection but that the user present a certificate.
I think that at least around 8.2, the postmaster interprets the presence
of root.crt as indicating that it should demand client certs. Better
check the docs though (and this is something I think Magnus changed in
8.4, but not totally sure, so be sure to check the right version of
the docs).
regards, tom lane
On Thu, 28 Jan 2010, Tom Lane wrote: > > > Depending on which test, I get either: > > > LOG: could not accept SSL connection: sslv3 alert certificate unknown > > LOG: could not accept SSL connection: peer did not return a certificate > > > ...which seems to (strongly) suggest that it's requiring not only an > > encrypted connection but that the user present a certificate. > > I think that at least around 8.2, the postmaster interprets the presence > of root.crt as indicating that it should demand client certs. Better > check the docs though (and this is something I think Magnus changed in > 8.4, but not totally sure, so be sure to check the right version of > the docs). > > regards, tom lane Thanks again, Tom. ...I reviewed the release notes this AM and going through your comments, etc, I decided to try SSL on a new pg installation. Just to be pedantic, I created the ssl files in a separate directory - instead of ~/data. This gave the ability to satisfy the server with a key at a time. I then started the server by hand and let it complain. I then copied over each file, one at a time to satisfy its demands: -bash-3.2$ postgres FATAL: could not load server certificate file "server.crt": No such file or directory -bash-3.2$ cp -p ssl/server.crt . -bash-3.2$ postgres FATAL: could not access private key file "server.key": No such file or directory -bash-3.2$ cp -p ssl/server.key . -bash-3.2$ postgres LOG: could not load root certificate file "root.crt": No such file or directory DETAIL: Will not verify client certificates. ...And it sat there. It took a moment to realize that even though it complained about root.crt, it was up and accepting conecions but it wasn't going to ask for certs. Apparent Success! I wondered to myself if I hadn't had success previously but had errantly kept going by satisfying its complaint about root.crt, and had the server up but demanding keys. So, I checked this hunch; My former installation's ~/pgstartup.log had been overwritten already, so there was no telling from the log. So, I removed and rebuilt the keys the same way as the new installation and had the same apparently successful result! The _first_ test for non-certificated ssl connection worked flawlessly. Conclusions: 1) I had apparently fat-fingered the keys (pun-intended) by going beyond what was required. 2) I was too focused on the _error_ contents of ~/pgstartup.log to notice that the server had in fact started and that I'd reached success. 3) Cross version can work for mixing at least the several different versions I'm using (though I agree with the suggestion to start with a consistent version environment). 4) While it was completely my fault I didn't have success earlier, some kind of reference to PGSSLMODE would be a great addition to: http://jdbc.postgresql.org/documentation/80/ssl.html and / or on some of the more obvious SSL-related pages. Similarly, a comment like "stop here if you don't want user authentication via ssl certificates; you will see a request for root.crt which you must ignore." somewhere on this page would be great: http://www.postgresql.org/docs/current/static/ssl-tcp.html And, perhaps a comment about not assuming cross-version success between clients and servers somewhere in the mix would be helpful, too. These suggestions are NO COMPLAINT OR CRITICISM. It was my fault I missed these points. Generally, the PG docs are world-class. Thanks again, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-717-6942 rtroy@ScienceTools.com, http://ScienceTools.com/