Postgresql and SSL

Benjamin Smith wrote:

But I am stumped as to how to create a client certificate
that's enforced!

I tried the instructions found
http://marc.info/?l=tomcat-user&m=106293430225790&w=2

and used the "ca.pem" created there as the postgres root.crt
and although the PG daemon no longer indicates that it
couldn't find root.crt, it also doesn't require a client
certificate installed to access with psql.

Any pointers for somebody who is NOT an ssl guru? (like myself!)

What does pg_hba.conf look like?

What is the psql command line you use to connect to the database?

Yours,
Laurenz Albe

On Thursday 20 September 2007 05:15:39 Albe Laurenz wrote:

What does pg_hba.conf look like?

I don't know if I misunderstood him, but I thought he was willing to have SSL
on both sides, i.e., both the client and the server identify themselves
trough SSL certificates.

Even though one can require connections using only SSL on the server side, I
don't see a method (in pg_hba.conf) that would allow clients with SSL
certificates.

All I have here is:

# METHOD can be "trust", "reject", "md5", "crypt", "password",
# "krb5", "ident", "pam" or "ldap". Note that "password" sends passwords
# in clear text; "md5" is preferred since it sends encrypted passwords.

At least, this is what I understand by "client certificate"...

--
Jorge Godoy <jgodoy@gmail.com>

Jorge Godoy wrote:

What does pg_hba.conf look like?

I don't know if I misunderstood him, but I thought he was
willing to have SSL on both sides, i.e., both the client
and the server identify themselves trough SSL certificates.

I had the same impression.

Even though one can require connections using only SSL on the
server side, I don't see a method (in pg_hba.conf) that
would allow clients with SSL certificates.

Nor do I.

The complaint was that he could connect even if he didn't
have a valid client certificate.

That would mean that the connection was not established
with SSL. I want to see the pg_hba.conf to see if there is
an entry that allows him to connect without using SSL,
like a "host" entry.

Yours,
Laurenz Albe

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:

Jorge Godoy wrote:

Even though one can require connections using only SSL on the
server side, I don't see a method (in pg_hba.conf) that
would allow clients with SSL certificates.

Nor do I.

If you mean *require* clients to have certificates, that's not
determined by pg_hba.conf, it's determined by whether you provide
a root.crt file. See
http://www.postgresql.org/docs/8.2/static/ssl-tcp.html

regards, tom lane

On Thursday 20 September 2007 11:41:00 Tom Lane wrote:

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:

Jorge Godoy wrote:

Even though one can require connections using only SSL on the
server side, I don't see a method (in pg_hba.conf) that
would allow clients with SSL certificates.

Nor do I.

If you mean *require* clients to have certificates, that's not
determined by pg_hba.conf, it's determined by whether you provide
a root.crt file. See
http://www.postgresql.org/docs/8.2/static/ssl-tcp.html

Thank you! Complemented with
http://www.postgresql.org/docs/8.2/static/libpq-ssl.html this is exactly
what we were guessing the OP asked for...

I'll have to dig if the libraries I use support that. It would be much more
interesting changing certificates once a year than hardcoding passwords on
code...

--
Jorge Godoy <jgodoy@gmail.com>

Jorge Godoy wrote:

I'll have to dig if the libraries I use support that. It
would be much more interesting changing certificates once
a year than hardcoding passwords on code...

But remember that you will still be asked for a password
unless you use trust authentication.

Yours,
Laurenz Albe