Overview

1. The instructions are tested on PostgreSQL versions 11.17 and 15.2. SSL configuration may vary based on your PostgreSQL version. Refer to the official PostgreSQL documentation (https://www.postgresql.org/docs/current/ssl-tcp.html) for version-specific details. 
   
2. This article uses self-signed certificates for testing purposes. In production, it is recommended to use certificates obtained from a Certificate Authority (CA).
   
3. Process Overhead : Configuring SSL for database connections introduces an additional layer of encryption and decryption processes, which may result in increased computational overhead. Before implementing SSL, it is crucial to assess the capabilities of the server machines to ensure that they can effectively handle this increased workload without compromising performance.

Procedure 

1. Certificate Generation

ProTip : Before generating certificates, determine the desired `sslmode` through which you wish to connect. This allows you to control the desired level of security and protection. Make use of the below chart for selecting appropriate mode suitable for you.

                                                                        

                                                                           Source : postgresql.org

In this article, we are going to configure ssl in "verify-full" mode which is the most secure mode of connecting ssl. For using database in the same server as that of application, that allows only local connection, will not require this high level of secure mode, for that case "require" mode will be more suitable.

While a self-signed certificate can be used for testing (as shown below), a certificate signed by a certificate authority (CA) (usually an enterprise-wide root CA) should be used in production.

To create a server certificate whose identity can be validated by clients, first create a certificate signing request (CSR) and a public/private key file. Replace <hostname.domain.com> with your respective server host name. Generate a certificate from the machine where PostgreSQL server is hosted.

1. openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=<hostname.domain.com>"
2. chmod og-rwx root.key
   

Then, sign the request with the key to create a root certificate authority (using the default OpenSSL configuration file location on Linux):

1. openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/ssl/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt
   

Finally, create a server certificate signed by the new root certificate authority:

1. openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=<hostname.domain.com> "
   
2. chmod og-rwx server.key
   
3. openssl x509 -req -in server.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt
   

server.crt and server.key should be stored on the PostgreSQL server machine and root.crt should be stored on the client machine in which our application ServiceDeskPlus is installed.

2. Configure PostgreSQL Server for SSL

This section consist of configuring file located at <postgres_installation_location>\data\ directory, as mentioned below. Before that, copy the server.key & server.crt generated to a directory in which the user has permission to read and write. Enter the absolute path of the files in the below configuration file.

A. postgresql.conf file : To enable SSL configuration in Postgresql server.

          Paste the following lines in the postgresql.conf file situated in the above directory.

1. ssl = on
2. ssl_key_file = 'server.key'
3. ssl_cert_file = 'server.crt'

If you are using the PostgreSQL server bundled with our product, PostgreSQL installation location will be at <product_installation_location>\pgsql directory. Before migrating the product, backup the postgresql.conf file since the configurations in this file will not be retained in our application migration.

B. pg_hba.conf:  Change the connection type to "hostssl" mode and restrict the unencrypted connection requests

            Change the "TYPE" of all the connections to "hostssl" mode as shown in the below example.

From,

1. # TYPE  DATABASE        USER            ADDRESS                 METHOD
2. host    all             all             127.0.0.1/32            md5

to

1. # TYPE  DATABASE        USER            ADDRESS                 METHOD
2. hostssl    all             all             127.0.0.1/32            md5     

                Restart the PostgreSQL server after making these changes.

We recommend using `md5` authentication for postgres user authentication, since we have employed that algorithm. 

ProTip : you can check whether the ssl settings & other details with this query     

SELECT * FROM pg_settings WHERE name = 'ssl';

3. Configure Application to Connect with SSL

3.1. Copy Root Certificate:

                  Copy root.crt to <product_installation_location>\ssl folder.                  

3.2. Update database_params.conf:

                  Add the following to the end of "url" and "rouser_url" in database_params.conf:

1. &ssl\=on&sslmode\=verify-full&sslrootcert\=<absolute-path of root.crt>

Recommendations

1. Use latest TLS version (Atleast 1.3 & above) due to its better performance.
2. Use More Secure ciphers
   
3. Employ principle of least privilege in Server Machines & Server Networks