Re: SSL cleanups/hostname verification - Mailing list pgsql-hackers

On Mon, Oct 20, 2008 at 05:50, Magnus Hagander <magnus@hagander.net> wrote:
> Attached patch cleans up the certificate verification in libpq, and adds
> a configuration paraqmeter to control it. The new parameter is
> "sslverify", and can be set to:
>
> * cn = default = will validate that the certificate chains to a trusted
> root, *and* that the cn on the certificate matches the hostname
> specificed in the connection. This is the only option that prevents
> man-in-the-middle attacks completely, and therefor is the default.
>
> * cert = what we had before if there was a root certificate file = will
> validate that the certificate chains to a trusted root, but ignore the cn.
>
> * none = will disable certificate validation completely

Hrm I must be misunderstanding something cant get it to work for me
(or maybe I am and I just don't understand exactly?). Can you give me
a pointer to where im going wrong?

$ echo $HOSTNAME
bahdushka

### first try a ca with a bad common name
$ openssl genrsa -out bca.key

$ openssl req -new -key bca.key -out bca.csr
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:asdf
Email Address []:

$ openssl x509 -req -days 3650 -in bca.csr -signkey bca.key -out bca.crt
Signature ok
subject=/C=US/ST=Some-State/O=Internet Widgits Pty Ltd/CN=asdf
Getting Private key

$ openssl genrsa -out bpg.key

$ openssl req -new -key bpg.key -out bpg.csr
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:1234
Email Address []:

$ openssl x509 -req -days 365 -CA bca.crt  -CAkey bca.key
-CAcreateserial -in bpg.csr -out bpg.crt
Signature ok
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=1234
Getting CA Private Key

$ cp bca.crt ~/.postgresql/root.crt
$ cp bca.crt data/root.crt
$ cp bca.key data/root.key
$ cp bpg.crt ~/.postgresql/postgresql.crt
$ cp bpg.key ~/.postgresql/postgresql.key
$ cp bpg.crt data/server.crt
$ cp bpg.key data/server.key
$ chmod 0600 data/root*
$ chmod 0600 data/server*
$ chmod 0600 ~/.postgresql/*

$ SSLVERIFY=cn ./psql junk -h bahdushka
psql (8.4devel)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

junk=#

$ SSLVERIFY=cn ./psql junk -h 192.168.0.2
psql: server common name 'bahdushka' does not match hostname
'192.168.0.2'FATAL:  no pg_hba.conf entry for host "192.168.0.2", user
"alex", database "junk", SSL off

$ SSLVERIFY=none ./psql junk -h 192.168.0.2
psql: server common name 'bahdushka' does not match hostname
'192.168.0.2'FATAL:  no pg_hba.conf entry for host "192.168.0.2", user
"alex", database "junk", SSL off

### hrm ok must not be ca common name
### so now lets make a ca with a good common name
$ openssl genrsa -out ca.key

$ openssl req -new -key ca.key -out ca.csr
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:bahdushka
Email Address []:

$ openssl x509 -req -days 3650 -in ca.csr -signkey ca.key -out ca.crt
Signature ok
subject=/C=US/ST=Some-State/O=Internet Widgits Pty Ltd/CN=bahdushka
Getting Private key

### now make a key that has a good cn just to make sure

$ openssl genrsa -out postgres.key

$ openssl req -new -key postgres.key -out postgres.csr
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:bahdushka
Email Address []:

$ openssl x509 -req -days 365 -CA ca.crt  -CAkey ca.key
-CAcreateserial -in postgres.csr -out postgres.crt
Signature ok
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=bahdushka
Getting CA Private Key

$ cp ca.crt data/root.crt
$ cp ca.key data/root.key
$ cp postgres.crt data/server.crt
$ cp postgres.key data/server.key
$ rm ~/.postgresql/*

# restart postgres

$ SSLVERIFY=none ./psql junk -h bahdushka
psql: root certificate file (/home/alex/.postgresql/root.crt)

$ cp ca.crt ~/.postgresql/root.crt

$ SSLVERIFY=none ./psql junk -h bahdushka
psql (8.4devel)
Type "help" for help.

junk=#
LOG:  could not accept SSL connection: peer did not return a certificate

$ SSLVERIFY=cn ./psql junk -h bahdushka
psql (8.4devel)
Type "help" for help.

junk=#
LOG:  could not accept SSL connection: peer did not return a certificate

$ cp postgres.crt ~/.postgresql/postgresql.crt
$ cp postgres.key ~/.postgresql/postgresql.key
$ chmod 0600 ~/.postgresql/*

$ SSLVERIFY=cn ./psql junk -h 127.0.0.1
psql (8.4devel)
Type "help" for help.

junk=#
LOG:  could not receive data from client: Connection reset by peer

$ SSLVERIFY=cn ./psql junk -h bahdushka
psql (8.4devel)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

junk=#

### now make a crt that has a bad common name
$ openssl genrsa -out pg.key

$ openssl req -new -key pg.key -out pg.csr
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:asdf
Email Address []:

$ openssl x509 -req -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial
-in pg.csr -out pg.crt
Signature ok
subject=/C=US/ST=Some-State/O=Internet Widgits Pty Ltd/CN=asdf
Getting CA Private Key

$ cp pg.crt ~/.postgresql/postgresql.crt
$ cp pg.key ~/.postgresql/postgresql.key
$ chmod 0400 ~/.postgresql/*

$ SSLVERIFY=cn ./psql junk -h bahdushka
psql (8.4devel)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

junk=#

### ok no difference here must be the other way bad cn on the server

$ pg_ctl -D data -m fast stop
$ cp pg.crt data/server.crt
$ cp pg.key data/server.key
$ chmod 0600 data/server.*
$ ./postgres  -D data/
LOG:  SSL certificate revocation list file "root.crl" not found,
skipping: No such file or directory
DETAIL:  Certificates will not be checked against revocation list.
LOG:  could not create IPv6 socket: Address family not supported by protocol
LOG:  database system was shut down at 2008-11-10 23:10:21 MST
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections

$ SSLVERIFY=cn ./psql junk -h bahdushka
psql (8.4devel)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

junk=#

### ok now lets try one not signed by the CA
$ openssl req -new -text -out server.req
Generating a 1024 bit RSA private key
....++++++
......++++++
writing new private key to 'privkey.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:

Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:bahdushka
Email Address []:

$ openssl rsa -in privkey.pem -out server.key
Enter pass phrase for privkey.pem:
writing RSA key

$ openssl req -x509 -in server.req -test -key server.key -out server.crt

$ cp server.crt ~/.postgresql/postgresql.crt
$ cp server.key ~/.postgresql/postgresqlkey
$ chmod 0600 ~/.postgresql/*

$ SSLVERIFY=cn ./psql junk -h bahdushka
psql (8.4devel)
Type "help" for help.

junk=#
LOG:  could not accept SSL connection: no certificate returned

$ SSLVERIFY=none ./psql junk -h bahdushka
psql (8.4devel)
Type "help" for help.

junk=#
LOG:  could not accept SSL connection: no certificate returned

But other than that looks good other than the promised documentation
and the mem leak Tom Lane noted. (unless I missed an updated patch?)

(ptr to the documentation I was referring to ...)
On Tue, Oct 21, 2008 at 01:09, Magnus Hagander <magnus@hagander.net> wrote:
> On 21 okt 2008, at 10.04, Peter Eisentraut <peter_e@gmx.net> wrote:
>
>> Magnus Hagander wrote:
>>>
>>> Robert Haas wrote:
>>>>>>
>>>>>> How can you make that the default?  Won't it immediately break every
>>>>>> installation without certificates?
>>>>>
>>>>> *all* SSL installations have certificate on the server side. You cannot
>>>>> run without it.
>>>>
>>>> s/without certificates/with self-signed certificates/
>>>>
>>>> which I would guess to be a common configuration
>>>
>>> Self-signed still work. In a self-signed scenario, the server
>>> certificate *is* the CA certificate.
>>
>> But the user needs to copy the CA to the client, which most people
>> probably don't do nowadays.
>
> True. I'll update the docs to make this even more clear, for those who don't
> know ssl. I still consider that a feature and not a problem ..