Обсуждение: PQConnectdb SSL (sslmode): Is this a bug

hi,
I am not sure if this is a bug.

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;
}
--------------
OUTPUT:
connection failure: SSL error: sslv3 alert handshake failure

-----------

Now when I change the above function to one with sslmode=allow, the
connection is established and query runs.

I do have (ssl=true) in postgresql.conf and have tested the
certificates and they are working right. I also restart the postmaster
after every change in postgresql.conf and pg_hba.conf.

I would prefer the connections are SSL and hence would like to use the
default "prefer" sslmode.

vish

On Fri, Aug 26, 2005 at 10:23:10AM -0700, vishal saberwal wrote:
> OUTPUT:
> connection failure: SSL error: sslv3 alert handshake failure

What appears in the server's log when this happens?

> I do have (ssl=true) in postgresql.conf and have tested the
> certificates and they are working right.

How did you verify that the certificates are working?  By using
psql and seeing "SSL connection"?

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.  If your
system has ldd, which libpq does it show your program linked against?
If the certificates work with psql, which libpq does ldd show psql
linked against?

--
Michael Fuhr

Michael Fuhr <mike@fuhr.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

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@localhost serv]# ./bin/test_lib
Connection failed: SSL error: sslv3 alert handshake failure

ret=-1
[root@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@localhost DBMApi]# export LD_LIBRARY_PATH=/usr/local/pgsql/lib
[root@localhost DBMApi]# env | grep "LD_LIBRARY"
LD_LIBRARY_PATH=/usr/local/pgsql/lib
[root@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@localhost DBMApi]#
[root@localhost libk2]# ./bin/test_k2
Connection failed: could not open certificate file
"/root/.postgresql/postgresql.crt": No such file or directory

ret=-1
[root@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@localhost serv]# export LD_LIBRARY_PATH=/usr/lib
[root@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@localhost serv]# ./bin/test_lib
Connection failed: SSL error: sslv3 alert handshake failure

ret=-1
[root@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@sss.pgh.pa.us> wrote:
> Michael Fuhr <mike@fuhr.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
>

sorry, but forgot to mention,
when i change to sslmode=allow, it lets me connect and runs my query
on 'select * from test_table' which is a dummy table ...

[root@localhost serv]# ./bin/test_lib

ret=0
GOT CONNECTION
NAME                     AGE
----------------------------
me                      1
you                     2
they                    3
us                      4
[root@localhost serv]#

On Fri, Aug 26, 2005 at 01:57:36PM -0700, vishal saberwal wrote:
> 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@localhost serv]# ./bin/test_lib
> Connection failed: SSL error: sslv3 alert handshake failure

I asked what appeared in the server's logs when this happened but
I don't see that information in your post.  Those log entries might
be useful, so please post them if you continue to have trouble.

> ret=-1
> [root@localhost serv]# ldd ./bin/test_lib
> ...
>         libpq.so.3 => /usr/lib/libpq.so.3 (0x002ee000)

What's the output of the following command?

  ls -l /usr/lib/libpq.so*

In my 7.4.8 installation I see the following:

  libpq.so -> libpq.so.3.1
  libpq.so.3 -> libpq.so.3.1
  libpq.so.3.1

As I recall, 8.0.1's libpq was libpq.so.3.2 (this was changed to
libpq.so.4.0 in 8.0.2), so the library's minor version should tell
you which version of PostgreSQL you're linked against.

I asked if the certificate works with psql, and if it does, for you
to show the output of "ldd psql".  I don't see that output -- does
psql work?  If so then please post its ldd output.

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

What's test_k2?  It's looking for the certificate, although perhaps
not where you want it to.  What happens if you run this program as
a user that has a certificate, or if you install the certificate
and key in /root/.postgresql?  Also, I don't see test_k2's ldd
output -- is it linked against /usr/local/pgsql/lib/libpq.so.3?
If so, what's the output of the following command?

  ls -l /usr/local/pgsql/lib/libpq.so*

I'm wondering if you have PostgreSQL 7.4's libraries installed in
/usr/lib and 8.0.1's libraries in /usr/local/pgsql/lib.  Is that
what you've done?

--
Michael Fuhr

On Fri, Aug 26, 2005 at 02:03:05PM -0700, vishal saberwal wrote:
> sorry, but forgot to mention,
> when i change to sslmode=allow, it lets me connect and runs my query

That's because "allow" attempts a non-SSL connection first, whereas
"prefer" attempts an SSL connection first.  If the server permits
non-SSL connections then "allow" will get you connected as non-SSL
without ever attempting SSL.

--
Michael Fuhr

hi michael and tom,
(A) With LIBPQ.SO.3.2

