Subject: no verification of client certificate?

From: Ray Stell <[email-masked]>
Date: Fri, 23 Mar 2007 14:16:26 -0400

8.2.3

I was hoping to not have to support client certs.  I want
encryption and to verify the server, but no to verify the client.
Does this work and I've got the config wrong?

I get ssl connections if I add in the .postgresql/postgresql.crt+key.
Results seem similar to this report:

http://archives.postgresql.org/pgsql-general/2006-11/msg01148.php

---

From: Tom Lane <[email-masked]>
Date: Fri, 23 Mar 2007 18:01:17 -0400

> Does this work and I've got the config wrong?

Maybe I misunderstand what you want --- doesn't leaving out the
server's root.crt file do that?

			regards, tom lane

---

From: "Daniel Ricardo Medina" <[email-masked]>
Date: Fri, 23 Mar 2007 18:19:03 -0500

Ayuda por favor

Cuando ejecuto este comando me sale una doble tupla del usuario
postgres, esto me error cuando ejecuto el comando
Pg_dump para realizar el respectivo backup..

radar=# select * from pg_user;
  usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  |
valuntil | useconfig
-----------+----------+-------------+----------+-----------+----------+-
---------+-----------
 postgres  |        1 | t           | t        | t         | ******** |
|
 postgres  |        1 | t           | t        | t         | ******** |
|
 userradar |      100 | f           | f        | f         | ******** |
|
(3 rows)

He intentado borrar los usuario directamente de la tabla subiendo
postgres com postgres -O -P  -D directorio

Y corriendo estos comandos..

select * from pg_shadow;
delete from pg_shadow;
CREATE USER postgres WITH CREATEDB CREATEUSER PASSWORD '***' SYSID 1;
CREATE USER  userradar  PASSWORD '***' 

Pero me crea los mismos tres usuarios en la tabla pg_shadow, el problema
es que no he podido hacer el backup y ya no tengo espacio en la
particion donde tengo montada la base

---

From: Ray Stell <[email-masked]>
Date: Fri, 23 Mar 2007 22:04:34 -0400

> server's root.crt file do that?

It doesn't look like it to me.  I hope you can steer me back.

When I first looked at the ssl doc, I didn't see any description of
installing the root ca on the client.  This seemed odd.  On my web client,
when I need to verify the server crt, I install the appropriate ca in
the client.  

Anyway, two permutations of the various config items provided ssl
connections.  One was with a client crt and the other was, as you said,
no root crt on the server datadir.  The verions without the client cert
was closer to what I was after.  

I describe the config here:

no root.crt in the data dir 
no .postgresql/    <--- this is what made me think there was no server verification
server.crt/key in the data dir
pg_hba.conf set to hostssl
PGSSLMODE=required or prefer 

connect:
--------
$  psql  -h serve.vt.edu -p 5437 testdb jira
Password for user jira:
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.

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

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

strace the above connection, it tries/fails to open the client ca :
--------------------------------------------------------------------
stat64("/home/postgresql/.postgresql/root.crt", 0xbfee27d0) = -1 ENOENT (No such file or directory)
stat64("/home/postgresql/.postgresql/root.crt", 0xbfee27d0) = -1 ENOENT (No such file or directory)

So, it looks to me like I get encryption this way, but no server verification.  Hope I'm wrong.  
Thanks for you help.

---

From: Tom Lane <[email-masked]>
Date: Sun, 25 Mar 2007 22:01:20 -0400

> It doesn't look like it to me.  I hope you can steer me back.

I looked more closely and you are right: if the server does not have
a root.crt file then it doesn't send its server cert to the client,
and so there's no way for the client to verify the cert.  Whereas if
it does have root.crt then it insists on verifying the client's cert.
This seems to be a restriction of OpenSSL: sending of the server cert is
implicitly enabled by enabling checking of client certs using root.crt.
Perhaps there's a way around that, but it'll take more knowledge of
OpenSSL than I have to fix it.

Offhand your desire doesn't seem completely unreasonable, so perhaps
there is a way to get OpenSSL to do it that we don't know about.
Bruce, would you add something to the TODO list?

* Support SSL configurations in which client checks server's cert but
  not vice versa.

			regards, tom lane

---

From: Michael Fuhr <[email-masked]>
Date: Sun, 25 Mar 2007 20:57:13 -0600

On Sun, Mar 25, 2007 at 10:01:20PM -0400, Tom Lane wrote:
> and so there's no way for the client to verify the cert.

