Having covered the choice of certificate authorities, I want to explain the internals of creating server certificates in Postgres. The instructions are already in the Postgres documentation.
When using these instructions for creating a certificate signing request (csr), two files are created:
-- certificate signing request file with extension req
-- key file, containing public and private server keys, with extension pem
(It is also possible to use an existing key file.) You can view the contents of the csr using openssl, e.g.:
$ openssl req -in server.req -text
Certificate Request:
Data:
Version: 0 (0x0)
Subject: … CN=momjian.us …
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
Public-Key: (2048 bit)
Modulus:
00:b2:ea:53:a6:5e:0a:bd:dd:59:08:52:0d:bb:77:
…
Exponent: 65537 (0x10001)
Attributes:
a0:00
Signature Algorithm: sha256WithRSAEncryption
…
-----BEGIN CERTIFICATE REQUEST-----
…
-----END CERTIFICATE REQUEST-----
The first argument to the openssl command-line tool always starts with a subcommand, in this case req. (The openssl manual pages are split based on the subcommand, so man req shows openssl arguments for the subcommand req.)
The common name (cn) shown above is potentially checked by the client. The encoded section ("certificate request") can be suppressed with -noout. The file server.req already contains this information in text format because -text was specified during its creation.
The server public and private keys stored in privkey.pem can also be viewed:
$ openssl pkey -in privkey.pem -text
-----BEGIN PRIVATE KEY-----
MIIEvwIBADANBgkqhkiG9w0BAQEFAASCBKkwggSlAgEAAoIBAQCy6lOmXgq93VkI
…
-----END PRIVATE KEY-----
Private-Key: (2048 bit)
modulus:
00:b2:ea:53:a6:5e:0a:bd:dd:59:08:52:0d:bb:77:
…
-----END PRIVATE KEY-----
Private-Key: (2048 bit)
modulus:
00:a3:ad:e4:61:2b:5f:c0:5b:23:fc:8c:b4:ab:70:
…
publicExponent: 65537 (0x10001)
…
privateExponent:
…
prime1:
…
prime2:
…
exponent1:
…
exponent2:
…
coefficient:
…
All of this can be very complex so I have created a diagram which illustrates what is happening. At the top-left is the server key generated by openssl req. This command also creates the certificate signing request (the csr, top-right) which contains:
- Certificate signing information in X.509 format, e.g. common name (cn)
- Public server key to be used by clients wishing to authenticate the server's identity
- The above signed by the server's private key to prove that the server owner supplied this information
The certificate signing request (csr) can't be used as a certificate because it is unsigned. For self-signed certificates, the server's private key is used to sign the csr. (These are the instructions given in the Postgres documentation.) A more powerful option, as I mentioned in my previous blog post, is to use a local certificate authority. In my diagram, a certificate authority (bottom-left) uses its private key to sign the csr and create a certificate (bottom-right).
Once the server has a certificate, any client who has the signer of the server's certificate in their ~/.postgresql/root.crt file can authenticate the server certificate by using the sslmode connection parameter verify-ca. They can also verify that the certificate was created for the specific server's host name by checking the common name (cn) using verify-full. (Clients can record revoked certificates in ~/.postgresql/root.crl.)
Hopefully you can make better use of ssl server certificates now that you understand how they are created, inspected, and verified.
Bruce Momjian is Senior Database Architect at EnterpriseDB.
This post originally appeared on Bruce's personal blog.