Subject: Correction of intermediate certificate handling

From: Bruce Momjian <[email-masked]>
Date: Mon, 15 Jan 2018 19:22:38 -0500

We have been confused by the behavior of intermediate certificates in
Postgres for many years.  Some people put the intermediate certificates
only on the server and they were supplied to the client, while other
people couldn't get that to work.  In our documentation we recommended
storing intermediate certificates on the client and server.

As part of research for my security talks:

	https://momjian.us/main/presentations/security.html

I asked Stephen Frost and David Steele for details on the arcane art of
SSL certificate creation.  They showed me scripts they use and explained
that they properly pass intermediate certificates to clients.  The trick
was to use the v3_ca extension when creating root and intermediate
certificates.

My talk documents this behavior.  In this talk:

	https://momjian.us/main/writings/pgsql/tls.pdf

slide 47 and 49 use -extensions v3_ca.  Slides 73 and 74 show that the
intermediate is not needed on the client if it is created with v3_ca and
exist on the server.  Slide 75 shows that the server certificate must be
first in server.crt.

I have created the attached doc patch to add this information to our
docs.  I would like to backpatch this since what we have now, while it
works, is inaccurate.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
new file mode 100644
index 4e46451..801b033
*** a/doc/src/sgml/libpq.sgml
--- b/doc/src/sgml/libpq.sgml
*************** ldap://ldap.acme.com/cn=dbserver,cn=host
*** 7601,7613 ****
    </para>
  
    <para>
!    To allow server certificate verification, the certificate(s) of one or more
!    trusted <acronym>CA</acronym>s must be
!    placed in the file <filename>~/.postgresql/root.crt</filename> in the user's home
!    directory. If intermediate <acronym>CA</acronym>s appear in
!    <filename>root.crt</filename>, the file must also contain certificate
!    chains to their root <acronym>CA</acronym>s. (On Microsoft Windows the file is named
!    <filename>%APPDATA%\postgresql\root.crt</filename>.)
    </para>
  
    <para>
--- 7601,7613 ----
    </para>
  
    <para>
!    To allow server certificate verification, one or more root certificates
!    must be placed in the file <filename>~/.postgresql/root.crt</filename>
!    in the user's home directory.  (On Microsoft Windows the file is named
!    <filename>%APPDATA%\postgresql\root.crt</filename>.)  Intermediate
!    certificates should also be added to the file if they are needed to link
!    the certificate chain sent by the server to the root certificates
!    stored on the client.
    </para>
  
    <para>
*************** ldap://ldap.acme.com/cn=dbserver,cn=host
*** 7660,7682 ****
    </para>
  
    <para>
!    In some cases, the client certificate might be signed by an
!    <quote>intermediate</quote> certificate authority, rather than one that is
!    directly trusted by the server.  To use such a certificate, append the
!    certificate of the signing authority to the <filename>postgresql.crt</filename>
!    file, then its parent authority's certificate, and so on up to a certificate
!    authority, <quote>root</quote> or <quote>intermediate</quote>, that is trusted by
!    the server, i.e. signed by a certificate in the server's root CA file
!    (<xref linkend="guc-ssl-ca-file"/>).
!   </para>
! 
!   <para>
!    Note that the client's <filename>~/.postgresql/root.crt</filename> lists the top-level CAs
!    that are considered trusted for signing server certificates.  In principle it need
!    not list the CA that signed the client's certificate, though in most cases
!    that CA would also be trusted for server certificates.
    </para>
- 
   </sect2>
  
   <sect2 id="libpq-ssl-protection">
--- 7660,7672 ----
    </para>
  
    <para>
!    The first certificate in <filename>postgresql.crt</filename> must be the
!    clients's certificate because it must match the client's private key.
!    The certificates of <quote>intermediate</quote> certificate authorities
!    can be optionally appended to the file.  Doing this avoids requiring
!    the intermediate certificates to be stored on the server (<xref
!    linkend="guc-ssl-ca-file"/>).
    </para>
   </sect2>
  
   <sect2 id="libpq-ssl-protection">
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
new file mode 100644
index a2ebd3e..6352d53
*** a/doc/src/sgml/runtime.sgml
--- b/doc/src/sgml/runtime.sgml
*************** pg_dumpall -p 5432 | psql -d postgres -p
*** 2247,2286 ****
    </para>
  
    <para>
!    In some cases, the server certificate might be signed by an
!    <quote>intermediate</quote> certificate authority, rather than one that is
!    directly trusted by clients.  To use such a certificate, append the
!    certificate of the signing authority to the <filename>server.crt</filename> file,
!    then its parent authority's certificate, and so on up to a certificate
!    authority, <quote>root</quote> or <quote>intermediate</quote>, that is trusted by
!    clients, i.e. signed by a certificate in the clients'
!    <filename>root.crt</filename> files.
    </para>
  
    <sect2 id="ssl-client-certificates">
     <title>Using Client Certificates</title>
  
    <para>
!    To require the client to supply a trusted certificate, place
!    certificates of the certificate authorities (<acronym>CA</acronym>s)
!    you trust in a file named <filename>root.crt</filename> in the data
     directory, set the parameter <xref linkend="guc-ssl-ca-file"/> in
!    <filename>postgresql.conf</filename> to <literal>root.crt</literal>,
!    and add the authentication option <literal>clientcert=1</literal> to the
!    appropriate <literal>hostssl</literal> line(s) in <filename>pg_hba.conf</filename>.
!    A certificate will then be requested from the client during
!    SSL connection startup.  (See <xref linkend="libpq-ssl"/> for a
!    description of how to set up certificates on the client.)  The server will
     verify that the client's certificate is signed by one of the trusted
     certificate authorities.
    </para>
  
    <para>
