Re: Ref: BUG#1321: SSL error: sslv3 alert handshake failure

hi TJ,
thanks for your input but i think i am still doing something wrong ...
I have done exactly what the mysql site said ...
The way we have it set up, changing the version really hampers work
for many (as it is under use all the time) ... Can i achieve this
without changing the version to 8.0.3 ...

Now i have the following setup :
SERVER (192.168.200.10)
----------------------------------------
(a) /usr/local/pgsql/data
-rw-r--r-- 1 postgres postgres 1298 Aug 24 16:10 root.crt
-rw-r--r-- 1 postgres postgres 963 Aug 24 16:10 root.key
-rw-r--r-- 1 postgres postgres 3675 Aug 24 16:10 server.crt
-rw------- 1 postgres postgres 887 Aug 24 16:10 server.key
-rw-r--r-- 1 postgres postgres 2305 Aug 24 13:05 server.req
(b) /usr/local/pgsql/data/postgresql.conf
ssl=true
(c) /usr/local/pgsql/data/pg_hba.conf
local all all trust
host all all 127.0.0.1 255.255.255.255 trust
host all all 192.168.0.0/16 trust
hostssl dbm all 192.168.200.201 255.255.255.255 md5
(d) Postmaster command: (as postgres user)
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data/ -i >logfile 2>&1 &
I checked and it runs well ...
(e) logfile
LOG: database system was interrupted at 2005-08-24 13:12:32 PDT
LOG: checkpoint record is at 0/644F40E0
LOG: redo record is at 0/644F40E0; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 12230; next OID: 11903822
LOG: database system was not properly shut down; automatic recovery in progress
LOG: record with zero length at 0/644F411C
LOG: redo is not required
LOG: database system is ready
LOG: could not accept SSL connection: peer did not return a certificate
LOG: could not accept SSL connection: peer did not return a certificate
LOG: could not accept SSL connection: peer did not return a certificate
LOG: could not accept SSL connection: peer did not return a certificate
LOG: could not accept SSL connection: peer did not return a certificate

CLIENT (192.168.200.201) (logged in as root)
------------------------------------------------------------------
(a) psql -d dbm -c 'select * from node limit 1;' -U postgres -h 192.168.200.10
psql: SSL error: sslv3 alert handshake failure
(b) ~/.postgresql/
-rw-r--r-- 1 root root 3675 Aug 24 16:25 postgresql.crt
-rw-r--r-- 1 root root 887 Aug 24 16:25 postgresql.key
(c) when ssl turned off, the query runs well ... so its not the query
or LAN ... there must be something wrong in the way i am doing it ...

On 8/24/05, T.J. Ferraro <tjtoocool(at)phreaker(dot)net> wrote:
> Vishal,
>
> I can't recall but I think if you use 8.0.3 the error would offer
> more explanation. Bug 1321 was related to SSL support on windows. The
> error you are getting isn't a bug. It's basically telling you it can't
> find the postgresql.key and postgresql.crt.
>
> For creating SSL certs I've always used the MySQL example at
> http://dev.mysql.com/doc/mysql/en/secure-create-certs.html
>
> You can ignore the last part about setting up my.cnf. Instead of doing
> the "replace ./demoCA $DIR -- $DIR/openssl.cnf" as they suggest just
> manually open your openssl.cnf file and change the ./demoCA to the
> location of your openssl.cnf file.
>
> Once finished rename some files:
>
> cacert.pem => root.crt
> cakey.pem => root.key
> server-key.pem => server.key
> server-cert.pem => server.crt
> client-key.pem => postgresql.key
> client-cert.pem => postgresql.crt
>
> Place the postgresql.* files into the ~/.postgresql directory on the
> client side.
>
> So that answers a).
>
> b) The client shouldn't be looking for root.crt
>
> c) That should do it. If you have any more questions feel free to email
> me off the list and I'll see what else ya need help with.
>
> T.J.
>
> vishal saberwal wrote:
>
> >hi,
> >I was reading the post for BUG # 1321 (Date: Tue, 16 Nov 2004 13:30:33
> >-0700) the error "sslv3 alert handshake failure" that i have
> >encountered now.
> >
> >I have postgreSQL 8.0.1 and have OpenSSL 0.9.7a.
> >I have read through the documentation and a few other sites.
> >
> >PG_HBA.CONF
> >----------------------
> >local all all trust
> >host all all 127.0.0.1 255.255.255.255 trust
> >host all all 192.168.0.0/16 trust
> >hostssl dbm all 192.168.200.201 255.255.255.255 md5
> >
> >SERVER
> >--------------
> >(Fedora Core 2)
> >
> >Database =DBM, owner=dbmuser
> >
> >I have done the following steps:
> >(a) openssl req -new -text -out server.req
> >(b) openssl rsa -in privkey.pem -out server.key
> >(c) rm privkey.pem
> >(d) openssl req -x509 -in server.req -text -key server.key -out server.crt
> >(e) chmod og-rwx server.key
> >
> >Above steps were from the documentation for 8.0. I tested the
> >communication to a remote machine (hostssl'ed with md5 in pg_hba.conf)
> >and the data (when viewed on ethereal) was encrypted.
> >
> >(f) ln -s server.crt root.crt
> >(g) ln -s server.key root.key
> >
> >Now i have [root.crt, root.key, server.crt, server.key, server.req]
> >files in /usr/local/pgsql/data (which is my $PGDATA) which are owned
> >by "chown postgres:postgres".
> >
> >CLIENT
> >------------
> >psql -d dbm -c 'select * from hosts;' -U postgres -h 192.168.200.201
> >
> >Here's where i am stuck.
> >I created the directory ~/.postgresql as it never existed on remote
> >machine (Fedora core 3) as user root.
> >
> >I have tried stopping and restarting the postmaster.
> >
> >I have following Questions:
> >(a) How do i create the ~/.postgresql/postgresql.crt and
> >~/.postgresql/postgresql.key files (not sure about the commands)?
> >(b) Where do i get the ~/.postgresql/root.crt from?
> >(c) Is there anything else i am missing thats not in document or that
> >i need to know?
> >
> >thanks,
> >vish
> >(Vishal Saberwal)
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 2: Don't 'kill -9' the postmaster
> >
> >
> >
>
>
>