HOW TO: Configure SSL communication for the PostgreSQL database

Version 1

    Perform the following steps to configure SSL communication for the PostgreSQL database:

    1. Verify that openssl is installed on the machine.
    2. Stop the Process Server and the PostgreSQL database.
    3. Navigate to the following directory:
      <Secure Agent installation directory>\apps\process-engine\data\PostGreSql\Data
    4. Verify that the following Postgres server 3 certificates are present in the directory:
      1. root.crt (trusted root certificate)
      2. server.crt (server certificate)
      3. server.key (private key)
    5. If the certificates are not available, create your own self-signed server certificates as mentioned in steps 2.2 to 2.6 in the following URL: How to Enable SSL authentication for an EDB Postgres Advanced Server. | EDB
    6. Add the following parameters to the user.conf file present within the Data folder:
      ssl = on
      ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
      ssl_prefer_server_ciphers = on
      ssl_cert_file = 'server.crt'
      ssl_key_file = 'server.key'
      ssl_ca_file = 'root.crt'
      If the user.conf file is not present, create the file.
    7. Open the pg_hba.conf file and check whether the ipaddress of the client connecting to the Postgres server is already present.
      If the ipaddress is present, change the type from ‘host’ to ‘hostssl’, and then save the pg_hba.conf file.
      If the ipaddress is not present, add the following line, and then save the pg_hba.conf file:
      hostssl       all         all        <clientIp address>/32       [scram-sha-256 or password use the value that is already configured in the file]
    8. Start the Postgres server.
    9. Verify that SSL has been set up properly for the Postgres server. To do this, navigate to the following directory:
      Windows: <Secure Agent installation directory>\apps\process-engine\data\db\postgresql-windows-x64-binaries\pgsql\bin
      Linux: <Secure Agent installation directory>\apps\process-engine\data\db\postgresql-linux-x64-binaries\pgsql\bin
    10. Run the following command:
      psql.bat -U bpeluser -h <hostname configured as CN in the server certificate> -d activevos
      If SSL is set up correctly, you will see the following lines in the Console:
      SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
    11. Log in to Informatica Intelligent Cloud Services and click Administrator.
    12. Click Runtime Environments on the left navigation bar.
    13. In the Process Server configuration, update the database URL as follows:
      jdbc:postgresql://localhost:5432/activevos?ssl=true
    14. Copy the root.crt (trusted root certificate) from the server Data folder to the following directory on the client’s machine:
      For Windows: %appdata%\postgresql\ directory
      For Linux: ~/.postgresql directory
      If the directory does not exist already, create it.
    15. Start the Process Server.