Thread: SSL cleanups/hostname verification

Attached patch cleans up the certificate verification in libpq, and adds
a configuration paraqmeter to control it. The new parameter is
"sslverify", and can be set to:

* cn = default = will validate that the certificate chains to a trusted
root, *and* that the cn on the certificate matches the hostname
specificed in the connection. This is the only option that prevents
man-in-the-middle attacks completely, and therefor is the default.

* cert = what we had before if there was a root certificate file = will
validate that the certificate chains to a trusted root, but ignore the cn.

* none = will disable certificate validation completely


This means that the connection string is now in charge of the security
policy, and not just the "if file exists or not". IMHO this is the only
proper way to do it. Now, if you for some reason loose the root
certificate file, libpq will refuse to connect unless you have
explicitly told it to connect without verifying the certificate.
Previously if you accidentally lost the file, you would connect
insecurely without knowing about it.


The error messages from the patch requires the
libpq-error-message-stacking patch as well (or something like it),
otherwise the error message will often get overwritten by a later one if
we retry without SSL.


I intend to follow this up with a similar patch for the server side,
which will make it a connection option instead of being dependent on the
presence of a file. This is depending on the pg_hba options patch,
however, so it's not ready yet.


//Magnus
*** a/doc/src/sgml/libpq.sgml
--- b/doc/src/sgml/libpq.sgml
***************
*** 259,264 ****
--- 259,291 ----
          </varlistentry>

          <varlistentry>
+          <term><literal>sslverify</literal></term>
+          <listitem>
+           <para>
+            This option controls how libpq verifies the certificate on the
+            server when performing an <acronym>SSL</> connection. There are
+            three options: <literal>none</> disables verification completely
+            (not recommended!); <literal>cert</> enables verification that
+            the certificate chains to a known CA only; <literal>cn</> will
+            both verify that the certificate chains to a known CA and that
+            the <literal>cn</> attribute of the certificate matches the
+            hostname the connection is being made to (default).
+           </para>
+
+           <para>
+            It is always recommended to use the <literal>cn</> value for
+            this parameter, since this is the only option that prevents
+            man-in-the-middle attacks. Note that this requires the server
+            name on the certificate to match exactly with the host name
+            used for the connection, and therefore does not support connections
+            to aliased names. It can be used with pure IP address connections
+            only if the certificate also has just the IP address in the
+            <literal>cn</i> field.
+           </para>
+          </listitem>
+         </varlistentry>
+
+         <varlistentry>
           <term><literal>requiressl</literal></term>
           <listitem>
            <para>
***************
*** 6011,6019 **** myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
    </para>

    <para>
!    To verify the server certificate is trustworthy, place certificates of
!    the certificate authorities (<acronym>CA</acronym>) you trust in the
!    file <filename>~/.postgresql/root.crt</> in the user's home directory.
     (On Microsoft Windows the file is named
     <filename>%APPDATA%\postgresql\root.crt</filename>.)
     <application>libpq</application> will then verify that the server's
--- 6038,6048 ----
    </para>

    <para>
!    When the <literal>sslverify</> parameter is set to <literal>cn</> or
!    <literal>cert</I>, libpq will verify that the server certificate is
!    trustworthy by checking the certificate chain up to a <acronym>CA</>.
!    For this to work, place the certificate of a trusted <acronum>CA</>
!    in the file <filename>~/.postgresql/root.crt</> in the user's home directory.
     (On Microsoft Windows the file is named
     <filename>%APPDATA%\postgresql\root.crt</filename>.)
     <application>libpq</application> will then verify that the server's
*** a/doc/src/sgml/runtime.sgml
--- b/doc/src/sgml/runtime.sgml
***************
*** 1418,1426 **** $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput
     <filename>server.key</filename> (key) and
     <filename>server.crt</filename> (certificate) files (<xref
     linkend="ssl-tcp">). The TCP client must connect using
!    <literal>sslmode='require'</> (<xref linkend="libpq-connect">) and have
!    a <filename>~/.postgresql/root.crt</> SSL certificate (<xref
!    linkend="libpq-ssl">).
    </para>
   </sect1>

--- 1418,1426 ----
     <filename>server.key</filename> (key) and
     <filename>server.crt</filename> (certificate) files (<xref
     linkend="ssl-tcp">). The TCP client must connect using
!    <literal>sslmode='require'</>, specify <literal>sslverify='cn'</>
!    or <literal>sslverify='cert'</> and have the required certificate
!    files present (<xref linkend="libpq-connect">).
    </para>
   </sect1>

***************
*** 1544,1551 **** $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput

     <listitem>
      <para>
!      It is possible for both the client and server to provide SSL keys
!      or certificates to each other. It takes some extra configuration
       on each side, but this provides stronger verification of identity
       than the mere use of passwords. It prevents a computer from
       pretending to be the server just long enough to read the password
--- 1544,1551 ----

     <listitem>
      <para>
!      It is possible for both the client and server to provide SSL
!      certificates to each other. It takes some extra configuration
       on each side, but this provides stronger verification of identity
       than the mere use of passwords. It prevents a computer from
       pretending to be the server just long enough to read the password
***************
*** 1757,1763 **** chmod og-rwx server.key
      A self-signed certificate can be used for testing, but a certificate
      signed by a certificate authority (<acronym>CA</>) (either one of the
      global <acronym>CAs</> or a local one) should be used in production
!     so the client can verify the server's identity.
     </para>

    </sect2>
--- 1757,1765 ----
      A self-signed certificate can be used for testing, but a certificate
      signed by a certificate authority (<acronym>CA</>) (either one of the
      global <acronym>CAs</> or a local one) should be used in production
!     so the client can verify the server's identity. If all the clients
!     are local to the organization, using a local <acronym>CA</> is
!     recommended.
     </para>

    </sect2>
