Managing certificates v8
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
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
, wherex
is the PostgreSQL database versionCopy 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 wordold
appended to the entry, use this command:Use the
openssl_rsa_generate_key()
function to generate theca_key.key
file:After creating the
ca_key.key
file,cat
the contents to the variableCA_KEY
for use when generating theca_certificate.crt
file. Modify the privileges on theca_key.key
file:Use the key to generate the
ca_certificate.crt
file. For simplicity, place the SQL query in a temporary file with a unique name:Then use the variable to execute the query, placing the content in the
ca_certificate.crt
file.Modify the permissions of the
ca_certificate.crt
file, and remove the temporary file that contained the SQL command:Reuse the
ca_certificate.crt
file as theroot.crt
file:Modify the permissions of the
root.crt
file:Use the
openssl_rsa_generate_crl()
function to create the certificate revocation list (root.crl
):Modify the permissions of the
root.crl
file:Use the
openssl_rsa_generate_key()
function to generate theserver.key
file:After creating the
server.key
file,cat
the contents to the variableSSL_KEY
for use when generating theserver.crt
file and modify the privileges on theserver.key
file:Use the
SSL_KEY
to generate the server certificate. Save the certificate in theserver.crt
file. For simplicity, first place the SQL query into a temporary file with a unique name:Modify the privileges on the
server.crt
file, and delete the temporary file:Restart the Postgres server:
On Linux with
init.d
, for example, on a Centos6 host:On Linux with
systemd
, for example, on a Centos7 host:
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:
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:Modify the privileges of the
agent.key
file:Generate a certificate for each agent. To generate a certificate, execute the following command, capturing the output in a certificate file:
Where
$ID
is the agent number of the agent (retrieved by the psql command line).Modify the privileges of the
agent.crt
file: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:On Linux with
systemd
, restart the service with the command: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.