Re: Client SSL validation using root.crt - Mailing list pgsql-general

"Sergio" <sergio.cinos@gmail.com> writes:
> I see a strange behaviour using root.crt. PostgreSQL always waits a
> client certificate to check agains root.crt. But I set up a
> 'hostnossl' auth line un pg_hba.conf, PostgreSQL still wants a client
> certificate.

No, not really.  The problem is that in the default PGSSLMODE=prefer
behavior, libpq tries an SSL connection first.  It's prepared to retry
with a non-SSL connection if it gets a rejection from the server ...
but if OpenSSL fails to establish the connection, it just dies
immediately.

I got the behavior I think you want with the attached patch, which
allows a retry for SSL setup errors as well as server rejections.
The trouble with this is that it masks SSL setup errors.  For example,
if the server has "hostssl" to require SSL on localhost connections,
and I don't have a client certificate:

$ psql -l -h localhost
psql: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "tgl", database "postgres", SSL off

The only way to get any info about what's wrong with the SSL setup
is to select a nondefault SSLMODE:

$ PGSSLMODE=require psql -l -h localhost
psql: could not open certificate file "/home/tgl/.postgresql/postgresql.crt": No such file or directory

I'm not sure there's anything much to be done about this --- in any
given failure scenario, the user is probably only interested in one or
the other of the SSL and non-SSL error messages, and libpq has no very
good way to guess which one.  (I thought for a bit about reporting both,
but that seems pretty likely to confuse more often than it helps.)

So I'm inclined to apply the patch, at least in HEAD (8.2), but wanted
to see if anyone had any other ideas.

Meanwhile, Sergio's best workaround is "export PGSSLMODE=allow".

            regards, tom lane

Index: fe-connect.c
===================================================================
RCS file: /cvsroot/pgsql/src/interfaces/libpq/fe-connect.c,v
retrieving revision 1.338
diff -c -r1.338 fe-connect.c
*** fe-connect.c    6 Oct 2006 17:14:00 -0000    1.338
--- fe-connect.c    21 Nov 2006 02:56:20 -0000
***************
*** 1400,1405 ****
--- 1400,1424 ----
                      conn->status = CONNECTION_MADE;
                      return PGRES_POLLING_WRITING;
                  }
+                 if (pollres == PGRES_POLLING_FAILED)
+                 {
+                     /*
+                      * Failed ... if sslmode is "prefer" then do a non-SSL
+                      * retry
+                      */
+                     if (conn->sslmode[0] == 'p' /* "prefer" */
+                         && conn->allow_ssl_try    /* redundant? */
+                         && !conn->wait_ssl_try) /* redundant? */
+                     {
+                         /* only retry once */
+                         conn->allow_ssl_try = false;
+                         /* Must drop the old connection */
+                         closesocket(conn->sock);
+                         conn->sock = -1;
+                         conn->status = CONNECTION_NEEDED;
+                         goto keep_going;
+                     }
+                 }
                  return pollres;
  #else                            /* !USE_SSL */
                  /* can't get here */