2.2.3. Installing and Configuring PostgreSQL

The following sections outline how ot install and configure PostgreSQL on Windows.

2.2.3.1. PostgreSQL Installation

PostgreSQL can be installed in a variety of ways, including with installers, package managers, or containers such as Docker.

We recommend you:

  • Download a v17 Windows installer

  • Run it on the server and choose the default settings.

  • If it prompts you about StackBuilder, you do not need it.

  • Keep track of the Superuser Password. You’ll need this later.

Note

  • Data Folder: The default is inside of the PostgreSQL installation folder. You should make sure this gets backed up.

At the end of this process, PostgreSQL will be running as a service using the Network Service account.

2.2.3.2. PostgreSQL Configuration

There are several settings that must be changed from the default installation. After you have made these changes, you should reboot the system for them to all take effect.

2.2.3.2.1. Create Database User

A special user will need to be created for the Cyber Triage® Server to access the database. We’ll use the name cyber_triage.

  1. From a Windows Command Prompt run:

    C:\Program Files\PostgreSQL\13\bin\psql -U postgres postgres

  2. You will be prompted for the superuser password you entered during the installation.

  3. You will next get a postgres=# prompt. To create the user enter the following code below and replace ChangeMeASAP with a real password.
    Ensure you record the password because you will need to enter it into the Cyber Triage® server.

    CREATE ROLE cyber_triage PASSWORD 'ChangeMeASAP' CREATEDB LOGIN;

  4. Type \q to exit prompt.

2.2.3.2.2. Basic Configuration Changes

There are several settings that we suggest making to improve performance and enable logging to make debugging problems easier.

Note

These are all oriented around a Windows installation and a Linux deployment have other settings that will make it more optimal.

Open the following file in a text editor:

C:\Program Files\PostgreSQL\13\data\postgresql.conf

