SSL communication settings for connecting EAInfoport and PostgreSQL database

Question

How to set up SSL communication for connecting EAInforport and PostgreSQL database?

Answer

The following procedure is verified on a PostgreSQL 13 and Infoport 8.7.x installation.

A detailed description of the database setup is provided directly in the current documentation https://www.postgresql.org/docs/current/ssl-tcp.html which we recommend to study carefully. In addition, https://methoddev.com/blog/postgresql-windows-encrypted-connection-ssl provides a step-by-step procedure on how to configure the database in practice.

To verify that the communication is secure, it is advisable to use the following sql query that can be run on the database:

SELECT application_name, datname, usename, ssl, client_addr

FROM pg_stat_ssl

INNER JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid;

The result will show if the session (a specific application connection - e.g. EAInfoport) is secured by ssl. It is recommended to check this both before starting any changes to the settings and after they are completed.

EAInfoport settings are (for now - later will be part of Configuration Manager) done using the appsettings.json configuration file. Here you need to add the value "InfoportConnection_Npgsql" with ";ApplicationName=Infoport;SSL Mode=VerifyCA". This setting means that EAInfoport will require an ssl connection to the database, and will check that a trusted certificate exists.

Note: if the db server is set to require ssl (=reject connection without ssl) but the connection string of EAInfoport is set to ";SSL Mode=Disable", the EAInfoport log will show a message like "28000: pg_hba.conf rejects connection for host "x.x.x", user "xx", database "xx", SSL off".

---

EAInfoport version 7.x has not been developed or tested for ssl connections, but the following can be tested:

Since version 6 (.NET - not the database) the parameter variations for ssl have been changed (see also Security and Encryption | Npgsql Documentation)

Npgsql v6.0+:

Disable
Allow
Prefer (default)
Require
VerifyCA
VerifyFull
Npgsql pre-v6.0:

Disable (default)
Prefer
Require
For .NET version 2 (= for EAInfoport 7.x) it is possible to try ';SSL Mode=Prefer' as a parameter or add that I trust the certificate, i.e. ";SSL Mode=Prefer;Trust Server Certificate=true"

The last option is to replace Prefer with Require