Enable TLS for PostgreSQL Connection

Remote PostgreSQL

Linux

Step 1: Install PostgreSQL and OpenSSL

For Ubuntu/Debian:

sudo apt update

sudo apt install postgresql postgresql-contrib openssl

For CentOS/RHEL:

sudo yum install postgresql postgresql-server postgresql-contrib openssl

Step 2: Create SSL Certificates

To enable TLS, you need a certificate and key for the server, and you can also create a certificate authority (CA) to sign the server certificate.

2.1 Create the CA Key and Certificate

openssl genrsa -des3 -out ca.key 4096

openssl req -new -x509 -days 365 -key ca.key -out ca.crt

• ca.key: Private key for the Certificate Authority.
• ca.crt: Public certificate for the Certificate Authority.

2.2 Create the Server Key and Certificate Signing Request (CSR)

openssl genrsa -out server.key 4096 

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

• server.key: Private key for the PostgreSQL server.
• server.csr: Certificate Signing Request (CSR) for the PostgreSQL server.

2.3 Sign the Server Certificate with the CA

openssl x509 -req -in server.csr \

-CA ca.crt -CAkey ca.key \

-CAcreateserial -out server.crt -days 365

• server.crt: Signed server certificate.

2.4 Remove the Passphrase from the Server Key

openssl rsa -in server.key -out server.key

Now you should have the following files:

• ca.crt
• server.crt
• server.key

Step 3: Configure PostgreSQL to Use SSL

You need to place the certificates and keys in the appropriate directory and update the PostgreSQL configuration to enable SSL.

3.1 Copy Certificates to PostgreSQL Data Directory

Copy the server.crt, server.key, and ca.crt files to PostgreSQL's data directory, usually located at the output of the below CLI

sudo -u postgres psql -c 'SHOW data_directory;'

The output is: /var/lib/postgresql/16/main

sudo cp server.crt server.key /var/lib/postgresql/16/main

sudo cp ca.crt /var/lib/postgresql/16/main

Ensure the correct permissions and ownership:

sudo chown postgres:postgres \

/var/lib/postgresql/16/main/server.crt \

/var/lib/postgresql/16/main/server.key \

/var/lib/postgresql/16/main/ca.crt

sudo chmod 600 /var/lib/postgresql/16/main/server.key

3.2 Update postgresql.conf

Edit the postgresql.conf file to enable SSL. The configuration file is usually located in the PostgreSQL data directory.

/etc/postgresql/16/main/conf.d/postgresql.conf

Set the following parameters:

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='ca.crt'

Replace <version> with your PostgreSQL version number.

3.3 Update pg_hba.conf

Edit the pg_hba.conf file to enforce SSL connections. The configuration file is usually located in the PostgreSQL data directory.

sudo nano /etc/postgresql/16/main/pg_hba.conf

Add or modify the following lines to require SSL connections:

# Require SSL for all connections 

hostssl all all 0.0.0.0/0 md5 

hostssl all all ::/0 md5

Step 4: Restart PostgreSQL

Restart the PostgreSQL service to apply the changes.

sudo systemctl restart postgresql

Step 5: Verify the SSL Setup

To verify that SSL is enabled and working, you can connect to your PostgreSQL server using psql or any other client with SSL support.

psql "host=localhost dbname=postgres user=postgres password=a sslmode=require"

Additionally, you can check if SSL is active by querying the server:

SHOW ssl;

It should return on if SSL is enabled.

Windows

Step 1: Download and install the Postgresql Server for Windows OS

Step 2: Create SSL Certificates:

Download OpenSSL for the Windows: https://slproweb.com/download/Win64OpenSSL-3_3_2.msi

Download dependency for OpenSSL: https://aka.ms/vs/17/release/vc_redist.x64.exe and install it now

After installed the OpenSSL on your system, please help add the OpenSSL to SYSTEM PATH via cli:

[Environment]::SetEnvironmentVariable("Path", $env:Path + ";C:\Program Files\OpenSSL-Win64\bin", [EnvironmentVariableTarget]::Machine)

Open a new Powershell windows

openssl --version

To enable TLS, you need a certificate and key for the server, and you can also create a certificate authority (CA) to sign the server certificate.

2.1 Create the CA Key and Certificate

Create a directory to store the certificate:

mkdir -p certs

openssl genrsa -des3 -out ca.key 4096

openssl req -new -x509 -days 365 -key ca.key -out ca.crt

• ca.key: Private key for the Certificate Authority.
• ca.crt: Public certificate for the Certificate Authority.

2.2 Create the Server Key and Certificate Signing Request (CSR)

openssl genrsa -out server.key 4096 

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

• server.key: Private key for the PostgreSQL server.
• server.csr: Certificate Signing Request (CSR) for the PostgreSQL server.

2.3 Sign the Server Certificate with the CA

openssl x509 -req -in server.csr \

-CA ca.crt -CAkey ca.key \

-CAcreateserial -out server.crt -days 365

• server.crt: Signed server certificate.

2.4 Remove the Passphrase from the Server Key

openssl rsa -in server.key -out server.key

Now you should have the following files:

• ca.crt
• server.crt
• server.key

Step 3: Configure PostgreSQL to Use SSL

