Re: Correction of intermediate certificate handling
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/ + +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 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 signature.asc Description: PGP signature
Re: Correction of intermediate certificate handling
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/ > > + > +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 > 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 http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + diff --git a/doc/src/sgml/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 !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: openssl req -new -x509 -days 365 -nodes -text -out server.crt \ ! -keyout server.key -subj "/CN=yourdomain.com" Then do: --- 2385,2400 !Creating Certificates ! To create a simple self-signed certificate for the server, valid for 365 days, use the following OpenSSL command, ! replacing dbhost.yourdomain.com with the ! server's host name: openssl req -new -x509 -days 365 -nodes -text -out server.crt \ ! -keyout server.key -subj "/CN=dbhost.yourdomain.com" Then do: *** 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. --- 2407,2491 ! 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 certificate signing request ! (CSR) and a public/private key file: ! ! 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 key to create a root certificate ! authority: ! ! openssl x509 -req -in root.csr -text -days 3650 \ ! -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. ! root.key should be stored offline for use in !
Re: Correction of intermediate certificate handling
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 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 +
removal of md5 from example code
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/citext.html Description: The documentation at https://www.postgresql.org/docs/current/static/citext.html shows an example using md5 for password hashes. This is generally a bad practice and not relevant to the feature documented. I recommend removing the password column from this example or replacing the md5 hash with something more secure (a secure hash algorithm with a salt).
pg_timezone_names
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.1/static/view-pg-timezone-names.html Description: We are exporting data from postgres into another "all in one" data store, and i had a quick question regarding the time offsets. The UTC_OFFSET, does it change when IS_DST is true/false? Or do I need to take the UTC_OFFSET interval and then possibly add 1 more if IS_DST is true? Example: 1/1/2018 not in DST, interval says -10. On 3/1/2018, IS_DST turns to TRUE. Will the interval now say -9, or do i need to take the -10 and substract 1 to get to -9?
Re: pg_timezone_names
=?utf-8?q?PG_Doc_comments_form?= writes: > We are exporting data from postgres into another "all in one" data store, > and i had a quick question regarding the time offsets. The UTC_OFFSET, does > it change when IS_DST is true/false? Yes; utc_offset reflects the zone's current offset from UTC, so it changes with the seasons if the zone has DST laws. > Or do I need to take the UTC_OFFSET > interval and then possibly add 1 more if IS_DST is true? Prepare to be disappointed; you can't assume it works like that. See the thread starting here: http://mm.icann.org/pipermail/tz/2017-December/025682.html You haven't said what it is you are hoping to accomplish, so it's hard to say if there's some safer way to do it. regards, tom lane
Re: Correction of intermediate certificate handling
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 signature.asc Description: PGP signature
Re: Correction of intermediate certificate handling
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 CAs) should be used in production. Nit here. CA should not be plural. + +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 signature.asc Description: PGP signature
Re: Correction of intermediate certificate handling
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 CAs) should be used in > production. > Nit here. CA should not be plural. > > + > +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 http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml new file mode 100644 index 4e46451..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. + +For instructions on creating certificates, see . + 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 !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: openssl req -new -x509 -days 365 -nodes -text -out server.crt \ ! -keyout server.key -subj "/CN=yourdomain.com" Then do: --- 2385,2400 !Creating Certificates ! To create a simple self-signed certificate for the server, valid for 365 days, use the following OpenSSL command, ! replacing dbhost.yourdomain.com with the ! server's host name: openssl req -new -x509 -days 365 -nodes -text -out server.crt \ ! -keyout server.key -subj "/CN=dbhost.yourdomain.com" Then do: *** 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. --- 2407,2492 ! While a self-signed certificate can be used for testing, a certificate ! signed by a certificate authority (CA) (usually an ! enterprise-wide root CA) should be used in production. + + To create a server certificate whose identity can be validated + by clients, first create a certificate signing request + (CSR) and a public/private key file: + + 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 key to create a root certificate + authority (using the default OpenSSL + configuration file location on Linux): + + openssl x509 -req -in root.csr -text -days 3650 \ + -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.
Re: Correction of intermediate certificate handling
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 signature.asc Description: PGP signature