!    If intermediate <acronym>CA</acronym>s appear in
!    <filename>root.crt</filename>, the file must also contain certificate
!    chains to their root <acronym>CA</acronym>s.  Certificate Revocation List
!    (CRL) entries
!    are also checked if the parameter <xref linkend="guc-ssl-crl-file"/> is set.
     <!-- If this URL changes replace it with a URL to www.archive.org. -->
     (See <ulink
     url="http://h71000.www7.hp.com/doc/83final/ba554_90007/ch04s02.html"></ulink>
--- 2247,2292 ----
    </para>
  
    <para>
!    The first certificate in <filename>server.crt</filename> must be the
!    server's certificate because it must match the server's private key.
!    The certificates of <quote>intermediate</quote> certificate authorities
!    can also be appended to the file.  Doing this avoids the necessity of
!    storing intermediate certificates on clients, assuming the root and
!    intermediate certificates were created with <literal>v3_ca</literal>
!    extensions.  This allows easier expiration of intermediate certificates.
!   </para>
! 
!   <para>
!    It is not necessary to add the root certificate to
!    <filename>server.crt</filename>.  Instead, clients must have the root
!    certificate of the server's certificate chain.
    </para>
  
    <sect2 id="ssl-client-certificates">
     <title>Using Client Certificates</title>
  
    <para>
!    To require the client to supply a trusted certificate,
!    place certificates of the root certificate authorities
!    (<acronym>CA</acronym>s) you trust in a file in the data
     directory, set the parameter <xref linkend="guc-ssl-ca-file"/> in
!    <filename>postgresql.conf</filename> to the new file name, and add the
!    authentication option <literal>clientcert=1</literal> to the appropriate
!    <literal>hostssl</literal> line(s) in <filename>pg_hba.conf</filename>.
!    A certificate will then be requested from the client during SSL
!    connection startup.  (See <xref linkend="libpq-ssl"/> for a description
!    of how to set up certificates on the client.)  The server will
     verify that the client's certificate is signed by one of the trusted
     certificate authorities.
    </para>
  
    <para>
!    Intermediate certificates that chain up to existing root certificates
!    can also appear in the <xref linkend="guc-ssl-ca-file"/> file if
!    you wish to avoid storing them on clients (assuming the root and
!    intermediate certificates were created with <literal>v3_ca</literal>
!    extensions).  Certificate Revocation List (CRL) entries are also
!    checked if the parameter <xref linkend="guc-ssl-crl-file"/> is set.
     <!-- If this URL changes replace it with a URL to www.archive.org. -->
     (See <ulink
     url="http://h71000.www7.hp.com/doc/83final/ba554_90007/ch04s02.html"></ulink>
*************** pg_dumpall -p 5432 | psql -d postgres -p
*** 2297,2310 ****
    </para>
  
    <para>
-    Note that the server's <filename>root.crt</filename> lists the top-level
-    CAs that are considered trusted for signing client certificates.
-    In principle it need
-    not list the CA that signed the server's certificate, though in most cases
-    that CA would also be trusted for client certificates.
-   </para>
- 
-   <para>
     If you are setting up client certificates, you may wish to use
     the <literal>cert</literal> authentication method, so that the certificates
     control user authentication as well as providing connection security.
--- 2303,2308 ----

---

From: Michael Paquier <[email-masked]>
Date: Tue, 16 Jan 2018 14:33:05 +0900

> docs.  I would like to backpatch this since what we have now, while it

I have spent some time looking at your patch, this gets a +1 from here.

This bit is important. I am happy that your patch mentions that
intermediate certificates avoid the need to store root ones on the
client. Should the docs mention terms like "chain of trust"?

Perhaps the docs could also include an example of command to create a
root and an intermediate certificate in runtime.sgml or such?

On top of that, src/test/ssl does not provide any kind of coverage for
that. It would be an area of improvement for those tests.
--
Michael

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 16 Jan 2018 11:21:22 -0500

> I have spent some time looking at your patch, this gets a +1 from here.

Thanks.

> client. Should the docs mention terms like "chain of trust"?

I think the question is how much do we want to "teach" people in our
docs.  We do oddly but wisely link from our docs to HP OpenVMS docs
about how the chain of trust works:

	http://h41379.www4.hpe.com/doc/83final/ba554_90007/ch04s02.html

I will write up a paragraph about the concepts for our docs for the
group's review.

> root and an intermediate certificate in runtime.sgml or such?

Yes, I have thought about that.  My presentation has clear examples that
we can use, again based on Stephen and David's scripts using v3_ca.  I
will work up a possible patch for that too.

> that. It would be an area of improvement for those tests.

Wow, I have no idea how to do that.  Let me look.  Seems I have more
work to do.

Instead of appending to this doc patch, I will work on a second one for
review.

---

From: Michael Paquier <[email-masked]>
Date: Wed, 17 Jan 2018 09:09:50 +0900

> I will write up a paragraph about the concepts for our docs for the

As a separate patch, I think that it would be fine as well.

> will work up a possible patch for that too.

That too.

> Wow, I have no idea how to do that.  Let me look.  Seems I have more

You would need to update src/test/ssl/Makefile to generate those
intermediate CAs, and then make ServerSetup::switch_server_cert smarter
in the way the series of certificates are handled. A suggestion I have
would be to create each certificate file separately and change the
routine so as it uses an array in input, the order of the items defining
what's the order the the data. For the client there is sslrootcert, so I
guess that a small routine able to take a set of certs and append them
to a single file would make it as well (switch_server_cert should use
it).

> Instead of appending to this doc patch, I will work on a second one for

I see nothing pressing here. If you are not familiar with the TAP test
facility, this could give you a good introduction to it.
--
Michael

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 16 Jan 2018 22:23:44 -0500

> As a separate patch, I think that it would be fine as well.

I ended up merging the "chain of trust" changes into the "intermediate"
patch since they affect adjacent sections of the docs.  You can see this
as the first attached patch.

> > will work up a possible patch for that too.

I did that as a separate patch, which is the second attachment.

> to a single file would make it as well (switch_server_cert should use

I don't think I will work on the testing changes.

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
new file mode 100644
index 4e46451..ec85132
*** a/doc/src/sgml/libpq.sgml
--- b/doc/src/sgml/libpq.sgml
*************** ldap://ldap.acme.com/cn=dbserver,cn=host
*** 7574,7590 ****
     the server certificate. This means that it is possible to spoof the server
     identity (for example by modifying a DNS record or by taking over the server
     IP address) without the client knowing. In order to prevent spoofing,
!    <acronym>SSL</acronym> certificate verification must be used.
    </para>
  
    <para>
     If the parameter <literal>sslmode</literal> is set to <literal>verify-ca</literal>,
     libpq will verify that the server is trustworthy by checking the
!    certificate chain up to a trusted certificate authority
!    (<acronym>CA</acronym>). If <literal>sslmode</literal> is set to <literal>verify-full</literal>,
!    libpq will <emphasis>also</emphasis> verify that the server host name matches its
!    certificate. The SSL connection will fail if the server certificate cannot
!    be verified. <literal>verify-full</literal> is recommended in most
     security-sensitive environments.
    </para>
  
--- 7574,7610 ----
     the server certificate. This means that it is possible to spoof the server
     identity (for example by modifying a DNS record or by taking over the server
     IP address) without the client knowing. In order to prevent spoofing,
!    the client must be able to verify the server's identity via a chain of
!    trust.  A chain of trust is established by placing a root (self-signed)
!    certificate authority (<acronym>CA</acronym>) certificate on one
!    computer and a leaf certificate <emphasis>signed</emphasis> by the
!    root certificate on another computer.  It is also possible to use an
!    <quote>intermediate</quote> certificate which is signed by the root
!    certificate and signs leaf certificates.
!   </para>
! 
!   <para>
!    To allow the client to verify the identity of the server, place a root
!    certificate on the client and a leaf certificate signed by the root
!    certificate on the server.  To allow the server to verify the identity
!    of the client, place a root certificate on the server and a leaf and
!    optional intermediate certificates signed by the root certificate on
!    the client.  Intermediate certificates (usually stored with the leaf
!    certificate) can also be used to link the leaf certificate to the
!    root certificate.
    </para>
  
    <para>
+    Once a chain of trust has been established, there are two ways for
+    the client to validate the leaf certificate sent by the server.
     If the parameter <literal>sslmode</literal> is set to <literal>verify-ca</literal>,
     libpq will verify that the server is trustworthy by checking the
!    certificate chain up to the root certificate stored on the client.
!    If <literal>sslmode</literal> is set to <literal>verify-full</literal>,
!    libpq will <emphasis>also</emphasis> verify that the server host
!    name matches the name stored in the server certificate. The
!    SSL connection will fail if the server certificate cannot be
!    verified. <literal>verify-full</literal> is recommended in most
     security-sensitive environments.
    </para>

    <para>
  
    <para>
--- 7621,7633 ----
    </para>
  
    <para>
  
    <para>
*************** ldap://ldap.acme.com/cn=dbserver,cn=host
*** 7641,7651 ****
    <title>Client Certificates</title>
  
    <para>
!    If the server requests a trusted client certificate,
!    <application>libpq</application> will send the certificate stored in
     file <filename>~/.postgresql/postgresql.crt</filename> in the user's home
!    directory.  The certificate must be signed by one of the certificate
!    authorities (<acronym>CA</acronym>) trusted by the server.  A matching
     private key file <filename>~/.postgresql/postgresql.key</filename> must also
     be present. The private
     key file must not allow any access to world or group; achieve this by the
--- 7661,7672 ----
    <title>Client Certificates</title>
  
    <para>
!    If the server attempts to verify the identity of the
!    client by requesting the client's leaf certificate,
!    <application>libpq</application> will send the certificates stored in
     file <filename>~/.postgresql/postgresql.crt</filename> in the user's home
!    directory.  The certificates must chain to the root certificate trusted
!    by the server.  A matching
     private key file <filename>~/.postgresql/postgresql.key</filename> must also
     be present. The private
     key file must not allow any access to world or group; achieve this by the
*************** ldap://ldap.acme.com/cn=dbserver,cn=host
*** 7660,7682 ****
    </para>
  
    <para>
  
--- 7681,7692 ----
    </para>
  
    <para>
!    The first certificate in <filename>postgresql.crt</filename> must be the
!    client's certificate because it must match the client's private key.
!    <quote>Intermediate</quote> certificates can be optionally appended
!    to the file &mdash; doing so avoids requiring storage of intermediate
!    certificates on the server (<xref linkend="guc-ssl-ca-file"/>).
    </para>
   </sect2>

    <para>

    <para>
  
    <para>
  
    <para>

    <para>
  
    <para>
  
    <para>
index a2ebd3e..dca113b
*** 2385,2394 ****
    </sect2>
  
    <sect2 id="ssl-certificate-creation">
!    <title>Creating a Self-signed Certificate</title>
  
     <para>
!      To create a quick self-signed certificate for the server, valid for 365
       days, use the following <productname>OpenSSL</productname> command,
       replacing <replaceable>yourdomain.com</replaceable> with the server's host name:
  <programlisting>
--- 2385,2394 ----
    </sect2>
  
    <sect2 id="ssl-certificate-creation">
!    <title>Creating Certificates</title>
  
     <para>
!      To create a simple self-signed certificate for the server, valid for 365
       days, use the following <productname>OpenSSL</productname> command,
       replacing <replaceable>yourdomain.com</replaceable> with the server's host name:
  <programlisting>
*************** chmod og-rwx server.key
*** 2406,2419 ****
     </para>
  
     <para>
!     A self-signed certificate can be used for testing, but a certificate
!     signed by a certificate authority (<acronym>CA</acronym>) (either one of the
!     global <acronym>CAs</acronym> or a local one) should be used in production
!     so that clients can verify the server's identity. If all the clients
!     are local to the organization, using a local <acronym>CA</acronym> is
!     recommended.
     </para>
  
    </sect2>
  
   </sect1>
--- 2406,2476 ----
     </para>
  
     <para>
!     While a self-signed certificate can be used for testing, a certificate
!     signed by a certificate authority (<acronym>CA</acronym>) (usually an
!     enterprise-wide root <acronym>CAs</acronym>) should be used in production.
     </para>
  
+    <para>
+     To create a server certificate whose identity can be validated
+     by clients, first create a public/private key pair and certificate
+     signing request (<acronym>CSR</acronym>):
+ <programlisting>
+ openssl req -new -nodes -text -out root.csr \
+   -keyout root.key -subj "/CN=<replaceable>root.yourdomain.com</replaceable>"
+ chmod og-rwx root.key
+ </programlisting>
+     Then, sign the request with the the private key to create a root
+ certificate authority:
+ <programlisting>
+ openssl x509 -req -in root.csr -text -days 365 \
+   -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
+   -signkey root.key -out root.crt
+ </programlisting>
+     Finally, create a server certificate signed by the new root certificate
+     authority:
+ <programlisting>
+ openssl req -new -nodes -text -out server.csr \
+   -keyout server.key -subj "/CN=<replaceable>dbhost.yourdomain.com</replaceable>"
+ chmod og-rwx server.key
+ 
+ openssl x509 -req -in server.csr -text -days 365 \
+   -CA root.crt -CAkey root.key -CAcreateserial \
+   -out server.crt
+ </programlisting>
+     <filename>server.crt</filename> and <filename>server.key</filename>
+     should be stored on the server, and <filename>root.crt</filename> should
+     be stored on the client so the client can verify that the server's leaf
+     certificate was signed by its trusted root certificate.
+    </para>
+ 
+    <para>
+     It is also possible to create a chain of trust that includes
+     intermediate certificates:
+ <programlisting>
+ openssl req -new -nodes -text -out root.csr \
+   -keyout root.key -subj "/CN=<replaceable>root.yourdomain.com</replaceable>"
+ chmod og-rwx root.key
+ openssl x509 -req -in root.csr -text -days 365 \
+   -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
+   -signkey root.key -out root.crt
+ 
+ openssl req -new -nodes -text -out intermediate.csr \
+   -keyout intermediate.key -subj "/CN=<replaceable>intermediate.yourdomain.com</replaceable>"
+ chmod og-rwx intermediate.key
+ openssl x509 -req -in intermediate.csr -text -days 365 \
+   -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
+   -CA root.crt -CAkey root.key -CAcreateserial \
+   -out intermediate.crt
+ 
+ openssl req -new -nodes -text -out server.csr \
+   -keyout server.key -subj "/CN=<replaceable>dbhost.yourdomain.com</replaceable>"
+ chmod og-rwx server.key
+ openssl x509 -req -in server.csr -text -days 365 \
+   -CA intermediate.crt -CAkey intermediate.key -CAcreateserial \
+   -out server.crt
+ </programlisting>
+    </para>
    </sect2>
  
   </sect1>

---

From: Michael Paquier <[email-masked]>
Date: Wed, 17 Jan 2018 17:20:00 +0900

> as the first attached patch.

Thanks. I looked at crt.diff and the surroundings in the docs. This one
looks consistent to me.

> I did that as a separate patch, which is the second attachment.

This is openssl.diff.

+certificate authority:
s/the the/the/

+<programlisting>
+openssl req -new -nodes -text -out root.csr \
+  -keyout root.key -subj "/CN=<replaceable>root.yourdomain.com</replaceable>"
+chmod og-rwx root.key
+openssl x509 -req -in root.csr -text -days 365 \
+  -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
+  -signkey root.key -out root.crt
The succession of commands of commands for the intermediate certificates
is wild. Could it be possible to explain what each command means? Users
would not get lost this way.

> I don't think I will work on the testing changes.

Fine for me. This could do for a fine TODO item. Not one of those hard,
complicated and basically impossible things on the TODO list.
--
Michael

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 17 Jan 2018 07:34:42 -0500

> Thanks. I looked at crt.diff and the surroundings in the docs. This one

Good, thanks.

> would not get lost this way.

Yes, I was not happy about that either.  I was afraid that pound-sign
comments would look like root prompts but I just added them and they
look fine.  Updated patch attached, with some expiration and wording
adjustments.  There is also a new paragraph at the end explaining where
to place the files.

> complicated and basically impossible things on the TODO list.

Agreed.

index a2ebd3e..7f83d0c
*** 2385,2399 ****
    </sect2>

     <para>
!      To create a quick self-signed certificate for the server, valid for 365
       days, use the following <productname>OpenSSL</productname> command,
!      replacing <replaceable>yourdomain.com</replaceable> with the server's host name:
  <programlisting>
  openssl req -new -x509 -days 365 -nodes -text -out server.crt \
!   -keyout server.key -subj "/CN=<replaceable>yourdomain.com</replaceable>"
  </programlisting>
      Then do:
  <programlisting>
--- 2385,2400 ----
    </sect2>

     <para>
!      To create a simple self-signed certificate for the server, valid for 365
       days, use the following <productname>OpenSSL</productname> command,
!      replacing <replaceable>dbhost.yourdomain.com</replaceable> with the
!      server's host name:
  <programlisting>
  openssl req -new -x509 -days 365 -nodes -text -out server.crt \
!   -keyout server.key -subj "/CN=<replaceable>dbhost.yourdomain.com</replaceable>"
  </programlisting>
      Then do:
  <programlisting>
*************** chmod og-rwx server.key
*** 2406,2419 ****
     </para>
  
     <para>
  
    </sect2>
  
   </sect1>
--- 2407,2491 ----
     </para>
  
     <para>
!     While a self-signed certificate can be used for testing, a certificate
!     signed by a certificate authority (<acronym>CA</acronym>) (usually an
!     enterprise-wide root <acronym>CAs</acronym>) should be used in production.
!    </para>
! 
!    <para>
!     To create a server certificate whose identity can be validated
!     by clients, first create a certificate signing request
!     (<acronym>CSR</acronym>) and a public/private key file:
! <programlisting>
! openssl req -new -nodes -text -out root.csr \
!   -keyout root.key -subj "/CN=<replaceable>root.yourdomain.com</replaceable>"
! chmod og-rwx root.key
! </programlisting>
!     Then, sign the request with the the key to create a root certificate
!     authority:
! <programlisting>
! openssl x509 -req -in root.csr -text -days 3650 \
!   -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
!   -signkey root.key -out root.crt
! </programlisting>
!     Finally, create a server certificate signed by the new root certificate
!     authority:
! <programlisting>
! openssl req -new -nodes -text -out server.csr \
!   -keyout server.key -subj "/CN=<replaceable>dbhost.yourdomain.com</replaceable>"
! chmod og-rwx server.key
! 
! openssl x509 -req -in server.csr -text -days 365 \
!   -CA root.crt -CAkey root.key -CAcreateserial \
!   -out server.crt
! </programlisting>
!     <filename>server.crt</filename> and <filename>server.key</filename>
!     should be stored on the server, and <filename>root.crt</filename> should
!     be stored on the client so the client can verify that the server's leaf
!     certificate was signed by its trusted root certificate. 
!     <filename>root.key</filename> should be stored offline for use in
!     creating future certificates.
     </para>
  
+    <para>
+     It is also possible to create a chain of trust that includes
+     intermediate certificates:
+ <programlisting>
+ # root
+ openssl req -new -nodes -text -out root.csr \
+   -keyout root.key -subj "/CN=<replaceable>root.yourdomain.com</replaceable>"
+ chmod og-rwx root.key
+ openssl x509 -req -in root.csr -text -days 3650 \
+   -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
+   -signkey root.key -out root.crt
+ 
+ # intermediate
+ openssl req -new -nodes -text -out intermediate.csr \
+   -keyout intermediate.key -subj "/CN=<replaceable>intermediate.yourdomain.com</replaceable>"
+ chmod og-rwx intermediate.key
+ openssl x509 -req -in intermediate.csr -text -days 1825 \
+   -extfile /etc/ssl/openssl.cnf -extensions v3_ca \
+   -CA root.crt -CAkey root.key -CAcreateserial \
+   -out intermediate.crt
+ 
+ # leaf
+ openssl req -new -nodes -text -out server.csr \
+   -keyout server.key -subj "/CN=<replaceable>dbhost.yourdomain.com</replaceable>"
+ chmod og-rwx server.key
+ openssl x509 -req -in server.csr -text -days 365 \
+   -CA intermediate.crt -CAkey intermediate.key -CAcreateserial \
+   -out server.crt
+ </programlisting>
+     <filename>server.crt</filename> and
+     <filename>intermediate.crt</filename> should be concatenated
+     into a certificate file bundle and stored on the server.
+     <filename>server.key</filename> should also be stored on the server.
+     <filename>root.crt</filename> should be stored on the client so
+     the client can verify that the server's leaf certificate was signed
+     by a chain of certificates linked to its trusted root certificate.
+     <filename>root.key</filename> and <filename>intermediate.key</filename>
+     should be stored offline for use in creating future certificates.
+    </para>
    </sect2>
  
   </sect1>

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 17 Jan 2018 08:39:55 -0500

> adjustments.  There is also a new paragraph at the end explaining where

Oh, and how far back should these be backpatched?  10?  9.6?  9.3? I am
thinking it should be done as far back as possible as long as it is
simple.

---

From: Michael Paquier <[email-masked]>
Date: Thu, 18 Jan 2018 10:04:11 +0900

> thinking it should be done as far back as possible as long as it is

No objections from here to back-patch depending on the difficulty. I
think that a0572203 has created some conflicts in this area for
REL_10_STABLE, nothing huge though.
--
Michael

---

From: Michael Paquier <[email-masked]>
Date: Thu, 18 Jan 2018 10:25:03 +0900

> adjustments.  There is also a new paragraph at the end explaining where

Thanks, that's a net improvement. So +1 for this version.

+    enterprise-wide root <acronym>CAs</acronym>) should be used in production.
Nit here. CA should not be plural.

+</programlisting>
+    Then, sign the request with the the key to create a root certificate
+    authority:
You still have a "the the" here.

/etc/ssl/openssl.cnf is not available on macos or Windows, which can
lead to a bit of confusion as I would imagine that people would
copy/paste such commands when testing things. Perhaps it would be worth
mentioning that this path is proper to usual Linux distributions (I can
see it at least on ArchLinux and Debian), with a reference to this
OpenSSL link: 
https://www.openssl.org/docs/manmaster/man5/config.html

There is as well a set of tiny configuration files in src/test/ssl.
--
Michael

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 17 Jan 2018 21:00:17 -0500

> There is as well a set of tiny configuration files in src/test/ssl.

One odd thing about the configuration file is that you don't need to
modify it, but you do need to specify it for that command.

Fixed patch attached.

index 4e46451..d38760e
*** 7677,7682 ****
--- 7677,7686 ----
     that CA would also be trusted for server certificates.
    </para>
  
+   <para>
+    For instructions on creating certificates, see <xref
+    linkend="ssl-certificate-creation"/>.
+   </para>
   </sect2>
  
index a2ebd3e..989fcff

     <para>

     <para>
  
     <para>
  
    </sect2>
  
   </sect1>
--- 2407,2492 ----
     </para>
  
     <para>
!     While a self-signed certificate can be used for testing, a certificate
!     signed by a certificate authority (<acronym>CA</acronym>) (usually an
!     enterprise-wide root <acronym>CA</acronym>) should be used in production.
     </para>
  
+    <para>
+     by clients, first create a certificate signing request
+     (<acronym>CSR</acronym>) and a public/private key file:
+     Then, sign the request with the key to create a root certificate
+     authority (using the default <productname>OpenSSL</productname>
+     configuration file location on <productname>Linux</productname>):
+     <filename>root.key</filename> should be stored offline for use in
+     creating future certificates.
  
   </sect1>

---

From: Michael Paquier <[email-masked]>
Date: Thu, 18 Jan 2018 12:17:40 +0900

> modify it, but you do need to specify it for that command.

OK, what you are proposing here looks fine to me. That's an
improvement.
--
Michael

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 20 Jan 2018 21:47:29 -0500

> OK, what you are proposing here looks fine to me. That's an

Applied back through 9.3.  Thanks for the review and suggestions.

---

From: Peter Eisentraut <[email-masked]>
Date: Thu, 25 Jan 2018 22:59:23 -0500

> that. It would be an area of improvement for those tests.

The tests already cover this:

# intermediate client_ca.crt is provided by client, and isn't in
server's ssl_ca_file
switch_server_cert($node, 'server-cn-only', 'root_ca');
$common_connstr =
"user=ssltestuser dbname=certdb sslkey=ssl/client_tmp.key
sslrootcert=ssl/root+server_ca.crt hostaddr=$SERVERHOSTADDR";

test_connect_ok($common_connstr,
    "sslmode=require sslcert=ssl/client+client_ca.crt");
test_connect_fails($common_connstr, "sslmode=require
sslcert=ssl/client.crt");

If you change the Makefile rule for generating the client CA to omit the
-extensions v3_ca option, then the first test will fail.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 26 Jan 2018 08:09:30 -0500

> -extensions v3_ca option, then the first test will fail.

Oh, very good!

---

From: Michael Paquier <[email-masked]>
Date: Fri, 26 Jan 2018 22:28:07 +0900

> > -extensions v3_ca option, then the first test will fail.

Good point, I didn't notice that. Thanks Peter.
--
Michael