I've just spent a while this afternoon attempting to get SSL support
working.  It appears to be lacking in a few areas, foremost
documentation.  I've got a patch filling in the missing pieces for the
server side, but I am unsure where I should document the client side
bits (~/.postgresql/root.crt and friends).  I am also unsure of the
procedures for submitting patches; is it ok to just send to hackers?

Going through the code I spotted what appear to be problems.  Although
I'm not familiar with the internals and this is only a cursory glance
through the code.

* src/backend/libpq/be-secure.c

  In initialize_SSL(), we call SSL_CTX_set_verify(), but we don't pass
  in the SSL_VERIFY_FAIL_IF_NO_PEER_CERT flag.  This means that a client
  can present no certificate and still get access to the server.

  There's nothing that gets logged to say that an SSL connection was
  made.  This would be useful for testing.  Something like logging the
  connection as "1.2.3.4/ssl"?

* src/interfaces/libpq/fe-secure.c

  In initialize_SSL(), we call SSL_CTX_set_verify_depth(SSL_context, 1).
  This should probably be a configurable item.  I /think/ it might be
  stopping me from successfully verifying the server certificate is
  signed by the CA listed in my client's root.crt file, but I'm not
  sure.

  There is no ability to indicate failure to read the client root.crt.
  This is because it's just magically turned on by the presence of that
  file.  Perhaps it should be another PQconnectdb() option?

  In open_client_SSL(), the call to SSL_get_verify_result() is commented
  out.  This means that crucial things such as the validity of the
  certificate of the server you are connecting to is not checked.  So
  the client will happily connect to an expired certificate.

  In open_client_SSL() again, the call to verify that the CN of the
  certificate is the same as the hostname you've connected to is
  commented out.  So you have no idea whether or not you've connected to
  the right server.

* [both files]

  Having hard coded file names is a bit of a pain.  Particularly so for
  the client, as it means that two users of libpq cannot use different
  certificates.  I admit this is unlikely, but still.  Of course, it
  would be slightly better if documented.

I'm happy to have a go at fixing these problems, if that's acceptable.
Unfortunately the SSL support isn't really usable for me without them.

-Dom
Tue, 21 Sep 2004 08:24:52 +0100
dom@happygiraffe.net (Dominic Mitchell)
SSL Support
Am Dienstag, 21. September 2004 09:24 schrieb Dominic Mitchell:
> I am also unsure of the
> procedures for submitting patches; is it ok to just send to hackers?

pgsql-patches@postgresql.org

>   In initialize_SSL(), we call SSL_CTX_set_verify(), but we don't pass
>   in the SSL_VERIFY_FAIL_IF_NO_PEER_CERT flag.  This means that a client
>   can present no certificate and still get access to the server.

Client-side certificates as an authentication mechanism are not intended to be 
supported.  It might be a nice feature to add, though.

>   There's nothing that gets logged to say that an SSL connection was
>   made.  This would be useful for testing.  Something like logging the
>   connection as "1.2.3.4/ssl"?

That seems reasonable.

>   In initialize_SSL(), we call SSL_CTX_set_verify_depth(SSL_context, 1).
>   This should probably be a configurable item.  I /think/ it might be
>   stopping me from successfully verifying the server certificate is
>   signed by the CA listed in my client's root.crt file, but I'm not
>   sure.

I think verification of the server certificates is not supported either.  SSL 
only serves for encryption, not authentication or integrity checking (which 
is probably a stupid idea).

>   In open_client_SSL() again, the call to verify that the CN of the
>   certificate is the same as the hostname you've connected to is
>   commented out.  So you have no idea whether or not you've connected to
>   the right server.

This seems to match the pattern I described above.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Tue, 21 Sep 2004 10:17:51 +0200
Peter Eisentraut <peter_e@gmx.net>
Re: SSL Support
Hi

> I think verification of the server certificates is not supported either. 
> SSL only serves for encryption, not authentication or integrity checking
> (which is probably a stupid idea).

I have this feeling that SSL in PostgreSQL isn't category 1 supported if you 
can put it that way. Maybe I'm wrong?

Another way to ensure encrypted (and authenticated, I believe) connections is 
to use stunnel with PostgreSQL.

