This patch attempts to note the use of the root.crt file in the server.
Given that PostgreSQL will output a message complaining about it's
absence if you're using SSL mode, I feel it's important that it gets a
mention in the documentation at some point.

-Dom
Wed, 22 Sep 2004 08:05:40 +0100
Dominic Mitchell <dom@happygiraffe.net>
doc patch for ssl in server

Patch applied.  Thanks.  Your documentation changes can be viewed in
five minutes using links at the bottom of the developer's page,
http://developer.postgresql.org/index.php.


---------------------------------------------------------------------------


Dominic Mitchell wrote:
> This patch attempts to note the use of the root.crt file in the server.
> Given that PostgreSQL will output a message complaining about it's
> absence if you're using SSL mode, I feel it's important that it gets a
> mention in the documentation at some point.
> 
> -Dom

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 23 Sep 2004 09:15:43 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: doc patch for ssl in server
Dominic Mitchell <dom@happygiraffe.net> writes:
> +   If verification of client certificates is required, place the
> +   certificates of the <acronym>CA</acronym> you wish to check for in
> +   the file <filename>root.crt</filename> in the data directory.  When
> +   present, a client certificate will be requested from the client
> +   making the connection and it must have been signed by one of the
> +   certificates present in <filename>root.crt</filename>.  If no
> +   certificate is presented, the connection will be allowed to proceed
> +   anway.

That last statement is not actually correct, is it?  AFAICS we do tell
SSL to enforce certificates if we find a valid root.crt file.

			regards, tom lane

Thu, 23 Sep 2004 16:37:52 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: doc patch for ssl in server 
On Thu, Sep 23, 2004 at 04:37:52PM -0400, Tom Lane wrote:
> Dominic Mitchell <dom@happygiraffe.net> writes:
> > +   If verification of client certificates is required, place the
> > +   certificates of the <acronym>CA</acronym> you wish to check for in
> > +   the file <filename>root.crt</filename> in the data directory.  When
> > +   present, a client certificate will be requested from the client
> > +   making the connection and it must have been signed by one of the
> > +   certificates present in <filename>root.crt</filename>.  If no
> > +   certificate is presented, the connection will be allowed to proceed
> > +   anway.
> 
> That last statement is not actually correct, is it?  AFAICS we do tell
> SSL to enforce certificates if we find a valid root.crt file.

Nope, the code says "ask the client to give me a certificate, but carry
on anyway if you don't get one".  The call to SSL_CTX_set_verify in
be-secure.c/initialize_SSL() specifies SSL_VERIFY_PEER |
SSL_VERIFY_CLIENT_ONCE.  According to the docs[1], you also need
SSL_VERIFY_FAIL_IF_NO_PEER_CERT if you want requests that do not send a
certificate to be rejected.  That terminates the connection immediately.
I've no idea what that would do to the server's state at that point.

More to the point, I can definitely connect in this mode:

    % ls -l ~/.postgresql
    total 0
    % sudo ls -l ~pgsql/data
    Password:
    total 36
    -rw-------   1 pgsql  pgsql     4 Dec  5  2003 PG_VERSION
    drwx------  10 pgsql  pgsql   512 Sep 17 22:42 base
    drwx------   2 pgsql  pgsql   512 Sep 23 22:10 global
    drwx------   2 pgsql  pgsql   512 Dec  5  2003 pg_clog
    -r--r--r--   1 pgsql  pgsql  3480 Sep 16 21:28 pg_hba.conf
    -rw-------   1 pgsql  pgsql  1441 Dec  5  2003 pg_ident.conf
    drwx------   2 pgsql  pgsql   512 Sep 21 04:09 pg_xlog
    -r--r--r--   1 pgsql  pgsql  8033 Sep 21 23:37 postgresql.conf
    -rw-------   1 pgsql  pgsql    26 Sep 22 07:38 postmaster.opts
    -rw-------   1 pgsql  pgsql    48 Sep 23 06:01 postmaster.pid
    -rw-r--r--   1 pgsql  pgsql  1204 Sep 16 21:30 root.crt
    -rw-r--r--   1 pgsql  pgsql  3469 Sep 16 21:24 server.crt
    -r--------   1 pgsql  pgsql   887 Sep 16 21:24 server.key
    % psql -h db.happygiraffe.net
    Welcome to psql 7.4.5, the PostgreSQL interactive terminal.

    Type:  \copyright for distribution terms
           \h for help with SQL commands
           \? for help on internal slash commands
           \g or terminate with semicolon to execute query
           \q to quit

    SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

    dom=#

-Dom

[1] http://www.openssl.org/docs/ssl/SSL_CTX_set_verify.html

Thu, 23 Sep 2004 22:11:58 +0100
dom@happygiraffe.net (Dominic Mitchell)
Re: doc patch for ssl in server
dom@happygiraffe.net (Dominic Mitchell) writes:
> On Thu, Sep 23, 2004 at 04:37:52PM -0400, Tom Lane wrote:
>> That last statement is not actually correct, is it?  AFAICS we do tell
>> SSL to enforce certificates if we find a valid root.crt file.

> According to the docs[1], you also need
> SSL_VERIFY_FAIL_IF_NO_PEER_CERT if you want requests that do not send a
> certificate to be rejected.  That terminates the connection immediately.
> [1] http://www.openssl.org/docs/ssl/SSL_CTX_set_verify.html

Hmm.  Reading the SSL man page more closely, you're right.  This is a bug
IMHO --- the intention was that presence of a root.crt file would force
verification.  What we wanted to do was to allow servers to operate
without a root.crt file if they didn't care about verifying client
certificates.

It looks like the original coder simply got this backwards: the backend
code doesn't set SSL_VERIFY_FAIL_IF_NO_PEER_CERT, but the frontend code
does, which is silly because the flag is ignored on the client side.

Does anyone see a reason not to turn on SSL_VERIFY_FAIL_IF_NO_PEER_CERT
on the backend side?

			regards, tom lane

Thu, 23 Sep 2004 17:26:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: doc patch for ssl in server 