SSL Certificate Generation and Server Configuration

Follow the steps to generate a certificate:

1. Establish a CA environment- Assume that user omm has been created and the CA path is test.

   Log in to SUSE Linux as user root and switch to user omm.

   Execute the following command:

   mkdir test
   cd /etc/ssl

   Copy the configuration file openssl. cnf to test.

   Command:

   cp openssl.cnf ~/test
   cd ~/test

   Establish the CA environment under the test folder.

   Create folder in demoCA./demoCA/newcerts./demoCA/private path.

   Command:

   mkdir ./demoCA ./demoCA/newcerts ./demoCA/private
   chmod 777 ./demoCA/private

   Create the serial file and write it to 01.

   Command:

   echo '01'>./demoCA/serial

   Create the index.txt file.

   Command:

   touch /home/omm/test/demoCA/index.txt

   Modify parameters in the openssl.cnf configuration file.

   Command:

   dir = /home/omm/test/demoCA
   default_md = sha256

   The CA environment has been established.

2. Generate a root private key - Generate a CA private key.

   Command:

   openssl genrsa -aes256 -out demoCA/private/cakey.pem 2048

   A 2048-bit RSA private key is generated.

3. Generate a root certificate request file- CA root certificate application file named server.req.

   Command:

   openssl req -config openssl.cnf -new -key demoCA/private/cakey.pem -out demoCA/careq.pem

   Enter pass phrase for demoCA/private/cakey.pem

   Enter the root private key password Gauss@MppDB.

   You are about to be asked to enter information that will be incorporated into your certificate request.

   What you are about to enter is what is called a Distinguished Name or a DN.

   There are quite a few fields but you can leave some blank.

   For some fields there will be a default value, enter a period (.) to leave the field blank. Enter the following information in the generated server certificate and client certificate.

   Country Name (2 letter code) [AU]:CN
   State or Province Name (full name) [Some-State]:shanxi
   Locality Name (eg, city) []:xian
   Organization Name (eg, company) [Internet Widgits Pty Ltd]:Abc
   Organizational Unit Name (eg, section) []:hello
   -Common name can be any name
   Common Name (eg, YOUR name) []:world
   -Email is optional.
   Email Address []:
   A challenge password []:
   An optional company name []:

4. Generate a self-signed root certificate.

   Command:

   openssl ca -config openssl.cnf -out demoCA/cacert.pem -keyfile demoCA/private/cakey.pem -selfsign -infiles demoCA/careq.pem

   Configurations in openssl.cnf are used.

   Enter pass phrase for demoCA/private/cakey.pem.

   Enter the root private key password Gauss@MppDB.

   Check that the request matches the signature.

   Signature ok
   Certificate Details:
   Serial Number: 1 (0x1)
   Validity
   Not Before: Feb 28 02:17:11 2017 GMT
   Not After : Feb 28 02:17:11 2018 GMT
   Subject:
   countryName = CN
   stateOrProvinceName = shanxi
   organizationName = Abc
   organizationalUnitName = hello
   commonName = world
   X509v3 extensions:
   X509v3 Basic Constraints:
   CA:FALSE
   Netscape Comment:
   OpenSSL Generated Certificate
   X509v3 Subject Key Identifier:
   F9:91:50:B2:42:8C:A8:D3:41:B0:E4:42:CB:C2:BE:8D:B7:8C:17:1F
   X509v3 Authority Key Identifier:
   keyid:F9:91:50:B2:42:8C:A8:D3:41:B0:E4:42:CB:C2:BE:8D:B7:8C:17:1F
   Certificate is to be certified until Feb 28 02:17:11 2018 GMT (365 days)
   Sign the certificate? [y/n]:y
   1 out of 1 certificate requests certified, commit? [y/n]y
   Write out database with 1 new entries
   Data Base Updated

   A CA root certificate named demoCA/cacert.pem has been issued.

5. Generate a private key for the server certificate- Generate a private key file named server.key.

   Command:

   openssl genrsa -aes256 -out server.key 2048

6. Generate a server certificate request file- Generate a server certificate request file server.req.

   Command:

   openssl req -config openssl.cnf -new -key server.key -out server.req

   Enter pass phrase for server.key:

   You are about to be asked to enter information that will be incorporated into your certificate request.

   What you are about to enter is what is called a Distinguished Name or a DN.

   There are quite a few fields but you can leave some blank.

   For some fields there will be a default value. If you enter a period (.), the field will be left blank.

   Set the following information and make sure that it is same as that when CA is created.

   Country Name (2 letter code) [AU]:CN
   State or Province Name (full name) [Some-State]:shanxi
   Locality Name (eg, city) []:xian
   Organization Name (eg, company) [Internet Widgits Pty Ltd]:Abc
   Organizational Unit Name (eg, section) []:hello
   -Common name can be any name
   Common Name (eg, YOUR name) []:world
   Email Address []:
   -- The following information is optional.
   A challenge password []:
   An optional company name []:

