SSL connection for PostgreSQL communication

Note

The SSL connection configuration is possible thanks to the support made by PostgreSQL. For reference: https://www.postgresql.org/docs/13/ssl-tcp.html

MetaDefender Core is tested against this mode on particular version 4.21.1

Generate SSL certificate for PostgreSQL server

Note

Following instructions are for self-sign certificate. For production environment, making sure to use the certificate signed by your trusted CA.

  1. Generate a private key

openssl genrsa -des3 -out server.key 1024

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

  1. Remove the pass phrase to automatically start up the PostgreSQL server

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

On Linux, make sure to set permission on the server.key file

chmod og-rwx server.key

  1. 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/

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:

    1. Windows: <Installation folder>\postgres\ssl.conf

    2. Linux: /var/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'

  1. 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 md5

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

Note

Commenting out all lines starting with "host" if you only want SSL connections to your PostgreSQL.

# 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] hostssl all all ::1/128 md5 #More content.......

  1. 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)


null
On This Page
SSL connection for PostgreSQL communicationGenerate SSL certificate for PostgreSQL serverConfigure PostgreSQL server for SSL authentication connection