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/

Search Discussions

  • Tom Lane at Jan 27, 2010 at 10:32 pm

    Richard Troy 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
  • Richard Troy at Jan 28, 2010 at 4:07 pm

    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/
  • Tom Lane at Jan 28, 2010 at 4:11 pm

    Richard Troy 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
  • Richard Troy at Jan 28, 2010 at 7:49 pm

    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/

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJan 27, '10 at 9:39p
activeJan 28, '10 at 7:49p
posts5
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Richard Troy: 3 posts Tom Lane: 2 posts

People

Translate

site design / logo © 2022 Grokbase