PostgreSQL

Adding and configuring a PostgreSQL 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 how to add PostgreSQL as a source datastore in Qualytics. It covers the entire process, from initial connection setup to testing and finalizing the configuration. A PostgreSQL datastore can also be the place Qualytics writes to; see Use as an Enrichment Datastore.

By following these instructions, enterprises can ensure their PostgreSQL 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 🚀

PostgreSQL Setup Guide

Qualytics connects to PostgreSQL through the PostgreSQL JDBC driver. It reads the standard information schema to list the schemas the role can see, and uses standard JDBC metadata APIs for tables, columns, primary keys, and incremental fields.

Minimum PostgreSQL Permissions (Source Datastore)

Additional Permissions for an Enrichment Datastore

When PostgreSQL is used as an enrichment datastore, the following additional permissions are required so Qualytics can write its outputs, which are named from the linked source's prefix (for example _tpch_failed_checks):

The database user that creates the enrichment tables owns them, so no additional grant is needed to change their structure during version migrations or to remove them during cleanup. PostgreSQL derives both from table ownership.

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
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;

Example: Enrichment Database User (Read-Write)

-- Create a dedicated read-write role
CREATE ROLE qualytics_readwrite_role LOGIN PASSWORD '<password>';

-- Grant connection, schema access, and table creation
GRANT CONNECT ON DATABASE <database_name> TO qualytics_readwrite_role;
GRANT USAGE, CREATE ON SCHEMA <schema_name> TO qualytics_readwrite_role;

-- Grant full data manipulation on all existing and future tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <schema_name> TO qualytics_readwrite_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO qualytics_readwrite_role;

Incremental Loading with Commit Timestamps

For optimal incremental load performance, it is recommended to enable track_commit_timestamp = on in your PostgreSQL configuration (postgresql.conf). This allows Qualytics to detect recently modified rows using transaction commit timestamps (pg_xact_commit_timestamp), reducing the amount of data read during incremental operations.

Fallback to a Full Load

If the source database does not track commit timestamps (track_commit_timestamp is off), an incremental load automatically falls back to a full load instead of failing. The operation completes normally, reads the full table, and reports a warning explaining that commit timestamp tracking is disabled on the source database. Because the fallback reads the full table, the operation does not record an incremental position for that load.

If Qualytics cannot verify the setting on the source database, the configured incremental behavior is kept unchanged.

To restore incremental loading:

1. Set track_commit_timestamp = on in postgresql.conf and restart the PostgreSQL server. The setting only takes effect after a restart.
2. Run a Sync operation on the datastore.

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.
• 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 PostgreSQL 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 PostgreSQL as a source lets Qualytics query it through the PostgreSQL JDBC driver and run quality operations on the tables it discovers.

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

Field reference

The Add Datastore page shows the sections below when PostgreSQL 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 Location and the General fields. 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 PostgreSQL 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.

Location

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

General

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

Below the form, an information banner lists the Public addresses and Private addresses your datastore connections originate from. Allow the ones that match your network setup through your security groups or firewall rules; see How Connections Work for where the addresses come from.

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.

Use as an Enrichment Datastore

An enrichment datastore is where Qualytics writes its findings: scan results, source record examples, remediation snapshots, materialized copies, and exports. Keep it on a dedicated schema or storage path, separate from the source data you govern. When a source datastore is linked to it, that source's writes land there. The destination can share the source's connection when those credentials have the required write access.

To use a PostgreSQL datastore as a destination, its connection user needs the write grants listed under Additional Permissions for an Enrichment Datastore. Then link it from the datastore that will write to it: open its Settings menu, click Enrichment , and either pick the PostgreSQL datastore under Use existing or create it there under Add new. The same choice is offered in the second step of the Add Datastore page. The flow is the same for every connector and is documented once, in Link an Enrichment Destination and Link Enrichment on Datastore Creation.

The rest of this section covers only what is specific to PostgreSQL: the fields you fill in when creating the destination on PostgreSQL from inside that dialog. For what Qualytics writes and how the outputs are named, see Enrichment Outputs.

Field reference

The Enrichment Destination step shows the sections below when PostgreSQL is selected. When reusing an existing connection, the Connection Properties and Secrets Management sections come already filled in and read-only.

Connection Properties

These fields define the PostgreSQL server Qualytics connects to and the role it connects with. They are the same fields as on the source datastore flow, repeated here so this section stands on its own.

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.

Location

Where Qualytics writes the enrichment tables.

General

Write Behavior

Whether a Scan also writes a snapshot of the anomalous records, and whether Qualytics syncs and profiles the destination after each write.

Steps

Fill in these fields under Add new, either in the Enrichment Destination dialog of the datastore that will write here or in the second step of the Add Datastore page, then click Test connection and save. The steps are the same for every connector: see Link an Enrichment Destination or Link Enrichment on Datastore Creation.

API Payload Examples

This section provides detailed examples of API payloads to guide you through the process of creating and managing datastores using Qualytics API. Each example includes endpoint details, sample payloads, and instructions on how to replace placeholder values with actual data relevant to your setup.

Creating a Source Datastore

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

Endpoint: /api/datastores (post)

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "database": "postgresql_database",
    "schema": "postgresql_schema",
    "enrichment_only": false,
    "trigger_sync": true,
    "connection": {
        "name": "your_connection_name",
        "type": "postgresql",
        "host": "postgresql_host",
        "port": 5432,
        "username": "postgresql_username",
        "password": "postgresql_password"
    }
}

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

# Step 1: Create a Connection
qualytics connections create \
    --type postgresql \
    --name "your_connection_name" \
    --host ${DB_HOST} \
    --port 5432 \
    --username ${DB_USER} \
    --password ${DB_PASSWORD}

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

Create an Enrichment Datastore

This section provides sample payloads for creating an enrichment datastore. Replace the placeholder values with actual data relevant to your setup.

Endpoint: /api/datastores (post)

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "database": "postgresql_database",
    "schema": "postgresql_schema",
    "enrichment_only": true,
    "connection": {
        "name": "your_connection_name",
        "type": "postgresql",
        "host": "postgresql_host",
        "port": 5432,
        "username": "postgresql_username",
        "password": "postgresql_password"
    }
}

{
    "name": "your_datastore_name",
    "teams": ["Public"],
    "database": "postgresql_database",
    "schema": "postgresql_schema",
    "enrichment_only": true,
    "connection_id": 123
}

# Step 1: Create a Connection
qualytics connections create \
    --type postgresql \
    --name "your_connection_name" \
    --host ${DB_HOST} \
    --port 5432 \
    --username ${DB_USER} \
    --password ${DB_PASSWORD}

# Step 2: Create an Enrichment Datastore
qualytics datastores create \
    --name "your_datastore_name" \
    --connection-name "your_connection_name" \
    --database your_database \
    --schema your_enrichment_schema \
    --enrichment-only

Link an Enrichment Destination

Use the provided endpoint to link a datastore to its enrichment destination:

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