TimescaleDB

Adding and configuring a TimescaleDB connection within Qualytics empowers the platform to build a symbolic link with your schema to perform operations like data discovery, visualization, reporting, syncing, profiling, scanning, anomaly surveillance, and more.

This documentation provides a step-by-step guide on adding TimescaleDB as a source datastore in Qualytics. It covers the entire process, from initial connection setup to testing and finalizing the configuration.

By following these instructions, enterprises can ensure their TimescaleDB environment is properly connected with Qualytics, unlocking the platform’s potential to help you proactively manage your full data quality lifecycle.

Let’s get started 🚀

TimescaleDB Setup Guide

Qualytics connects to TimescaleDB through the PostgreSQL JDBC driver. TimescaleDB is a PostgreSQL extension, so the permission model follows the same PostgreSQL conventions. Qualytics uses the standard JDBC metadata APIs both to list the schemas the role can see and to discover tables, including hypertables, along with their columns and primary keys. Qualytics automatically filters out TimescaleDB internal schemas (timescaledb_information, timescaledb_experimental) during schema discovery.

Minimum TimescaleDB Permissions (Source Datastore)

Example: Source Datastore Role (Read-Only)

Replace <database_name>, <schema_name>, and <password> with your actual values.

-- Create a dedicated read-only role
CREATE ROLE qualytics_read_role LOGIN PASSWORD ‘<password>’;

-- Grant connection and schema access
GRANT CONNECT ON DATABASE <database_name> TO qualytics_read_role;
GRANT USAGE ON SCHEMA <schema_name> TO qualytics_read_role;

-- Grant read access to all existing and future tables (including hypertables)
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO qualytics_read_role;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA <schema_name> TO qualytics_read_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO qualytics_read_role;

Troubleshooting Common Errors

Detailed Troubleshooting Notes

Authentication Errors

The error FATAL: password authentication failed indicates that the credentials provided are incorrect or the role does not exist.

Common causes:

• Incorrect password: the password does not match the one set for the role.
• Role does not exist: the role name was misspelled or was never created.
• Authentication method mismatch: the pg_hba.conf file requires a different authentication method (e.g., md5 vs scram-sha-256).

Permission Errors

The error permission denied for schema or permission denied for table means the role authenticated successfully but lacks the necessary grants.

Common causes:

• Missing USAGE on schema: the role cannot access the schema even if table-level grants exist.
• Missing SELECT on tables: the role has schema access but cannot read specific tables or hypertables.
• Default privileges not set: new tables created after the initial grant are not automatically accessible. Use ALTER DEFAULT PRIVILEGES to fix this.

Connection Errors

The error FATAL: no pg_hba.conf entry for host means the TimescaleDB server does not recognize the Qualytics host IP.

Common causes:

• IP not whitelisted: the Qualytics server IP is not listed in pg_hba.conf.
• Wrong database name: the pg_hba.conf entry restricts access to specific databases.
• SSL required: the server requires SSL connections but the client is connecting without SSL.

Add a Source Datastore

A source datastore is a storage location Qualytics connects to so it can profile, scan, and monitor data. Adding TimescaleDB as a source lets Qualytics query it over JDBC with the PostgreSQL driver and run quality operations on the tables it discovers.

Before you start, review the Minimum TimescaleDB Permissions the connecting role needs.

Field reference

The Add Datastore page shows the sections below when TimescaleDB is selected. When reusing an existing connection, the Connection Properties and Secrets Management sections come already filled in and read-only: Qualytics has already validated those credentials, so you fill in only the Datastores Extraction and the Datastore Properties. To change a saved connection's credentials, edit the connection through the Manage Connections page; edits there apply to every datastore that reuses the connection.

Connection Properties

These fields define the TimescaleDB server Qualytics connects to and the role it connects with. They belong to the connection: when reusing an existing connection, they come already filled in and read-only.

Secrets Management

This group is optional: use it only if you want Qualytics to pull credentials from a secrets manager instead of typing them into the form. Turn on HashiCorp Vault to show the fields below. Despite the label, any secrets manager that exposes a compatible REST API works, not only HashiCorp Vault; see Secrets Management. It also belongs to the connection: read-only when reusing an existing connection.

Datastores Extraction

Pick the database and the schema or schemas Qualytics should read from. You fill these in on both flows.

Datastore Properties

Common fields for every source datastore, shown below the Datastores Extraction section. You fill these in on both flows.

Steps

There are two ways to set up the connection: reuse a connection you already saved (Existing Connection) or create a new one from scratch (New Connection). The tabs below walk through each option; pick the one you want to follow. Each field is described in the Field reference above.

API Payload Examples

Creating a Source Datastore

This section provides a sample payload for creating a TimescaleDB datastore. Replace the placeholder values with actual data relevant to your setup.

Endpoint (Post): /api/datastores (post)

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "database": "timescale_database",
    "schema": "timescale_schema",
    "enrichment_only": false,
    "trigger_sync": true,
    "connection": {
        "name": "your_connection_name",
        "type": "timescale",
        "host": "timescale_host",
        "port": 5432,
        "username": "timescale_username",
        "password": "timescale_password"
    }
}

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "database": "timescale_database",
    "schema": "timescale_schema",
    "enrichment_only": false,
    "trigger_sync": true,
    "connection_id": 123
}

# Step 1: Create a Connection
qualytics connections create \
    --type timescale \
    --name "your_connection_name" \
    --host ${TIMESCALE_HOST} \
    --port 5432 \
    --username ${TIMESCALE_USER} \
    --password ${TIMESCALE_PASSWORD}

# Step 2: Create a Source Datastore
qualytics datastores create \
    --name "your_datastore_name" \
    --connection-name "your_connection_name" \
    --database your_database \
    --schema public

Link an Enrichment Datastore to a Source Datastore

Endpoint Details: /api/datastores/{datastore-id}/enrichment/{enrichment-id} (patch)