Managing certificates v8

Suggest edits

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

ca_certificate.crt
ca_key.key
server.crt
server.key
root.crl
root.crt

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

Makes a backup of the existing certificate files.
Creates new certificate files, appending the new CA certificate file to the root.crt file on the PEM server.
Creates a job that renews the certificate file of any active agents.
Restarts the PEM server.

When you uninstall an agent, the certificate associated with that agent is added to the certificate revocation list maintained in the root.crl file to ensure that you can't use the certificate to connect to the PEM server.

You can manually replace certificate files.

Replacing SSL certificates

You can replace 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:

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 systemd, for example, Centos 7 or 8
```
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. Select the service name in the Services dialog box and select Stop the service.
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
Copy 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, to creates a backup of the ca_certificate file with the word old appended to the entry, use this command:
```
# cp ca_certificate.crt ca_certificate_old.crt
```
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. 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
```

Use the key to generate the ca_certificate.crt file. For simplicity, place the SQL query in 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 in 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.$$

Reuse 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
```

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

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
```

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

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.$$
```
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's 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. 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 overwrite the previously generated file.

To generate a PEM agent key file pair:

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 under HKEY_LOCAL_MACHINE\Software\EnterpriseDB\PEM\agent.

After identifying the agents that 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
```

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 by the psql command line).

Modify the privileges of the agent.crt file:
```
chmod 600 agent.crt
```
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. Select the service name in the Services dialog box and select Start the service.