Managing Certificates

Files stored in the data directory of the PEM server backing database contain information that helps the PEM server utilize secure connections:

  • ca_certificate.crt

  • ca_key.key

  • server.crt

  • server.key

  • root.crl

  • root.crt

The PEM agent that is installed with the PEM server monitors the expiration date of the ca_certificate.crt file. When the certificate is about to expire, PEM will:

  • Make a backup of the existing certificate files.

  • Create new certificate files, appending the new CA certificate file to the root.crt file on the PEM server.

  • Create a job that renews the certificate file of any active agents.

  • Restart the PEM server.

When you uninstall an agent, the certificate associated with that agent will be added to the certificate revocation list (maintained in the root.crl file) to ensure that the certificate cannot be used to connect to the PEM server.

The following sections contain detailed information about manually replacing certificate files.

Replacing SSL Certificates

The following steps detail replacing the SSL certificates on an existing PEM installation. If you plan to upgrade your server to a new version at the same time, invoke all of the PEM installers (first the server installer, then agent installers) before replacing the SSL certificates. Then:

  1. Stop all running PEM agents, first on the server host, and then on any monitored node.

    To stop a PEM agent on a Linux host, open a terminal window, assume superuser privileges, and enter the command:

    On Linux with init.d, for eg: Centos6

    /etc/init.d/pemagent stop

    On Linux with systemd, for eg: Centos7

    systemctl stop pemagent

    On a Windows host, you can use the Services applet to stop the PEM agent. The PEM agent service is named Postgres Enterprise Manager Agent; highlight the service name in the Services dialog, and click Stop the service.

  2. Take a backup of the existing SSL keys and certificates. The SSL keys and certificates are stored in the data directory under your PEM installation. For example, the default location on a Linux system is:

    /var/lib/pgsql/x/data where x is the PostgreSQL database version.

    Make a copy of the following files, adding an extension to each file to make the name unique:

    • ca_certificate.crt

    • ca_key.key

    • root.crt

    • root.crl

    • server.key

    • server.crt

    For example, the command:

    # cp ca_certificate.crt ca_certificate_old.crt

    Creates a backup of the ca_certificate file with the word old appended to the entry.

  3. Use the openssl_rsa_generate_key() function to generate the ca_key.key file:

    /usr/pgsql-x.x/bin/psql -U postgres -d pem --no-psqlrc -t -A -c "SELECT public.openssl_rsa_generate_key(1024)" > /var/lib/pgsql/x/data/ca_key.key

    After creating the ca_key.key file, cat the contents to the variable CA_KEY for use when generating the ca_certificate.crt file and modify the privileges on the ca_key.key file:

    CA_KEY=$(cat /var/lib/pgsql/x/data/ca_key.key)

chmod 600 /var/lib/pgsql/x/data/ca_key.key

  4. Use the key to generate the ca_certificate.crt file. For simplicity, place the SQL query into a temporary file with a unique name:

    echo "SELECT openssl_csr_to_crt(openssl_rsa_key_to_csr('${CA_KEY}',
'PEM','US', 'MA', 'Bedford', 'Postgres Enterprise Manager',
'support@enterprisedb.com'), NULL,
'/var/lib/pgsql/x/data/ca_key.key')" > /tmp/_random.$$

    Then use the variable to execute the query, placing the content into the ca_certificate.crt file.

    /usr/pgsql-x/bin/psql -U postgres -d pem --no-psqlrc -t -A -f /tmp/_random.$$ > /var/lib/pgsql/x/data/ca_certificate.crt

    Modify the permissions of the ca_certificate.crt file, and remove the temporary file that contained the SQL command:

    chmod 600 /var/lib/pgsql/x/data/ca_certificate.crt

rm -f /tmp/_random.$$

  5. Re-use the ca_certificate.crt file as the root.crt file:

    cp /var/lib/pgsql/x/data/ca_certificate.crt /var/lib/pgsql/x/data/root.crt

    Modify the permissions of the root.crt file:

    chmod 600 /var/lib/pgsql/x/data/root.crt

  6. Use the openssl_rsa_generate_crl() function to create the certificate revocation list (root.crl) :

    /usr/pgsql-x/bin/psql -U postgres -d pem --no-psqlrc -t -A -c
 "SELECT openssl_rsa_generate_crl('/var/lib/pgsql/x/data/ca_certificate.crt', '/var/lib/pgsql/x/data/ca_key.key')" > /var/lib/pgsql/x/data/root.crl

    Modify the permissions of the root.crl file:

    chmod 600 /var/lib/pgsql/x/data/root.crl

  7. Use the openssl_rsa_generate_key() function to generate the server.key file:

    /usr/pgsql-x/bin/psql -U postgres -d pem --no-psqlrc -t -A -c "SELECT public.openssl_rsa_generate_key(1024)" >> /var/lib/pgsql/x/data/server.key

    After creating the server.key file, cat the contents to the variable SSL_KEY for use when generating the server.crt file and modify the privileges on the server.key file:

    SSL_KEY=$(cat /var/lib/pgsql/x/data/server.key)

