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

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(at)localhost serv]# ./bin/test_lib
Connection failed: could not open certificate file
"/root/.postgresql/postgresql.crt": No such file or directory
ret=-1
[root(at)localhost serv]#
[root(at)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(at)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(at)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(at)localhost root]#

Connection String:
"hostaddr=169.254.59.60 <http://169.254.59.60> dbname=dbm user=postgres
sslmode=prefer"

[root(at)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(at)localhost serv]# ./bin/test_lib
Connection failed: could not open certificate file
"/root/.postgresql/postgresql.crt": No such file or directory
ret=-1
[root(at)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(at)localhost serv]#

Where am i going wrong?

thanks,
vish

On 8/29/05, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> 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> <http://169.254.59.60> dbname=dbm
> user=postgres
> > sslmode=prefer") parameters.
> >
> > [root(at)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
>