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.
Kindly refer this PostgreSQL documentation for SSL Certificate generation and Basic setup : https://www.postgresql.org/docs/current/ssl-tcp.html
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.
For example, let us create a sample self-signed certificate using openSSL in a linux machine and use that in this example. 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 two files located at <postgres_installation_location>\data\ directory, as mentioned below. Before that, copy the server.key & server.crt generated to the same directory.
A. postgresql.conf file : To enable SSL configuration in Postgresql server.
Paste the following lines in the postgresql.conf file situated in the same 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:
create a folder at <product_installation_location>\ssl and copy root.crt into it.
3.2. Update database_params.conf:
Add the following to the end of "url" and "rodatasource.url" in database_params.conf. Please escape the filepath when specifying there.
- &ssl\=on&sslmode\=<ssl mode>&sslrootcert\=<absolute-path of root.crt>
For example,
- &ssl\=on&sslmode\=verify-full&sslrootcert\= C:\\Program Files\\ManageEngine\\ServiceDesk\\ssl\\cert.pem
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 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 ...How to install SSL certificate in AssetExplorer
SSL Installation Do you have a Wildcard or a Multi-domain certificate already running in your other servers and want to reinstall in on AssetExplorer server ?, then click here to find how to export SSL certificate using MMC. Do you already have a ...How to change the database from MSSQL to PostgreSQL
The steps mentioned below are applicable only if you migrate the DB from MSSQL to PgSQL. This means that the application server is going to remain the same. The data alone is going to be transferred to PgSQL DB from the existing MSSL DB. For ...How to connect to the ServiceDesk database?
Open command prompt on the server and change the directory to MangeEngine\ServiceDesk\bin, invoke the batch file 'changeDBServer.bat' For MSSQL database Connect to the query analyzer of the SQL server and execute the queries. For PostgreSQL database ...