Creating the keystore for a database server

About this task

As SSL server, a database server needs to own a private key and the corresponding certificate. Both items should be stored in a PKCS #12 keystore file. To avoid the disadvantages of using self-signed certificates (as explained in the topic "Concepts of Keys, Certificates and Keystores for TLS"), the database server should use certificates issued by a CA. However, requesting and receiving certificates from an established public CA may be a slow process and usually even costs real money. The compromise between these two possibilities is to use one's own CA - a CA that was set up as shown in the previous example.

Procedure

Create a RSA private key for the database server in a PEM file:
```
$ openssl genrsa -out server1.key.pem
```
The command creates the private key with the default size of 2048 bits and writes it in PEM format to the output file "server1.key.pem". The PEM file can be parsed with a command like:
```
openssl rsa -in server1.key.pem -text -noout
```
Note: The private key in the PEM file is not encrypted.
Use the server's private key to create a certificate request:
```
$ mydn="/C=US/ST=Florida/L=Anytown/O=Acme Software Inc./OU=DB Servers/"
$ mydn=${mydn}"CN=DB Server 1/emailAddress=db1@acme.info"
$ openssl req -new -key server1.key.pem \
> -subj "${mydn}" -out server1.req.pem
```
The command "openssl req" uses the previously generated private key to create a new certificate request and writes it to the output file "server1.req.pem". The option "-subj ..." specifies the subject name of this database server. For convenience, the longish subject name is given as the variable "${mydn}", defined by the previous two shell commands. This subject name is a distinguished name that consists of several fields like country, state, location, organization, organizational unit, common name, etc. The fields in the string are separated by a '/'.
The PEM file with the certificate request can be parsed with the following command:
```
$ openssl req -in server1.req.pem -text -noout
Certificate Request:
    Data:  
        Version: 1 (0x0)
        Subject: C = US, ST = Florida, L = Anytown, O = Acme Software Inc.,
                 OU = DB Servers, CN = DB Server 1,
                 emailAddress = db1@acme.info
        Subject Public Key Info:
           Public Key Algorithm: rsaEncryption
               RSA Public-Key: (2048 bit)
               Modulus: ...
               Exponent: 65537 (0x10001)
        Attributes:
            a0:00
    Signature Algorithm: sha256WithRSAEncryption
         ...
$
```
Note: The Signature Algorithm is "sha256WithRSAEncryption". A signature algorithm of lower quality, like "sha1WithRSAEncryption", may not be accepted by newer versions of crypto libraries. With "openssl", "sha256WithRSAEncryption" normally is the default configuration. If for some reason the "openssl" command created a certificate request with a lower quality signature algorithm, it may be a good idea to override the default configuration by adding the option "-sha256" to the command.
The PEM file with this certificate request now must be passed to the CA, so that the CA, based on the request, can issue and sign the user certificate.
As CA, use the certificate request to issue and sign the user certificate for the database server:
```
$ openssl x509 -req -inform PEM -in server1.req.pem -set_serial 5 \
> -CA rootCA1.cert.pem -CAkey rootCA1.key.pem -passin pass:ca1passwd \
> -days 365 -outform PEM -out server1.cert.pem
```
The command reads the input certificate request file "server1.req.pem" (as specified in PEM format) and writes the signed certificate to the output file "server1.cert.pem" (also in the specified PEM format). The certificate is valid for 1 year beginning with the creation time.

This command must be performed as the CA, because it needs the CA's root CA certificate as well as the CA's private key. Both are read from PEM files as specified by the options "-CA rootCA1.cert.pem -CAkey rootCA1.key.pem". Because the CA's private key in file "rootCA1.key.pem" is encrypted, it is also necessary that the CA running this command provides the password for the input PEM file with the CA's private key. When creating the CA's private key, the password specified for this PEM file was "ca1passwd".

Note: For compatibility reasons with different versions of different crypto libraries, it may be necessary to add the option "-extensions usr_cert" to the command. This option refers to section "[usr_cert]" in the OpenSSL configuration file. There, it is stated:
# These extensions are added when 'ca' signs a request.
# This goes against PKIX guidelines but some CAs do it and some software
# requires this to avoid interpreting an end user certificate as a CA.

(With OpenSSL version 1.1.1, used to test the example commands, this option does not make a difference for the issued certificate.)

The CA then hands the newly issued user certificate as file "server1.cert.pem" back to the requester, i.e. to the database server. For completeness, the CA also hands over a copy of its own CA certificate in PEM format, i.e. file "rootCA1.cert.pem". (While with OpenSSL the database server does not need the CA certificate that signed its own user certificate, this may be required by some other security libraries. Therefore the CA generally should provide a copy together with the user certificate.)
Create the keystore for the database server:
The keystore for the database server must contain the database server's private key and the corresponding user certificate. The user certificate is the one just received from the CA. Both items are in PEM files, "server1.key.pem" and "server1.cert.pem", that can be readily used to create the keystore with the following command. The inclusion of the CA certificate in the keystore is optional for OpenSSL, but may be required by some other security library. Therefore the following command also uses file "rootCA1.cert.pem", so that the keystore is more compatible.
```
$ openssl pkcs12 -export -in server1.cert.pem -inkey server1.key.pem \
> -name server1 -certfile rootCA1.cert.pem -caname rootCA1 \
> -passout pass:s1passwd -out server1.p12
```
The option "-export" causes the command to export the content read from the three input files into a PKCS #12 format keystore. The output file is "server1.p12" and the keystore is protected with password "s1passwd". The option "-name server1" tells the command to use "server1" as value for the friendly name attribute of the Safebags with the database server's private key and the user certificate. The option "-caname rootCA1" provides the friendly name attribute value "rootCA1" for the SafeBag with the CA certificate.
The content of the new keystore in file "server1.p12" can be parsed and examined with the following command:
```
$ openssl pkcs12 -in server1.p12 -passin pass:s1passwd -nodes -info
MAC: sha1, Iteration 2048
MAC length: 20, salt length: 8
PKCS7 Encrypted data: pbeWithSHA1And40BitRC2-CBC, Iteration 2048
Certificate bag
Certificate bag
PKCS7 Data
Shrouded Keybag: pbeWithSHA1And3-KeyTripleDES-CBC, Iteration 2048
Bag Attributes
    friendlyName: server1
    localKeyID: B8 D8 27 ED 1C FF 82 5F 50 42 AA 5E 5D FF E8 A2 95 C4 17 A5
subject=C = US, ST = Florida, L = Anytown, O = Acme Software Inc.,
        OU = DB Servers, CN = DB Server 1, emailAddress = db1@acme.info
issuer=C = US, ST = Florida, L = Anytown, O = Acme Software Inc.,
       OU = Database CA, CN = Database CA Root1,
       emailAddress = dba_ca1@acme.info
-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----
Bag Attributes
    friendlyName: rootCA1
subject=C = US, ST = Florida, L = Anytown, O = Acme Software Inc.,
        OU = Database CA, CN = Database CA Root1,
        emailAddress = dba_ca1@acme.info
issuer=C = US, ST = Florida, L = Anytown, O = Acme Software Inc.,
       OU = Database CA, CN = Database CA Root1,
       emailAddress = dba_ca1@acme.info
-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----
Bag Attributes
    friendlyName: server1
    localKeyID: B8 D8 27 ED 1C FF 82 5F 50 42 AA 5E 5D FF E8 A2 95 C4 17 A5
Key Attributes:
-----BEGIN PRIVATE KEY-----
...
-----END PRIVATE KEY-----
```
Note:
- The output of the above command shows that there are two certificates and one private key in the keystore. The private key is encrypted with PBE (password based encryption) and stored in a "shrouded keybag". The friendlyName and the localKeyID attribute values of the SafeBags for the private key and the corresponding user certificate are matching. The SafeBag with CA certificate has different values for those attributes.
- It is possible to combine all three input PEM files into a single PEM file, e.g. with a simple "cat" command, and then for the "openssl pkcs12 -export" command only provide the name of the single file with the "-in ..." option, omitting the options "-inkey ..." and "-certfile ...". Still, the "-name ..." and "-caname ..." options are always needed, as otherwise the private key or one of the certificates may be stored without a friendlyName attribute and later not be accepted for use in the TLS handshake.
- For a private key, the corresponding user certificate must be contained in the input. Furthermore, only one private key with corresponding user certificate should be present. A keystore normally does not contain multiple pairs of private key and corresponding user certificate.
- If additional certificates are present, then they are considered CA certificates and also stored in the keystore. For each CA certificate in the input, an option "-caname ..." with an individual value must be provided. Multiple "-caname ..." option values are applied for the additional certificates in the order they appear on the command line.
- Each certificate can be stored only once in the keystore, with a distinct friendlyName attribute value. Duplicate certificates in the input therefore should be eliminated beforehand. Duplicate certificates in a keystore are considered an error.
Remove the PEM file with the database server's private key:

The database server's private key now is stored with password protection in the PKCS #12 keystore. But it is still unprotected in the PEM file "server1.key.pem". To prevent misuse of this private key, the PEM file should be removed, or otherwise specially protected. Alternatively, the private key PEM file can be created with password protection right away. See the step "Create a RSA private key in a PEM file:" in the previous example for "Setting up a CA with OpenSSL" where this was done for the CA's private key. If the private key PEM file is password protected, then the password must be supplied for every read access to the private key. I.e. option "-passin pass:..." would be needed for the commands when creating the certificate request and when creating the keystore.