PostgreSQL Implementation Details

This article outlines the basic implementation details for using PostgreSQL as the database management system for Lasernet Keep. This guide is intended for technical users who are already familiar with PostgreSQL’s functionality and have the software installed.

For more information, consult PostgreSQL’s documentation.

Prerequisites

To ensure any tables, indexes, and permissions used specifically for the Keep application are operationally separate from any existing data in your PostgreSQL database, we recommend creating the following:

A new connecting user
A new database (optional)
A new schema

All steps should be completed while connected as an existing admin user. By default, this is the postgres user, but the name may vary based on what was chosen at install time.

Create a User

To create a new connecting user, enter the following command within the PostgreSQL terminal:

CREATE ROLE keep_app 
  LOGIN PASSWORD '<strong-password>' 
  NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;

Note
The command above explicitly specifies the NOSUPERUSER, NOCREATEDB, NOCREATEROLE, NOREPLICATION, and NOBYPASSRLS permissions, as you may wish to change them at creation time.
However, they are the default options, so they are omitted when using the following shorter command:
CREATE USER keep_app WITH PASSWORD '<strong-password>';

Create a Database

Creating a bespoke database for the Keep app is optional but advisable, as it ensures that any components used specifically for Keep are operationally separate from existing data in PostgreSQL.

To create a new database, enter the following commands within the PostgreSQL terminal:

CREATE DATABASE keepdb OWNER keep_app; 

REVOKE ALL ON DATABASE keepdb FROM PUBLIC; 

GRANT CONNECT ON DATABASE keepdb TO keep_app;

Note
If you choose to create a new database for the Keep app, you must disconnect from the previous database and reconnect to the new one before continuing with the steps below.

Create a Schema

Once you have connected to the database you will use for the Keep app, we recommend creating a separate schema as well.

When creating the new schema, you must set the user created above (in this case, the keep_app user) as its owner.

Create the new schema by entering the following commands within the PostgreSQL terminal:

CREATE SCHEMA keep AUTHORIZATION keep_app; 

ALTER ROLE keep_app IN DATABASE <database_used_by_keep> 
  SET search_path = keep, pg_catalog;

This links app queries to the newly created schema (in this case, the keep schema).

Restricting Permissions for the `public` Schema

Every new PostgreSQL database contains a default schema called public. This is the schema that PostgreSQL uses by default for your objects, unless you specify otherwise.

Depending on your PostgreSQL version and whether the database has been upgraded from an older version, users may have CREATE privilege on the public schema.

If this is a concern, you can revoke it by entering the following command within the PostgreSQL terminal:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Authentication and Security Hardening

To protect both authentication credentials and database traffic while in transit, we strongly recommend enabling Secure Sockets Layer/Transport Layer Security (SSL/TLS) for PostgreSQL connections.

Keep currently connects to PostgreSQL using username and password authentication.

By default, Keep requests an encrypted TLS connection when the PostgreSQL server supports it. However, the default TLS configuration is not set to validate certificates or identity.

If TLS is unavailable in the PostgreSQL instance, Keep will downgrade to an unencrypted connection.

For maximum security, we recommend configuring the Keep connection to PostgreSQL to require TLS with full verification. This ensures that credentials and data are always encrypted during transmission and helps prevent connections to untrusted servers.

For full verification, Root CA certificates must be trusted.

To configure Keep to enforce TLS, follow these instructions:

Open the node.properties Keep configuration file.

Note
The node.properties configuration file is located in the standalone\configuration\ area of the Keep installation directory.
For example: C:\Program Files\Lasernet\Lasernet Keep\Server_<Keep version number> - [<node name>]\wildfly-<Wildfly version number>.Final\standalone\configuration\.
Appropriately substitute <Keep version number>, <node name>, and <Wildfly version number>.

Edit the sslmode parameter as needed, choosing among the available modes; its default value is prefer. To enable enforced SSL, we recommend the require, verify-ca, or verify-full modes.
Ensure the Root CA (Certificate Authority) is present in the Java Development Kit (JDK) truststore, located in the Keep installation directory. If it is not already present, import it:
1. Export the PostgreSQL server certificate to a file (X.509) and copy it to a local folder on the Keep server.
2. On the Keep server, open a command prompt as an administrator.
3. Navigate to the bin folder of the JDK in the Lasernet Keep installation folder:
  Lasernet Keep 11.1 and Later
  C:\Program Files\Lasernet\Lasernet Keep\Server_11.<minor version> - <node name>\jdk-<JDK version number>\bin.
  Appropriately substitute <minor version> , <node name> and <JDK version number> .
  Lasernet Keep 11.0
  C:\Program Files\Formpipe Software\Lasernet Keep\Server_11.0 - <node name>\jdk-<JDK version number>\bin.
  Appropriately substitute <node name> and <JDK version number> .
4. Run the following command.
  1. - Substitute <path to certificate> for the path to the PostgreSQL server certificate that you copied to the Keep server.
    - Substitute <domain name> for the domain name. This part of the command provides the alias to apply to the new key in the keystore. In this procedure, the domain name is used as the alias by convention.
```
keytool.exe -importcert -file <path to certificate> -alias <domain name> -keystore ..\lib\security\cacerts
```
5. Enter the Java cacerts password when prompted; the default password is changeit.
Restart the Keep Windows service.

Troubleshooting

For information on troubleshooting and common PostgreSQL errors, consult the dedicated article.