After reading your response i copied the libpq.so.3.2 from the compiled source tree to /usr/lib where the version available was libpq.so.3.1.
I recreated the symbolic links and now the links are as below:
[root@localhost DBApi]# ls -l /usr/lib/libpq*
-rw-r--r--  1 postgres root 1480452 Mar 10  2004 /usr/lib/libpq.a
lrwxrwxrwx  1 postgres root      21 Aug 29 15:00 /usr/lib/libpq.so -> /usr/lib/libpq.so.3.2
lrwxrwxrwx  1 postgres root      21 Aug 29 14:59 /usr/lib/libpq.so.3 -> /usr/lib/libpq.so.3.2
-rwxr-xr-x  1 postgres root  113988 Mar 10  2004 /usr/lib/libpq.so.3.1
-rwxr-xr-x  1 postgres root  122177 Aug 26 12:55 /usr/lib/libpq.so.3.2

[root@localhost DBApi]#   ls -l /usr/local/pgsql/lib/libpq.so*
lrwxrwxrwx  1 root root     12 Aug 26 13:17 /usr/local/pgsql/lib/libpq.so -> libpq.so.3.2
lrwxrwxrwx  1 root root     12 Aug 26 13:17 /usr/local/pgsql/lib/libpq.so.3 -> libpq.so.3.2
-rwxr-xr-x  1 root root 122177 Aug 26 13:17 /usr/local/pgsql/lib/libpq.so.3.2

[root@localhost DBApi]# which psql
/usr/bin/psql
[root@localhost DBApi]# psql --version
psql (PostgreSQL) 8.0.1
contains support for command-line editing
[root@localhost DBApi]# env |grep -i "LD_LIBRARY"
LD_LIBRARY_PATH=/usr/local/pgsql/lib
[root@localhost DBApi]#

The postmaster command is:
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data/ -i >logfile 2>&1 &

As "postgres" user:
-bash-2.05b$ which psql
/usr/local/pgsql/bin/psql
-bash-2.05b$ psql --version
psql (PostgreSQL) 8.0.1
contains support for command-line editing
-bash-2.05b$ env |grep -i "LD_LIBRARY"
LD_LIBRARY_PATH=/usr/local/pgsql/lib/
-bash-2.05b$

"test_k2" was a typo (sorry about that) ...

[root@localhost serv]# ldd ./bin/test_lib
        linux-gate.so.1 =>  (0x00a4e000)
        libpthread.so.0 => /lib/tls/libpthread.so.0 (0x003c8000)
        libpq.so.3 => /usr/lib/libpq.so.3 (0x008b4000)
        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 (0x00714000)
        libdl.so.2 => /lib/libdl.so.2 (0x002d5000)
        libz.so.1 => /usr/lib/libz.so.1 (0x002db000)
[root@localhost serv]#

now i ran the program i had that has a conect command with ("hostaddr=169.254.59.60 dbname=dbm user=postgres sslmode=prefer") parameters.

[root@localhost serv]# ./bin/test_lib
Connection failed: could not open certificate file "/root/.postgresql/postgresql.crt": No such file or directory
ret=-1

I don't think i need to have ~/.postgresql/postgresql.crt on server. I thought that was the requirement only with the clients ... so, i think i shouldn't be getting this error. On server (as per documentation) i need to have the files in $PGDATA rather than in ~/.postgresql. Hence this question.

[root@localhost serv]# cat /var/lib/pgsql/logfile
LOG:  database system was interrupted at 2005-08-29 12:56:46 PDT
LOG:  checkpoint record is at 0/655FF5F4
LOG:  redo record is at 0/655FF5F4; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 15567; next OID: 11920206
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 0/655FF630
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

Now with allow:
"hostaddr=169.254.59.60 dbname=dbm user=postgres sslmode=allow"
[root@localhost libk2]# ./bin/test_k2
 
ret=0
GOT CONNECTION
NAME                     AGE
----------------------------
me                      1
you                     2
they                    3
us                      4
[root@localhost libk2]#

(B) With LIBPQ.SO.3.1