The following settings should be changed or uncommented (by removing the leading #):

  • Edit the maximum number of connections based on how many clients you have. We recommend 185 plus 20 for each client. So a 3 client deployment would have 185 + (3 * 20) = 185 + 60 = 245.
    max_connections = 245
  • Increase the value of the buffer setting:
    shared_buffers = 512MB
  • Enable huge_pages by uncommenting this line:
    huge_pages = try
  • Uncomment the following performance-oriented lines and change the default values:
    temp_buffers = 80MB
shared_memory_type = windows
fsync = off
synchronous_commit = off

  • Uncomment and change the effective cache size based on the amount of RAM available. We recommend the value be 50% of the total RAM.

    effective_cache_size = 16GB
  • Uncomment the following log-oriented lines and change the default values:
    log_min_duration_statement = 300
log_lock_waits = on

This file also allows you to restrict access to the database from other hosts.

  • If PostgreSQL is on the same host as the Cyber Triage® server, then edit the listen_address line to the following:
    listen_addresses = 'localhost'
  • If PostgreSQL is on a different host, then confirm that the line is:
    listen_addresses = '*'

2.2.3.2.3. Lock Pages in Memory Setting

If you are running PostgreSQL on a Windows system, we recommend that you also enable the Lock Pages in Memory setting for the Windows user that the service will be running as. By default, this is the Network Service account.

  1. Open the Local Group Policy Editor

  2. Navigate to Local Computer Policy -> Windows Settings -> Security Settings -> Local Policies -> User Rights Assignment

  3. Select the Lock pages in memory item and double click on it.

../../_images/7_2.jpg

Local Group Policy Editor

  1. Add the user that the PostgreSQL service will be running as (i.e. Network Service)

2.2.3.2.4. Dedicated PostgreSQL Server Changes

If you are running PostgreSQL Server on a system different from the Cyber Triage® server, you’ll need to make an additional change.

Open the following in a text editor:

C:\Program Files\PostgreSQL\13\data\pg_hba.conf

Add a rule to the bottom similar to:

host all cyber_triage 10.10.10.10/32 scram-sha-256

Update the above rule based on:

  • IP Address of the Cyber Triage® Server

  • The password_encryption value in the postgresql.conf file.

2.2.3.2.5. Restart The Host

After all the above configuration changes, restart the computer so that the service and database settings take effect.

Before moving on to further configurations, you should test the PostgreSQL speed, as outlined in Testing PostgreSQL Speed.

2.2.3.3. Testing PostgreSQL Speed

To make sure your PostgreSQL server has enough resources and is on fast hardware, we recommend you measure its performance using pgbench. We have found this is much more reliable than focusing on hardware specs.

The following should be done after PostgreSQL is installed.

  1. Setup the test by running the following. It will prompt you for the superuser password you entered during the installation.
    C:\Program Files\PostgreSQL\13\bin\pgbench -U postgres -i -s 50 postgres
  2. Run the test:
    C:\Program Files\PostgreSQL\13\bin\pgbench -U postgres -c 5 -j 2 -t 1000 postgres

Note

The final values may change in between runs, so you might want to run it a few times.

We recommend that your PostgreSQL installation is able to get at least 4250 transactions per second (TPS). If you cannot, then either obtain a larger server or move PostgreSQL to its own server. SSD drives also help.

2.2.3.4. Encrypting Network Traffic with SSL

You can ensure the data between the server and database is encrypted.

To enable encryption on the database, use the PostgreSQL Instructions

To prevent man in the middle attacks, you should configure the Cyber Triage Server to know about the PostgreSQL certificates and verify them. To do so:

  • Copy the PostgreSQL server certificate (server.crt) to the Cyber Triage Server.

  • Locate the trust store file that is in the Cyber Triage data folder. An example would be:

    C:\Users\USER_NAME\AppData\Local\cybertriage\customer_certs\ctTruststore.jks

  • Import the certificate using the ‘keytool’ program that is part of Java and ships with Cyber Triage. This should all be run on the server from a command prompt:

    > cd c:\Program Files\CyberTriage\jre\bin
> keytool -keystore "C:\Users\USER_NAME\AppData\Local\cybertriage\customer_certs\ctTruststore.jks" -alias postgres_ca -import -file "\path\to\server.crt"

Replace C:UsersUSER_NAMEAppDataLocalcybertriagecustomer_certsctTruststore.jks with the actual path to your Cyber Triage customer certificate truststore. Replace pathtoserver.crt with the actual path to the server certificate file.

The password for Cyber Triage customer certificate truststore can be found by opening Cyber Triage on the Team Server machine, and Navigating to Options -> Certificate Info -> TLS Store Config. NOTE: The customer certificate trusttore password is only displayed when Cyber Triage is configured to run in “Team - Server” mode in Options -> Deployment Mode.

Type in yes when prompted Trust this certificate? [no]:. You should see command line output stating Certificate was added to keystore.

2.2.3.5. Debugging PostgreSQL SSL Issues

If you have errors connecting with SSL, then follow these steps:

  • First verify that you can successfully connect to PostgreSQL with SSL disabled. If that “Test Connection” fails, then review:

    • The PostgreSQL is running.

    • The client IP address range settings in pg_hba.conf

    • Firewall settings. You can try to temporarily disable firewall for debugging purposes.

  • Once you can connect without SSL, then enable SSL, but do not verify the certificate. If that fails, then check:

    • Configuration: Your PostgreSQL configuration files, especially postgresql.conf and pg_hba.conf. Ensure that the file paths for SSL certificate and key are correct and there are no typos or missing files.

    • Permissions: Make sure the PostgreSQL service account has proper permissions to access the certificate and key files. The account running the PostgreSQL service needs read access to these files.

    • Port Conflict: If you configured PostgreSQL to use SSL on a different port (e.g., 5432 for non-SSL and 5433 for SSL), verify that the specified port is available and not in use by other services.

    • Windows Firewall: Check if the Windows Firewall or any other firewall software is blocking the specified SSL port. Make sure the firewall rules allow incoming connections on the SSL port.

    • Certificate Validity: Ensure that the self-signed certificate is still valid. Self-signed certificates usually have a limited validity period, and if it has expired, PostgreSQL may fail to start. You can check the certificate’s validity using the openssl x509 command.

    • Log Files: Review the PostgreSQL log files for more detailed error messages. The log files are typically located in the PostgreSQL data directory. They can provide insight into what specifically caused the service to fail to start.

    • System Event Viewer: Check the Windows Event Viewer for error messages related to the PostgreSQL service. It may provide additional information about the startup failure.

    • Service Account Permissions: Ensure that the service account used to run the PostgreSQL service has the necessary permissions to access PostgreSQL data and configuration files.

  • If you want to verify the certificate and it fails only when that is enabled, then check:

    • Verify that the PostgreSQL server certificate was added to the truststore that is packaged in the Cyber Triage installation directory, NOT the system truststore.

While debugging, you can also use PgAdmin. - Open PgAdmin and connect to Posgres database. - Query the pg_stat_ssl view to check the SSL status for active connections SELECT * FROM pg_stat_ssl. This view provides information about the SSL state of client connections to the PostgreSQL server. This query will show you details about each SSL connection, including the SSL version, cipher, and whether SSL is enabled for each connection.

2.2.3.6. Upgrading PostgreSQL

While we do not want to duplicate any official PostgreSQL documentation, we want to provide some key points for upgrading PostgreSQL. The process is different depending on if it is a major PostgreSQL update or minor.

Updates to the minor versions of PostgreSQL are straightforward:

  • Stop the Cyber Triage Server.

  • Uninstall the existing PostgreSQL version

  • Install the new version

  • Confirm that the changes made in Basic Configuration Changes are still there. If not, reapply them.

Major version updates require the database files to be upgraded as well. The basic steps would be:

  • Stop the Cyber Triage Server.

  • Install the new major version. It will go into a new folder.

  • This new installation needs to be configured since it is in a new folder. Refer to the steps in Basic Configuration Changes.

  • Migrate the data over using the steps outlined on the PostgreSQL site.