SSL connection for PostgreSQL communication

Step 1: Generate SSL certificate for PostgreSQL server

1) Generate a private key

openssl genrsa -aes256 -out server.key 2048

Sample output:

Generating RSA private key, 1024 bit long modulus ........++++++

................++++++

e is 65537 (0x10001)

Enter pass phrase for server.key:

Verifying - Enter pass phrase for server.key:

Type your desired password / pass phrase to encrypt the private key

2) Remove the pass phrase to automatically start up the PostgreSQL server

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

chmod og-rwx server.key

3) Create a self-signed certificate

openssl req -new -key server.key -days 3650 -out server.crt -x509

You will be prompted to enter detailed information which is incorporated into your self-signed certificate request.

Country Name (2 letter code) [XX]:US

State or Province Name (full name) []:CA

Locality Name (eg, city) [Default City]:San Francisco

Organization Name (eg, company) [Default Company Ltd]:OPSWAT

Unit Name (eg, section) []:OPSWAT

Common Name (eg, your name or your server's hostname) []:localhost

Email Address []:dummy_email@opswat.com

For self-signed certificate, use the server ceriticate as the trusted root certificate:

cp server.crt root.crt

Copy server.key, server.crt, root.crt to PostgreSQL data folder. For bundled local MetaDefender Core's PostgreSQL:

• Windows: <Installation folder>\data\pg_data\
• Linux: /var/lib/ometascan/pg_data/

Step 2: Configure PostgreSQL server for SSL authentication connection

1) Create a custom config file (e.g. ssl.conf) for PostgreSQL.

For bundled local MetaDefender Core's PostgreSQL:

• Windows: <Installation folder>\postgres\ssl.conf
• Linux: /usr/lib/ometascan/postgres/ssl.conf

2) Content of ssl.conf:

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'

3) Modify pg_hba.conf

For bundled local MetaDefender Core's PostgreSQL:

• Windows: <Installation folder>\data\pg_data\pg_hba.conf
• Linux: /var/lib/ometascan/pg_data/pg_hba.conf

Add following (modify ::1/128 if needed to match with your environment requirement)

hostssl all all      ::1/128      scram-sha-256

After the modify, the content of pg_hba.conf should be like this (example):

# PostgreSQL Client Authentication Configuration File

# ===================================================

#

# Refer to the "Client Authentication" section in the PostgreSQL

# documentation for a complete description of this file.  A short

# synopsis follows.

#

# This file controls: which hosts are allowed to connect, how clients

# are authenticated, which PostgreSQL user names they can use, which

# databases they can access.  Records take one of these forms:

#

# local         DATABASE  USER  METHOD  [OPTIONS]

# host          DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# hostssl       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# hostnossl     DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# hostgssenc    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# hostnogssenc  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

local    all all                   password/scram-sha-256

host     all all      ::1/128      password/scram-sha-256

hostssl  all all      ::1/128      password/scram-sha-256

​

#More content.......

4) Reload the services

• Standalone DB mode: Restart ometascan service (MetaDefender Core service)
• Shared DB mode: Restart ometascan-pg/ometascan-postgresql service (MetaDefender Core PostgreSQL service) and then ometascan service (MetaDefender Core service)

Test your SSL database connection:

psql.exe "sslmode=require dbname=postgres user=postgres host=localhost port=5432"

Password for user postgres:

psql (12.7)

WARNING: Console code page (437) differs from Windows code page (1252)

         8-bit characters might not work correctly. See psql reference

         page "Notes for Windows users" for details.

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

Type "help" for help.

This message indicate that you are now using SSL connection:

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)