Eh?  ssldump shows otherwise here with 8.2.3.  Here's a connection
where the server has server.key and server.crt but no root.crt, and
the client has the root.crt that signed server.crt and nothing else
in ~/.postgresql:

1 1  0.0338 (0.0338)  C>S  Handshake      ClientHello
1 2  0.5179 (0.4841)  S>C  Handshake      ServerHello
1 3  0.5179 (0.0000)  S>C  Handshake      Certificate
1 4  0.5181 (0.0001)  S>C  Handshake      ServerKeyExchange
1 5  0.5181 (0.0000)  S>C  Handshake      ServerHelloDone
1 6  0.6115 (0.0934)  C>S  Handshake      ClientKeyExchange
1 7  0.6115 (0.0000)  C>S  ChangeCipherSpec
1 8  0.6115 (0.0000)  C>S  Handshake
1 9  0.9605 (0.3489)  S>C  ChangeCipherSpec
1 10 0.9605 (0.0000)  S>C  Handshake

The client is now connected with DHE-RSA-AES256-SHA.

Here's a dump with the same server configuration (server.key, server.crt,
no root.crt) but now the client has a different root.crt than the one
that signed server.crt:

1 1  0.0335 (0.0335)  C>S  Handshake      ClientHello
1 2  0.5626 (0.5290)  S>C  Handshake      ServerHello
1 3  0.5626 (0.0000)  S>C  Handshake      Certificate
1 4  0.5628 (0.0001)  S>C  Handshake      ServerKeyExchange
1 5  0.5628 (0.0000)  S>C  Handshake      ServerHelloDone
1 6  0.5644 (0.0016)  C>S  Alert          fatal          unknown_ca

If the client has PGSSLMODE set to "require" then the connection
fails at the client with "psql: SSL error: certificate verify failed"
and the server logs "could not accept SSL connection: tlsv1 alert
unknown ca".

Did you run any tests?  If so, how did you generate the server's
certificate?

-- 
Michael Fuhr

---

From: Tom Lane <[email-masked]>
Date: Mon, 26 Mar 2007 00:04:21 -0400

> Eh?  ssldump shows otherwise here with 8.2.3.

Well, if it works then why is the OP complaining?

Perhaps there is some non-obvious configuration issue that accounts
for the difference between your results and his?

			regards, tom lane

---

From: Michael Fuhr <[email-masked]>
Date: Sun, 25 Mar 2007 23:21:25 -0600

> for the difference between your results and his?

I don't see in the OP's messages that he's tried the configuration

> > PGSSLMODE=required or prefer

My test configuration looks the same on the server but different
on the client:

Server, in $PGDATA
==================
server.key
server.crt (signed by some CA)
no root.crt

Client, in ~/.postgresql
========================
root.crt (for the CA that signed server.crt)
no postgresql.key or postgresql.crt

The OP did say that 

> > when I need to verify the server crt, I install the appropriate ca in

The "SSL Support" section of the libpq documentation mentions
installing root.crt on the client:

http://www.postgresql.org/docs/8.2/interactive/libpq-ssl.html

"If the file ~/.postgresql/root.crt is present in the user's home
directory, libpq will use the certificate list stored therein to
verify the server's certificate.  (On Microsoft Windows the file is
named %APPDATA%\postgresql\root.crt.)  The SSL connection will fail
if the server does not present a certificate; therefore, to use
this feature the server must also have a root.crt file."

The requirement that the server have a root.crt appears to be
incorrect, at least in the tests I ran.  Unless somebody can justify
that statement I'll submit a documentation patch to correct it.

-- 
Michael Fuhr

---

From: Ray Stell <[email-masked]>
Date: Mon, 26 Mar 2007 09:03:53 -0400

Thanks, I'll see if I can test that this morning.  

A minor suggestion, perhaps said config or perhaps a reference to the
Chapter 29. libpq - C Library could be added to the section on ssl:

http://www.postgresql.org/docs/8.2/interactive/ssl-tcp.html

If you don't mind, how do you use ssldump to trace a psql setup?
That would be very helpful to know.

---

From: Ray Stell <[email-masked]>
Date: Mon, 26 Mar 2007 09:35:33 -0400

> Well, if it works then why is the OP complaining?

Two reasons:

1. I was following:

I did not know this page existed:

Connecting the two pages would have helped me. 

2. I probably made a mistake trying the various combinations.
Knowing how Michael traced the connection with ssldump would be
VERY helpful.  Trying to put it together from strace is much harder
and I probably made multiple mistakes.  I was on a fishing expedition
at best as I didn't know how it went together.