7. Generate a server certificate - Change the demoCA/index.txt.attr attribute to no.

   vi demoCA/index.txt.attr

   Issue the generated server certificate request file. After it is issued, an official server certificate server.crt is generated.

   openssl ca -config openssl.cnf -in server.req -out server.crt -days 3650 -md sha256

   Configurations in /etc/ssl/openssl.cnf are used.

   Enter pass phrase for ./demoCA/private/cakey.pem.

   Check whether the request matches the signature.

   Signature ok
   Certificate Details:
   Serial Number: 2 (0x2)
   Validity
   Not Before: Feb 27 10:11:12 2017 GMT
   Not After : Feb 25 10:11:12 2027 GMT
   Subject:
   countryName = CN
   stateOrProvinceName = shanxi
   organizationName = Abc
   organizationalUnitName = hello
   commonName = world
   X509v3 extensions:
   X509v3 Basic Constraints:
   CA:FALSE
   Netscape Comment:
   OpenSSL Generated Certificate
   X509v3 Subject Key Identifier:
   EB:D9:EE:C0:D2:14:48:AD:EB:BB:AD:B6:29:2C:6C:72:96:5C:38:35
   X509v3 Authority Key Identifier:
   keyid:84:F6:A1:65:16:1F:28:8A:B7:0D:CB:7E:19:76:2A:8B:F5:2B:5C:6A
   Certificate is to be certified until Feb 25 10:11:12 2027 GMT (3650 days)
   -- Choose y to sign and issue the certificate.
   Sign the certificate? [y/n]:y
   -- Select y, the certificate singing and issuing is complete.
   1 out of 1 certificate requests certified, commit? [y/n]y
   Write out database with 1 new entries
   Data Base Updated

   Enable password protection for the private key: If the password protection of the server private key is not disabled, you need to use gs_guc to encrypt the password.

   gs_guc encrypt -M server -K Gauss@MppDB -D ./

   After the password is encrypted using gs_guc, two private key password protection files server.key.cipher and server.key.rand are generated.

8. Generate the client certificate and private key - Generate a client private key.

   openssl genrsa -aes256 -out client.key 2048

   Generate a certificate request file for a client.

   openssl req -config openssl.cnf -new -key client.key -out client.req

   After the generated certificate request file for client is signed and issued, the formal client certificate client.crt is generated.

   openssl ca -config openssl.cnf -in client.req -out client.crt -days 3650 -md sha256

   

   If METHOD is set to cert in the pg_hba.conf file of the server, the client must use the username (common name) configured in the license file (client.crt) for the database connection. If METHOD is set to md5 or sha256, the client does not have this restriction.

   If password protection for a client private key is not removed, you need to use gs_guc to encrypt the password.

   gs_guc encrypt -M client -K Gauss@MppDB -D ./  

   After the password is encrypted using gs_guc, two private key password protection, files client.key.cipher and client.key.rand are generated.

Replacing Certificates

Default security certificates and private keys required for SSL connection are configured in LibrA. The formal certificates and keys for the server and client have been obtained from the CA.

1. Prepare for a certificate and a key. Conventions for configuration file names on the server:

   l Certificate name: server.crt
   l Key name: server.key
   l Key password and encrypted file: server.key.cipher and server.key.rand
   Conventions for configuration file names on the client:
   l Certificate name: client.crt
   l Key name: client.key
   l Key password and encrypted file: client.key.cipher and client.key.rand
   l Certificate name: cacert.pem
   l Names of files on in the revoked certificate list: sslcrl-file.crl

2. Create a compressed package.

   Package name: db-cert-replacement.zip

   Package format: ZIP

   Package file list: server.crt, server.key,server.key.cipher, server.key.rand, client.crt, client.key, client.key.cipher,client.key.rand, cacert.pem. If you need to configure the certificate revocation list (CRL), the list must contain sslcrl-file.crl.

   Command:

   zip db-cert-replacement.zip client.crt client.key client.key.cipher client.key.rand server.crt server.key server.key.cipher server.key.rand
   zip -u ../db-cert-replacement.zip cacert.pem

3. Invoke the certificate replacement interface to replace a certificate. Upload the prepared package db-cert-replacement.zip to any path of a cluster user. For example: /home/gaussdba/test/db-cert-replacement.zip

   Run the following command to perform the replacement in coodinator:

   gs_om -t cert --cert-file=/home/gaussdba/test/db-cert-replacement.zip

   Starting SSL cert files replace.

   Backing up old SSL cert files.

   Backup SSL cert files on BLR1000029898 successfully.

   Backup SSL cert files on BLR1000029896 successfully.

   Backup SSL cert files on BLR1000029897 successfully.

   Backup gds SSL cert files on successfully.

   BLR1000029898 replace SSL cert files successfully.

   BLR1000029896 replace SSL cert files successfully.

   BLR1000029897 replace SSL cert files successfully.

   Replace SSL cert files successfully.

   Distribute cert files on all coordinators successfully.

   You can run the gs_om -t cert --rollback command to remotely invoke the interface and gs_om -t cert --rollback -L

Configuration For Client

1. Run the below command on the client key file.

   openssl pkcs8 -topk8 -inform PEM -outform DER -in Client.key -out client.pk8

2. Copy the client.pk8, client.crt, cacert.pem that were created above to the client machine.
   

   When the DataStudio tool selects the client SSL key, the key file cannot be selected, and the *.pk8 file needs to be selected. However, the downloaded certificate does not contain the pk8 file.

3. Configure Two way SSL authentication for the client on the server.

   hostssl      all           all           10.18.158.95/32        cert

   Configure One way SSL authentication for the client on the server.

   hostssl      all           all           10.18.158.95/32        sha256

4. During log in to Data Studio password is not validated during Two Way SSL authentication.

   

   

   SSL password needs to be entered.