public inbox for [email protected]help / color / mirror / Atom feed
Correction of intermediate certificate handling 16+ messages / 3 participants [nested] [flat]
* Correction of intermediate certificate handling @ 2018-01-16 00:22 Bruce Momjian <[email protected]> 0 siblings, 1 reply; 16+ messages in thread From: Bruce Momjian @ 2018-01-16 00:22 UTC (permalink / raw) To: pgsql-docs; +Cc: Stephen Frost <[email protected]>; David Steele <[email protected]> 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 protected]> 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 + Attachments: [text/x-diff] crt.diff (8.1K, 2-crt.diff) download | inline diff: 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 ---- ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-16 05:33 Michael Paquier <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 2 replies; 16+ messages in thread From: Michael Paquier @ 2018-01-16 05:33 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> On Mon, Jan 15, 2018 at 07:22:38PM -0500, Bruce Momjian wrote: > 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. 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 Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-16 16:21 Bruce Momjian <[email protected]> parent: Michael Paquier <[email protected]> 1 sibling, 1 reply; 16+ messages in thread From: Bruce Momjian @ 2018-01-16 16:21 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> On Tue, Jan 16, 2018 at 02:33:05PM +0900, Michael Paquier wrote: > > 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. > > I have spent some time looking at your patch, this gets a +1 from here. Thanks. > 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. > 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. > 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. Instead of appending to this doc patch, I will work on a second one for review. -- Bruce Momjian <[email protected]> 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 + ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-17 00:09 Michael Paquier <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 16+ messages in thread From: Michael Paquier @ 2018-01-17 00:09 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> 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. > > 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. > > 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). > Instead of appending to this doc patch, I will work on a second one for > review. 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 Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-17 03:23 Bruce Momjian <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 1 reply; 16+ messages in thread From: Bruce Momjian @ 2018-01-17 03:23 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> 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 <[email protected]> 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 + Attachments: [text/x-diff] crt.diff (12.4K, 2-crt.diff) download | inline 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, ! <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> *************** 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> --- 7621,7633 ---- </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 *** 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> ! 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"> --- 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 — doing so avoids requiring storage of intermediate ! certificates 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 ---- [text/x-diff] openssl.diff (4.3K, 3-openssl.diff) download | inline 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 **** </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> ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-17 08:20 Michael Paquier <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 16+ messages in thread From: Michael Paquier @ 2018-01-17 08:20 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> 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. > > > > 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. 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. > 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 Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-17 12:34 Bruce Momjian <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 2 replies; 16+ messages in thread From: Bruce Momjian @ 2018-01-17 12:34 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> 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 <[email protected]> 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 + Attachments: [text/x-diff] openssl.diff (5.5K, 2-openssl.diff) download | inline 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> ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-17 13:39 Bruce Momjian <[email protected]> parent: Bruce Momjian <[email protected]> 1 sibling, 1 reply; 16+ messages in thread From: Bruce Momjian @ 2018-01-17 13:39 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> On Wed, Jan 17, 2018 at 07:34:42AM -0500, Bruce Momjian wrote: > > 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. 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. -- Bruce Momjian <[email protected]> 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 + ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-18 01:04 Michael Paquier <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 0 replies; 16+ messages in thread From: Michael Paquier @ 2018-01-18 01:04 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> On Wed, Jan 17, 2018 at 08:39:55AM -0500, Bruce Momjian wrote: > On Wed, Jan 17, 2018 at 07:34:42AM -0500, Bruce Momjian wrote: > > > 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. > > 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. 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 Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-18 01:25 Michael Paquier <[email protected]> parent: Bruce Momjian <[email protected]> 1 sibling, 1 reply; 16+ messages in thread From: Michael Paquier @ 2018-01-18 01:25 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> On Wed, Jan 17, 2018 at 07:34:42AM -0500, Bruce Momjian wrote: > On Wed, Jan 17, 2018 at 05:20:00PM +0900, Michael Paquier wrote: > > 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. 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 Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-18 02:00 Bruce Momjian <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 1 reply; 16+ messages in thread From: Bruce Momjian @ 2018-01-18 02:00 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> 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 <[email protected]> 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 + Attachments: [text/x-diff] openssl.diff (6.1K, 2-openssl.diff) download | inline 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> ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-18 03:17 Michael Paquier <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 16+ messages in thread From: Michael Paquier @ 2018-01-18 03:17 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> On Wed, Jan 17, 2018 at 09:00:17PM -0500, Bruce Momjian wrote: > On Thu, Jan 18, 2018 at 10:25:03AM +0900, Michael Paquier wrote: > > /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 > > 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. OK, what you are proposing here looks fine to me. That's an improvement. -- Michael Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-21 02:47 Bruce Momjian <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 0 replies; 16+ messages in thread From: Bruce Momjian @ 2018-01-21 02:47 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> On Thu, Jan 18, 2018 at 12:17:40PM +0900, Michael Paquier wrote: > On Wed, Jan 17, 2018 at 09:00:17PM -0500, Bruce Momjian wrote: > > On Thu, Jan 18, 2018 at 10:25:03AM +0900, Michael Paquier wrote: > > > /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 > > > > 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. > > OK, what you are proposing here looks fine to me. That's an > improvement. Applied back through 9.3. Thanks for the review and suggestions. -- Bruce Momjian <[email protected]> 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 + ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-26 03:59 Peter Eisentraut <[email protected]> parent: Michael Paquier <[email protected]> 1 sibling, 1 reply; 16+ messages in thread From: Peter Eisentraut @ 2018-01-26 03:59 UTC (permalink / raw) To: Michael Paquier <[email protected]>; Bruce Momjian <[email protected]>; +Cc: pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> On 1/16/18 00:33, Michael Paquier wrote: > 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. 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 ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-26 13:09 Bruce Momjian <[email protected]> parent: Peter Eisentraut <[email protected]> 0 siblings, 1 reply; 16+ messages in thread From: Bruce Momjian @ 2018-01-26 13:09 UTC (permalink / raw) To: Peter Eisentraut <[email protected]>; +Cc: Michael Paquier <[email protected]>; pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> On Thu, Jan 25, 2018 at 10:59:23PM -0500, Peter Eisentraut wrote: > On 1/16/18 00:33, Michael Paquier wrote: > > 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. > > 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. Oh, very good! -- Bruce Momjian <[email protected]> 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 + ^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Correction of intermediate certificate handling @ 2018-01-26 13:28 Michael Paquier <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 0 replies; 16+ messages in thread From: Michael Paquier @ 2018-01-26 13:28 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; pgsql-docs; Stephen Frost <[email protected]>; David Steele <[email protected]> On Fri, Jan 26, 2018 at 08:09:30AM -0500, Bruce Momjian wrote: > On Thu, Jan 25, 2018 at 10:59:23PM -0500, Peter Eisentraut wrote: > > If you change the Makefile rule for generating the client CA to omit the > > -extensions v3_ca option, then the first test will fail. > > Oh, very good! Good point, I didn't notice that. Thanks Peter. -- Michael Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2018-01-26 13:28 UTC | newest] Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-01-16 00:22 Correction of intermediate certificate handling Bruce Momjian <[email protected]> 2018-01-16 05:33 ` Michael Paquier <[email protected]> 2018-01-16 16:21 ` Bruce Momjian <[email protected]> 2018-01-17 00:09 ` Michael Paquier <[email protected]> 2018-01-17 03:23 ` Bruce Momjian <[email protected]> 2018-01-17 08:20 ` Michael Paquier <[email protected]> 2018-01-17 12:34 ` Bruce Momjian <[email protected]> 2018-01-17 13:39 ` Bruce Momjian <[email protected]> 2018-01-18 01:04 ` Michael Paquier <[email protected]> 2018-01-18 01:25 ` Michael Paquier <[email protected]> 2018-01-18 02:00 ` Bruce Momjian <[email protected]> 2018-01-18 03:17 ` Michael Paquier <[email protected]> 2018-01-21 02:47 ` Bruce Momjian <[email protected]> 2018-01-26 03:59 ` Peter Eisentraut <[email protected]> 2018-01-26 13:09 ` Bruce Momjian <[email protected]> 2018-01-26 13:28 ` Michael Paquier <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox