How to replace Postgres Enterprise Manager self-signed certificates with certificates provided by Certificate Authority (CA) 

EDB Postgres Enterprise Manager (PEM) is a graphical database management/monitoring tool that helps database administrators, systems administrators, and database developers to manage databases efficiently and proactively. The PEM Agent is typically installed on each managed database server host in the environment. It is used to collect metrics about the database server and the underlying host that it is monitoring and to execute tasks when required.

Self-signed certificates are responsible for making the connection between the PEM Agents and the PEM server. These certificates get created during PEM installation when we configure the backend database server.

Now the question arises, can we replace those self-signed certificates with the trusted Certificate Authority (CA) authorized certificates? This post provides the answer.

Prerequisites

Let’s say we have PEM 7.13 running on a RHEL 7(Red Hat Enterprise Linux) machine with EDB Postgres Advanced Server (EPAS) v12 as a PEM server and a standalone PEM Agent is registered to PEM Server. We need to replace the certificates of backend EPAS 12 and of both the agents (a standalone and default agent of PEM server). 

Replacing the certificates

1. Make sure that the PEM Agents and PEM server are up and running.

2. Navigate to the data directory of the server (cd /var/lib/edb/as12/data/).

3. Take the backup of the following keys and certificate files before we replace them:

• ca_key.key and ca_certificate.crt
• server.key and server.crt
• root.crt

4. Replace the above keys and certs with the CA-authorized keys.

5. Change the owner of all the replaced keys and certs to the database user and, in our case, EnterpriseDB. 

6. Change the permission of the keys and certs to 600.

7. Modify the following parameters in the file /var/lib/edb/as12/data/postgresql.conf:

• ssl = on
• ssl_cert_file = 'server.crt'
• ssl_key_file = 'server.key'
• ssl_ca_file = 'root.crt'
• Comment on the ssl_crl_file parameter

NOTE: Do not restart the database server.

8. Create the following PL/SQL function using the query editor in PEM:

DROP FUNCTION IF EXISTS pem.create_job_for_renewing_certs(int4[]);
CREATE OR REPLACE FUNCTION pem.create_job_for_renewing_certs(_agents int4[] DEFAULT NULL)
RETURNS void
LANGUAGE 'plpgsql'
VOLATILE
COST 100
AS $BODY$
DECLARE
	v_jobid int;
	v_jobstepid int;
	cur_agent_list cursor(agents int[]) FOR 
	SELECT * FROM pem.agent a WHERE a.active = 'true' AND (agents IS NULL OR a.id = ANY(agents));
	rec_agent RECORD;
BEGIN
	OPEN cur_agent_list(_agents);
	LOOP
		FETCH cur_agent_list INTO rec_agent;
		EXIT WHEN NOT FOUND;
		-- create a job for renew the agent certificates
		INSERT INTO pem.job(
			jobname, jobdesc, agent_id, jobnextrun) 
		VALUES(
			'Renew agent certificate','This job renew the agent certificate', rec_agent.id, now()) 
		RETURNING jobid INTO v_jobid;
		-- create the jobstep for the above created job
		INSERT INTO pem.jobstep(
			jstjobid, jstname, jstdesc, jstkind, jstcode) 
		VALUES (
			v_jobid, 'Renew agent certificate','This job step runs to renew the agent certificate.', 'i','renew_agent_certificate') RETURNING jstid INTO v_jobstepid;
		RAISE INFO 'Created a job (#% with step #%) for Agent#% (%)', v_jobid, v_jobstepid, rec_agent.id, rec_agent.description;
	END LOOP;
	CLOSE cur_agent_list;
END
$BODY$;

9. Execute the function created above:

SELECT pem.create_job_for_renewing_certs();

10. Restart the PEM server using “systemctl restart edb-as-12.service” (This is with reference to RHEL 7 systemd utility. Use appropriate service restart command depending upon underlying OS).

We are there, but a quick sanity check is required after the above steps execute, as pointed below:-

• Agent level jobs get created which then re-create the certificates for both of the agents.
• New keys and certificates get created for each agent in /root/.pem/.
• The PEM server and agents are up and running.

I hope this helps you replace the default PEM self-signed certificates with the trusted CA certificates successfully.

PS: These steps are applicable to PEM versions 7.2 and later. Also, this post was written when PEM 7.13 was the last released version of PEM. Maybe in future releases you will be able to find the create_job_for_renewing_certs function in the PEM schema.