*** a/src/interfaces/libpq/fe-connect.c
--- b/src/interfaces/libpq/fe-connect.c
***************
*** 92,99 **** static int ldapServiceLookup(const char *purl, PQconninfoOption *options,
--- 92,101 ----
  #define DefaultPassword          ""
  #ifdef USE_SSL
  #define DefaultSSLMode    "prefer"
+ #define DefaultSSLVerify "cn"
  #else
  #define DefaultSSLMode    "disable"
+ #define DefaultSSLVerify "none"
  #endif

  /* ----------
***************
*** 181,186 **** static const PQconninfoOption PQconninfoOptions[] = {
--- 183,191 ----
      {"sslmode", "PGSSLMODE", DefaultSSLMode, NULL,
      "SSL-Mode", "", 8},            /* sizeof("disable") == 8 */

+     {"sslverify", "PGSSLVERIFY", DefaultSSLVerify, NULL,
+     "SSL-Verify", "", 5},        /* sizeof("chain") == 5 */
+
  #if defined(KRB5) || defined(ENABLE_GSS) || defined(ENABLE_SSPI)
      /* Kerberos and GSSAPI authentication support specifying the service name */
      {"krbsrvname", "PGKRBSRVNAME", PG_KRB_SRVNAM, NULL,
***************
*** 412,417 **** connectOptions1(PGconn *conn, const char *conninfo)
--- 417,424 ----
      conn->connect_timeout = tmp ? strdup(tmp) : NULL;
      tmp = conninfo_getval(connOptions, "sslmode");
      conn->sslmode = tmp ? strdup(tmp) : NULL;
+     tmp = conninfo_getval(connOptions, "sslverify");
+     conn->sslverify = tmp ? strdup(tmp) : NULL;
  #ifdef USE_SSL
      tmp = conninfo_getval(connOptions, "requiressl");
      if (tmp && tmp[0] == '1')
***************
*** 527,532 **** connectOptions2(PGconn *conn)
--- 534,557 ----
          conn->sslmode = strdup(DefaultSSLMode);

      /*
+      * Validate sslverify option
+      */
+     if (conn->sslverify)
+     {
+         if (strcmp(conn->sslverify, "none") != 0
+             && strcmp(conn->sslverify, "cert") != 0
+             && strcmp(conn->sslverify, "cn") != 0)
+         {
+             conn->status = CONNECTION_BAD;
+             printfPQExpBuffer(&conn->errorMessage,
+                             libpq_gettext("invalid sslverify value: \"%s\"\n"),
+                               conn->sslverify);
+             return false;
+         }
+     }
+
+
+     /*
       * Only if we get this far is it appropriate to try to connect. (We need a
       * state flag, rather than just the boolean result of this function, in
       * case someone tries to PQreset() the PGconn.)
*** a/src/interfaces/libpq/fe-secure.c
--- b/src/interfaces/libpq/fe-secure.c
***************
*** 493,569 **** verify_cb(int ok, X509_STORE_CTX *ctx)
      return ok;
  }

- #ifdef NOT_USED
  /*
   *    Verify that common name resolves to peer.
   */
  static int
  verify_peer_name_matches_certificate(PGconn *conn)
  {
!     struct hostent *cn_hostentry = NULL;
!     struct sockaddr server_addr;
!     struct sockaddr_in *sin (struct sockaddr_in *) &server_addr;
!     ACCEPT_TYPE_ARG3 len;
!     char      **s;
!     unsigned long l;
!
!     /* Get the address on the other side of the socket. */
!     len = sizeof(server_addr);
!     if (getpeername(conn->sock, &server_addr, &len) == -1)
!     {
!         char        sebuf[256];
!
!         printfPQExpBuffer(&conn->errorMessage,
!                           libpq_gettext("error querying socket: %s\n"),
!                           SOCK_STRERROR(SOCK_ERRNO, sebuf, sizeof(sebuf)));
!         return -1;
!     }

!     if (server_addr.sa_family != AF_INET)
      {
          printfPQExpBuffer(&conn->errorMessage,
!                           libpq_gettext("unsupported protocol\n"));
          return -1;
      }
!
!     /* Get the IP addresses of the certificate's common name (CN) */
      {
-         struct hostent hpstr;
-         char        buf[BUFSIZ];
-         int            herrno = 0;
-
          /*
!          * Currently, pqGethostbyname() is used only on platforms that don't
!          * have getaddrinfo().    If you enable this function, you should
!          * convert the pqGethostbyname() function call to use getaddrinfo().
           */
!         pqGethostbyname(conn->peer_cn, &hpstr, buf, sizeof(buf),
!                         &cn_hostentry, &herrno);
!     }
!
!     /* Did we get an IP address? */
!     if (cn_hostentry == NULL)
!     {
!         printfPQExpBuffer(&conn->errorMessage,
!           libpq_gettext("could not get information about host \"%s\": %s\n"),
!                           conn->peer_cn, hstrerror(h_errno));
!         return -1;
!     }
!
!     /* Does one of the CN's IP addresses match the server's IP address? */
!     for (s = cn_hostentry->h_addr_list; *s != NULL; s++)
!         if (!memcmp(&sin->sin_addr.s_addr, *s, cn_hostentry->h_length))
              return 0;
!
!     l = ntohl(sin->sin_addr.s_addr);
!     printfPQExpBuffer(&conn->errorMessage,
!                       libpq_gettext(
!                         "server common name \"%s\" does not resolve to %ld.%ld.%ld.%ld\n"),
!                       conn->peer_cn, (l >> 24) % 0x100, (l >> 16) % 0x100,
!                       (l >> 8) % 0x100, l % 0x100);
!     return -1;
  }
- #endif   /* NOT_USED */

  /*
   *    Callback used by SSL to load client cert and key.
--- 493,533 ----
      return ok;
  }

  /*
   *    Verify that common name resolves to peer.
   */
  static int
  verify_peer_name_matches_certificate(PGconn *conn)
  {
!     /*
!      * If told not to verify the peer name, don't do it. Return
!      * 0 indicating that the verification was successful.
!      */
!     if(strcmp(conn->sslverify, "cn") != 0)
!         return 0;

!     if (conn->pghostaddr)
      {
          printfPQExpBuffer(&conn->errorMessage,
!                           libpq_gettext("verified SSL connections are only supported when connecting to a
hostname"));
          return -1;
      }
!     else
      {
          /*
!          * Connect by Host name
           */
!         if (strcmp(conn->peer_cn, conn->pghost) != 0)
!         {
!             printfPQExpBuffer(&conn->errorMessage,
!                               libpq_gettext("server common name '%s' does not match hostname '%s'"),
!                               conn->peer_cn, conn->pghost);
!             return -1;
!         }
!         else
              return 0;
!     }
  }

  /*
   *    Callback used by SSL to load client cert and key.
***************
*** 901,906 **** initialize_SSL(PGconn *conn)
--- 865,876 ----
      if (init_ssl_system(conn))
          return -1;

+     /*
+      * If sslverify is set to anything other than "none", perform certificate
+      * verification. If set to "cn" we will also do further verifications after
+      * the connection has been completed.
+      */
+
      /* Set up to verify server cert, if root.crt is present */
      if (pqGetHomeDirectory(homedir, sizeof(homedir)))
      {
***************
*** 944,949 **** initialize_SSL(PGconn *conn)
--- 914,937 ----

              SSL_CTX_set_verify(SSL_context, SSL_VERIFY_PEER, verify_cb);
          }
+         else
+         {
+             if (strcmp(conn->sslverify, "none") != 0)
+             {
+                 printfPQExpBuffer(&conn->errorMessage,
+                                   libpq_gettext("root certificate file (%s) not found"), fnbuf);
+                 return -1;
+             }
+         }
+     }
+     else
+     {
+         if (strcmp(conn->sslverify, "none") != 0)
+         {
+             printfPQExpBuffer(&conn->errorMessage,
+                               libpq_gettext("cannot find home directory to locate root certificate file"));
+             return -1;
+         }
      }

      /* set up mechanism to provide client certificate, if available */
***************
*** 1059,1071 **** open_client_SSL(PGconn *conn)
                                NID_commonName, conn->peer_cn, SM_USER);
      conn->peer_cn[SM_USER] = '\0';

- #ifdef NOT_USED
      if (verify_peer_name_matches_certificate(conn) == -1)
      {
          close_SSL(conn);
          return PGRES_POLLING_FAILED;
      }
- #endif

      /* SSL handshake is complete */
      return PGRES_POLLING_OK;
--- 1047,1057 ----
*** a/src/interfaces/libpq/libpq-int.h
--- b/src/interfaces/libpq/libpq-int.h
***************
*** 291,296 **** struct pg_conn
--- 291,297 ----
      char       *pguser;            /* Postgres username and password, if any */
      char       *pgpass;
      char       *sslmode;        /* SSL mode (require,prefer,allow,disable) */
+     char       *sslverify;        /* Verify server SSL certificate (none,chain,cn) */
  #if defined(KRB5) || defined(ENABLE_GSS) || defined(ENABLE_SSPI)
      char       *krbsrvname;        /* Kerberos service name */
  #endif

Magnus Hagander <magnus@hagander.net> writes:
> Attached patch cleans up the certificate verification in libpq, and adds
> a configuration paraqmeter to control it. The new parameter is
> "sslverify", and can be set to:

> * cn = default = will validate that the certificate chains to a trusted
> root, *and* that the cn on the certificate matches the hostname
> specificed in the connection. This is the only option that prevents
> man-in-the-middle attacks completely, and therefor is the default.

How can you make that the default?  Won't it immediately break every
installation without certificates?

The patch seems pretty far short of sufficient as far as supporting a
new conninfo option goes --- for instance it appears to leak the string
at disconnect.  Check through all the references to some existing option
field to see if you missed anything else.
        regards, tom lane

Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> Attached patch cleans up the certificate verification in libpq, and adds
>> a configuration paraqmeter to control it. The new parameter is
>> "sslverify", and can be set to:
> 
>> * cn = default = will validate that the certificate chains to a trusted
>> root, *and* that the cn on the certificate matches the hostname
>> specificed in the connection. This is the only option that prevents
>> man-in-the-middle attacks completely, and therefor is the default.
> 
> How can you make that the default?  Won't it immediately break every
> installation without certificates?

*all* SSL installations have certificate on the server side. You cannot
run without it.

And obviously the setting only has effect if you are actually running
over SSL.

> The patch seems pretty far short of sufficient as far as supporting a
> new conninfo option goes --- for instance it appears to leak the string
> at disconnect.  Check through all the references to some existing option
> field to see if you missed anything else.

Hmm. yeah, I hadn't finished that part - and promptly forgot about that
:S Will look it over again.

//Magnus

>> How can you make that the default?  Won't it immediately break every
>> installation without certificates?
>
> *all* SSL installations have certificate on the server side. You cannot
> run without it.

s/without certificates/with self-signed certificates/

which I would guess to be a common configuration

...Robert

Robert Haas wrote:
>>> How can you make that the default?  Won't it immediately break every
>>> installation without certificates?
>> *all* SSL installations have certificate on the server side. You cannot
>> run without it.
> 
> s/without certificates/with self-signed certificates/
> 
> which I would guess to be a common configuration

Self-signed still work. In a self-signed scenario, the server
certificate *is* the CA certificate.

//Magnus

Robert Haas wrote:
>>> How can you make that the default?  Won't it immediately break every
>>> installation without certificates?
>> *all* SSL installations have certificate on the server side. You cannot
>> run without it.
> 
> s/without certificates/with self-signed certificates/
> 
> which I would guess to be a common configuration

Yeah, but those setups are already broken anyway; the users just appear 
not to know it.

If you install a new web browser, would you want it to be configured by 
default to warn about untrusted certificates or to "not bother" the user 
about it?  It's pretty much the same question here.

Magnus Hagander wrote:
> Robert Haas wrote:
>>>> How can you make that the default?  Won't it immediately break every
>>>> installation without certificates?
>>> *all* SSL installations have certificate on the server side. You cannot
>>> run without it.
>> s/without certificates/with self-signed certificates/
>>
>> which I would guess to be a common configuration
> 
> Self-signed still work. In a self-signed scenario, the server
> certificate *is* the CA certificate.

But the user needs to copy the CA to the client, which most people 
probably don't do nowadays.

On 21 okt 2008, at 10.04, Peter Eisentraut <peter_e@gmx.net> wrote:

> Magnus Hagander wrote:
>> Robert Haas wrote:
>>>>> How can you make that the default?  Won't it immediately break  
>>>>> every
>>>>> installation without certificates?
>>>> *all* SSL installations have certificate on the server side. You  
>>>> cannot
>>>> run without it.
>>> s/without certificates/with self-signed certificates/
>>>
>>> which I would guess to be a common configuration
>> Self-signed still work. In a self-signed scenario, the server
>> certificate *is* the CA certificate.
>
> But the user needs to copy the CA to the client, which most people  
> probably don't do nowadays.

True. I'll update the docs to make this even more clear, for those who  
don't know ssl. I still consider that a feature and not a problem ..

/magnus

Then they may as well not have bothered with generating a key in the  
first place since an attacker can generate one of his own just as  
easily...

Actually that's not entirely true. A non-authenticated connection  
still protects against passive attacks like sniffers. But active  
attacks are known in the wild.

greg

On 21 Oct 2008, at 09:04 AM, Peter Eisentraut <peter_e@gmx.net> wrote:

> Magnus Hagander wrote:
>> Robert Haas wrote:
>>>>> How can you make that the default?  Won't it immediately break  
>>>>> every
>>>>> installation without certificates?
>>>> *all* SSL installations have certificate on the server side. You  
>>>> cannot
>>>> run without it.
>>> s/without certificates/with self-signed certificates/
>>>
>>> which I would guess to be a common configuration
>> Self-signed still work. In a self-signed scenario, the server
>> certificate *is* the CA certificate.
>
> But the user needs to copy the CA to the client, which most people  
> probably don't do nowadays.
>
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Oct 21, 2008 at 11:02:11AM +0300, Peter Eisentraut wrote:
> If you install a new web browser, would you want it to be configured by
> default to warn about untrusted certificates or to "not bother" the user
> about it?  It's pretty much the same question here.

We "don't bother" users when there is no certificate at all, so why
would you if the certificate is untrusted?

You seem to be making the assertion that making an encrypted connection
to an untrusted server is worse than making a plaintext connection to
an untrusted server, which seems bogus to me.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout <kleptog@svana.org> writes:

> You seem to be making the assertion that making an encrypted connection
> to an untrusted server is worse than making a plaintext connection to
> an untrusted server, which seems bogus to me.

Hm, is it? If you use good old traditional telnet you know you're typing on an
insecure connection. If you use ssh you expect it to be secure and indeed ssh
throws up big errors if it fails to get a secure connection -- it doesn't
silently fall back to an insecure connection.

Actually even the example given before of the browsers follows this model. If
you visit an insecure web site you get your web page. But if you visit a
secure web site with a bogus certificate you get a big warning.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!

On Tue, Oct 21, 2008 at 11:55:32AM +0100, Gregory Stark wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>
> > You seem to be making the assertion that making an encrypted connection
> > to an untrusted server is worse than making a plaintext connection to
> > an untrusted server, which seems bogus to me.
>
> Hm, is it? If you use good old traditional telnet you know you're typing on an
> insecure connection. If you use ssh you expect it to be secure and indeed ssh
> throws up big errors if it fails to get a secure connection -- it doesn't
> silently fall back to an insecure connection.

SSH is a good example, it only works with self-signed certificates, and
relies on the client to check it. Libpq provides a mechanism for the
client to verify the server's certificate, and that is safe even if it
is self-signed.

If the client knows the certificate the server is supposed to present,
then you can't have a man-in-the-middle attack, right? Whether it's
self-signed or not is irrelevent.

Preventing casual snooping without preventing MitM is a rational choice
for system administrators.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On 21 okt 2008, at 13.12, Martijn van Oosterhout <kleptog@svana.org>  
wrote:

> On Tue, Oct 21, 2008 at 11:55:32AM +0100, Gregory Stark wrote:
>> Martijn van Oosterhout <kleptog@svana.org> writes:
>>
>>> You seem to be making the assertion that making an encrypted  
>>> connection
>>> to an untrusted server is worse than making a plaintext connection  
>>> to
>>> an untrusted server, which seems bogus to me.
>>
>> Hm, is it? If you use good old traditional telnet you know you're  
>> typing on an
>> insecure connection. If you use ssh you expect it to be secure and  
>> indeed ssh
>> throws up big errors if it fails to get a secure connection -- it  
>> doesn't
>> silently fall back to an insecure connection.
>
> SSH is a good example, it only works with self-signed certificates,  
> and
> relies on the client to check it. Libpq provides a mechanism for the
> client to verify the server's certificate, and that is safe even if it
> is self-signed.

Are you referring to the method we have now? If so, it has two  
problems: it's not enforceable from the app, and it's off by default.  
Other than that, it works.

> If the client knows the certificate the server is supposed to present,
> then you can't have a man-in-the-middle attack, right? Whether it's
> self-signed or not is irrelevent.

Yes. The importance being that it must know which, and not just  
blindly accept anything.

>
> Preventing casual snooping without preventing MitM is a rational  
> choice
> for system administrators.
>
Yes, but it should not be the default. It still allows you to do this...

/mha

Martijn van Oosterhout wrote:
> SSH is a good example, it only works with self-signed certificates, and
> relies on the client to check it. Libpq provides a mechanism for the
> client to verify the server's certificate, and that is safe even if it
> is self-signed.
> 
> If the client knows the certificate the server is supposed to present,
> then you can't have a man-in-the-middle attack, right? Whether it's
> self-signed or not is irrelevent.

That appears to be correct, but that was not the original issue under 
discussion.

Both a web browser and an SSH client will, when faced with an untrusted 
certificate, pop a question to the user.  The user then verifies the 
certificate some other way (in theory), answers/clicks yes, and then web 
browser and SSH client store the certificate locally marked as trusted, 
so this question goes away the next time.

An SSL-enabled libpq program will, when faced with an untrusted 
certificate, go ahead anyway, without notification.  (Roughly speaking.  If I understand this right, there are other
scenariosdepending on 
 
whether the client user has set up the requires files in ~/.postgresql.  All this just leads users to do the wrong
thingby neglect, ignorance, 
 
or error.)

The change Magnus proposes is that SSL-enabled libpq programs will in 
the future refuse to connect without a trusted certificate.  Being a 
library, we cannot really go ask the user, as web browser and SSH client 
do, but I could imagine that we could make psql do that and store the 
trusted certificate automatically in a local place.  Then we would be 
close to the usual operating mode for SSH and web browsers, and then 
chances are better that users can understand this setup and use it 
securely and easily.

> Preventing casual snooping without preventing MitM is a rational choice
> for system administrators.

I am not an expert in these things, but it seems to me that someone who 
can casually snoop can also casually insert DHCP or DNS packages and 
redirect traffic.  There is probably a small niche where just encryption 
without server authentication prevents information leaks, but it is not 
clear to me where this niche is or how it can be defined, and I 
personally wouldn't encourage this sort of setup.

On 21 okt 2008, at 13.41, Peter Eisentraut <peter_e@gmx.net> wrote:

> Martijn van Oosterhout wrote:
>> SSH is a good example, it only works with self-signed certificates,  
>> and
>> relies on the client to check it. Libpq provides a mechanism for the
>> client to verify the server's certificate, and that is safe even if  
>> it
>> is self-signed.
>> If the client knows the certificate the server is supposed to  
>> present,
>> then you can't have a man-in-the-middle attack, right? Whether it's
>> self-signed or not is irrelevent.
>
> That appears to be correct, but that was not the original issue  
> under discussion.
>
> Both a web browser and an SSH client will, when faced with an  
> untrusted certificate, pop a question to the user.  The user then  
> verifies the certificate some other way (in theory), answers/clicks  
> yes, and then web browser and SSH client store the certificate  
> locally marked as trusted, so this question goes away




>
>> Preventing casual snooping without preventing MitM is a rational  
>> choice
>> for system administrators.
>
> I am not an expert in these things, but it seems to me that someone  
> who can casually snoop can also casually insert DHCP or DNS packages  
> and redirect traffic.  There is probably a small niche where just  
> encryption without server authentication prevents information leaks,  
> but it is not clear to me where this niche is or how it can be  
> defined, and I personally wouldn't encourage this sort of setup.

On 21 okt 2008, at 13.41, Peter Eisentraut <peter_e@gmx.net> wrote:

> Martijn van Oosterhout wrote:
>> SSH is a good example, it only works with self-signed certificates,  
>> and
>> relies on the client to check it. Libpq provides a mechanism for the
>> client to verify the server's certificate, and that is safe even if  
>> it
>> is self-signed.
>> If the client knows the certificate the server is supposed to  
>> present,
>> then you can't have a man-in-the-middle attack, right? Whether it's
>> self-signed or not is irrelevent.
>
> That appears to be correct, but that was not the original issue  
> under discussion.
>
> Both a web browser and an SSH client will, when faced with an  
> untrusted certificate, pop a question to the user.  The user then  
> verifies the certificate some other way (in theory), answers/clicks  
> yes, and then web browser and SSH client store the certificate  
> locally marked as trusted, so this question goes away the next time.
>
> An SSL-enabled libpq program will, when faced with an untrusted  
> certificate, go ahead anyway, without notification.  (Roughly  
> speaking.  If I understand this right, there are other scenarios  
> depending on whether the client user has set up the requires files  
> in ~/.postgresql.  All this just leads users to do the wrong thing  
> by neglect, ignorance, or error.)
>
> The change Magnus proposes is that SSL-enabled libpq programs will  
> in the future refuse to connect without a trusted certificate.   
> Being a library, we cannot really go ask the user, as web browser  
> and SSH client do, but I could imagine that we could make psql do  
> that and store the trusted certificate automatically in a local  
> place.  Then we would be close to the usual operating mode for SSH  
> and web browsers, and then chances are better that users can  
> understand this setup and use it securely and easily.
>
>> Preventing casual snooping without preventing MitM is a rational  
>> choice
>> for system administrators.
>
> I am not an expert in these things, but it seems to me that someone  
> who can casually snoop can also casually insert DHCP or DNS packages  
> and redirect traffic.  There is probably a small niche where just  
> encryption without server authentication prevents information leaks,  
> but it is not clear to me where this niche is or how it can be  
> defined, and I personally wouldn't encourage this sort of setup.

Yes, see the discussion with Dan Kaminsky on list a while back, which  
is what prompted me to finally getting around to fixing this long-time  
todo...

/mha

Martijn van Oosterhout <kleptog@svana.org> writes:

> SSH is a good example, it only works with self-signed certificates, and
> relies on the client to check it. Libpq provides a mechanism for the
> client to verify the server's certificate, and that is safe even if it
> is self-signed.

Sort of. SSH requires you to install the certificate of the server locally
before connecting. If you don't it pops up a big warning and asks if you want
to install it. On subsequent connections it looks up the key for the name of
the host you're trying to connect to and insists it match. If it doesn't it
pops up a *huge* error and refuses to connect.

> Preventing casual snooping without preventing MitM is a rational choice
> for system administrators.

I think the word you're looking for is "naive" :)

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production
Tuning

Gregory Stark <stark@enterprisedb.com> writes:
> Sort of. SSH requires you to install the certificate of the server locally
> before connecting. If you don't it pops up a big warning and asks if you want
> to install it. On subsequent connections it looks up the key for the name of
> the host you're trying to connect to and insists it match. If it doesn't it
> pops up a *huge* error and refuses to connect.

Um, IIRC what it's checking there is the server's key signature, which
has nada to do with certificates.
        regards, tom lane

On Tue, Oct 21, 2008 at 08:47:35AM -0400, Tom Lane wrote:

> Um, IIRC what it's checking there is the server's key signature, which
> has nada to do with certificates.

That depends on whether you used an X.509 certificate to authenticate
the original signature.  Just about nobody does, but AIUI, there's a
way to do so.  Anyway, in the strict sense you're right, but the
comparison is wrong anyway.  SSH doesn't pretend to be authenticating
over SSL.  It's authenticating using the SSH protocol, which has its
own RFCs describing it.

If I understand the description of the current behaviour, I have to
agree with those who say the current behaviour is almost worse than
nothing.  In the presence of DNS forgery (and I'll bet a pretty good
lunch most people aren't using DNSSEC), it's not hard to send a client
to the wrong server.  If the ssl-using client will blithely proceed if
it can't authenticate the server, it's pretty hard to see in what
sense this is a conforming use of anything I know as SSL.  SSL is
supposed to provide both encryption and authentication (the
self-signed certificate nonsense is actually breakage that everyone in
the protocol community wails about whenever given the opportunity,
because of the results in user behaviour.  It was a compromise that
people made back in the period when Verisign had a lock on the market
and would charge you an arm and a leg for a cert). 

A

[Actually, to be pedantic, it might be better to call the
authentication method TLS, so as not to conflate it with the
Netscape-defined SSL.  But this is maybe straying into a different
topic.]
-- 
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Tue, Oct 21, 2008 at 02:41:11PM +0300, Peter Eisentraut wrote:
> >Preventing casual snooping without preventing MitM is a rational choice
> >for system administrators.
>
> I am not an expert in these things, but it seems to me that someone who
> can casually snoop can also casually insert DHCP or DNS packages and
> redirect traffic.  There is probably a small niche where just encryption
> without server authentication prevents information leaks, but it is not
> clear to me where this niche is or how it can be defined, and I
> personally wouldn't encourage this sort of setup.

The example I know of is where there is a passive monitoring system
which monitors and logs all network traffic. In this case MitM is not
an issue because that's being monitored for. But avoiding the extra
duplication of confidential data is worth something.

It's not exactly a huge user group, but it exists.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On Tuesday 21 October 2008 15:47:35 Tom Lane wrote:
> Gregory Stark <stark@enterprisedb.com> writes:
> > Sort of. SSH requires you to install the certificate of the server
> > locally before connecting. If you don't it pops up a big warning and asks
> > if you want to install it. On subsequent connections it looks up the key
> > for the name of the host you're trying to connect to and insists it
> > match. If it doesn't it pops up a *huge* error and refuses to connect.
>
> Um, IIRC what it's checking there is the server's key signature, which
> has nada to do with certificates.

It checks the fingerprint of the server public key.  And a certificate is 
exactly a public key with additional information that explains whose public 
key that is.  So when you install the fingerprint sent by the SSH server in 
your local known_hosts, then the server public key becomes a certificate.  
Sort of.  But it's related.

Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:

> The patch seems pretty far short of sufficient as far as supporting a
> new conninfo option goes --- for instance it appears to leak the string
> at disconnect.  Check through all the references to some existing option
> field to see if you missed anything else.

Looking over it again, that's pretty much the only one I find. I checked
against sslmode. Did you spot something else?

Found a bug in the GSSAPI code though, which also does not free :) Will fix.

//Magnus

Magnus Hagander <magnus@hagander.net> writes:
> Looking over it again, that's pretty much the only one I find. I checked
> against sslmode. Did you spot something else?

No, I just thought that it looked like too small a patch for adding
a new conn option, and complained as soon as I found something missing.
If you've got it working the same as sslmode then it should be okay.
        regards, tom lane

Magnus Hagander wrote:
> Attached patch cleans up the certificate verification in libpq, and adds
> a configuration paraqmeter to control it. The new parameter is
> "sslverify", and can be set to:

Because SSL offers both encryption and authentication, I wonder if we
should call this "sslauthenticate".

> * cn = default = will validate that the certificate chains to a trusted
> root, *and* that the cn on the certificate matches the hostname
> specificed in the connection. This is the only option that prevents
> man-in-the-middle attacks completely, and therefor is the default.

Should this be "commonname"?

> * cert = what we had before if there was a root certificate file = will
> validate that the certificate chains to a trusted root, but ignore the cn.

Should this be "chain"?

> * none = will disable certificate validation completely
> 
> 
> This means that the connection string is now in charge of the security
> policy, and not just the "if file exists or not". IMHO this is the only
> proper way to do it. Now, if you for some reason loose the root

Agreed.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Magnus Hagander wrote:
>> Attached patch cleans up the certificate verification in libpq, and adds
>> a configuration paraqmeter to control it. The new parameter is
>> "sslverify", and can be set to:
> 
> Because SSL offers both encryption and authentication, I wonder if we
> should call this "sslauthenticate".

I think that would confuse people with actual SSL certificate based
authentication, which we do not (yet) have.


>> * cn = default = will validate that the certificate chains to a trusted
>> root, *and* that the cn on the certificate matches the hostname
>> specificed in the connection. This is the only option that prevents
>> man-in-the-middle attacks completely, and therefor is the default.
> 
> Should this be "commonname"?

"cn" is a fairly standard way to refer to it, but if people think the
longer name is better, I'm ok with changing it.


>> * cert = what we had before if there was a root certificate file = will
>> validate that the certificate chains to a trusted root, but ignore the cn.
> 
> Should this be "chain"?

Could be, not sure.


//Magnus

On Mon, Oct 20, 2008 at 05:50, Magnus Hagander <magnus@hagander.net> wrote:
> Attached patch cleans up the certificate verification in libpq, and adds
> a configuration paraqmeter to control it. The new parameter is
> "sslverify", and can be set to:
>
> * cn = default = will validate that the certificate chains to a trusted
> root, *and* that the cn on the certificate matches the hostname
> specificed in the connection. This is the only option that prevents
> man-in-the-middle attacks completely, and therefor is the default.
>
> * cert = what we had before if there was a root certificate file = will
> validate that the certificate chains to a trusted root, but ignore the cn.
>
> * none = will disable certificate validation completely

Hrm I must be misunderstanding something cant get it to work for me
(or maybe I am and I just don't understand exactly?). Can you give me
a pointer to where im going wrong?

$ echo $HOSTNAME
bahdushka

### first try a ca with a bad common name
$ openssl genrsa -out bca.key

$ openssl req -new -key bca.key -out bca.csr
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:asdf
Email Address []:

$ openssl x509 -req -days 3650 -in bca.csr -signkey bca.key -out bca.crt
Signature ok
subject=/C=US/ST=Some-State/O=Internet Widgits Pty Ltd/CN=asdf
Getting Private key

$ openssl genrsa -out bpg.key

$ openssl req -new -key bpg.key -out bpg.csr
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:1234
Email Address []:

$ openssl x509 -req -days 365 -CA bca.crt  -CAkey bca.key
-CAcreateserial -in bpg.csr -out bpg.crt
Signature ok
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=1234
Getting CA Private Key

$ cp bca.crt ~/.postgresql/root.crt
$ cp bca.crt data/root.crt
$ cp bca.key data/root.key
$ cp bpg.crt ~/.postgresql/postgresql.crt
$ cp bpg.key ~/.postgresql/postgresql.key
$ cp bpg.crt data/server.crt
$ cp bpg.key data/server.key
$ chmod 0600 data/root*
$ chmod 0600 data/server*
$ chmod 0600 ~/.postgresql/*

$ SSLVERIFY=cn ./psql junk -h bahdushka
psql (8.4devel)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

junk=#

$ SSLVERIFY=cn ./psql junk -h 192.168.0.2
psql: server common name 'bahdushka' does not match hostname
'192.168.0.2'FATAL:  no pg_hba.conf entry for host "192.168.0.2", user
"alex", database "junk", SSL off

$ SSLVERIFY=none ./psql junk -h 192.168.0.2
psql: server common name 'bahdushka' does not match hostname
'192.168.0.2'FATAL:  no pg_hba.conf entry for host "192.168.0.2", user
"alex", database "junk", SSL off

### hrm ok must not be ca common name
### so now lets make a ca with a good common name
$ openssl genrsa -out ca.key

$ openssl req -new -key ca.key -out ca.csr
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:bahdushka
Email Address []:

$ openssl x509 -req -days 3650 -in ca.csr -signkey ca.key -out ca.crt
Signature ok
subject=/C=US/ST=Some-State/O=Internet Widgits Pty Ltd/CN=bahdushka
Getting Private key

### now make a key that has a good cn just to make sure

$ openssl genrsa -out postgres.key

$ openssl req -new -key postgres.key -out postgres.csr
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:bahdushka
Email Address []:

$ openssl x509 -req -days 365 -CA ca.crt  -CAkey ca.key
-CAcreateserial -in postgres.csr -out postgres.crt
Signature ok
subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd/CN=bahdushka
Getting CA Private Key

$ cp ca.crt data/root.crt
$ cp ca.key data/root.key
$ cp postgres.crt data/server.crt
$ cp postgres.key data/server.key
$ rm ~/.postgresql/*

# restart postgres

$ SSLVERIFY=none ./psql junk -h bahdushka
psql: root certificate file (/home/alex/.postgresql/root.crt)

$ cp ca.crt ~/.postgresql/root.crt

$ SSLVERIFY=none ./psql junk -h bahdushka
psql (8.4devel)
Type "help" for help.

junk=#
LOG:  could not accept SSL connection: peer did not return a certificate

$ SSLVERIFY=cn ./psql junk -h bahdushka
psql (8.4devel)
Type "help" for help.

junk=#
LOG:  could not accept SSL connection: peer did not return a certificate

$ cp postgres.crt ~/.postgresql/postgresql.crt
$ cp postgres.key ~/.postgresql/postgresql.key
$ chmod 0600 ~/.postgresql/*

$ SSLVERIFY=cn ./psql junk -h 127.0.0.1
psql (8.4devel)
Type "help" for help.

junk=#
LOG:  could not receive data from client: Connection reset by peer

$ SSLVERIFY=cn ./psql junk -h bahdushka
psql (8.4devel)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

junk=#

### now make a crt that has a bad common name
$ openssl genrsa -out pg.key

$ openssl req -new -key pg.key -out pg.csr
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:asdf
Email Address []:

$ openssl x509 -req -days 365 -CA ca.crt -CAkey ca.key -CAcreateserial
-in pg.csr -out pg.crt
Signature ok
subject=/C=US/ST=Some-State/O=Internet Widgits Pty Ltd/CN=asdf
Getting CA Private Key

$ cp pg.crt ~/.postgresql/postgresql.crt
$ cp pg.key ~/.postgresql/postgresql.key
$ chmod 0400 ~/.postgresql/*

$ SSLVERIFY=cn ./psql junk -h bahdushka
psql (8.4devel)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

junk=#

### ok no difference here must be the other way bad cn on the server

$ pg_ctl -D data -m fast stop
$ cp pg.crt data/server.crt
$ cp pg.key data/server.key
$ chmod 0600 data/server.*
$ ./postgres  -D data/
LOG:  SSL certificate revocation list file "root.crl" not found,
skipping: No such file or directory
DETAIL:  Certificates will not be checked against revocation list.
LOG:  could not create IPv6 socket: Address family not supported by protocol
LOG:  database system was shut down at 2008-11-10 23:10:21 MST
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections

$ SSLVERIFY=cn ./psql junk -h bahdushka
psql (8.4devel)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

junk=#

### ok now lets try one not signed by the CA
$ openssl req -new -text -out server.req
Generating a 1024 bit RSA private key
....++++++
......++++++
writing new private key to 'privkey.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:

Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:bahdushka
Email Address []:

$ openssl rsa -in privkey.pem -out server.key
Enter pass phrase for privkey.pem:
writing RSA key

$ openssl req -x509 -in server.req -test -key server.key -out server.crt

$ cp server.crt ~/.postgresql/postgresql.crt
$ cp server.key ~/.postgresql/postgresqlkey
$ chmod 0600 ~/.postgresql/*

$ SSLVERIFY=cn ./psql junk -h bahdushka
psql (8.4devel)
Type "help" for help.

junk=#
LOG:  could not accept SSL connection: no certificate returned

$ SSLVERIFY=none ./psql junk -h bahdushka
psql (8.4devel)
Type "help" for help.

junk=#
LOG:  could not accept SSL connection: no certificate returned

But other than that looks good other than the promised documentation
and the mem leak Tom Lane noted. (unless I missed an updated patch?)

(ptr to the documentation I was referring to ...)
On Tue, Oct 21, 2008 at 01:09, Magnus Hagander <magnus@hagander.net> wrote:
> On 21 okt 2008, at 10.04, Peter Eisentraut <peter_e@gmx.net> wrote:
>
>> Magnus Hagander wrote:
>>>
>>> Robert Haas wrote:
>>>>>>
>>>>>> How can you make that the default?  Won't it immediately break every
>>>>>> installation without certificates?
>>>>>
>>>>> *all* SSL installations have certificate on the server side. You cannot
>>>>> run without it.
>>>>
>>>> s/without certificates/with self-signed certificates/
>>>>
>>>> which I would guess to be a common configuration
>>>
>>> Self-signed still work. In a self-signed scenario, the server
>>> certificate *is* the CA certificate.
>>
>> But the user needs to copy the CA to the client, which most people
>> probably don't do nowadays.
>
> True. I'll update the docs to make this even more clear, for those who don't
> know ssl. I still consider that a feature and not a problem ..

Alex Hunsaker wrote:
> On Mon, Oct 20, 2008 at 05:50, Magnus Hagander <magnus@hagander.net> wrote:

> $ SSLVERIFY=cn ./psql junk -h 192.168.0.2
> psql: server common name 'bahdushka' does not match hostname
> '192.168.0.2'FATAL:  no pg_hba.conf entry for host "192.168.0.2", user
> "alex", database "junk", SSL off

It needs to be PGSSLVERIFY if it's an environment variable. sslverify is
the connection parameter.

I think that's confusing your tests all the way through :(

Also, I'd recommend running the server with a log on a different console
or to a file so you don't get client and server error messages mixed up.


> $ SSLVERIFY=none ./psql junk -h bahdushka
> psql: root certificate file (/home/alex/.postgresql/root.crt)

Is that really the whole error message, or was it cut off? Because if it
is, then that is certainly a bug!


> But other than that looks good other than the promised documentation
> and the mem leak Tom Lane noted. (unless I missed an updated patch?)

I think you did, because there is certainly docs in the last one I sent
:-) But here's the very latest-and-greatest - I changed the cn matching
to be case insensitive per offlist comment from Dan Kaminsky, and an
internal return type to bool instead of int.

//Magnus
*** a/doc/src/sgml/libpq.sgml
--- b/doc/src/sgml/libpq.sgml
***************
*** 260,265 ****
--- 260,292 ----
          </varlistentry>

          <varlistentry>
+          <term><literal>sslverify</literal></term>
+          <listitem>
+           <para>
+            This option controls how libpq verifies the certificate on the
+            server when performing an <acronym>SSL</> connection. There are
+            three options: <literal>none</> disables verification completely
+            (not recommended!); <literal>cert</> enables verification that
+            the certificate chains to a known CA only; <literal>cn</> will
+            both verify that the certificate chains to a known CA and that
+            the <literal>cn</> attribute of the certificate matches the
+            hostname the connection is being made to (default).
+           </para>
+
+           <para>
+            It is always recommended to use the <literal>cn</> value for
+            this parameter, since this is the only option that prevents
+            man-in-the-middle attacks. Note that this requires the server
+            name on the certificate to match exactly with the host name
+            used for the connection, and therefore does not support connections
+            to aliased names. It can be used with pure IP address connections
+            only if the certificate also has just the IP address in the
+            <literal>cn</> field.
+           </para>
+          </listitem>
+         </varlistentry>
+
+         <varlistentry>
           <term><literal>requiressl</literal></term>
           <listitem>
            <para>
***************
*** 5682,5687 **** myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
--- 5709,5730 ----
      <listitem>
       <para>
        <indexterm>
+        <primary><envar>PGSSLVERIFY</envar></primary>
+       </indexterm>
+       <envar>PGSSLVERIFY</envar> controls how libpq verifies the certificate on the
+        server when performing an <acronym>SSL</> connection. There are
+        three options: <literal>none</> disables verification completely
+        (not recommended!); <literal>cert</> enables verification that
+        the certificate chains to a known CA only; <literal>cn</> will
+        both verify that the certificate chains to a known CA and that
+        the <literal>cn</> attribute of the certificate matches the
+        hostname the connection is being made to (default).
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       <indexterm>
         <primary><envar>PGREQUIRESSL</envar></primary>
        </indexterm>
        <envar>PGREQUIRESSL</envar> sets whether or not the connection must
***************
*** 6026,6034 **** myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
    </para>

    <para>
!    To verify the server certificate is trustworthy, place certificates of
!    the certificate authorities (<acronym>CA</acronym>) you trust in the
!    file <filename>~/.postgresql/root.crt</> in the user's home directory.
     (On Microsoft Windows the file is named
     <filename>%APPDATA%\postgresql\root.crt</filename>.)
     <application>libpq</application> will then verify that the server's
--- 6069,6079 ----
    </para>

    <para>
!    When the <literal>sslverify</> parameter is set to <literal>cn</> or
!    <literal>cert</>, libpq will verify that the server certificate is
!    trustworthy by checking the certificate chain up to a <acronym>CA</>.
!    For this to work, place the certificate of a trusted <acronym>CA</>
!    in the file <filename>~/.postgresql/root.crt</> in the user's home directory.
     (On Microsoft Windows the file is named
     <filename>%APPDATA%\postgresql\root.crt</filename>.)
     <application>libpq</application> will then verify that the server's
*** a/doc/src/sgml/runtime.sgml
--- b/doc/src/sgml/runtime.sgml
***************
*** 1418,1426 **** $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput
     <filename>server.key</filename> (key) and
     <filename>server.crt</filename> (certificate) files (<xref
     linkend="ssl-tcp">). The TCP client must connect using
!    <literal>sslmode='require'</> (<xref linkend="libpq-connect">) and have
!    a <filename>~/.postgresql/root.crt</> SSL certificate (<xref
!    linkend="libpq-ssl">).
    </para>
   </sect1>

--- 1418,1426 ----
     <filename>server.key</filename> (key) and
     <filename>server.crt</filename> (certificate) files (<xref
     linkend="ssl-tcp">). The TCP client must connect using
!    <literal>sslmode='require'</>, specify <literal>sslverify='cn'</>
!    or <literal>sslverify='cert'</> and have the required certificate
!    files present (<xref linkend="libpq-connect">).
    </para>
   </sect1>

***************
*** 1544,1551 **** $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput

     <listitem>
      <para>
!      It is possible for both the client and server to provide SSL keys
!      or certificates to each other. It takes some extra configuration
       on each side, but this provides stronger verification of identity
       than the mere use of passwords. It prevents a computer from
       pretending to be the server just long enough to read the password
--- 1544,1551 ----

     <listitem>
      <para>
!      It is possible for both the client and server to provide SSL
!      certificates to each other. It takes some extra configuration
       on each side, but this provides stronger verification of identity
       than the mere use of passwords. It prevents a computer from
       pretending to be the server just long enough to read the password
***************
*** 1757,1763 **** chmod og-rwx server.key
      A self-signed certificate can be used for testing, but a certificate
      signed by a certificate authority (<acronym>CA</>) (either one of the
      global <acronym>CAs</> or a local one) should be used in production
!     so the client can verify the server's identity.
     </para>

    </sect2>
--- 1757,1765 ----
      A self-signed certificate can be used for testing, but a certificate
      signed by a certificate authority (<acronym>CA</>) (either one of the
      global <acronym>CAs</> or a local one) should be used in production
!     so the client can verify the server's identity. If all the clients
!     are local to the organization, using a local <acronym>CA</> is
!     recommended.
     </para>

    </sect2>
*** a/src/interfaces/libpq/fe-connect.c
--- b/src/interfaces/libpq/fe-connect.c
***************
*** 92,99 **** static int ldapServiceLookup(const char *purl, PQconninfoOption *options,
--- 92,101 ----
  #define DefaultPassword          ""
  #ifdef USE_SSL
  #define DefaultSSLMode    "prefer"
+ #define DefaultSSLVerify "cn"
  #else
  #define DefaultSSLMode    "disable"
+ #define DefaultSSLVerify "none"
  #endif

  /* ----------
***************
*** 181,186 **** static const PQconninfoOption PQconninfoOptions[] = {
--- 183,191 ----
      {"sslmode", "PGSSLMODE", DefaultSSLMode, NULL,
      "SSL-Mode", "", 8},            /* sizeof("disable") == 8 */

+     {"sslverify", "PGSSLVERIFY", DefaultSSLVerify, NULL,
+     "SSL-Verify", "", 5},        /* sizeof("chain") == 5 */
+
  #if defined(KRB5) || defined(ENABLE_GSS) || defined(ENABLE_SSPI)
      /* Kerberos and GSSAPI authentication support specifying the service name */
      {"krbsrvname", "PGKRBSRVNAME", PG_KRB_SRVNAM, NULL,
***************
*** 415,420 **** connectOptions1(PGconn *conn, const char *conninfo)
--- 420,427 ----
      conn->connect_timeout = tmp ? strdup(tmp) : NULL;
      tmp = conninfo_getval(connOptions, "sslmode");
      conn->sslmode = tmp ? strdup(tmp) : NULL;
+     tmp = conninfo_getval(connOptions, "sslverify");
+     conn->sslverify = tmp ? strdup(tmp) : NULL;
  #ifdef USE_SSL
      tmp = conninfo_getval(connOptions, "requiressl");
      if (tmp && tmp[0] == '1')
***************
*** 530,535 **** connectOptions2(PGconn *conn)
--- 537,560 ----
          conn->sslmode = strdup(DefaultSSLMode);

      /*
+      * Validate sslverify option
+      */
+     if (conn->sslverify)
+     {
+         if (strcmp(conn->sslverify, "none") != 0
+             && strcmp(conn->sslverify, "cert") != 0
+             && strcmp(conn->sslverify, "cn") != 0)
+         {
+             conn->status = CONNECTION_BAD;
+             printfPQExpBuffer(&conn->errorMessage,
+                             libpq_gettext("invalid sslverify value: \"%s\"\n"),
+                               conn->sslverify);
+             return false;
+         }
+     }
+
+
+     /*
       * Only if we get this far is it appropriate to try to connect. (We need a
       * state flag, rather than just the boolean result of this function, in
       * case someone tries to PQreset() the PGconn.)
***************
*** 2008,2013 **** freePGconn(PGconn *conn)
--- 2033,2040 ----
          free(conn->pgpass);
      if (conn->sslmode)
          free(conn->sslmode);
+     if (conn->sslverify)
+         free(conn->sslverify);
  #if defined(KRB5) || defined(ENABLE_GSS) || defined(ENABLE_SSPI)
      if (conn->krbsrvname)
          free(conn->krbsrvname);
*** a/src/interfaces/libpq/fe-secure.c
--- b/src/interfaces/libpq/fe-secure.c
***************
*** 87,95 ****
  #define ERR_pop_to_mark()    ((void) 0)
  #endif

! #ifdef NOT_USED
! static int    verify_peer_name_matches_certificate(PGconn *);
! #endif
  static int    verify_cb(int ok, X509_STORE_CTX *ctx);
  static int    client_cert_cb(SSL *, X509 **, EVP_PKEY **);
  static int    init_ssl_system(PGconn *conn);
--- 87,93 ----
  #define ERR_pop_to_mark()    ((void) 0)
  #endif

! static bool verify_peer_name_matches_certificate(PGconn *);
  static int    verify_cb(int ok, X509_STORE_CTX *ctx);
  static int    client_cert_cb(SSL *, X509 **, EVP_PKEY **);
  static int    init_ssl_system(PGconn *conn);
***************
*** 438,514 **** verify_cb(int ok, X509_STORE_CTX *ctx)
      return ok;
  }

- #ifdef NOT_USED
  /*
   *    Verify that common name resolves to peer.
   */
! static int
  verify_peer_name_matches_certificate(PGconn *conn)
  {
!     struct hostent *cn_hostentry = NULL;
!     struct sockaddr server_addr;
!     struct sockaddr_in *sin (struct sockaddr_in *) &server_addr;
!     ACCEPT_TYPE_ARG3 len;
!     char      **s;
!     unsigned long l;
!
!     /* Get the address on the other side of the socket. */
!     len = sizeof(server_addr);
!     if (getpeername(conn->sock, &server_addr, &len) == -1)
!     {
!         char        sebuf[256];
!
!         printfPQExpBuffer(&conn->errorMessage,
!                           libpq_gettext("error querying socket: %s\n"),
!                           SOCK_STRERROR(SOCK_ERRNO, sebuf, sizeof(sebuf)));
!         return -1;
!     }

!     if (server_addr.sa_family != AF_INET)
      {
          printfPQExpBuffer(&conn->errorMessage,
!                           libpq_gettext("unsupported protocol\n"));
!         return -1;
      }
!
!     /* Get the IP addresses of the certificate's common name (CN) */
      {
-         struct hostent hpstr;
-         char        buf[BUFSIZ];
-         int            herrno = 0;
-
          /*
!          * Currently, pqGethostbyname() is used only on platforms that don't
!          * have getaddrinfo().    If you enable this function, you should
!          * convert the pqGethostbyname() function call to use getaddrinfo().
           */
!         pqGethostbyname(conn->peer_cn, &hpstr, buf, sizeof(buf),
!                         &cn_hostentry, &herrno);
!     }
!
!     /* Did we get an IP address? */
!     if (cn_hostentry == NULL)
!     {
!         printfPQExpBuffer(&conn->errorMessage,
!           libpq_gettext("could not get information about host \"%s\": %s\n"),
!                           conn->peer_cn, hstrerror(h_errno));
!         return -1;
      }
-
-     /* Does one of the CN's IP addresses match the server's IP address? */
-     for (s = cn_hostentry->h_addr_list; *s != NULL; s++)
-         if (!memcmp(&sin->sin_addr.s_addr, *s, cn_hostentry->h_length))
-             return 0;
-
-     l = ntohl(sin->sin_addr.s_addr);
-     printfPQExpBuffer(&conn->errorMessage,
-                       libpq_gettext(
-                         "server common name \"%s\" does not resolve to %ld.%ld.%ld.%ld\n"),
-                       conn->peer_cn, (l >> 24) % 0x100, (l >> 16) % 0x100,
-                       (l >> 8) % 0x100, l % 0x100);
-     return -1;
  }
- #endif   /* NOT_USED */

  /*
   *    Callback used by SSL to load client cert and key.
--- 436,479 ----
      return ok;
  }

  /*
   *    Verify that common name resolves to peer.
   */
! static bool
  verify_peer_name_matches_certificate(PGconn *conn)
  {
!     /*
!      * If told not to verify the peer name, don't do it. Return
!      * 0 indicating that the verification was successful.
!      */
!     if(strcmp(conn->sslverify, "cn") != 0)
!         return true;

!     if (conn->pghostaddr)
      {
          printfPQExpBuffer(&conn->errorMessage,
!                           libpq_gettext("verified SSL connections are only supported when connecting to a
hostname"));
!         return false;
      }
!     else
      {
          /*
!          * Connect by hostname.
!          *
!          * XXX: Should support alternate names here
!          * XXX: Should support wildcard certificates here
           */
!         if (pg_strcasecmp(conn->peer_cn, conn->pghost) != 0)
!         {
!             printfPQExpBuffer(&conn->errorMessage,
!                               libpq_gettext("server common name '%s' does not match hostname '%s'"),
!                               conn->peer_cn, conn->pghost);
!             return false;
!         }
!         else
!             return true;
      }
  }

  /*
   *    Callback used by SSL to load client cert and key.
***************
*** 846,851 **** initialize_SSL(PGconn *conn)
--- 811,822 ----
      if (init_ssl_system(conn))
          return -1;

+     /*
+      * If sslverify is set to anything other than "none", perform certificate
+      * verification. If set to "cn" we will also do further verifications after
+      * the connection has been completed.
+      */
+
      /* Set up to verify server cert, if root.crt is present */
      if (pqGetHomeDirectory(homedir, sizeof(homedir)))
      {
***************
*** 889,894 **** initialize_SSL(PGconn *conn)
--- 860,883 ----

              SSL_CTX_set_verify(SSL_context, SSL_VERIFY_PEER, verify_cb);
          }
+         else
+         {
+             if (strcmp(conn->sslverify, "none") != 0)
+             {
+                 printfPQExpBuffer(&conn->errorMessage,
+                                   libpq_gettext("root certificate file (%s) not found"), fnbuf);
+                 return -1;
+             }
+         }
+     }
+     else
+     {
+         if (strcmp(conn->sslverify, "none") != 0)
+         {
+             printfPQExpBuffer(&conn->errorMessage,
+                               libpq_gettext("cannot find home directory to locate root certificate file"));
+             return -1;
+         }
      }

      /* set up mechanism to provide client certificate, if available */
***************
*** 1004,1016 **** open_client_SSL(PGconn *conn)
                                NID_commonName, conn->peer_cn, SM_USER);
      conn->peer_cn[SM_USER] = '\0';

! #ifdef NOT_USED
!     if (verify_peer_name_matches_certificate(conn) == -1)
      {
          close_SSL(conn);
          return PGRES_POLLING_FAILED;
      }
- #endif

      /* SSL handshake is complete */
      return PGRES_POLLING_OK;
--- 993,1003 ----
                                NID_commonName, conn->peer_cn, SM_USER);
      conn->peer_cn[SM_USER] = '\0';

!     if (!verify_peer_name_matches_certificate(conn))
      {
          close_SSL(conn);
          return PGRES_POLLING_FAILED;
      }

      /* SSL handshake is complete */
      return PGRES_POLLING_OK;
*** a/src/interfaces/libpq/libpq-int.h
--- b/src/interfaces/libpq/libpq-int.h
***************
*** 291,296 **** struct pg_conn
--- 291,297 ----
      char       *pguser;            /* Postgres username and password, if any */
      char       *pgpass;
      char       *sslmode;        /* SSL mode (require,prefer,allow,disable) */
+     char       *sslverify;        /* Verify server SSL certificate (none,chain,cn) */
  #if defined(KRB5) || defined(ENABLE_GSS) || defined(ENABLE_SSPI)
      char       *krbsrvname;        /* Kerberos service name */
  #endif

On Tue, Nov 11, 2008 at 06:16, Magnus Hagander <magnus@hagander.net> wrote:
> Alex Hunsaker wrote:
>> On Mon, Oct 20, 2008 at 05:50, Magnus Hagander <magnus@hagander.net> wrote:
>
>> $ SSLVERIFY=cn ./psql junk -h 192.168.0.2
>> psql: server common name 'bahdushka' does not match hostname
>> '192.168.0.2'FATAL:  no pg_hba.conf entry for host "192.168.0.2", user
>> "alex", database "junk", SSL off
>
> It needs to be PGSSLVERIFY if it's an environment variable. sslverify is
> the connection parameter.

Doh! Will go retry just as soon as I find a boot big enough to kick myself with.

> I think that's confusing your tests all the way through :(
>
> Also, I'd recommend running the server with a log on a different console
> or to a file so you don't get client and server error messages mixed up.

Well it was on a different console, I just put them into the same view
to show that I was actually restarting postgres when I changed the ssl
keys :)

>> $ SSLVERIFY=none ./psql junk -h bahdushka
>> psql: root certificate file (/home/alex/.postgresql/root.crt)
>
> Is that really the whole error message, or was it cut off? Because if it
> is, then that is certainly a bug!

Err it said psql: root certificate file
(/home/alex/.postgresql/root.crt) not found

>> But other than that looks good other than the promised documentation
>> and the mem leak Tom Lane noted. (unless I missed an updated patch?)
>
> I think you did, because there is certainly docs in the last one I sent
> :-) But here's the very latest-and-greatest - I changed the cn matching
> to be case insensitive per offlist comment from Dan Kaminsky, and an
> internal return type to bool instead of int.

Thanks

On 12 nov 2008, at 05.28, "Alex Hunsaker" <badalex@gmail.com> wrote:

> On Tue, Nov 11, 2008 at 06:16, Magnus Hagander <magnus@hagander.net>  
> wrote:
>> Alex Hunsaker wrote:
>>> On Mon, Oct 20, 2008 at 05:50, Magnus Hagander  
>>> <magnus@hagander.net> wrote:
>>
>>> $ SSLVERIFY=cn ./psql junk -h 192.168.0.2
>>> psql: server common name 'bahdushka' does not match hostname
>>> '192.168.0.2'FATAL:  no pg_hba.conf entry for host "192.168.0.2",  
>>> user
>>> "alex", database "junk", SSL off
>>
>> It needs to be PGSSLVERIFY if it's an environment variable.  
>> sslverify is
>> the connection parameter.
>
> Doh! Will go retry just as soon as I find a boot big enough to kick  
> myself with.

:)