chmod 600 /var/lib/pgsql/x/data/server.key

  8. Use the SSL_KEY to generate the server certificate. Save the certificate in the server.crt file. For simplicity, first place the SQL query into a temporary file with a unique name:

    echo "SELECT openssl_csr_to_crt(openssl_rsa_key_to_csr('${SSL_KEY}',
'PEM','US', 'MA', 'Bedford', 'Postgres Enterprise Manager',
'support@enterprisedb.com'),
'/var/lib/pgsql/x/data/ca_certificate.crt',
'/var/lib/pgsql/x/data/ca_key.key')" > /tmp/_random.$$

 /usr/pgsql-x/bin/psql -U postgres -d pem --no-psqlrc -t -A -f /tmp/_random.$$ >> /var/lib/pgsql/x/data/server.crt

  9. Modify the privileges on the server.crt file, and delete the temporary file:

    chmod 600 /var/lib/pgsql/x/data/server.crt

rm -f /tmp/_random.$$

  10. Restart the Postgres server:

    On Linux with init.d; for example, on a Centos6 host:

/etc/init.d/postgresql-x restart

On Linux with systemd; for example, on a Centos7 host:

systemctl restart postgresql-x

Updating Agent SSL Certificates

For each agent that interacts with the PEM server, you must:

  • generate an rsa key and a certificate.

  • copy the key and certificate to the agent.

  • restart the agent.

Each agent has a unique identifier that is stored in the pem.agent table in the pem database. You must replace the key and certificate files with the key or certificate that corresponds to the agent’s identifier. Please note that you must move the agent.key and agent.crt files (generated in Steps 2 and 3 into place on their respective PEM agent host before generating the next key file pair; subsequent commands will overwrite the previously generated file.

To generate a PEM agent key file pair:

  1. Use psql to find the number of agents and their corresponding identifiers:

    /usr/pgsql-x/bin/psql -U postgres -d pem --no-psqlrc -t -A -c "SELECT ID FROM pem.agent"

  • On Linux, you can also find the agent identifier and location of the keys and certificates in the PEMagent section of the /etc/postgres-reg.ini file.

  • On Windows, the information is stored in the registry:

    • On a 64-bit Windows installation, check:

     HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\EnterpriseDB\PEM\agent

* On a 32-bit Windows installation, check:

    HKEY_LOCAL_MACHINE\SOFTWARE\EnterpriseDB\PEM\agent

  1. After identifying the agents that will need key files, generate an agent.key for each agent. To generate the key, execute the following command, capturing the output in a file:

    /usr/pgsql-x/bin/psql -U postgres -d pem --no-psqlrc -t -A -c "SELECT openssl_rsa_generate_key(1024)" > agent.key

Modify the privileges of the ``agent.key`` file:

    chmod 600 agent.key

  2. Generate a certificate for each agent. To generate a certificate, execute the following command, capturing the output in a certificate file:

    /usr/pgsql-x/bin/psql -U postgres -d pem --no-psqlrc -t -A -c
"SELECT openssl_csr_to_crt(openssl_rsa_key_to_csr('$(cat agent.key)',
'agent<$ID>', 'US', 'MA', 'Bedford', 'Postgres Enterprise Manager',
'support@enterprisedb.com'),
'/var/lib/pgsql/x/data/ca_certificate.crt',
'/var/lib/pgsql/x/data/ca_key.key')" > agent.crt

Where $ID is the agent number of the agent (retrieved via the psql command line).

  1. Modify the privileges of the agent.crt file:

    chmod 600 agent.crt

  2. Replace each agent’s key and certificate file with the newly generated files before restarting the PEM agent service:

    On Linux with init.d, restart the service with the command:

    /etc/init.d/pemagent start

    On Linux with systemd, restart the service with the command:

    systemctl start pemagent

    On a Windows host, you can use the Services applet to start the PEM agent. The PEM agent service is named Postgres Enterprise Manager Agent; highlight the service name in the Services dialog, and click Start the service.