Creating and enabling an SSL certificate for a secure database connection

About this task

SSL/TLS protects the transport of information between a client (in this case, your web server) and a server (in this case, your database server) from being tampered with and eavesdropped on by anyone on the network in between (including being able to get on those two machines). To assess whether SSL is useful, you need to assume that the attacker is in a position to complete the attack that SSL is designed to protect you against. That is, the attacker would need to be in a position to sniff packets on the network or on either machine.

Important: If you use your own certificates, make sure that the private key parameter does not have a password. Password-enabled private keys are not supported.

Procedure

  1. Create a CA authority. 
    1. openssl genrsa -out CAKey.pem 2048
    2. openssl req -new -x509 -days 365 -key CAKey.pem -sha512 -out cacert.pem
  2. Create a Server Certificate.
    Run the commands and follow the runtime instructions:
    1. openssl genrsa -aes256 -out svr-key.pem 2048
    2. openssl req -new -key svr-key.pem -out server.csr 
    3. openssl x509 -req -days 365 -in server.csr -CA cacert.pem -CAkey CAKey.pem -set_serial 00001 -sha512 -out server-cert.pem
  3. Remove the password from server-private key. 
    openssl rsa -in svr-key.pem -out server-key.pem
    Note: If you are using your own certificates, you must remove the password for server-private key.
  4. Verify the changes. 
    1. openssl x509 -in cacert.pem -text -noout
    2. openssl x509 -in server-cert.pem -text -noout
  5. Change directories. 
    cd /nz/kit/share/security
  6. Copy the server-cert.pem and server-key.pem files to /nz/kit/share/security and run the following commands.
    • For 11.0.7.1 and higher:
      1. mv server-cert-sp800-131a.pem.sample server-cert-sp800-131a.pem.sample.BKP
      2. mv server-key-sp800-131a.pem.sample server-key-sp800-131a.pem.sample.BKP
      3. mv server-cert.pem server-cert-sp800-131a.pem.sample
      4. mv server-key.pem server-key-sp800-131a.pem.sample
    • For 11.0.7.0 or lower without enable_tls_v12:
      1. mv server-key.pem.sample server-key.pem.sample.BKP
      2. mv server-cert.pem.sample server-cert.pem.sample.BKP
      3. mv server-key.pem server-key.pem.sample
      4. mv server-cert.pem server-cert.pem.sample
    • For 11.0.7.0 or lower with enable_crypto_std_v1 =1:
      1. mv server-cert-sp800-131a.pem.sample server-cert-sp800-131a.pem.sample.BKP
      2. mv server-key-sp800-131a.pem.sample server-key-sp800-131a.pem.sample.BKP
      3. mv server-cert.pem server-cert-sp800-131a.pem.sample
      4. mv server-key.pem server-key-sp800-131a.pem.sample
  7. Restart the system. 
    1. nzstop
    2. nzstart
  8. Verify the changes.
    1. Change directories to /nz/data/security.
      cd /nz/data/security
    2. openssl x509 -in server-cert.pem -text -noout
    3. Run the nzsql command:
      nzsql -u admin -pw password -securityLevel onlySecured
      Example:
      nz@netezza01 ?]$ nzsql -u admin -p password -securityLevel onlySecured
Welcome to nzsql, the IBM Netezza SQL interactive terminal. 

Type: \h for help with SQL commands 
\? for help on internal slash commands 
\g or terminate with semicolon to execute query 
\q to quit 

SSL enabled connection. Cipher: AES256-SHA, bits: 256
      SSL enabled connection. Cipher:AES256-SHA, bits: 256 specifies that you are using the correct SSL communication.
    4. Look for the following entry in postgres/pg.log.
      DEBUG: Attempting SSL_accept() 
DEBUG: SSL_accept succeeded
    5. On Windows, set up the connection with the "SSL Mode" = "Require" option.