[root@localhost DBApi]# ll /usr/lib/libpq*
-rw-r--r--  1 postgres root 1480452 Mar 10  2004 /usr/lib/libpq.a
lrwxrwxrwx  1 postgres root      21 Aug 29 15:00 /usr/lib/libpq.so -> /usr/lib/libpq.so.3.2
lrwxrwxrwx  1 postgres root      21 Aug 29 14:59 /usr/lib/libpq.so.3 -> /usr/lib/libpq.so.3.2
-rwxr-xr-x  1 postgres root  113988 Mar 10  2004 /usr/lib/libpq.so.3.1
-rwxr-xr-x  1 postgres root  122177 Aug 26 12:55 /usr/lib/libpq.so.3.2
[root@localhost DBApi]# rm /usr/lib/libpq.so.3
rm: remove symbolic link `/usr/lib/libpq.so.3'? y
[root@localhost DBApi]# ln -s /usr/lib/libpq.so.3.1 /usr/lib/libpq.so.3
[root@localhost DBApi]# rm /usr/lib/libpq.so
rm: remove symbolic link `/usr/lib/libpq.so'? y
[root@localhost DBApi]# ln -s /usr/lib/libpq.so.3.1 /usr/lib/libpq.so
[root@localhost DBApi]# chown postgres /usr/lib/libpq*
[root@localhost DBApi]# ll /usr/lib/libpq*
-rw-r--r--  1 postgres root 1480452 Mar 10  2004 /usr/lib/libpq.a
lrwxrwxrwx  1 postgres root      21 Aug 29 16:07 /usr/lib/libpq.so -> /usr/lib/libpq.so.3.1
lrwxrwxrwx  1 postgres root      21 Aug 29 16:07 /usr/lib/libpq.so.3 -> /usr/lib/libpq.so.3.1
-rwxr-xr-x  1 postgres root  113988 Mar 10  2004 /usr/lib/libpq.so.3.1
-rwxr-xr-x  1 postgres root  122177 Aug 26 12:55 /usr/lib/libpq.so.3.2
[root@localhost DBApi]#

doing nothing but above commands ... killing and restarting the postmaster ...
(1) sslmode=allow:
[root@localhost serv]# ./bin/test_lib
 ret=0
GOT CONNECTION
NAME                     AGE
----------------------------
me                      1
you                     2
they                    3
us                      4
[root@localhost serv]# vi src/test_lib.cpp
(2) sslmode=prefer
[root@localhost serv]# ./bin/test_lib
Connection failed: SSL error: sslv3 alert handshake failure
 
ret=-1
[root@localhost serv]#

This is what i am curious about:
(a) Where am i going wrong?
(b) Why are the error messages different?
(c) When LD_LIBRARY_PATH is set to /usr/local/pgsql/lib, then why does it matter if the links on /usr/lib/libpq.so are changed?

thanks,
vish

oops i forgot to attach logfile output for the second case (LIBPQ.SO.3.1) :
[root@localhost DBApi]# cat /var/lib/pgsql/logfile
LOG:  database system was interrupted at 2005-08-29 15:01:11 PDT
LOG:  checkpoint record is at 0/655FF630
LOG:  redo record is at 0/655FF630; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 15567; next OID: 11920206
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 0/655FF66C
LOG:  redo is not required
LOG:  database system is ready
LOG:  could not accept SSL connection: peer did not return a certificate

