Report Manager support for PostgreSQL in Cerberus FTP Server 11.2.5

Cerberus FTP Server Enterprise edition offers the Report Manager for collecting detailed usage statistics and generating detailed reports.

To use the Report Manager, administrators must first configure a reporting database. Cerberus integrates with a number of different database servers, and now in Cerberus FTP Server 11.2.5 we have added support for PostgreSQL version 10 and higher.

PostgreSQL works with many different ODBC drivers; however, Cerberus supports only the official Windows ODBC driver.

Setup

Cerberus requires Password authentication via ODBC, so it is important to ensure PostgreSQL’s Authentication Method is set accordingly. On Windows, PostgreSQL is configured to use MD5 Password authentication by default and the installer prompts for creating the ‘postgres’ superuser password. On Linux, this will vary by version & distribution with many defaulting to Peer or Ident, so administrators may need to edit pg_hba.conf and create a user (see Advanced Configuration below for an example).

PostgreSQL must also listen on an address that Cerberus can access, so administrators should check network connections to ensure the port and interface are correct; these may be adjusted in postgresql.conf.

On all platforms, ensure that firewall rules grant the Cerberus server access to the PostgreSQL port.

Once installed and configured, Cerberus can be configured by going to Reporting and selecting Database Configuration. A typical configuration might look like this:

ODBC Driver	PostgreSQL ODBC Driver(UNICODE)
Database Name	cerberus_stats
Database Server	postgres.example.com
Port	5432
Database Username	postgres
Database Password	strong-password-here

Once entered, click Connect to verify the configuration; if successful, click Create Tables to have Cerberus create the tables it requires. Toggle Enable Statistics Collection to On and click Update to save these changes.

We recommend that you open the Data Source Administrator – for 64-bit systems this will be “ODBC Data Sources (64-bit)” – and go to the “Connection Pooling” tab. Enable Pooling for the selected PostgreSQL driver; the default Pool Timeout of 60 seconds should be acceptable. Skipping this step may lead to performance and memory issues in some environments and usage configurations.

Advanced Configuration

If your organization or industry has access control requirements, sharing superuser credentials to Cerberus may be inappropriate. In this case, you can create a user & database manually to restrict Cerberus’ permissions a bit better:

• createuser -U postgres -P cerberus
  Enter the Role password for the ‘cerberus’ user. Then enter the password for the ‘postgres’ superuser creating the user.
• createdb -U postgres --encoding=UTF8 --lc-collate=C --owner=cerberus --template=template0 cerberus_stats
  Enter the password for the ‘postgres’ superuser creating the user.

Be sure to check that the interactive tool ‘psql’ can connect by issuing:
psql -U cerberus cerberus_stats

Back in Cerberus configuration, update the Database Configuration parameters with the ‘cerberus’ credentials, click Create Tables, toggle Enable Statistics Collection, and click Update to save the changes.