I'm not sure which solution is the best. SSL in PostgreSQL is integrated. 
Stunnel has the advantage of being more generic. having tried none, I don't 
know about performance.

-- 
Kaare Rasmussen            --Linux, spil,--        Tlf:        3816 2582
Kaki Data                tshirts, merchandize      Fax:        3816 2501
Nordre Fasanvej 12         ï¿œben 12.00-18.00        Email: kar@kakidata.dk
2000 Frederiksberg        Lï¿œrdag 12.00-16.00       Web:      www.suse.dk

Tue, 21 Sep 2004 10:44:22 +0200
Kaare Rasmussen <kar@kakidata.dk>
Re: SSL Support
On Tue, Sep 21, 2004 at 10:17:51AM +0200, Peter Eisentraut wrote:
> Am Dienstag, 21. September 2004 09:24 schrieb Dominic Mitchell:
> > I am also unsure of the
> > procedures for submitting patches; is it ok to just send to hackers?
> 
> pgsql-patches@postgresql.org

Thanks, I'll send it along there.

> >   In initialize_SSL(), we call SSL_CTX_set_verify(), but we don't pass
> >   in the SSL_VERIFY_FAIL_IF_NO_PEER_CERT flag.  This means that a client
> >   can present no certificate and still get access to the server.
> 
> Client-side certificates as an authentication mechanism are not
> intended to be supported.  It might be a nice feature to add, though.

The code is all there to do so, pretty much.  What it's missing is a few
toggles to make it say "I want to enforce this to happen".

> >   There's nothing that gets logged to say that an SSL connection was
> >   made.  This would be useful for testing.  Something like logging the
> >   connection as "1.2.3.4/ssl"?
> 
> That seems reasonable.

Ok, I'll knock up a patch to do so.

> >   In initialize_SSL(), we call SSL_CTX_set_verify_depth(SSL_context, 1).
> >   This should probably be a configurable item.  I /think/ it might be
> >   stopping me from successfully verifying the server certificate is
> >   signed by the CA listed in my client's root.crt file, but I'm not
> >   sure.
> 
> I think verification of the server certificates is not supported either.  SSL 
> only serves for encryption, not authentication or integrity checking (which 
> is probably a stupid idea).
>
> >   In open_client_SSL() again, the call to verify that the CN of the
> >   certificate is the same as the hostname you've connected to is
> >   commented out.  So you have no idea whether or not you've connected to
> >   the right server.
> 
> This seems to match the pattern I described above.

I think it's misleading to talk about SSL being supported without these
options.  I've used SSL in other places (apache/mod_ssl, curl, stunnel)
and I came to expect this sort of verification as standard behaviour.
What's more, the code is there to do it, it's just #ifdef'd out, or
needs a toggle.

I'm not even concerned about client certificates (though that would be
useful), but just the ordinary sort of checking that goes with SSL.
This is about the same level of checking that a browser would do when
visiting a HTTPS site.

-Dom

Tue, 21 Sep 2004 10:35:56 +0100
dom@happygiraffe.net (Dominic Mitchell)
Re: SSL Support
On Tue, Sep 21, 2004 at 10:44:22AM +0200, Kaare Rasmussen wrote:
> > I think verification of the server certificates is not supported either. 
> > SSL only serves for encryption, not authentication or integrity checking
> > (which is probably a stupid idea).
> 
> I have this feeling that SSL in PostgreSQL isn't category 1 supported if you 
> can put it that way. Maybe I'm wrong?
> 
> Another way to ensure encrypted (and authenticated, I believe) connections is 
> to use stunnel with PostgreSQL.
> 
> I'm not sure which solution is the best. SSL in PostgreSQL is integrated. 
> Stunnel has the advantage of being more generic. having tried none, I don't 
> know about performance.

stunnel is a possible solution, but it'll make it difficult to determine
remote connections, as you'll only ever see 127.0.0.1 in your logs.

As I said in my other reply, the code to do most of this is already
there, it's just #ifdef'd out.

-Dom

Tue, 21 Sep 2004 10:37:17 +0100
dom@happygiraffe.net (Dominic Mitchell)
Re: SSL Support
On Tue, Sep 21, 2004 at 10:35:56AM +0100, Dominic Mitchell wrote:

