Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1ebmvb-00080f-Mw
	for pgsql-docs@arkaria.postgresql.org; Wed, 17 Jan 2018 12:34:51 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1ebmva-0006Km-25
	for pgsql-docs@arkaria.postgresql.org; Wed, 17 Jan 2018 12:34:50 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <bruce@momjian.us>)
	id 1ebmvZ-0006Kc-Si
	for pgsql-docs@lists.postgresql.org; Wed, 17 Jan 2018 12:34:49 +0000
Received: from momjian.us ([72.94.173.45])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <bruce@momjian.us>)
	id 1ebmvV-0005bl-NR
	for pgsql-docs@postgresql.org; Wed, 17 Jan 2018 12:34:49 +0000
Received: from bruce by momjian.us with local (Exim 4.84_2)
	(envelope-from <bruce@momjian.us>)
	id 1ebmvS-0004gt-Jx; Wed, 17 Jan 2018 07:34:42 -0500
Date: Wed, 17 Jan 2018 07:34:42 -0500
From: Bruce Momjian <bruce@momjian.us>
To: Michael Paquier <michael.paquier@gmail.com>
Cc: PostgreSQL-documentation <pgsql-docs@postgresql.org>,
	Stephen Frost <sfrost@snowman.net>,
	David Steele <david@pgmasters.net>
Subject: Re: Correction of intermediate certificate handling
Message-ID: <20180117123442.GB26285@momjian.us>
References: <20180116002238.GC12724@momjian.us>
 <20180116053305.GB2212@paquier.xyz>
 <20180116162122.GB1470@momjian.us>
 <20180117000950.GB935@paquier.xyz>
 <20180117032344.GA26285@momjian.us>
 <20180117082000.GA13673@paquier.xyz>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="UHN/qo2QbUvPLonB"
Content-Disposition: inline
In-Reply-To: <20180117082000.GA13673@paquier.xyz>
User-Agent: Mutt/1.5.23 (2014-03-12)
List-Id: <pgsql-docs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-docs@lists.postgresql.org>
List-Owner: <mailto:pgsql-docs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-docs>
Precedence: bulk


--UHN/qo2QbUvPLonB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Wed, Jan 17, 2018 at 05:20:00PM +0900, Michael Paquier wrote:
> On Tue, Jan 16, 2018 at 10:23:44PM -0500, Bruce Momjian wrote:
> > On Wed, Jan 17, 2018 at 09:09:50AM +0900, Michael Paquier wrote:
> > > On Tue, Jan 16, 2018 at 11:21:22AM -0500, Bruce Momjian wrote:
> > > > On Tue, Jan 16, 2018 at 02:33:05PM +0900, Michael Paquier wrote:
> > 
> > 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.
> 
> Thanks. I looked at crt.diff and the surroundings in the docs. This one
> looks consistent to me.

Good, thanks.

> > I did that as a separate patch, which is the second attachment.
> 
> This is openssl.diff.
> 
> +    Then, sign the request with the the private key to create a root
> +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.

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.

> > 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.

Agreed.

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

--UHN/qo2QbUvPLonB
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="openssl.diff"

diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
new file mode 100644
index a2ebd3e..7f83d0c
*** a/doc/src/sgml/runtime.sgml
--- b/doc/src/sgml/runtime.sgml
*************** pg_dumpall -p 5432 | psql -d postgres -p
*** 2385,2399 ****
    </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>
  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>
  
    <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>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>
!     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>
--- 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>

--UHN/qo2QbUvPLonB--