Connecting TimescaleDB/PostgreSQL from Node-RED

Encountering self-signed certificate errors or blocked connections while querying TimescaleDB from Node-RED? These obstacles often arise when navigating SSL settings and PostgreSQL configurations. Certificates simplify secure connections. This guide will walk you through connecting to TimescaleDB via Node-RED with tailored adjustments for SSL or non-SSL setups.

We’ll cover:

1. Configuring Node-RED PostgreSQL with the simplest connection settings.
2. Resolving SSL issues, including self-signed certificates and bypass methods.
3. Adjusting PostgreSQL’s pg_hba.conf for both SSL-enabled and SSL-off scenarios.

A. Basic Node-RED PostgreSQL Setup

A1. Installing the PostgreSQL Node

1. Open Node-RED and navigate to the Manage Palette.
2. Search for node-red-contrib-postgresql
3. Install the package and drag a postgresql node to your flow.

A2. Setting Up the Connection

• Host: Use united-manufacturing-hub (simplest and valid within the same namespace).
• Port: Default PostgreSQL port is 5432.
• Database: Specify your database, e.g., umh_v2.
• User & Password: Use the credentials you set for PostgreSQL access, e.g., grafanareader and changeme

A3. Common Error Scenario

If SSL is enabled (default in TimescaleDB Helm charts) and Node-RED does not have the required certificate, you’ll encounter Error: self signed certificate. If SSL is disabled, and the pg_hba.conf file does not permit non-SSL connections, the error will be FATAL: pg_hba.conf rejects connection... SSL off.

B. Resolving SSL Issues in Node-RED

B1. Fixing the Self-Signed Certificate Error

When using united-manufacturing-hub with SSL enabled, Node-RED throws the self-signed certificate error. Two options can resolve this:

1. Import the Self-Signed Certificate into Node-RED: Export the server’s certificate (tls.crt) and configure Node-RED to trust it.
2. Turn SSL Off: This requires adjusting the PostgreSQL pg_hba.conf to permit non-SSL connections (covered in Chapter C).

B2. Recommended Workaround

Instead of modifying certificates, you can bypass SSL verification directly in Node-RED. Add a Function node before the PostgreSQL node with the following content:

msg.pgConfig = {
    user: 'kafkatopostgresqlv2',
    password: 'changemetoo',
    host: 'united-manufacturing-hub',
    database: 'umh_v2',
    port: 5432,
    ssl: {
        rejectUnauthorized: false // SSL is on, but certificate verification is bypassed
    }
};

return msg;

This approach maintains SSL encryption while disabling strict certificate verification.

C. Adjusting pg_hba.conf for Non-SSL Connections

C1. Locating pg_hba.conf

If you prefer to disable SSL entirely (e.g., in closed, air-gapped environments), you need to adjust the pg_hba.conf file. In TimescaleDB’s Helm chart, this configuration is found under timescaledb-single.patroni.postgresql.pg_hba in the values.yaml.

C2. Updating pg_hba.conf

Add a line to permit non-SSL connections for your specific user and database:

pg_hba:
  - local     all postgres                   peer
  - hostnossl umh_v2 kafkatopostgresqlv2 0.0.0.0/0 md5
  - hostssl   all all              0.0.0.0/0 md5
  - hostnossl all all              0.0.0.0/0 reject

Ensure this hostnossl line is above any “reject” entries to allow non-SSL access. After updating, restart the TimescaleDB pod to apply the changes.

Summary

Connecting Node-RED to TimescaleDB can be simplified with a few adjustments:

1. Use united-manufacturing-hub as the host when in the same namespace.
2. To avoid SSL issues, either import the self-signed certificate into Node-RED or bypass verification with a Function node.
3. For non-SSL connections in closed environments, adjust the pg_hba.conf in the Helm chart to allow non-SSL access.

Certificates simplify secure connections. Choose the setup that best matches your environment—whether bypassing SSL verification, importing certificates, or securely disabling SSL altogether.

Now apply these adjustments to ensure seamless queries in your Node-RED flows.