Set up PostgreSQL in Certificate Manager

This article describes how to install and set up the PostgreSQL database, used in Smart ID Certificate Manager (CM).

For detailed information about how to install PostgreSQL, see the documentation available at http://www.postgresql.org/.

Prerequisites

When PostgreSQL is used for the CMDB database, tables and users must be created before the CM server is installed.

1. Install the PostgreSQL database according to the instructions for the downloaded PostgreSQL installation package.

2. Extract the PostgreSQL database scripts create_db_and_role.sql and create_cmdb.sql, located at program_files/install/PostgreSQL, from the server installation zip file, cm_server_<version>.zip.

Step-by-step instruction

Edit settings on the PostgreSQL server

1. Run the script, that creates the lcmreq user and the cmdb database, as database superuser:

psql -d postgres -U postgres -f create_db_and_role.sql >create_cmdb.log

2. Change the password for the lcmreq user, created by the script. The default password is REQreq01. The database name and user name should be in lower case,
   since they are case sensitive.

3. Check the log file.

4. Run the script for creating the cmdb tables:

psql -d cmdb -U lcmreq -f create_cmdb.sql >create_cmdb.log

5. Check the log file.

Edit settings on the CM server

When installing the CM server, include the JDBC component to create the PostgreSQL database connection parameters, which are stored in the cm.conf configuration file.

Secure the connection

1. Enable TLS support for the connection to the PostgreSQL database server.
   You can configure CM to connect to PostgreSQL using certificate authentication for both server and client certificates. A complete guide on how to enable TLS on the PostgreSQL server can be found here: https://www.postgresql.org/docs/current/static/ssl-tcp.html.

2. After completing the guide, add additional JDBC parameters in the cm.conf file to enable secure connection between CM and the database.

   1. A trust store must be configured by specifying a trust store path to a PEM encoded certificate, that contains both root and intermediate certificates. 
      To specify a trust store, add the following parameters in cm.conf:

      CODE

      Database.security.1 = sslmode=verify-ca
      Database.security.2 = sslrootcert=/opt/cm/server/certs/pgsql-root-intermediate.crt

   2. If client authentication is enabled on the PostgreSQL server, a client key and certificate also have to be configured by specifying a DER encoded certificate and private key. Note that the private key file MUST be protected against unauthorized usage. On Linux, this can be achieved by issuing the command chmod 0600 lcmreq-tls-key.p8. 
      To specify a client TLS certificate and key, add the following parameters in cm.conf:

      CODE

      Database.security.3 = sslcert=/opt/cm/server/certs/lcmreq-tls.der
      Database.security.4 = sslkey=/opt/cm/server/certs/lcmreq-tls-key.p8

If client authentication is enabled, the database password can be removed (or commented out) from cm.conf. However, the username still needs to be present.

Additional information