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 =3DDBM, owner=3Ddbmuser

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

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

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/se...ate-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:



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

hi TJ,
thanks for your input but i think i am still doing something wrong ...=20
I have done exactly what the mysql site said ...=20
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=3Dtrue
(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=20
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 pro=
gress
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:
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Thanks a lot TJ,
You were right ...
I installed 8.0.3 and then placed the certificates as suggested by you
.... It worked ...

But, Management requires me to use 8.0.1 (on both servers and clients)
as our total infrastructure and coding is based on it ... Is there a
patch I can apply to 8.0.1 so that I could install same version on all
clients and servers ...

I also looked at the below code (from old version but of relevance to
know the permissions expected ) ...
/*
* make sure $HOME/.postgresql/postgresql.key file exists,
* is regular file, owned by current user, and not group-
* or world-accessable.
*/
snprintf(fnbuf, sizeof fnbuf, "%s/.postgresql/postgresql.key",=20
pwd->pw_dir);
if (lstat(fnbuf, &buf) =3D=3D -1)
{
fprintf(stderr, "certificate file exists, but no private key\n");
SSL_use_certificate(ssl, NULL);
return -1;
}
if (!S_ISREG(buf.st_mode) || buf.st_uid !=3D uid ||
(buf.st_mode & (S_IRWXG | S_IRWXO)) !=3D 0)
{
fprintf(stderr,
"private key file has wrong ownership or permissions\n");
SSL_use_certificate(ssl, NULL);
return -1;
}

thanks again,
vish

On 8/24/05, T.J. Ferraro <tjtoocool (AT) phreaker (DOT) net> wrote:
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

vishal saberwal <vishalsaberwal (AT) gmail (DOT) com> writes:
It looks to me like you've got the server side set up correctly, but not
the client side. The client needs to have a certificate to return when
the server demands it. See
http://www.postgresql.org/docs/8.0/s...libpq-ssl.html

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

vishal saberwal <vishalsaberwal (AT) gmail (DOT) com> writes:
There's not really going to be a whole lot of sympathy around here for
people who refuse to adopt a point update.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

hi tom,
I agree with what you said ... They considered 8.0.1 as a stable
version they can rely on (but i believe i can propose a change now
after this SSL fix in 8.0.3) ...

Can you direct me to a website that shows the bug fixes and new
features in 8.0.3 when compared to 8.0.1 and 8.0.2 ...

thanks a lot TJ and Tom ...
you've been a great help ...

vish

On 8/24/05, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

vishal saberwal <vishalsaberwal (AT) gmail (DOT) com> writes:
Well, actually, I just looked through the CVS logs, and I do not
see any changes related to SSL between 8.0.1 and 8.0.3; the only
significant changes in libpq at all were fixes to make Kerberos
work on Windows.

So I'm wondering if the 8.0.3 update was irrelevant, and the important
point was just to get the client-side key files installed correctly.

As far as fighting with your management goes, you might want to wait
a week and then lean on them to update to 8.0.4, instead. There are
a couple of data-loss risks fixed for 8.0.4.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Yes you are right again tom ...
When i checked, i found that fedora core came with postgresql 7.4 ...
hence the reason for my SSL not working ...

Installed 8.0.1 on the client and yes it works ...

Guess i can stay on 8.0.1 until we get 8.0.4 ... (is it right for me
to ask what data-loss you are talking about) ...

thanks again,
vish

On 8/24/05, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

vishal saberwal <vishalsaberwal (AT) gmail (DOT) com> writes:
Yeah, they're all documented in the pgsql-committers archives.
Let's see...

2005-08-19 20:39 tgl

* src/: backend/access/heap/heapam.c, backend/commands/async.c,
backend/commands/trigger.c, backend/commands/vacuum.c,
backend/executor/execMain.c, backend/utils/time/tqual.c,
include/access/heapam.h, include/access/htup.h,
include/executor/executor.h, include/utils/tqual.h: Repair problems
with VACUUM destroying t_ctid chains too soon, and with
insufficient paranoia in code that follows t_ctid links. (We must
do both because even with VACUUM doing it properly, the
intermediate state with a dangling t_ctid link is visible
concurrently during lazy VACUUM, and could be seen afterwards if
either type of VACUUM crashes partway through.) Also try to improve
documentation about what's going on. Patch is a bit bulky because
passing the XMAX information around required changing the APIs of
some low-level heapam.c routines, but it's not conceptually very
complicated. Per trouble report from Teodor and subsequent
analysis. This needs to be back-patched, but I'll do that after
8.1 beta is out.

2005-06-25 18:47 tgl

* doc/src/sgml/backup.sgml, src/backend/commands/dbcommands.c
(REL8_0_STABLE), doc/src/sgml/backup.sgml,
src/backend/commands/dbcommands.c: Force a checkpoint before
committing a CREATE DATABASE command. This should fix the recent
reports of "index is not a btree" failures, as well as preventing a
more obscure race condition involving changes to a template
database just after copying it with CREATE DATABASE.

2005-05-31 15:10 tgl

* src/backend/access/transam/: xlog.c (REL7_3_STABLE), xlog.c
(REL7_4_STABLE), xlog.c (REL7_2_STABLE), xlog.c (REL8_0_STABLE),
xlog.c: Add test to WAL replay to verify that xl_prev points back
to the previous WAL record; this is necessary to be sure we
recognize stale WAL records when a WAL page was only partially
written during a system crash.

I seem to recall another one, but am not seeing it in the logs right
now. There are also the usual quota of plain old crashing bugs.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings