Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ebeKN-0003fS-KZ for pgsql-docs@arkaria.postgresql.org; Wed, 17 Jan 2018 03:23: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 ) id 1ebeKN-00072B-54 for pgsql-docs@arkaria.postgresql.org; Wed, 17 Jan 2018 03:23:51 +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 ) id 1ebeKM-00071y-Lj for pgsql-docs@lists.postgresql.org; Wed, 17 Jan 2018 03:23:50 +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 ) id 1ebeKI-0001tw-7G for pgsql-docs@postgresql.org; Wed, 17 Jan 2018 03:23:49 +0000 Received: from bruce by momjian.us with local (Exim 4.84_2) (envelope-from ) id 1ebeKG-0007fB-5C; Tue, 16 Jan 2018 22:23:44 -0500 Date: Tue, 16 Jan 2018 22:23:44 -0500 From: Bruce Momjian To: Michael Paquier Cc: PostgreSQL-documentation , Stephen Frost , David Steele Subject: Re: Correction of intermediate certificate handling Message-ID: <20180117032344.GA26285@momjian.us> References: <20180116002238.GC12724@momjian.us> <20180116053305.GB2212@paquier.xyz> <20180116162122.GB1470@momjian.us> <20180117000950.GB935@paquier.xyz> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="envbJBWh7q8WU6mo" Content-Disposition: inline In-Reply-To: <20180117000950.GB935@paquier.xyz> User-Agent: Mutt/1.5.23 (2014-03-12) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --envbJBWh7q8WU6mo Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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: > > > 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"? > > > > 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. > > 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. > > > Perhaps the docs could also include an example of command to create a > > > 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 too. I did that as a separate patch, which is the second attachment. > > > 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. > > > > Wow, I have no idea how to do that. Let me look. Seems I have more > > work to do. > > 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). I don't think I will work on the testing changes. -- Bruce Momjian 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 + --envbJBWh7q8WU6mo Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="crt.diff" 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, ! SSL certificate verification must be used. If the parameter sslmode is set to verify-ca, libpq will verify that the server is trustworthy by checking the ! certificate chain up to a trusted certificate authority ! (CA). If sslmode is set to verify-full, ! libpq will also verify that the server host name matches its ! certificate. The SSL connection will fail if the server certificate cannot ! be verified. verify-full is recommended in most security-sensitive environments. --- 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 (CA) certificate on one ! computer and a leaf certificate signed by the ! root certificate on another computer. It is also possible to use an ! intermediate certificate which is signed by the root ! certificate and signs leaf certificates. ! ! ! ! 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. + 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 sslmode is set to verify-ca, libpq will verify that the server is trustworthy by checking the ! certificate chain up to the root certificate stored on the client. ! If sslmode is set to verify-full, ! libpq will also 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. verify-full is recommended in most security-sensitive environments. *************** ldap://ldap.acme.com/cn=dbserver,cn=host *** 7601,7613 **** ! To allow server certificate verification, the certificate(s) of one or more ! trusted CAs must be ! placed in the file ~/.postgresql/root.crt in the user's home ! directory. If intermediate CAs appear in ! root.crt, the file must also contain certificate ! chains to their root CAs. (On Microsoft Windows the file is named ! %APPDATA%\postgresql\root.crt.) --- 7621,7633 ---- ! To allow server certificate verification, one or more root certificates ! must be placed in the file ~/.postgresql/root.crt ! in the user's home directory. (On Microsoft Windows the file is named ! %APPDATA%\postgresql\root.crt.) 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. *************** ldap://ldap.acme.com/cn=dbserver,cn=host *** 7641,7651 **** Client Certificates ! If the server requests a trusted client certificate, ! libpq will send the certificate stored in file ~/.postgresql/postgresql.crt in the user's home ! directory. The certificate must be signed by one of the certificate ! authorities (CA) trusted by the server. A matching private key file ~/.postgresql/postgresql.key must also be present. The private key file must not allow any access to world or group; achieve this by the --- 7661,7672 ---- Client Certificates ! If the server attempts to verify the identity of the ! client by requesting the client's leaf certificate, ! libpq will send the certificates stored in file ~/.postgresql/postgresql.crt in the user's home ! directory. The certificates must chain to the root certificate trusted ! by the server. A matching private key file ~/.postgresql/postgresql.key 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 **** ! In some cases, the client certificate might be signed by an ! intermediate 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 postgresql.crt ! file, then its parent authority's certificate, and so on up to a certificate ! authority, root or intermediate, that is trusted by ! the server, i.e. signed by a certificate in the server's root CA file ! (). ! ! ! ! Note that the client's ~/.postgresql/root.crt 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. - --- 7681,7692 ---- ! The first certificate in postgresql.crt must be the ! client's certificate because it must match the client's private key. ! Intermediate certificates can be optionally appended ! to the file — doing so avoids requiring storage of intermediate ! certificates on the server (). 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 **** ! In some cases, the server certificate might be signed by an ! intermediate 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 server.crt file, ! then its parent authority's certificate, and so on up to a certificate ! authority, root or intermediate, that is trusted by ! clients, i.e. signed by a certificate in the clients' ! root.crt files. Using Client Certificates ! To require the client to supply a trusted certificate, place ! certificates of the certificate authorities (CAs) ! you trust in a file named root.crt in the data directory, set the parameter in ! postgresql.conf to root.crt, ! and add the authentication option clientcert=1 to the ! appropriate hostssl line(s) in pg_hba.conf. ! A certificate will then be requested from the client during ! SSL connection startup. (See 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. ! If intermediate CAs appear in ! root.crt, the file must also contain certificate ! chains to their root CAs. Certificate Revocation List ! (CRL) entries ! are also checked if the parameter is set. (See --- 2247,2292 ---- ! The first certificate in server.crt must be the ! server's certificate because it must match the server's private key. ! The certificates of intermediate 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 v3_ca ! extensions. This allows easier expiration of intermediate certificates. ! ! ! ! It is not necessary to add the root certificate to ! server.crt. Instead, clients must have the root ! certificate of the server's certificate chain. Using Client Certificates ! To require the client to supply a trusted certificate, ! place certificates of the root certificate authorities ! (CAs) you trust in a file in the data directory, set the parameter in ! postgresql.conf to the new file name, and add the ! authentication option clientcert=1 to the appropriate ! hostssl line(s) in pg_hba.conf. ! A certificate will then be requested from the client during SSL ! connection startup. (See 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. ! Intermediate certificates that chain up to existing root certificates ! can also appear in the file if ! you wish to avoid storing them on clients (assuming the root and ! intermediate certificates were created with v3_ca ! extensions). Certificate Revocation List (CRL) entries are also ! checked if the parameter is set. (See *************** pg_dumpall -p 5432 | psql -d postgres -p *** 2297,2310 **** - Note that the server's root.crt 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. - - - If you are setting up client certificates, you may wish to use the cert authentication method, so that the certificates control user authentication as well as providing connection security. --- 2303,2308 ---- --envbJBWh7q8WU6mo 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..dca113b *** a/doc/src/sgml/runtime.sgml --- b/doc/src/sgml/runtime.sgml *************** pg_dumpall -p 5432 | psql -d postgres -p *** 2385,2394 **** ! Creating a Self-signed Certificate ! To create a quick self-signed certificate for the server, valid for 365 days, use the following OpenSSL command, replacing yourdomain.com with the server's host name: --- 2385,2394 ---- ! Creating Certificates ! To create a simple self-signed certificate for the server, valid for 365 days, use the following OpenSSL command, replacing yourdomain.com with the server's host name: *************** chmod og-rwx server.key *** 2406,2419 **** ! A self-signed certificate can be used for testing, but a certificate ! signed by a certificate authority (CA) (either one of the ! global CAs 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 CA is ! recommended. --- 2406,2476 ---- ! While a self-signed certificate can be used for testing, a certificate ! signed by a certificate authority (CA) (usually an ! enterprise-wide root CAs) should be used in production. + + To create a server certificate whose identity can be validated + by clients, first create a public/private key pair and certificate + signing request (CSR): + + openssl req -new -nodes -text -out root.csr \ + -keyout root.key -subj "/CN=root.yourdomain.com" + chmod og-rwx root.key + + Then, sign the request with the the private key to create a root + certificate authority: + + openssl x509 -req -in root.csr -text -days 365 \ + -extfile /etc/ssl/openssl.cnf -extensions v3_ca \ + -signkey root.key -out root.crt + + Finally, create a server certificate signed by the new root certificate + authority: + + openssl req -new -nodes -text -out server.csr \ + -keyout server.key -subj "/CN=dbhost.yourdomain.com" + chmod og-rwx server.key + + openssl x509 -req -in server.csr -text -days 365 \ + -CA root.crt -CAkey root.key -CAcreateserial \ + -out server.crt + + server.crt and server.key + should be stored on the server, and root.crt should + be stored on the client so the client can verify that the server's leaf + certificate was signed by its trusted root certificate. + + + + It is also possible to create a chain of trust that includes + intermediate certificates: + + openssl req -new -nodes -text -out root.csr \ + -keyout root.key -subj "/CN=root.yourdomain.com" + 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=intermediate.yourdomain.com" + 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=dbhost.yourdomain.com" + chmod og-rwx server.key + openssl x509 -req -in server.csr -text -days 365 \ + -CA intermediate.crt -CAkey intermediate.key -CAcreateserial \ + -out server.crt + + --envbJBWh7q8WU6mo--