---

From: Michael Fuhr <[email-masked]>
Date: Mon, 26 Mar 2007 07:42:53 -0600

> If you don't mind, how do you use ssldump to trace a psql setup?

I usually capture the connection to a file with tcpdump and then
use ssldump to read the file.  Sometimes I use wireshark (formerly
ethereal) but it's a bit heavyweight for most of my needs.  As for
how to interpret what ssldump shows, I'd recommend reading Eric
Rescorla's _SSL and TLS_.  Rescorla wrote ssldump and used it for
many of the book's diagrams; he's also the author or co-author of
several of the relevant RFCs (e.g., RFC 4346 The Transport Layer
Security (TLS) Protocol Version 1.1).

-- 
Michael Fuhr

---

From: Michael Fuhr <[email-masked]>
Date: Mon, 26 Mar 2007 08:00:04 -0600

On Mon, Mar 26, 2007 at 09:35:33AM -0400, Ray Stell wrote:
> VERY helpful.

The simple way is to run ssldump in one shell while running psql
in another:

ssldump -q port 5482    # my 8.2.3 db listens on port 5482

If I want to do more analysis I usually save the connection with
tcpdump first:

tcpdump -s0 -w dumpfile port 5482

The -s0 option is important: it tells tcpdump to capture the entire
packet.

When I'm done with psql I stop tcpdump and run ssldump over the dumpfile:

ssldump -r dumpfile -q

-- 
Michael Fuhr

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 29 Mar 2007 23:44:58 -0400 (EDT)

I researched this and found that the documentation was wrong because it
said if the client has a 'root.crt', the server must have a 'root.crt',
when in fact on the server a 'server.crt' is required.  Documentation
updated, and mention of libpq SSL section added to server documentation.

The libpq comment verifies this:

    /* Set up to verify server cert, if root.crt is present */

Doc patch attached.  Backpatched to 8.2.X.

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

Michael Fuhr wrote:
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
> 
>                 http://www.postgresql.org/about/donate

-- 
  Bruce Momjian  <[email-masked]>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/libpq.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/libpq.sgml,v
retrieving revision 1.234
diff -c -c -r1.234 libpq.sgml
*** doc/src/sgml/libpq.sgml	20 Feb 2007 19:35:17 -0000	1.234
--- doc/src/sgml/libpq.sgml	30 Mar 2007 03:14:01 -0000
***************
*** 4501,4507 ****
     <filename>%APPDATA%\postgresql\root.crt</filename>.)
     The SSL connection will
     fail if the server does not present a certificate; therefore, to
!    use this feature the server must also have a <filename>root.crt</> file.
     Certificate Revocation List (CRL) entries are also checked if the file
     <filename>~/.postgresql/root.crl</filename> exists (<filename>%APPDATA%\postgresql\root.crl</filename>
     on Microsoft Windows).
--- 4501,4507 ----
     <filename>%APPDATA%\postgresql\root.crt</filename>.)
     The SSL connection will
     fail if the server does not present a certificate; therefore, to
!    use this feature the server must have a <filename>server.crt</> file.
     Certificate Revocation List (CRL) entries are also checked if the file
     <filename>~/.postgresql/root.crl</filename> exists (<filename>%APPDATA%\postgresql\root.crl</filename>
     on Microsoft Windows).
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.380
diff -c -c -r1.380 runtime.sgml
*** doc/src/sgml/runtime.sgml	6 Mar 2007 09:59:22 -0000	1.380
--- doc/src/sgml/runtime.sgml	30 Mar 2007 03:14:04 -0000
***************
*** 1574,1583 ****
     certificates of the <acronym>CA</acronym>(s) 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
!    during SSL connection startup, and it must have been signed by one of the
!    certificates present in <filename>root.crt</filename>.  Certificate 
!    Revocation List (CRL) entries are also checked if the file 
!    <filename>root.crl</filename> exists.
    </para>
  
    <para>
--- 1574,1584 ----
     certificates of the <acronym>CA</acronym>(s) 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
!    during SSL connection startup, and it must have been signed by one of
!    the certificates present in <filename>root.crt</filename>.  (See <xref
!    linkend="libpq-ssl"> for a description of how to set up client
!    certificates.) Certificate Revocation List (CRL) entries are also
!    checked if the file <filename>root.crl</filename> exists.
    </para>
  
    <para>