>
>> RI think that's confusing your tests all the way through :(
>>
>> Also, I'd recommend running the server with a log on a different  
>> console
>> or to a file so you don't get client and server error messages  
>> mixed up.
>
> Well it was on a different console, I just put them into the same view
> to show that I was actually restarting postgres when I changed the ssl
> keys :)

Heh, ok.


>>> $ SSLVERIFY=none ./psql junk -h bahdushka
>>> psql: root certificate file (/home/alex/.postgresql/root.crt)
>>
>> Is that really the whole error message, or was it cut off? Because  
>> if it
>> is, then that is certainly a bug!
>
> Err it said psql: root certificate file
> (/home/alex/.postgresql/root.crt) not found

Ok, good, then it's not broken.

/Magnus

>>

OK  now that im using the right env var everything seems to work as
described.  FYI I also tried to exercise the various new error paths
and everything seems good so as far as i'm concerned this looks good
to me.  Ill go mark it as "ready for commiter" on the wiki.  (whatever
that means you being a commiter :) )

-----------
$ PGSSLVERIFY=none ./psql postgres -h 127.0.0.1
psql (8.4devel)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=# \q

$ PGSSLVERIFY=cert ./psql postgres -h 127.0.0.1
psql (8.4devel)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=# \q

$ ./psql postgres -h 127.0.0.1
psql: server common name 'bahdushka' does not match hostname
'127.0.0.1'FATAL:  no pg_hba.conf entry for host "127.0.0.1", user
"alex", database "postgres", SSL off