On Mon, Aug 29, 2005 at 04:23:13PM -0700, vishal saberwal wrote:
> now i ran the program i had that has a conect command with ("hostaddr=
> 169.254.59.60 <http://169.254.59.60> dbname=dbm user=postgres
> sslmode=prefer") parameters.
>
> [root@localhost serv]# ./bin/test_lib
> Connection failed: could not open certificate file
> "/root/.postgresql/postgresql.crt": No such file or directory
> ret=-1
>
> I don't think i need to have ~/.postgresql/postgresql.crt on server. I
> thought that was the requirement only with the clients ... so, i think i
> shouldn't be getting this error. On server (as per documentation) i need to
> have the files in $PGDATA rather than in ~/.postgresql. Hence this question.

An application that connects to the database is a client, regardless
of what machine it runs on.  If the client (the application) makes
a TCP connection to the server (the database) and the server requests
a certificate, then the client must provide a certificate or the
server will reject the connection.  To learn more about what files
go where and how they're used, see "Secure TCP/IP Connections with
SSL" and "SSL Support" in the documentation:

http://www.postgresql.org/docs/8.0/static/ssl-tcp.html
http://www.postgresql.org/docs/8.0/static/libpq-ssl.html

> (a) Where am i going wrong?

You're trying to do client authentication with a version of libpq
that won't work, and when you do link with a good version of libpq
then you're not providing a client certificate.

> (b) Why are the error messages different?

Because the failure modes are different.  In one case the client
is apparently attempting to make an SSL connection without a
certificate; in the other case the client is looking for a certificate
and can't find one.

> (c) When LD_LIBRARY_PATH is set to /usr/local/pgsql/lib, then why does it
> matter if the links on /usr/lib/libpq.so are changed?

That's a system issue, not a PostgreSQL issue.  Some people consider
LD_LIBRARY_PATH to be an ugly hack anyway and recommend against its
use except for testing purposes.  You might want to consider using
linker options that tell the executable where to find its shared
libraries at run time; see your build tools' documentation for details.

--
Michael Fuhr

Thanks michael for your response ...
I had read the links (you suggested) before, but yes i missed some important points ...
hmmm i believe it was me who was wrong again ...
I was trying to connect to the server from the same machine server is running on ...
well, in this case it has to serve as client as well ... you are right ...

Then I create the directory and place the files, but i am still unable to connect ...

Root user:
/root/.postgressql:
total 8
-rw-r--r--  1 root root 3675 Aug 30 09:16 postgresql.crt
-rw-------  1 root root  887 Aug 30 09:16 postgresql.key

Postgres user:
-bash-2.05b$ ls -al ~/.postgresql/*
-rw-r--r--  1 postgres postgres 3675 Aug 30 09:30 /var/lib/pgsql/.postgresql/postgresql.crt
-rw-------  1 postgres postgres  887 Aug 30 09:30 /var/lib/pgsql/.postgresql/postgresql.key
-bash-2.05b$ chown postgres:postgres ~/.postgresql/

[root@localhost serv]# ./bin/test_lib
Connection failed: could not open certificate file "/root/.postgresql/postgresql.crt": No such file or directory
ret=-1
[root@localhost serv]#
[root@localhost root]# ll /usr/lib/libpq*
-rw-r--r--  1 postgres root 1480452 Mar 10  2004 /usr/lib/libpq.a
lrwxrwxrwx  1 root     root      12 Aug 30 09:23 /usr/lib/libpq.so -> libpq.so.3.2
lrwxrwxrwx  1 root     root      12 Aug 30 09:23 /usr/lib/libpq.so.3 -> libpq.so.3.2
-rwxr-xr-x  1 postgres root  113988 Mar 10  2004 /usr/lib/libpq.so.3.1
-rwxr-xr-x  1 postgres root  122177 Aug 26 12:55 /usr/lib/libpq.so.3.2
[root@localhost root]# ll /usr/local/pgsql/lib/libpq*
-rw-r--r--  1 root root 144470 Aug 26 13:17 /usr/local/pgsql/lib/libpq.a
lrwxrwxrwx  1 root root     12 Aug 26 13:17 /usr/local/pgsql/lib/libpq.so -> libpq.so.3.2
lrwxrwxrwx  1 root root     12 Aug 26 13:17 /usr/local/pgsql/lib/libpq.so.3 -> libpq.so.3.2
-rwxr-xr-x  1 root root 122177 Aug 26 13:17 /usr/local/pgsql/lib/libpq.so.3.2
[root@localhost root]# ll /usr/local/pgsql/data/
total 100
drwx------  20 postgres postgres  4096 Aug 29 10:35 base
drwx------   2 postgres postgres  4096 Aug 30 10:21 global
drwx------   2 postgres postgres  4096 Aug 22 17:48 pg_clog
-rw-------   1 postgres postgres   154 Aug 25 17:56 pg_hba.conf
-rw-------   1 postgres postgres  1460 Aug 22 17:48 pg_ident.conf
drwx------   2 postgres postgres  4096 Aug 22 17:48 pg_subtrans
drwx------   2 postgres postgres  4096 Aug 22 17:48 pg_tblspc
-rw-------   1 postgres postgres     4 Aug 22 17:48 PG_VERSION
drwx------   3 postgres postgres  4096 Aug 29 10:41 pg_xlog
-rw-------   1 postgres postgres 11043 Aug 25 17:14 postgresql.conf
-rw-------   1 postgres postgres    59 Aug 30 09:44 postmaster.opts
-rw-------   1 postgres postgres    47 Aug 30 09:44 postmaster.pid
-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
[root@localhost root]#

Connection String:
 "hostaddr=169.254.59.60 dbname=dbm user=postgres sslmode=prefer"

[root@localhost serv]# ldd ./bin/test_lib
        linux-gate.so.1 =>  (0x00138000)
        libpthread.so.0 => /lib/tls/libpthread.so.0 (0x003c8000)
        libpq.so.3 => /usr/local/pgsql/lib/libpq.so.3 (0x005de000)
        libstdc++.so.5 => /usr/lib/libstdc++.so.5 (0x0018d000)
        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 (0x005f7000)
        /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 (0x00248000)
        libcom_err.so.2 => /lib/libcom_err.so.2 (0x00111000)
        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@localhost serv]# ./bin/test_lib
Connection failed: could not open certificate file "/root/.postgresql/postgresql.crt": No such file or directory
ret=-1
[root@localhost serv]# cat /var/lib/pgsql/logfile
LOG:  database system was shut down at 2005-08-30 09:39:28 PDT
LOG:  checkpoint record is at 0/65650CD0
LOG:  redo record is at 0/65650CD0; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 15622; next OID: 11928398
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
[root@localhost serv]#

Where am i going wrong?

thanks,
vish

On Tue, Aug 30, 2005 at 10:40:26AM -0700, vishal saberwal wrote:
> Root user:
> /root/.postgressql:

Is this the actual directory name?  It's misspelled: it should be
".postgresql", not ".postgressql".

--
Michael Fuhr

Why do i have to be the one doing such a silly mistake ...
thanks for your help and yes you were right ...
this fixed my problem ...
thanks,
vish