Thread: SSL Connection help, pls...

SSL Connection help, pls...

From
Richard Troy
Date:
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/




Re: SSL Connection help, pls...

From
Tom Lane
Date:
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

Re: SSL Connection help, pls...

From
Richard Troy
Date:
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/



Re: SSL Connection help, pls...

From
Tom Lane
Date:
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

Re: SSL Connection help, pls...

From
Richard Troy
Date:
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/