$ PGHOSTADDR=127.0.0.1 ./psql postgres -h 127.0.0.1
psql: verified SSL connections are only supported when connecting to a
hostnameFATAL:  no pg_hba.conf entry for host "127.0.0.1", user
"alex", database "postgres", SSL off

$ rm ~/.postgresql/root.crt

$ PGSSLVERIFY=none ./psql postgres -h 127.0.0.1
psql (8.4devel)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=# \q

$ PGSSLVERIFY=cert ./psql postgres -h 127.0.0.1
psql: root certificate file (/home/alex/.postgresql/root.crt) not found

It means I will go ahead and apply it once I have looked it over once  
more.

Thanks for review+testing!

You may now move on to the next ssl patch if you're interested ;)

/Magnus


On 12 nov 2008, at 17.05, "Alex Hunsaker" <badalex@gmail.com> wrote:

> OK  now that im using the right env var everything seems to work as
> described.  FYI I also tried to exercise the various new error paths
> and everything seems good so as far as i'm concerned this looks good
> to me.  Ill go mark it as "ready for commiter" on the wiki.  (whatever
> that means you being a commiter :) )
>
> -----------
> $ PGSSLVERIFY=none ./psql postgres -h 127.0.0.1
> psql (8.4devel)
> SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
> Type "help" for help.
>
> postgres=# \q
>
> $ PGSSLVERIFY=cert ./psql postgres -h 127.0.0.1
> psql (8.4devel)
> SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
> Type "help" for help.
>
> postgres=# \q
>
> $ ./psql postgres -h 127.0.0.1
> psql: server common name 'bahdushka' does not match hostname
> '127.0.0.1'FATAL:  no pg_hba.conf entry for host "127.0.0.1", user
> "alex", database "postgres", SSL off
>
> $ PGHOSTADDR=127.0.0.1 ./psql postgres -h 127.0.0.1
> psql: verified SSL connections are only supported when connecting to a
> hostnameFATAL:  no pg_hba.conf entry for host "127.0.0.1", user
> "alex", database "postgres", SSL off
>
> $ rm ~/.postgresql/root.crt
>
> $ PGSSLVERIFY=none ./psql postgres -h 127.0.0.1
> psql (8.4devel)
> SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
> Type "help" for help.
>
> postgres=# \q
>
> $ PGSSLVERIFY=cert ./psql postgres -h 127.0.0.1
> psql: root certificate file (/home/alex/.postgresql/root.crt) not  
> found

On Thu, Nov 13, 2008 at 01:05, Magnus Hagander <magnus@hagander.net> wrote:
> It means I will go ahead and apply it once I have looked it over once more.
>
> Thanks for review+testing!
>
> You may now move on to the next ssl patch if you're interested ;)

Sure thing probably wont get to it till tomorrow...