> I think it's misleading to talk about SSL being supported without these
> options.  I've used SSL in other places (apache/mod_ssl, curl, stunnel)
> and I came to expect this sort of verification as standard behaviour.
> What's more, the code is there to do it, it's just #ifdef'd out, or
> needs a toggle.

I think we got to the current situation because somebody started to
enhance the previous (apparently poor) SSL support, and then fled in the
middle of his hacking.  What this code needs is a mantainer.  If you
want to work on it, it will be surely appreciated.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No hay hombre que no aspire a la plenitud, es decir,
la suma de experiencias de que un hombre es capaz"


Tue, 21 Sep 2004 09:44:43 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: SSL Support
dom@happygiraffe.net (Dominic Mitchell) writes:
> On Tue, Sep 21, 2004 at 10:17:51AM +0200, Peter Eisentraut wrote:
>> Am Dienstag, 21. September 2004 09:24 schrieb Dominic Mitchell:
>>> In initialize_SSL(), we call SSL_CTX_set_verify(), but we don't pass
>>> in the SSL_VERIFY_FAIL_IF_NO_PEER_CERT flag.  This means that a client
>>> can present no certificate and still get access to the server.

> The code is all there to do so, pretty much.  What it's missing is a few
> toggles to make it say "I want to enforce this to happen".

This is intentional.  See past discussions.

			regards, tom lane

Tue, 21 Sep 2004 09:52:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SSL Support 
Tom Lane wrote:
> dom@happygiraffe.net (Dominic Mitchell) writes:
>>On Tue, Sep 21, 2004 at 10:17:51AM +0200, Peter Eisentraut wrote:
>>>Am Dienstag, 21. September 2004 09:24 schrieb Dominic Mitchell:
>>>>In initialize_SSL(), we call SSL_CTX_set_verify(), but we don't pass
>>>>in the SSL_VERIFY_FAIL_IF_NO_PEER_CERT flag.  This means that a client
>>>>can present no certificate and still get access to the server.
> 
> 
>>The code is all there to do so, pretty much.  What it's missing is a few
>>toggles to make it say "I want to enforce this to happen".
> 
> This is intentional.  See past discussions.

Ok, I'll go and review them and stick to documentation patches.  I hope 
I can avoid other people being surprised in the manner I was.

Thanks,
-Dom

Tue, 21 Sep 2004 19:11:48 +0100
Dominic Mitchell <dom@happygiraffe.net>
Re: SSL Support
dom@happygiraffe.net (Dominic Mitchell) writes:
> I've just spent a while this afternoon attempting to get SSL support
> working.  It appears to be lacking in a few areas, foremost
> documentation.

After reviewing this stuff, it's clear that there were some things that
were supposed to work but were broken.  The agreement that we'd come to
about SSL support, as I understood it, was that we'd not *require*
client certificates to be present and validatable, so that it would be
possible to use SSL in an encryption-but-not-authentication fashion.
However there was never any intention to *prevent* use of the
authentication features.  Unfortunately it seems a lot of bit rot
has snuck in, and I'm not sure some of the bugs weren't there all along
(the original SSL patch had a lot of problems :-().

I've committed a patch that gets it up to minimum functionality, ie,
the server and client do seem to both detect bogus certificates on the
other end when they have root.crt files to work from.  I'm no SSL expert
though, so it could be there's still work to be done.

>   In initialize_SSL(), we call SSL_CTX_set_verify(), but we don't pass
>   in the SSL_VERIFY_FAIL_IF_NO_PEER_CERT flag.

Seems to have been put on the wrong set_verify call :-(.  Fixed.

>   In initialize_SSL(), we call SSL_CTX_set_verify_depth(SSL_context, 1).
>   This should probably be a configurable item.

I can't see a reason to have it at all, especially when there is no
corresponding limitation on the server side.  Removed.

>   In open_client_SSL(), the call to SSL_get_verify_result() is commented
>   out.  This means that crucial things such as the validity of the
>   certificate of the server you are connecting to is not checked.  So
>   the client will happily connect to an expired certificate.

Not in my testing ... it could be we should also uncomment that, but
I'm unconvinced that it's needed.

			regards, tom lane

Sun, 26 Sep 2004 19:09:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SSL Support 