no verification of client certificate?

Ray Stell <stellr@cns.vt.edu> writes:

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?

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

regards, tom lane

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

On Fri, Mar 23, 2007 at 06:01:17PM -0400, Tom Lane wrote:

Ray Stell <stellr@cns.vt.edu> writes:

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?

Maybe I misunderstand what you want --- doesn't leaving out the
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.

Ray Stell <stellr@cns.vt.edu> writes:

On Fri, Mar 23, 2007 at 06:01:17PM -0400, Tom Lane wrote:

Ray Stell <stellr@cns.vt.edu> writes:

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?

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

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

On Sun, Mar 25, 2007 at 10:01:20PM -0400, Tom Lane wrote:

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.

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

Michael Fuhr <mike@fuhr.org> writes:

On Sun, Mar 25, 2007 at 10:01:20PM -0400, Tom Lane wrote:

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.

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

On Mon, Mar 26, 2007 at 12:04:21AM -0400, Tom Lane wrote:

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?

I don't see in the OP's messages that he's tried the configuration
I used. He said he was using the following:

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

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 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.

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

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.

On Mon, Mar 26, 2007 at 12:04:21AM -0400, Tom Lane wrote:

Michael Fuhr <mike@fuhr.org> writes:

On Sun, Mar 25, 2007 at 10:01:20PM -0400, Tom Lane wrote:

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.

Eh? ssldump shows otherwise here with 8.2.3.

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

Two reasons:

1. I was following:

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

I did not know this page existed:

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

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.

On Mon, Mar 26, 2007 at 09:03:53AM -0400, Ray Stell wrote:

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

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

Knowing how Michael traced the connection with ssldump would be
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

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:

On Mon, Mar 26, 2007 at 12:04:21AM -0400, Tom Lane wrote:

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?

I don't see in the OP's messages that he's tried the configuration
I used. He said he was using the following:

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

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 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.

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Ray Stell wrote:

Thanks, I'd, also, suggest a link on the "Secure TCP/IP Connections with SSL"
page in chapt 16: http://www.postgresql.org/docs/current/static/ssl-tcp.html
to the discussion of the client config issues in chapter 29:
http://www.postgresql.org/docs/8.2/static/libpq-ssl.html
Otherwise, there is no mention of the client .postgresql/ directory, something
I was missing.

That is exactly where I put it:

http://momjian.us/main/writings/pgsql/sgml/ssl-tcp.html

Is that good? You don't see it on the web site because we haven't
rebuilt the documentation for 8.2.X yet.

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

On Thu, Mar 29, 2007 at 11:44:58PM -0400, Bruce Momjian wrote:

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:

On Mon, Mar 26, 2007 at 12:04:21AM -0400, Tom Lane wrote:

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?

I don't see in the OP's messages that he's tried the configuration
I used. He said he was using the following:

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

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 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.

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

--
Bruce Momjian <bruce@momjian.us> 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>

--
I asked for a car, I got a computer. How's that for being born under a bad sign? - Bueller

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +