Configuring PostgreSQL Cluster as the Backend Database

PostgreSQL is an advanced open-source RDBMS system with unique features, like fault-tolerant capabilities, scalability, stability, etc., designed to handle huge workloads. PAM360 already employs PostgreSQL as its default database, which comes bundled with the product. Now, to increase the scalability on a large scale and improve the operations and functionality of the application, PAM360 allows users to configure the PostgreSQL cluster as a standalone external backend database.

At the end of this document, you will have learned about configuring the PostgreSQL cluster as the backend database.

1. Configuring PostgreSQL Cluster as the Backend Database

  1. Download and install PAM360 on the preferred machine/server.

    Note: Do not start the PAM360 service after the installation.

  2. Download and install PostgreSQL.
  3. Create a database cluster in the PostgreSQL provided with the database owner's permission.
  4. Following the database cluster creation, ensure that the extension 'pgcrypto' is available in the extension drop-down. If there is no such available extension, create it using the command - CREATE EXTENSION pgcrypto;
  5. Navigate to <PostgreSQL_Installation_folder>pgsql_installation\data and open the file pg_hba.conf:
    1. Add an entry at the end with the IP address of the machine/server on which the PAM360 is running.
      E.g., host all <user name> <ip address>/32 md5

      2. Note: The above step applies only to users who use an external PostgreSQL enterprise database as their backend database. For users with cloud-based databases, the process will be performed in an automated process.

  6. Navigate to <PostgreSQL_Installation_folder>pgsql_installation\data in Windows or <PostgreSQL_Installation_folder>pgsql_installation\main in Linux and open the file postgresql.conf:
    1. Search for listen_addresses and provide the IP address of the server where PAM360 is running.
  7. Restart the PostgreSQL server.
  8. Navigate to <PAM360_Installation_folder>\conf and open the file customer-config.xml:
    1. Search for startdbserver= true and change its value to false.
    2. Now, save and close the file customer-config.xml.
  9. Navigate to <PAM360_Installation_folder>\conf and open the file database_param.conf.
    1. Replace localhost detail with the listener name of the server where the PostgreSQL database cluster is running.
    2. Update the port number to the actual port number of the PostgreSQL cluster in the database. By default, the initial port number for the PostgreSQL will be 5432.
    3. Update the username and password.
    4. Update the database name as provided while creating the cluster.
    5. Search for db.password.encrypted=true and change it as db.password.encrypted=false.
    6. Now, save and close the file database_param.conf.

      7. Notes: The password given here will be in plain text at that instance and will be encrypted from the next consequent occurrence.

  10. Download the root CA certificate of the PostgreSQL database cluster and import it into PAM360.
    If you are unaware of creating an SSL for the PostgreSQL database, follow the below sections to generate the required SSL certificates (CA-Signed or Self-Signed) and validate the SSL connection.
  11. Start the PAM360 service.

Now, you have successfully configured the PostgreSQL cluster as the backend database.

2. SSL Generation for PostgreSQL Cluster

An SSL connection is required for a secured connection between the PostgreSQL database cluster and the PAM360 application. To establish the SSL connection, the root CA of the PostgreSQL database cluster is required.

2.1 Using a CA-Signed SSL Certificate for the PostgreSQL Cluster

If you have a CA-signed SSL certificate, proceed directly with the certificate installation from step 4. Else, follow the below steps 1 to 3 to generate and install a new CA-signed certificate:

  1. First, generate a private key using openssl. Execute the following command in your terminal:

    openssl genrsa -des3 -out server.key 2048

    This command generates a 2048-bit RSA private key and saves it to server.key, protected by a passphrase.
  2. Use the private key to create a Certificate Signing Request (CSR). This request will be sent to a CA for signing. Execute the following command:

    openssl req -new -key server.key -out server.csr

    During this process, you will be prompted to enter the Passphrase for the key and provide information such as the Common Name, Hostname or IP Address, where Common Name should be the Fully Qualified Domain Name (FQDN) of your PostgreSQL cluster.
  3. Submit the generated CSR (server.csr) to a third-party CA such as VeriSign, Thawte, or RapidSSL for signing. Refer to the respective CA’s documentation for instructions on submitting the CSR and completing the signing process. Note that this service usually requires a fee.
    In a few days, you will receive your signed SSL certificate and the CA's root certificate as `.cer` files.
  4. Install the server certificate on the PostgreSQL cluster machine using the MMC console:
    1. Click Start and select Run. In the Run dialog box, type MMC and press enter. This opens the Microsoft Management Console (MMC).
    2. From the Console menu, select Add/Remove Snap-in.
    3. Click Add, then select Certificates and click Add again.
    4. When prompted, choose to manage certificates for the Computer Account.
    5. Navigate to Manage User Certificates (Local Computer) >> Personal >> Certificates.
    6. Right-click on Certificates, select All Tasks, and then click Import
    7. Browse to the location of your signed server certificate, select it, and complete the import process.
  5. Install the CA root certificate in PAM360 using the following steps:
    1. Copy the CA's root certificate to the <PAM360 Installation Folder>\bin directory.
    2. From the <PAM360 Installation Folder>\bin directory, execute the following command:

      importCert.bat <root_certificate_name.cer>

      Replace <root_certificate_name.cer> with the actual name of the root certificate file you copied. This command adds the root certificate to the PAM360 certificate store.

