Symptoms

After the upgrade to Odin Automation 7.1, when trying to deploy new functionalities using PostgreSQL DB replication mechanisms there are issues in Task Manager related to the certificate validity.

Cause

Starting from 7.1, the nodes hosting master PostgreSQL database (Operations/Billing) and its replica, use public and private keys for data encryption. Therefore, if the keys are invalid or expired the communications between slave and master DB will be broken. This causes issues with all the functionalities using DB replication mechanisms (master-slave PostgreSQL DB communications) like Business Intelligence, APS Booster, PostgreSQL high availability (PGHA) cluster. For the new 7.1 installations the certificate is 100 years by default, however, the upgrade scenarios to Odin Automation 7.1 imply that the certificate validity stays as it was configured during its first installation. Before 7.1 release, the certificate's default validity was 30 days.

Resolution

Important: Before installing the APS Booster, PGHA cluster, and Business Intelligence, make sure that the certificate's remaining period of validity is minimum 5 years (here we imply the period from the current date till the expiration date is not less than 5 years). Otherwise, you should update your certificate. BUT before generating a new valid certificate check if the existing certificate is already used in your system. Generating a new certificate will overwrite the existing one which will cause issues with the services already using it.

To check the certificate's period of validity, do the following:

  1. Log in to the node, hosting the master PostgreSQL DB (MN or Billing DB node).

  2. To view the certificate's period of validity, run the following command:

    # openssl x509 -in /var/lib/pgsql/9.X/data/server.crt -noout -dates
    

    where 9.X - hereinafter should be replaced with your DB version, for example 9.6

    For example, the output can be the following:

    notBefore=Oct  9 20:17:21 2010 GMT
    notAfter=Aug 13 20:17:21 2017 GMT
    

Let's consider the following scenarios how to solve the issue with the certificate in each case depending on the valitidy.

Certificate validity

Case 1. The existing certificate is expired

You can regenerate a new valid certificate using the following command on master DB node:

# su - postgres -c 'openssl req -nodes -new -x509 -days 36500 -keyout /var/lib/pgsql/9.X/data/server.key -out /var/lib/pgsql/9.X/data/server.crt -subj "/C=RU/O=Odin/CN=OA System Database Master Endpoint"'

The certificate and key should belong to user postgres.

This command will re-issue a new server certificate for approximately 100 years. After generation of a new certificate, restart PostgreSQL. This will cause a downtime for a while.

Case 2. The existing certificate validity is less than 5 years and it is not used by slave nodes

In this case regenerate the certificate as it is described in the Case 1.

Case 3. The existing certificate validity is less than 5 years and it is already used by slave nodes

If the existing certificate is already used by slave nodes, the generation of a new certificate will overwrite the existing one and break the existing communications between master and slave nodes, hosting the DBs.

Important: If you make a decision to generate a new certificate, make sure that the newly issued certificate is propagated to all slave nodes, hosting the database.

Follow the steps below:

  1. On master node:

    1. Regenerate a new valid certificate as it is described in the Case 1.

    2. Copy the newly generated certificates to a slave node (if you have several slave nodes, then copy to all of them).

      # scp /var/lib/pgsql/9.X/data/server.* root@<slave_node_IP_address>:/var/lib/pgsql/9.X/data
      
  2. On slave node(s):

    1. Create a directory for slave certificate (if it does not exist yet):

      # su - postgres -c 'mkdir -p "/var/lib/pgsql/.postgresql"'
      
    2. Copy the certificate from /var/lib/pgsql/9.X/data to /var/lib/pgsql/.postgresql/ and rename it as root.crt.

      # su - postgres -c 'cp -f "/var/lib/pgsql/9.X/data/server.crt" "/var/lib/pgsql/.postgresql/root.crt"'
      
    3. Restart PostgreSQL.

Internal content