3.1 Copy Certificates to PostgreSQL Data Directory

Copy these files above to the C:\Program Files\PostgreSQL\15\data

3.2: Update postgresql.conf

Edit C:\Program Files\PostgreSQL\15\datapostgresql.conf file to enable SSL. The configuration file is usually located in the PostgreSQL data directory.

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='ca.crt'

3.3: Update pg_hba.conf

Edit the C:\Program Files\OPSWAT\Metadefender ICAP Server\data\pg_data\pg_hba.conf file to enforce SSL connections. The configuration file is usually located in the PostgreSQL data directory.

## Add these lines at the end of the file

hostssl all all 0.0.0.0/0 md5 

hostssl all all ::/0 md5

Step 4: Restart the Postgresql service

Open the tab Service on the Task Manager to find the Postgresql service

Step 5: Verify the SSL Setup

psql "host=localhost dbname=postgres user=postgres password=a sslmode=require port=5432"

Local PostgreSQL

Linux

Step 1: Installed MD ICAP Server with local mode

Refer: https://docs.opswat.com/mdicap/installation/installation-icap-v520#21without-ignition-file

Step 2: Create SSL Certificates:

Same as step 2: Create SSL Certificate at Linux

Step 3: Configure PostgreSQL to Use SSL

3.1 Copy Certificates to PostgreSQL Data Directory

sudo cp server.crt server.key /var/lib/mdicapsrv/pg_data/

sudo cp ca.crt /var/lib/mdicapsrv/pg_data/

3.2: Update postgresql.conf

Edit the /usr/lib/mdicapsrv/postgres/postgresql.conf file to enable SSL. The configuration file is usually located in the PostgreSQL data directory.

sudo nano /usr/lib/mdicapsrv/postgres/postgresql.conf

Set the following parameters:

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='ca.crt'

3.3: Update pg_hba.conf

Edit the /var/lib/mdicapsrv/pg_data/pg_hba.conf file to enforce SSL connections. The configuration file is usually located in the PostgreSQL data directory.

sudo nano /var/lib/mdicapsrv/pg_data/pg_hba.conf

Add or modify the following lines to require SSL connections:

## Add these lines at the end of the file

hostssl all all 0.0.0.0/0 md5 

hostssl all all ::/0 md5

Step 4: Restart the mdicapsrv service

sudo systemctl restart mdicapsrv

Step 5: Verify the SSL Setup

psql "host=localhost dbname=postgres user=postgres password=a sslmode=require port=5433"

Windows

Step 1: Installed MD ICAP Server with local mode

Step 2: Create SSL Certificates

Download OpenSSL for the Windows: https://slproweb.com/download/Win64OpenSSL-3_3_2.msi

Download dependency for OpenSSL: https://aka.ms/vs/17/release/vc_redist.x64.exe and install it now

After installed the OpenSSL on your system, please help add the OpenSSL to SYSTEM PATH via cli:

[Environment]::SetEnvironmentVariable("Path", $env:Path + ";C:\Program Files\OpenSSL-Win64\bin", [EnvironmentVariableTarget]::Machine)

Open a new Powershell windows

openssl --version

To enable TLS, you need a certificate and key for the server, and you can also create a certificate authority (CA) to sign the server certificate.

2.1 Create the CA Key and Certificate

Create a directory to store the certificate:

mkdir -p certs

openssl genrsa -des3 -out ca.key 4096

openssl req -new -x509 -days 365 -key ca.key -out ca.crt

• ca.key: Private key for the Certificate Authority.
• ca.crt: Public certificate for the Certificate Authority.

2.2 Create the Server Key and Certificate Signing Request (CSR)

openssl genrsa -out server.key 4096 

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

• server.key: Private key for the PostgreSQL server.
• server.csr: Certificate Signing Request (CSR) for the PostgreSQL server.

2.3 Sign the Server Certificate with the CA

openssl x509 -req -in server.csr \

-CA ca.crt -CAkey ca.key \

-CAcreateserial -out server.crt -days 365

• server.crt: Signed server certificate.

2.4 Remove the Passphrase from the Server Key

openssl rsa -in server.key -out server.key

Now you should have the following files:

• ca.crt
• server.crt
• server.key

Step 3: Configure PostgreSQL to Use SSL

3.1 Copy Certificates to PostgreSQL Data Directory

Copy these files above to the C:\Program Files\OPSWAT\Metadefender ICAP Server\data\pg_data

3.2: Update postgresql.conf

Create C:\Program Files\OPSWAT\Metadefender ICAP Server\postgres\postgresql.conf file to enable SSL. The configuration file is usually located in the PostgreSQL data directory.

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='ca.crt'

3.3: Update pg_hba.conf

Edit the C:\Program Files\OPSWAT\Metadefender ICAP Server\data\pg_data\pg_hba.conf file to enforce SSL connections. The configuration file is usually located in the PostgreSQL data directory.

## Add these lines at the end of the file

hostssl all all 0.0.0.0/0 md5 

hostssl all all ::/0 md5

Step 4: Restart the mdicapsrv service

Open the tab Service on the Task Manager to find the mdicapsrv service

Step 5: Verify the SSL Setup

psql "host=localhost dbname=postgres user=postgres password=a sslmode=require port=5433"