Re: PQConnectdb SSL (sslmode): Is this a bug

hi tom and michael,
thanks for your response ... i guess i am not that smart with libraries ...

I am not sure as to how i can find the version of libpq that i am
using on my server. My test file has sslmode=prefer. This is what i
did:
(a)
[root(at)localhost serv]# ./bin/test_lib
Connection failed: SSL error: sslv3 alert handshake failure

ret=-1
[root(at)localhost serv]# ldd ./bin/test_lib
linux-gate.so.1 => (0x0073d000)
libpthread.so.0 => /lib/tls/libpthread.so.0 (0x003c8000)
libpq.so.3 => /usr/lib/libpq.so.3 (0x002ee000)
libstdc++.so.5 => /usr/lib/libstdc++.so.5 (0x00590000)
libm.so.6 => /lib/tls/libm.so.6 (0x002b0000)
libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x004e7000)
libc.so.6 => /lib/tls/libc.so.6 (0x00193000)
/lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x00176000)
libssl.so.4 => /lib/libssl.so.4 (0x00c6a000)
libcrypto.so.4 => /lib/libcrypto.so.4 (0x0076f000)
libkrb5.so.3 => /usr/lib/libkrb5.so.3 (0x006aa000)
libcom_err.so.2 => /lib/libcom_err.so.2 (0x006a5000)
libcrypt.so.1 => /lib/libcrypt.so.1 (0x00caa000)
libresolv.so.2 => /lib/libresolv.so.2 (0x003ff000)
libnsl.so.1 => /lib/libnsl.so.1 (0x00c53000)
libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2 (0x00758000)
libk5crypto.so.3 => /usr/lib/libk5crypto.so.3 (0x00714000)
libdl.so.2 => /lib/libdl.so.2 (0x002d5000)
libz.so.1 => /usr/lib/libz.so.1 (0x002db000)

Then i did (i had done that yesterday too, after when forum adviced me
that i needed 8.0.1 for SSL) ./configure on 8.0.1 source, gmake, gmake
install.

I restarted the postmaster and I tested the file ... again ...
and it was the same output ...

(b) this is where it gets scary and i was not sure if i am doing it right:
[root(at)localhost DBMApi]# export LD_LIBRARY_PATH=/usr/local/pgsql/lib
[root(at)localhost DBMApi]# env | grep "LD_LIBRARY"
LD_LIBRARY_PATH=/usr/local/pgsql/lib
[root(at)localhost DBMApi]# /sbin/ldconfig /usr/local/pgsql/lib
/sbin/ldconfig: /usr/lib/libiodbcadm.so.2 is not a symbolic link

/sbin/ldconfig: /usr/lib/libiodbc.so.2 is not a symbolic link

/sbin/ldconfig: /usr/lib/libiodbcinst.so.2 is not a symbolic link

[root(at)localhost DBMApi]#
[root(at)localhost libk2]# ./bin/test_k2
Connection failed: could not open certificate file
"/root/.postgresql/postgresql.crt": No such file or directory

ret=-1
[root(at)localhost serv]# ldd ./bin/test_lib
linux-gate.so.1 => (0x00f64000)
libpthread.so.0 => /lib/tls/libpthread.so.0 (0x003c8000)
libpq.so.3 => /usr/local/pgsql/lib/libpq.so.3 (0x00712000)
libstdc++.so.5 => /usr/lib/libstdc++.so.5 (0x00590000)
libm.so.6 => /lib/tls/libm.so.6 (0x002b0000)
libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x004e7000)
libc.so.6 => /lib/tls/libc.so.6 (0x00193000)
/lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x00176000)
libssl.so.4 => /lib/libssl.so.4 (0x00c6a000)
libcrypto.so.4 => /lib/libcrypto.so.4 (0x0076f000)
libcrypt.so.1 => /lib/libcrypt.so.1 (0x00caa000)
libresolv.so.2 => /lib/libresolv.so.2 (0x003ff000)
libnsl.so.1 => /lib/libnsl.so.1 (0x00c53000)
libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2 (0x00758000)
libkrb5.so.3 => /usr/lib/libkrb5.so.3 (0x006aa000)
libcom_err.so.2 => /lib/libcom_err.so.2 (0x006a5000)
libk5crypto.so.3 => /usr/lib/libk5crypto.so.3 (0x00111000)
libdl.so.2 => /lib/libdl.so.2 (0x002d5000)
libz.so.1 => /usr/lib/libz.so.1 (0x002db000)
[root(at)localhost serv]# export LD_LIBRARY_PATH=/usr/lib
[root(at)localhost serv]# ldd ./bin/test_lib
linux-gate.so.1 => (0x006cb000)
libpthread.so.0 => /lib/tls/libpthread.so.0 (0x003c8000)
libpq.so.3 => /usr/lib/libpq.so.3 (0x002ee000)
libstdc++.so.5 => /usr/lib/libstdc++.so.5 (0x00590000)
libm.so.6 => /lib/tls/libm.so.6 (0x002b0000)
libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x004e7000)
libc.so.6 => /lib/tls/libc.so.6 (0x00193000)
/lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x00176000)
libssl.so.4 => /lib/libssl.so.4 (0x00c6a000)
libcrypto.so.4 => /lib/libcrypto.so.4 (0x0076f000)
libkrb5.so.3 => /usr/lib/libkrb5.so.3 (0x00309000)
libcom_err.so.2 => /lib/libcom_err.so.2 (0x006a5000)
libcrypt.so.1 => /lib/libcrypt.so.1 (0x00caa000)
libresolv.so.2 => /lib/libresolv.so.2 (0x003ff000)
libnsl.so.1 => /lib/libnsl.so.1 (0x00c53000)
libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2 (0x00758000)
libk5crypto.so.3 => /usr/lib/libk5crypto.so.3 (0x00714000)
libdl.so.2 => /lib/libdl.so.2 (0x002d5000)
libz.so.1 => /usr/lib/libz.so.1 (0x002db000)
[root(at)localhost serv]# ./bin/test_lib
Connection failed: SSL error: sslv3 alert handshake failure

ret=-1
[root(at)localhost serv]#

My 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

CODE
---------------------------
PGConn* connection=PQconnectdb("hostaddr=192.168.200.10 dbname=dbm
user=postgres sslmode=prefer");
if (PQstatus(connection)==CONNECTION_OK)
return 0;
else
{
fprintf(stderr. "connection failure: %s",PQerrorMessage(connection));
return -1;
}

On 8/26/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > Is it possible that your program is linked against an old version
> > of libpq? I can reproduce the above error with an otherwise working
> > 8.0.3 setup if I link the program against a 7.4.8 libpq.
>
> The CVS logs show quite a bit of work done on SSL support between 7.4
> and 8.0, for instance:
>
> 2004-11-19 19:18 tgl
>
> * src/: backend/libpq/be-secure.c, interfaces/libpq/fe-secure.c:
> Improve error reporting for SSL connection failures. Remove
> redundant free operations in client_cert_cb --- openssl will also
> attempt to free these structures, resulting in core dumps.
>
> 2004-09-26 18:51 tgl
>
> * doc/src/sgml/libpq.sgml, doc/src/sgml/runtime.sgml,
> src/backend/libpq/be-secure.c, src/interfaces/libpq/fe-secure.c:
> Fix multiple breakages in our support for SSL certificates.
>
> My suspicion is that you need to be using 8.0 if you want any degree of
> robustness in using SSL for certificate checking (as opposed to being
> just an encrypted communications channel).
>
> regards, tom lane
>