Following these steps will help you generate a private key and certificate request, get it signed by a CA, and install both the server and root certificates on the respective machines.

2.2 Self-Signed SSL Generation for the PostgreSQL Cluster

If you have a self-signed SSL certificate, proceed directly with the certificate installation from section 2.2.1. Else, follow the below steps to generate and install a new self-signed certificate:

  1. Navigate to <PostgreSQL Installation Directory>\data where the certificates are to be created and open the command prompt as an administrator to proceed with the following steps to create an SSL root CA for the PostgreSQL database cluster.
  2. Execute the command - openssl genrsa -passout pass:1111 -des3 -out rootca.key 4096
    (where 'pass:1111' is the PEM pass phrase and 'rootca.key' is the trust certificate key)
  3. Execute the command - openssl req -passin pass:1111 -new -x509 -days 365 -key rootca.key -out rootca.crt
  4. Enter the required certificate details accordingly to create a root certificate. E.g, The common name as the server name and the SAN name with the server IP address.
  5. Execute the command - openssl genrsa -passout pass:1111 -des3 -out server.key 4096
    (where 'pass:1111' is the PEM pass phrase and 'server.key' is the trust certificate key)
  6. Execute the command - openssl req -passin pass:1111 -new -key server.key -out server.csr
  7. Enter the required certificate details accordingly to create a server certificate. E.g, The common name as the server name and the SAN name with the server IP address.
  8. Execute the command - openssl x509 -req -passin pass:1111 -days 365 -in server.csr -CA rootca.crt -CAkey rootca.key -set_serial 01 -out server.crt

    9. Notes:
    1. Upon execution of the above command, the server.csr will be signed with the rootca.crt. To trust the certificates issued, add the rootca.crt to the respective trust store on the server.
    2.You can also name the private keys, certificates and csr on your own as needed.

    Note: If you are about to use a wildcard certificate for the PostgreSQL database, place the wildcard certificate along with the respective private key file in the <PostgreSQL Installation Directory>\data directory path.

2.2.1 Configuring the PostgreSQL Database Cluster to use the SSL

  1. Navigate to <PostgreSQL Installation Directory>\data and open the postgresql.conf file with the administrator privilege.
  2. Look out for the listen_addresses under the connection settings and edit/verify the parameter listen_addresses, as this allows connection from the external hosts.

    3. listen_addresses = '*'

  3. Search for the SSL section in the postgresql.conf and uncomment all the SSL parameters.
  4. Now, edit the SSL parameters with our certificate information as shown below.

    5. ssl = on
    ssl_ca_file = 'rootca.crt'
    ssl_cert_file = 'server.crt'
    ssl_crl_file = ''
    ssl_crl_dir = ''
    ssl_key_file = 'server.key'
    ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
    ssl_prefer_server_ciphers = on
    ssl_ecdh_curve = 'prime256v1'
    ssl_min_protocol_version = 'TLSv1.2'
    ssl_max_protocol_version = ''
    ssl_dh_params_file = ''
    ssl_passphrase_command = ''
    ssl_passphrase_command_supports_reload = off

    Note: If you are using a wildcard certificate for the PostgreSQL database, update the ssl_ca_file parameter with no attribute (ssl_ca_file='').

  5. Now, import the root CA certificate of the file PostgreSQL database cluster into PAM360.
  6. Now, restart the PostgreSQL database for the changes to get in effect.

1.3 SSL Connection Validation

To validate the SSL connection configuration either using a CA-signed or self-signed certificate, perform the following steps:

  1. Navigate to <PostgreSQL Installation Directory>\bin and open the command prompt with the administrator privilege.
  2. Execute the command - psql "postgresql://username@hostname/dbname?sslmode=require" Replace username, hostname, and dbname with your PostgreSQL cluster details.
    For example, psql "postgresql://postgres@127.0.0.1/postgres?sslmode=require"
  3. Now, enter the PostgreSQL database password that you have entered during the PostgreSQL database installation.
  4. Upon entering the password, you will be prompted with the below message for the successful SSL connection.
    pgsql-db
  5. Now, navigate to <PostgreSQL Installation Directory>\data and use the rootca.cert to import it into PAM360.
Top