PostgreSQL database

Starting with version 25.2, Connect exclusively supports PostgreSQL as the database, and Derby is officially deprecated. PostgreSQL offers significant advantages including better performance, improved scalability, and enhanced reliability for enterprise environments.

PostgreSQL system requirements

The database can be installed on the same server as Connect, or on a dedicated server.

If PostgreSQL is installed on the same server as Connect, the requirements for Connect include adequate capabilities for PostgreSQL.

If PostgreSQL is installed on a dedicated server, there are no official minimal system requirements for PostgreSQL. We recommend having at least the following:

  • 1 GHz processor

  • 2 GB of RAM

  • 512 MB of disk space

Back to top

Download and install PostgreSQL

Note: The currently supported PostgreSQL version is v16.

To download and install PostgreSQL, go to the PostgreSQL download page and select your operating system. For installation instructions, refer to the PostgreSQL documentation.

After installation, verify that you can connect to PostgreSQL to ensure that the connection is active. You can do this by running the following command:

`psql -h <hostname> -p <port> -U <username> -d <database>`

If you encounter any errors, refer to the PostgreSQL documentation for troubleshooting.

Back to top

Create database manually

When installing Connect, if you provide superuser credentials the database is created automatically by the installer.

If you want to manually create a PostgreSQL database that is compatible with Connect, execute the following SQL queries:

  1. Create a new user which will be used by Connect:

    CREATE USER <username>
    WITH LOGIN PASSWORD '<password>';

  2. Create the new database:

    CREATE DATABASE <databaseName>
    WITH OWNER = <username>
    ENCODING = 'UTF8'
    TEMPLATE = 'template0';

  3. Connect to the created database with the new user.

  4. Create the new schema:

    CREATE SCHEMA IF NOT EXISTS <schemaName>
    AUTHORIZATION <username>;

Back to top

Database connection configuration

You configure the connection to the database through a configuration file. This file defines the parameters required to establish a connection to the database. The database connection configuration file is located at Connect\AppData\data\database.properties.

This table provides the formatting information for the file's parameters.

Parameter Description
schema The default schema to use for the database.
database The name of the database to which the application will connect.
password

The encoded password for the database user.

Note: The password can be encrypted using the mfcEncryptPassword utility. For more details, see Secret encryption.

backup The backup location. (This option is currently disabled.)
port The port number on which the database server is listening.
vendor The database vendor. Currently, only the POSTGRES option is supported.
host The hostname or IP address of the database server.
user The username used to connect to the database.
ssl.mode The SSL mode for the database connection. The officially supported values for this property are disable and verify-full.

Back to top

Secure the connection

This section describes how to secure the connection between Connect and the database server. You can secure the connection from the Connect server to the PostgreSQL database server with encryption and two-way authentication.

The first step in implementing a secure connection is enabling SSL on the PostgreSQL server. For details, see the instructions on how to enable SSL for PostgreSQL in the PostgreSQL documentation.

To secure your connection with the PostgreSQL server:

  1. Obtain a PostgreSQL Server SSL/TLS certificate from your administrator.

  2. Configure the security properties for the connection from the Connect server to the PostgreSQL server in the Connect\AppData\data\database.properties configuration file by setting the ssl.mode property. For encrypted database connections, verify-full is the only supported mode. For details, refer to the PostgreSQL documentation for the sslmode JDBC property.

  3. Establish a trust relationship between Connect and the PostgreSQL server SSL certificate. For details, see Install SSL certificates.

  4. If the PostgreSQL server is configured to validate client certificates, perform the following steps:

    1. Obtain a Connect key and certificate for the connection to the PostgreSQL server.

    2. Establish a trust relationship between the PostgreSQL server and the Connect server certificate. For details, refer to the PostgreSQL documentation that describes client certificate validation.

    3. Prepare the PKCS12 keystore with the PostgreSQL client key and certificate.

    4. Run the Connect server with JVM options that specify a keystore with the Connect server key for the connection with the PostgreSQL server:
      -Djavax.net.ssl.keyStore=/path/to/KeyStore
      -Djavax.net.ssl.keyStorePassword=KeyStorePassword

By following these steps, you can ensure a secure and encrypted connection between the Connect server and the PostgreSQL database server.

Back to top

Troubleshooting

For remote PostgreSQL deployments, where the PostgreSQL database and Connect are on different machines, you may receive the following exception:

com.connect.api.exceptions.DBException: org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "192.168.65.1", user "me", database "postgres", no encryption

This indicates that the PostgreSQL server is not configured for remote access. Refer to the section in the PostgreSQL 16 documentation about remote TCP/IP connections. This section indicates that you need to set a value for the listen_addresses parameter.

To fix the problem:

  1. In the postgresql.conf file, add a line with listen_addresses = '*'

  2. In the pg_hba.conf file, add

    host	all		all		127.00.1/32		scram-sha-256  

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

    host	all		all		0.0.0.0/0		scram-sha-256

See also: