Re: SSL Connection help, pls...

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