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(at)ScienceTools(dot)com, http://ScienceTools.com/