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 1ebzVD-0005wb-JX
	for pgsql-docs@arkaria.postgresql.org; Thu, 18 Jan 2018 02:00:27 +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 1ebzVD-0007iS-4p
	for pgsql-docs@arkaria.postgresql.org; Thu, 18 Jan 2018 02:00:27 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	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 1ebzVC-0007iI-SR
	for pgsql-docs@lists.postgresql.org; Thu, 18 Jan 2018 02:00:27 +0000
Received: from momjian.us ([72.94.173.45])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <bruce@momjian.us>)
	id 1ebzV5-00065o-P7
	for pgsql-docs@postgresql.org; Thu, 18 Jan 2018 02:00:25 +0000
Received: from bruce by momjian.us with local (Exim 4.84_2)
	(envelope-from <bruce@momjian.us>)
	id 1ebzV3-0008Cm-VA; Wed, 17 Jan 2018 21:00:17 -0500
Date: Wed, 17 Jan 2018 21:00:17 -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: <20180118020017.GC19651@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>
 <20180117123442.GB26285@momjian.us>
 <20180118012503.GB29962@paquier.xyz>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="FCuugMFkClbJLl1L"
Content-Disposition: inline
In-Reply-To: <20180118012503.GB29962@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


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

On Thu, Jan 18, 2018 at 10:25:03AM +0900, Michael Paquier wrote:
> On Wed, Jan 17, 2018 at 07:34:42AM -0500, Bruce Momjian wrote:
> > 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.
> 
> 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.

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.

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

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

diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
new file mode 100644
index 4e46451..d38760e
*** a/doc/src/sgml/libpq.sgml
--- b/doc/src/sgml/libpq.sgml
*************** ldap://ldap.acme.com/cn=dbserver,cn=host
*** 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>
  
   <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..989fcff
*** 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,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>
+     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 key to create a root certificate
+     authority (using the default <productname>OpenSSL</productname>
+     configuration file location on <productname>Linux</productname>):
+ <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>

--FCuugMFkClbJLl1L--