Managing SSL certificates ========================= PEM uses self-signed SSL certificates: - To secure requests to the :ref:`Post-configuration steps when web server and PEM backend database are installed separately ` (Apache httpd), which provides the user interface and REST API. - To secure and authenticate the :ref:`PEM backend database server and agent connection certificates ` . Web-server certificates ----------------------- PEM uses self-signed SSL certificates for the Apache httpd server. The self-signed SSL certificate and key files for the Apache httpd server are generated during PEM installation. To use your own SSL certificate for PEM, update the Apache HTTP configuration file ``edb-ssl-pem.conf`` . Update these two SSL directives in the PEM VirtualHost section: - ``SSLCertificateFile`` is your certificate file, for example, ``your_domain_name.crt`` . - ``SSLCertificateKeyFile`` is the ``.key`` file generated when you created the certificate signing request (CSR), for example, ``your_private.key`` . For example, make the following updates: .. code:: shell # Change the server name and file names in the configuration file to # match your certificate files. SSLEngine on SSLCertificateFile /path/to/your_domain_name.crt SSLCertificateKeyFile /path/to/your_private.key To increase security, you can replace the httpd self-signed SSL certificates with trusted CA signed certificates in PEM. For more information, see `Replacing httpd self-signed SSL certificates `_ . PEM backend database server and agent connection certificates ------------------------------------------------------------- By default, PEM implements secured SSL/TLS connections between PEM agents and the backend database. It also acts as its own certificate authority (CA) to generate certificates and keys for the PEM server and agent. The self-signed SSL certificates and keys for the PEM server and agent are generated during PEM installation. These certificates and keys encrypt the connection from agent to server. In addition, PEM agents are authenticated using their certificate rather than a password. You can replace the PEM self-signed SSL certificates and keys with the trusted CA certificates and keys. For more information, see :ref:`Use certificates and keys signed by trusted CA ` . How PEM self-signed SSL certificates work ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ The PEM server configuration script generates self-signed SSL certificate and key files for the PEM backend database server. The backend database server uses these certificates and keys to authenticate and encrypt the agent connections. Each certificate has an expiry date. Regenerate the certificates when they near expiration. For more information, see :ref:`Regenerating the server self-signed SSL certificates ` . The PEM agent connects to the PEM backend database server using the libpq interface, acting as a client of the backend database server. The agent self-signed SSL certificates and keys get generated during :ref:`Registering a PEM agent ` . PEM agent establishes the connection with the PEM backend database server using the self-signed SSL certificate and key files. Each agent has a unique identifier, and the agent certificates and keys have the corresponding identifier. Each certificate has an expiry date. Regenerate the certificates when they near expiration. For more information, see :ref:`Regenerating the agent self-signed SSL certificates ` . If required, you can use the same certificate for all agents rather than one certificate per agent. For more information, see :ref:`Generate a common agent certificate and key pair ` . For more information on using the SSL certificates to connect in Postgres, see `Securing TCP/IP connections with SSL `_ . Certificate and key files generation ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ The PEM server generates the certificates and key files in the data directory of the backend database server: - ``ca_certificate.crt`` - ``ca_key.key`` - ``root.crt`` - ``root.crl`` - ``server.crt`` - ``server.key`` The ``ca_certificate.crt`` and ``ca_key.key`` files are used during the agent registration process to generate the agent’s SSL certificates and key files. The ``root.crt`` file is a copy of the ``ca_certificate.crt`` file. You use the root certificate for the backend database server by setting the ``ssl_ca_file`` parameter as ``root.crt`` in the ``postgresql.conf`` file. The ``root.crl`` has the certificate revocation list (CRL) of digital certificates revoked by the issuing CA before their actual or assigned expiration date. The ``server.crt`` file is the signed certificate for the PEM server, and the ``server.key`` file is the private key to the certificate. The PEM agent certificates are generated using these server certificate and key files. PEM self-signed SSL certificate renewal ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ The PEM agent 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 and appends the new CA certificate file to the ``root.crt`` file on the PEM server - Creates a job to renew the certificate file of any active agents - Restarts the PEM server Generate a common agent certificate and key pair ------------------------------------------------ By creating and using a single Postgres user for all PEM agents rather than one user per agent (the default), you can use the same certificate for all agents. Create a user, generate an agent certificate and key pair, and use them for all PEM agents. 1. Create one common agent user in the PEM backend database. Grant the ``pem_agent`` role to the user. .. code:: shell # Running as enterprisedb psql -p 5444 -U enterprisedb -d pem CREATE USER pem_agent_common_user; GRANT pem_agent TO pem_agent_common_user; 1. Generate an agent key: .. code:: shell # Running as root openssl genrsa -out agent.key 4096 1. Generate a CSR for the agent: .. code:: shell openssl req -new -key agent.key -out agent.csr -subj /C=IN/ST=MH/L=Pune/O=PEM/CN= Where ``-subj`` is provided as per your requirements. 1. Use the ``openssl x509`` command to sign the CSR and generate an agent certificate: .. code:: shell openssl x509 -req -days 365 -in agent.csr -CA ca_certificate.crt -CAkey ca_key.key -CAcreateserial -out agent.crt 1. Change the permissions on the ``agent.crt`` and ``agent.key`` file: .. code:: shell chmod 600 agent.crt agent.key 1. Use this agent certificate and key pair: - For registering the new PEM agent from the remote host to the PEM server. a. Copy the agent certificate and key pair to the remote agent host and register the agent: .. code:: shell export PEM_SERVER_PASSWORD=edb /usr/edb/pem/agent/bin/pemworker --register-agent \ --pem-server 192.168.99.130 \ --pem-user enterprisedb \ --pem-port 5444 \ --pem-agent-user pem_agent_common_user \ -o agent_ssl_crt= agent.crt \ -o agent_ssl_key= agent.key b. Enable and start the pemagent services: .. code:: shell systemctl enable pemagent systemctl start pemagent - To replace the agent certificate and key pair with the registered agent. a. Edit the ``agent_user`` , ``agent_ssl_key`` , and ``agent_ssl_crt`` parameters in ``agent.cfg`` file of the agent host: .. code:: shell vi /usr/edb/pem/agent/etc/agent.cfg # Edit the agent username agent_user=pem_agent_common_user # Edit the ssl parameters with new certificate and key file location agent_ssl_key=/agent.key agent_ssl_crt=/agent.crt b. Restart the pemagent service: .. code:: shell systemctl restart pemagent Use certificates and keys signed by trusted CA ---------------------------------------------- Replace the PEM self-signed SSL certificates and keys with certificates and keys signed by a trusted CA. After obtaining the trusted CA certificates and keys, replace the :ref:`Replace the self-signed server SSL certificates with the certificates signed by the trusted CA ` and :ref:`Replace the self-signed agent SSL certificates with the certificates signed by the trusted CA ` certificates and keys. Replace the self-signed server SSL certificates with the certificates signed by the trusted CA ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 1. Back up the old server certificate and key files: .. code:: shell # Running as root mkdir /var/lib/edb/as/data/certs cd /var/lib/edb/as/data/ mv server.* root.* ca_* /var/lib/edb/as/data/certs 1. Generate a private key for the server: .. code:: shell openssl genrsa -out server.key 4096 1. Generate a CSR for the server: .. code:: shell openssl req -new -key server.key -out server.csr -subj /C=IN/ST=MH/L=Pune/O=EDB/CN=PEM Where ``-subj`` is provided as per your requirements. We recommend using the hostname or domain qualified full name of the PEM server host for ``CN`` . 1. Obtain the CA certificate (``trusted_ca.crt`` ) from a trusted CA. 2. Ask your CA to sign the CSR and generate the server certificate for you. 3. Verify the details of the new server certificate aren’t tampered with and match your provided details: .. code:: shell openssl x509 -noout -text -in server.crt 1. Use the new certificate obtained from the CA as the ``root.crt`` file: .. code:: shell cp trusted_ca.crt root.crt 1. If the trusted CA doesn’t provide CRL, disable CRL usage by the server. To disable the CRL usage, comment the ``ssl_crl_file`` parameter in the ``postgresql.conf`` file. .. Note:: If you accidentally leave a CRL from a previous CA in place and do not comment out `ssl_crl_file` , the server will start but authentication will fail with an SSL error message `tlsv1 alert unknown ca` . The error doesn't specify that the CRL is the cause, so this can be difficult to debug if encountered out of context. 1. Copy the new ``root.crt`` , ``server.key`` , and ``server.crt`` files to the data directory of the backend database server: .. code:: shell cp root.crt server.key server.crt /var/lib/edb/as/data 1. Change the owner and permissions of the new certificates and key files to be the same as the data directory: .. code:: shell cd /var/lib/edb/as/data/ chown enterprisedb server.* root.crt ca_certificate.crt chmod 600 server.* root.crt ca_certificate.crt .. Note:: Don't restart the PEM server now. If you restart the PEM server, all the registered agents will stop working. 1. Replace each PEM agent SSL certificates with the trusted CA certificates. For more information, see these :ref:`Replace the self-signed agent SSL certificates with the certificates signed by the trusted CA ` . 2. Restart the PEM server. - On Linux: .. code:: shell # Running as root systemctl start edb-as- Restarting the backend database server restarts the PEM server. Replace the self-signed agent SSL certificates with the certificates signed by the trusted CA ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Replace the self-signed agent SSL certificates only after replacing the self-signed server certificates ``server.crt`` and ``server.key`` and CA certificate ``root.crt`` . 1. Use psql to find all the agent identifiers (IDs) needed to replace the SSL certificates: .. code:: shell psql -U enterprisedb -d pem --no-psqlrc -t -A -c "SELECT id FROM pem.agent WHERE active=true" 1. After identifying the agents that need key files, generate an ``agent.key`` for each agent: .. code:: shell openssl genrsa -out agent.key 4096 Where ```` is the agent identifier. 1. Generate a CSR for each agent: .. code:: shell openssl req -new -key agent.key -out agent.csr -subj /C=IN/ST=MH/L=Pune/O=PEM/CN=agent Where ``-subj`` is provided as per your requirements. Replace ```` in ``CN`` with an appropriate agent identifier. .. Note:: If you prefer to use a single certificate for all PEM agents rather than one per agent, create a common Postgres user and supply this username in place of `ID` . See :ref:`Generate a common agent certificate and key pair ` . 1. Ask your CA to sign the CSR and generate the agent certificate for you. 2. Copy the certificate and key files on the respective hosts, where ```` matches the ``agent_id`` in the ``/usr/edb/pem/agent/etc/agent.cfg`` file. 3. Change the ownership and permission on the new ``agent.crt`` and ``agent.key`` file: .. code:: shell chown root agent.crt agent.key chmod 600 agent.crt agent.key 1. Back up the old agent certificate and key file: .. code:: shell # Running as root mkdir root/.pem/certs mv root/.pem/agent.* root/.pem/certs 1. Replace each agent’s certificate and key file with the newly generated files: .. code:: shell cp agent.key agent.crt root/.pem 7. Restart the PEM agent service. - On Linux: .. code:: shell # Running as root systemctl restart pemagent - On Windows: Use the Services applet to restart the PEM agent. The PEM agent service is named Postgres Enterprise Manager Agent. Select the service name in the Services dialog box, and select **Restart the service**. Testing certificates -------------------- If you experience authentication problems, you can use these tests to validate certificates. Validate a certificate against the root certificate ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ To check whether a PEM agent certificate is trusted according to the server’s ``root.crt`` , copy both certificates to the same machine. Then execute the following command: .. code:: shell openssl verify -verbose -CAfile root.crt agent1.crt This command returns ``agent1.crt: OK`` on success or an explanatory message on failure. Make a test connection to the PEM backend database ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ To verify whether the agent user can connect using a certificate, on the server where the agent is located, execute the following commands as root: .. code:: shell PGHOST= PGPORT= PGUSER=agent PGSSLCERT=/root/.pem/agent.crt PGSSLKEY=/root/.pem/agent.key PGSSLMODE=require export PGHOST PGPORT PGUSER PGSSLCERT PGSSLKEY PGSSLMODE -A -t -c "SELECT version()" Where: - ```` is the full path to the psql executable, for example ``/usr/edb/as15/bin/psql`` . - ```` is the hostname or IP address of PEM server. - ```` is the PEM backend database server port. - ```` is the ID of the agent you’re testing, as defined in the file ``/usr/edb/pem/agent/etc/agent.cfg`` . .. Note:: If you used the instructions in :ref:`Generate a common agent certificate and key pair ` you must set ``PGUSER`` to the common agent username. If the connection succeeds, it returns the Postgres version of the database server. Success means that your certificate is valid and the Postgres user is correctly configured. .. toctree:: :maxdepth: 3 Administering--certificates--replacing_ssl_certificates Administering--certificates--regenerating_agent_certificates