Securing PostgreSQL Database Connection with SSL in ServiceDeskPlus
Overview
This article serves as a comprehensive guide for customers intending to secure their database connections when using with ServiceDeskPlus Application with PostgreSQL database, similar to securing Microsoft SQL Server (MSSQL) connections. The primary focus is on implementing SSL for added security, particularly in external PostgreSQL environments.
Key Considerations
- 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.
- This article uses self-signed certificates for testing purposes. In production, it is recommended to use certificates obtained from a Certificate Authority (CA).
- 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.
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.
- openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=<hostname.domain.com>"
- 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):
- 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:
- openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=<hostname.domain.com> "
- chmod og-rwx server.key
- 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.
- ssl = on
- ssl_key_file = 'server.key'
- 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,
- # TYPE DATABASE USER ADDRESS METHOD
- host all all 127.0.0.1/32 md5
to
- # TYPE DATABASE USER ADDRESS METHOD
- 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:
- &ssl\=on&sslmode\=verify-full&sslrootcert\=<absolute-path of root.crt>
Recommendations
- Use latest TLS version (Atleast 1.3 & above) due to its better performance.
- Use More Secure ciphers
- Employ principle of least privilege in Server Machines & Server Networks
References
- How to create a self signed certificate ? : link
- How to configure pg_hba.conf : link
- How to configure ssl parameters : link
New to ADSelfService Plus?
Related Articles
How to resolve Connectivity issues with Postgresql Database from our product?
Log Traces Feb 20, 2024 6:40:00 PM [SYSERR] [INFO] : java.sql.SQLException: java.lang.Exception: Exception during getConnection from pool Exception occurred during get connection from datasource Nov 15, 2023 1:29:10 PM [SYSERR] [INFO] : Caused by: ...How do I install SSL certificate for ServiceDeskPlus-MSP?
Introduction ServiceDesk Plus - MSP can run as a HTTPS service. But it requires a SSL (Secure Socket Layer) Certificate signed by a valid Certificate Authority (CA). By default, on a first-time start-up, it creates a self-signed certificate. This ...How to change the database from MySQL/PostgreSQL to MSSQL
The steps mentioned below are applicable only if you migrate the DB from PgSQL to MSSQL. This means that the application server is going to remain the same. The data alone is going to be transferred to MSSQL DB from the existing PgSQL DB. For ...SSL Installation
Do you have a Wildcard or a Multi-domain certificate already running in your other servers and want to reinstall in on ServiceDesk Plus server ?, then click here to find how to export SSL certificate using MMC. Do you already have a .PFX certificate ...Mail sending failed_ Unrecognized SSL message, plaintext connection?
Log error trace: Caused by: javax.mail.MessagingException: Could not connect to SMTP host: smtp.office365.com, port: 587; nested exception is: javax.net.ssl.SSLException: Unrecognized SSL message, plaintext connection? Solution: Error trace means ...